Database Reference
In-Depth Information
10
. The two metrics we have specific recommendations for are the Buffer Cache Hit
Ratio and the Cache Hit Ratio, due to their importance on overall database
performance.
Table 4.3
SQL Server Perfmon Counters
The Buffer Cache is your SQL Server data cache. Before any data can be manipulated
by the database, it must first reside in the buffer cache, even if it is just to be read by a
simple SQL statement. This metric tells you the percentage of time a page containing the
data the database needed is already sitting inside the cache. Our experience has taught
us a healthy database maintains greater than a 97% hit ratio on average. When a
database is first started, you would expect the ratio to be low due to the cache being
cold, but over the normal course of a business cycle, this ratio should attain an average
greater than 97%.
The Cache Hit Ratio is the database's program cache. This Cache Hit Ratio tells us how
often a compiled procedure is sitting in the cache, ready for the database to execute,
versus how often the procedure needs to be recompiled first. Our experience has taught
us that over a business cycle, a healthy database should see a hit ration on this cache of
greater than 70%. The busier the database, the higher this hit ratio should be.
A lot of great information can be harnessed from within the database concerning how it
is performing and how much it needs in terms of resources. The more you know about
how many resources the database needs to consume to perform its job and make sure the
virtualized infrastructure can provide those resources in the amount the database needs
them, when it needs them, the better your virtualized database will perform. Experience
has taught us that when an effort to virtualize a database fails, it has nothing to do with
the capabilities of vSphere; instead, it has to do with how the database was being
implemented on the virtualized infrastructure.
Bird's-Eye View: Virtualization Implementation
