SQL Server Tips by Robin Schumacher

Ratio Analysis

Ratios have gotten a bad reputation over the past few years with many database experts, especially those in the SQL Server community, stating that most ratios are worthless in the evaluation of the overall efficiency of a database server. Without question, there is some merit to these claims as global, cumulative ratios can give the DBA a false sense of security and convey a rosy picture, when in reality, performance has taken a turn for the worse.

However, one should avoid throwing the good out with the bad when considering the use of ratios in performance analysis. There are still plenty of valid ratios in the SQL Server that certainly deserve attention. For example, if a particular table had 1,000 forwarded rows in it, would the DBA be able to tell whether the table needed to be reorganized? Probably not. But if the DBA was told that 95% of the rows in that same table were forwarded in nature, all the information needed to take action would be available. That is why ratios are still valuable to the DBA.

Microsoft has tweaked some of the ratio-based performance counters over the years to make them more valuable to SQL Server professionals. For example, the cache hit ratio value was somewhat useless to DBAs once a server had been up and running for several hours, because the metric is cumulative in nature. If a large flood of physical I/O activity swept in, it would not make a dent in the overall statistic because there was too much other I/O involved in the calculation that prohibited its intervention. Now, however, the SQL Server service packs have included changes that cause the cache hit ratio metric to take into account only the last few thousand page I/Os, which makes the statistic much more meaningful.

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