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

Looking at Buffer Cache Performance

To help ensure excellent performance, the buffer cache hit ratio should be maintained in the neighborhood of 90% or higher. However, one should be aware that every server has its own personality and might exhibit excellent performance with below average readings for the cache hit ratio. One should also be aware that excessive logical I/O activity can produce a very high cache hit ratio while actually degrading overall database performance, so a high buffer cache hit ratio is not the silver bullet for overall high performance in SQL Server.

If the DBA is seeing low readings for the buffer cache hit ratio, the Page Life Expectancy statistic should be checked. This statistic indicates the length of time SQL Server estimates a page will remain in the buffer cache. Obviously, pages served from memory result in much shorter response times than pages that must be read from disk and then into the cache. So, it is wise for often used data to be pinned in the buffer cache. The page_life query easily provides the DBA with this measure:


* page_life.sql

-- Copyright 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.

select
cntr_value
from
master..sysperfinfo
where
object_name = 'SQLServer:Buffer Manager' and
counter_name = 'Page life expectancy'


Page life expectancy readings of 300 seconds or less often indicate too many table scans are occurring which is an activity that can flood the buffer cache with pages used only once or seldom, at best. Checking the I/O access patterns with scripts provided in the prior section can confirm excessive scan activity.

SQL Server tends to be either data-centric or code-centric. Most servers are data-centric, meaning memory is dominated by requested data pages. This can be verified with the data_centric query


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