| |
 |
|
SQL Server Tips by Robin Schumacher
|
Examining the Plan Cache
Attention can now focus on the procedure plan hit ratio. A high
procedure cache hit rate is a desirable thing; the DBA should strive
for a hit ratio between 95% - 100%, with 95% being a good
performance benchmark for code reference.
When a database is first started, the procedure cache hit rate will
not be at an optimal level because all code being used will be
relatively new, must be read in from disk, and placed into the
cache. If, however, after a solid hour or two of steady database
time, the procedure cache hit rate has not increased to desirable
levels, the DBA should look into increasing the amount of memory
given to SQL Server.
Plan reuse can backfire since input parameters are capable of
causing drastic changes in returned result sets. One set of
bind/input parameters could produce an end result set that works
well with a particular access path, but that same path may produce
terrible response times for a different set of bind parameters.
Peering into the cache is not difficult. A simple query from the
master.dbo.syscacheobjects table using the plan_objects.sql code is
all that is needed.
The output from the plan object cache query will provide not only
the object and query names/text, but it will also reveal information
such as how many times the code object or object structure was
referenced, how much memory it is taking up, and etc.
Ad-hoc SQL requests will also be seen in the cache as well. The
ad-hoc SQL hit ratio will tell how often general query plans are
being reused. A high ad hoc hit rate is desirable, but is harder to
maintain at a high level than something like a procedure cache hit
rate. Therefore, an 80% or greater ad hoc cache hit rate is a good
performance benchmark for code reference.
After first starting a database, it may take an hour or two of
steady database time before the ad hoc hit rate will approach
optimal levels. This occurs because all code being used is new, must
be read in from disk, and then placed into the cache. However, if
after this period of time the ad hoc cache hit rate has not reached
a desirable level, the DBA should consider increasing the amout of
memory given to the SQL Server.
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 |
