SQL Server Tips by Robin Schumacher

Correlating Statistics and Calculating Rates

Correlating various measures to understand true performance trends take practice. Plotting singular measures helps answer basic questions such as, which databases are growing in size or the time of day SQL Server experiences peak user load; however, the performance of statistical cross-referencing is where things get really interesting.

For instance, the DBA may wonder if SQL Server is performing properly during periods of peak I/O. This becomes easy to understand when physical I/O (page reads, writes, etc.) are plotted with the iostall metric or with metrics in SQL Server 2005 derived from the fn_virtualfilestats function call, which provides a number of excellent I/O statistics. Indications of heavy I/O activity with little rise in latency indicates things may be just fine on the SQL Server.

Calculating Rates

Calculating growth rates is important for the next area of capacity planning; forecasting. For example, the DBA may have a database currently adding 50MB of new space each day. This is nice to know, but it is perhaps more valuable to understand that the database is growing at one percent per day or 30+% a month.

Rate calculations can sometimes be difficult to accomplish on statistics that appear to spike and then crash. For instance, tempdb usage may rise and fall during the month, but over time it may be steadily going up. The rate calculations must be able to smooth out the extremes so a reasonable rate of growth or decline can be calculated.

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