Listando queries com alto consumo de cpu

Um problema recorrente na vida de qualquer DBA é quando o SGBD começa a “devorar” os recursos do servidor (memória, disco ou cpu). Irei mostrar nesse post como detectar e tratar situações que o processo do SQL Server faz alta utilização de cpu.

Primeiro, é preciso identificar se o processo do SQL Server está consumindo muitos recursos de cpu, como mostra a imagem abaixo:

Task Manager - CPU

Abra a console do SQL Server Management studio, abra o query editor e execute a seguinte instrução:

SELECT TOP 20
GETDATE() AS ‘Collection Date’,
qs.execution_count AS ‘Execution Count’,
SUBSTRING(qt.text,qs.statement_start_offset/2 +1,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END -qs.statement_start_offset)/2
) AS ‘Query Text’,
DB_NAME(qt.dbid) AS ‘DB Name’,
qs.total_worker_time AS ‘Total CPU Time’,
qs.total_worker_time/qs.execution_count AS ‘Avg CPU Time (ms)’,
qs.total_physical_reads AS ‘Total Physical Reads’,
qs.total_physical_reads/qs.execution_count AS ‘Avg Physical Reads’,
qs.total_logical_reads AS ‘Total Logical Reads’,
qs.total_logical_reads/qs.execution_count AS ‘Avg Logical Reads’,
qs.total_logical_writes AS ‘Total Logical Writes’,
qs.total_logical_writes/qs.execution_count AS ‘Avg Logical Writes’,
qs.total_elapsed_time AS ‘Total Duration’,
qs.total_elapsed_time/qs.execution_count AS ‘Avg Duration (ms)’,
qp.query_plan AS ‘Plan’
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE
qs.execution_count > 50 OR
qs.total_worker_time/qs.execution_count > 100 OR
qs.total_physical_reads/qs.execution_count > 1000 OR
qs.total_logical_reads/qs.execution_count > 1000 OR
qs.total_logical_writes/qs.execution_count > 1000 OR
qs.total_elapsed_time/qs.execution_count > 1000
ORDER BY
qs.execution_count DESC,
qs.total_elapsed_time/qs.execution_count DESC,
qs.total_worker_time/qs.execution_count DESC,
qs.total_physical_reads/qs.execution_count DESC,
qs.total_logical_reads/qs.execution_count DESC,
qs.total_logical_writes/qs.execution_count DESC
GO

O resultado dessa querie irá listar as 20 instruções sendo executadas no servidor, consumindo mais recursos de cpu, como mostra a imagem abaixo:

Resultado querie

OBS.:Por questões de segurança, ocultei o conteúdo da coluna query text, porém nessa coluna é listado as queries executadas.

Outro detalhe a ser percebido nesse resultado é o valor da linha 6, coluna “Total CPU Time“, expresso em microssegundos, e quando convertemos para minutos, identificamos que a querie em questão tomou exatos 17.8 minutos para executar !

É claro que apenas a identificação dessas queries não resolvem o problema, mas pelo menos já sabemos onde devemos atuar para otimizar o desempenho do SQL Server.

Outro ponto a ser considerado também é o desempenho geral do servidor, afinal de que adianta ficar otimizando queries, se o servidor tem poucos recursos, como memória e CPU, e possui discos SATA ?

Lembrem-se que o desempenho do ambiente deve ser avaliado como um todo.

Abraços e até a próxima.

Anúncios

8 comentários sobre “Listando queries com alto consumo de cpu

  1. Não há erros de sintaxe nessa consulta? Copiei e colei ela (sem alterações) e o SQL acusa um erro de falta/excesso de aspas duplas.

    • James, você tem razão, há um erro nos alias, onde estão com aspas duplas. Fiz a correção, e aproveitei para melhorar o design do texto, porém se você copiar o texto e colar no query editor, muda o contexto das aspas simples. Infelizmente creio que seja algum problema no WordPress, ou eu que não descobri a forma corrigir no blog. De qualquer forma, basta corrigir no query edito que vai funcionar. Obrigado.

    • Marcus, testei a query e está funcionando. Como informei na resposta ao comentário do James Sartori, quando o texto é copiado e colado no query editor, a aspa simples é removida, sendo substituída por aspas duplas ou até mesmo por 2 pontos (:)
      Basta substituir por aspas simples nos alias.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s