Forma rápida de verificar queries que estão pesando muito no servidor - Parte I

Oi pessoal! Aí vai um dica muito boa tirada do SQLServerPedia e que já usei algumas vezes para detectar problemas de performance no servidor. Quando tem uma série de consultas onerando o servidor, uma das primeiras coisas que o DBA pensa é em ver os processos em execução e, caso não encontre um padrão, usar o Profiler. O Profiler, para quem não conhece, é uma ferramenta que consegue listar atividades ocorridas no SQL Server, então é possível utilizá-la para verificar o consumo de CPU e I/O de consultas de maneira comparativa.

Existem situações em que usar o Profiler não é uma escolha: a execução do Profiler onera o servidor e pode piorar ainda mais a situação de um servidor sobrecarregado ou, ainda pior, o usuário pode ter só um Management Studio Express e não ter o Profiler instalado para fazer essa verificação. Nestes casos é possível, para SQL Server 2005 e 2008, apelar para as dynamic management views, as famosas DMVs. As DMVs fornecem informações extremamente valiosas para o gerenciamento de um servidor, e entre elas o procedure cache, que é um cache que armazena planos de execução corriqueiramente utilizados. Então, vamos à consulta:

select total_worker_time/execution_count as MediaCPU

, total_worker_time AS TotalCPU

, total_elapsed_time/execution_count as MediaDuration

, total_elapsed_time AS TotalDuration

, total_logical_reads/execution_count as MediaLogicalReads

, total_logical_reads AS TotalLogicalReads

, total_physical_reads/execution_count as MediaPhysicalReads

, total_physical_reads AS TotalPhysicalReads

, execution_count 

, substring(st.text, (qs.statement_start_offset/2)+1

, ((case qs.statement_end_offset  when -1 then datalength(st.text)

else qs.statement_end_offset

end - qs.statement_start_offset)/2) + 1) as txt

, query_plan

from sys.dm_exec_query_stats as qs

cross apply sys.dm_exec_sql_text(qs.sql_handle) as st

cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp

order by 1 desc

Se você quer entender os resultados da query acima, basicamente são apresentados nos oito primeiros campos contadores de média e total para uso de CPU, duração da execução, logical reads (leituras de cache) e physical reads, que é leitura direta de valores no disco. Como os indicadores do SQL são inexatos é melhor tomá-los como medidas relativas. O "order by 1 desc" trará as consultas que tiveram mais peso em processamento, alternando para "order by 5 desc" se teria os mais pesados em logical reads e por aí vai. O execution_count apresenta o número de execuções, txt o corpo da consulta e query_plan acaba por apresentar o plano de execução.

Nas primeiras consultas, utilize um "top" para restringir o número de registros retornados, principalmente se o cache comportar muitos registros. Com essa query já se resolvem muitos problemas. Amanhã vou dissecar a sys.dm_exec_query_stats para que, caso vocês queiram, vocês possam aplicar em outros cenários de monitoramento.

Novamente fico à disposição para quaisquer dúvidas. Se eu não souber, corro atrás. Abraços!

---

P.S.: Um amigo, ex-colega e leitor do blog, Jackson Barbian, sinalizou que não conseguiu executar o script no SQL Server 2005 pois estava apresentando erro de sintaxe. Descobrimos depois que o problema era o modo de compatibilidade do banco de dados com o SQL Server 2000, o que faz com que o CROSS APPLY não funcione. Nesses casos, acesse o banco de dados MASTER ou outro banco de dados e execute a query, pois os dados dela não são sensíveis ao banco de dados selecionado. Valeu Jackson!