como ver a taxa de acertos do cache do SQL Server?

Question

Estou usando o SQL Server 2008 Enterprise. Vou emitir muita consulta para o SQL Server. E eu acho que o próprio SQL Server usará alguma otimização internamente, como a pré-busca de dados no cache de dados interno do SQL Server ou o acesso freqüente de dados solicitados do cache antes do carregamento do arquivo de página física para melhorar o desempenho.

Alguma solução para ver a taxa de acertos do cache interno do SQL Server? Ou alguma prática recomendada para ajustar o cache para melhorar o desempenho das consultas?

obrigado antecipadamente, George

    

Answer 1

Dê uma olhada no BOL: SQL Server, objeto Gerenciador de Buffer .

As duas áreas em que você deve olhar primeiro são:

• Cache de procedimentos é a área da memória na qual o SQL armazena seus planos de consulta.

• Cache de buffer é a área da memória em que as páginas de dados são armazenadas.

Contadores de perfm relevantes:

• Taxa de acertos do cache de buffer
• Expectativa de vida da página
• A página lê / seg

Os principais problemas de desempenho do SQL Server 2005 para aplicativos OLTP contêm o seguinte:

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.