| |
 |
|
SQL Server Tips by Burleson |
Ratio Analysis
Ratios have gotten a bad reputation over the past few years with
many database experts, especially those in the Oracle 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 |
