how to see SQL Server cache hit rate?

Question

Hello everyone,

I am using SQL Server 2008 Enterprise. I will issue a lot of query to SQL Server. And I think SQL Server itself will use some optimization internally, like pre-fetching data into SQL Server internal data cache or accessing frequent requesting data from cache before load from physical page file to improve performance.

Any solutions to see SQL Server internal cache hit rate? Or any best practices to tune cache to make query performance better?

thanks in advance, George

Answer 1

Have a look at BOL: SQL Server, Buffer Manager Object.

The two areas that you should look at first are:

• Procedure cache is the area of memory where SQL stores your query plans.

• Buffer cache is the area of memory where data pages are stored.

Relevant perfmon counters:

• Buffer cache hit ratio
• Page life expectancy
• Page reads/sec

Top SQL Server 2005 Performance Issues for OLTP Applications contains the following:

CPU bottleneck if…

• Signal waits > 25% of total waits. See sys.dm_os_wait_stats for Signal waits and Total waits. Signal waits measure the time spent in the runnable queue waiting for CPU. High signal waits indicate a CPU bottleneck.

• Plan re-use < 90% . A query plan is used to execute a query. Plan re-use is desirable for OLTP workloads because re-creating the same plan (for similar or identical transactions) is a waste of CPU resources. Compare SQL Server SQL Statistics: batch requests/sec to SQL compilations/sec. Compute plan re-use as follows: Plan re-use = (Batch requests - SQL compilations) / Batch requests. Special exception to the plan re-use rule: Zero cost plans will not be cached (not re-used) in SQL 2005 SP2. Applications that use zero cost plans will have a lower plan re-use but this is not a performance issue.

• Parallel wait type cxpacket > 10% of total waits. Parallelism sacrifices CPU resources for speed of execution. Given the high volumes of OLTP, parallel queries usually reduce OLTP throughput and should be avoided. See sys.dm_os_wait_stats for wait statistics.

Memory bottleneck if…

• Consistently low average page life expectancy. See Average Page Life Expectancy Counter which is in the Perfmon object SQL Server Buffer Manager (this represents is the average number of seconds a page stays in cache). For OLTP, an average page life expectancy of 300 is 5 minutes. Anything less could indicate memory pressure, missing indexes, or a cache flush.

• Sudden big drop in page life expectancy. OLTP applications (e.g. small transactions) should have a steady (or slowly increasing) page life expectancy. See Perfmon object SQL Server Buffer Manager.

• Pending memory grants. See counter Memory Grants Pending, in the Perfmon object SQL Server Memory Manager. Small OLTP transactions should not require a large memory grant.

• Sudden drops or consistenty low SQL Cache hit ratio. OLTP applications (e.g. small transactions) should have a high cache hit ratio. Since OLTP transactions are small, there should not be (1) big drops in SQL Cache hit rates or (2) consistently low cache hit rates < 90%. Drops or low cache hit may indicate memory pressure or missing indexes.