Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

   
  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  

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks
 

Hit Counter