| |
 |
|
SQL Server Tips by Burleson |
Accurate Ratio Analysis
Techniques
How does one accurately perform ratio-based analysis? While there
are certainly many opinions as to which rules to follow, some
standards should always be adhered. To begin with, many formulas
that make up ratio-based analysis must be derived from delta
measurements instead of cumulative statistics, and this can be tough
in SQL Server.
Many of the global ratios that a DBA will examine come from the
master.dbo.sysperfinfo table. This table maintains a count of all
occurrences in the cntr_value column of a particular database
statistic since the server was brought up. For database servers kept
up for long periods of time, these values can grow quite large and
will impact how a particular ratio is interpreted.
For example, the buffer cache hit ratio was deemed fairly useless by
Microsoft if a server had been up for a long time period. If SQL
Server had been running for many weeks, the numbers representing the
I/O statistics would likely be enormous. The counts of logical reads
(memory reads) will be very large, and in most systems exceed the
count of physical reads by a wide margin. Such a situation can, if
it is computed solely with cumulative value counts, skew the buffer
cache hit ratio.
If an inefficient query is issued causing many physical reads,
adding them to the counters would probably not trigger a meaningful
dip in the overall cache hit ratio, as long as cumulative statistics
are used. However, if delta statistics are used, the portrayal of
the cache hit ratio would be more current and accurate. Indeed, more
recent versions of SQL Server now take into account only the last
few thousand page reads when factoring the statistics that make up
the ratio. Therefore, the measure has become more valuable.
Some ratios do not rely on sysperinfo, and as a result can be
derived from current/cumulative values. One example of this is the
blocking lock ratio. This lock ratio computes the percentage of user
sessions currently blocked on a system. Because locks in a database
are repeatedly obtained and released, the ratio can be computed with
cumulative numbers from one performance view without the need for
taking a before-and-after statistical snapshot.
In addition to using delta statistics to compute many of the key
metrics in ratio-based performance analysis, DBAs must also be
committed to examining all database categories that contribute to
overall health and well-being. This can mean employing ratios and
analytic percentages that have historically been neglected by DBAs.
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 |
