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.

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

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

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.