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

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.
--
-- To license this script for a commercial purpose,
-- contact rtp AT rampant.cc
-- *************************************************

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  

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