Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Jul 14 '11 at 9:14

This question came from our site for professional and enthusiast programmers.

add comment

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

share|improve this answer
    
Thanks Mitch, I have made some investigation, and find there is only Buffer Cache hit ratio in perfmon, there is no "Procedure cache" as you mentioned, appreciate if you could clarify. :-) Here is my screen snapshot. i27.tinypic.com/2pr9nv8.jpg –  George2 Aug 8 '09 at 10:00
    
Another question is, for my issue, which to find the cache hit ratio for queries, I think I just need to find Buffer cache hit ratio, why I still need "Procedure Cache"? –  George2 Aug 8 '09 at 10:00
1  
@George2 - "SQL Server - Procedure Cache " you couls also try running "DBCC PROCCACHE" in SSMS –  Mitch Wheat Aug 8 '09 at 10:15
    
Yes, I could get information of procedure cache from the command you mentioned. But for my issue, the query cache hit rario, why I need to care about procedure cache? :-) –  George2 Aug 8 '09 at 10:17
1  
@George2: becuase you only have finite amount of RAM, and it is shared between the cache types –  Mitch Wheat Aug 8 '09 at 10:25
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.