Call now: (800) 766-1884  


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA

 SQL Server Scripts
 Scripts Menu




  SQL Server Tips by Robin Schumacher

Setting up Trend Analysis

Once meaningful data is being acquired on a regular basis, the DBA can then begin analyzing the data: looking back in time as well as projecting forward.

In terms of analyzing historical data, it becomes easy to answer many of the questions posed at the beginning of this chapter such as “How fast is the database growing?” If the DBA decides to go granular on the collections, a drill-down analysis can be performed to discover answers to questions such as, “What objects are responsible for the spike in growth?”

In terms of data presentation, if a third-party tool for capacity planning is not being used, a number of good 4GL tools on the market or even Microsoft Excel can be utilized to perform much of the computations and comparisons. But, what type of ability should the DBA build into an individual capacity planning system or what features should a third party tool have?

There are four basic needs to account for. They are as follows:

  • Timeframes

  • Statistical Aggregation

  • Metric Rollups

  • Drill Down

For trend analysis, the DBA will always begin with a time period of interest for the review. It might be a week, a month or a couple of hours. The trend analysis system must be able to take in Start/Stop time periods. Another good option is the ability to feed in simple requests such as, “Calculate the last six month’s worth of data from the current date.”

Next, the DBA will need to be able to view data in various forms of aggregation. For example, one might want to see: the average number of reads over a certain time period; the maximum amount of space ever used in one of the tempdb databases for the month of January; and so on.

The ability to perform rollups of various statistics is also necessary. For example, Page Reads may be collected every hour on one SQL Server, but it may be necessary to view the data by day over a particular week. Rollup capabilities allow this task to be accomplished.

Finally, the ability to drill down helps the DBA get to the bottom of specific trends. For example, a particular database may appear to be growing at a much faster rate than all the others, but the reason is unknown. If the ability to zoom in and drill down to objects in that database exists, the tables responsible for the overall growth can be discovered.

The above book excerpt is from:

High-Performance SQL Server DBA
Tuning & Optimization Secrets

ISBN: 0-9761573-6-5
Robin Schumacher  


Burleson Consulting Remote DB Administration







Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.

Burleson Consulting
SQL Server database support


Copyright © 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter