 |
|
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 |
