| |
 |
|
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
http://www.rampant-books.com/book_2005_2_sql_server_dba.htm |
