Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

   
  SQL Server Tips by Robin Schumacher

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  

 

Burleson Consulting Remote DB Administration


 

 


 

 

 

 

 
Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.
 


Burleson Consulting
SQL Server database support

 

Copyright 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter