Como identifico consultas lentas no sql server?

Question

Encontrei instruções longas e complicadas assim quando pesquisando a resposta a esta pergunta, um breve link de referência como em este post .

Estou procurando um procedimento mais sucinto possível para gerar uma lista de consultas SQL com tempo de execução em que o tempo de execução > some_threshold.

    

Answer 1

Eu acho que a resposta não estava aqui porque é tão simples! Aqui está o que eu descobri:

1. Abra o SQL Server Profiler (em Ferramentas de desempenho)
2. File -> New Trace...
3. Conecte-se ao seu banco de dados
4. Clique na guia Seleção de eventos
5. Selecione apenas os eventos que correspondem ao término das consultas SQL:
   • RPC:Completed
   • SQL:BatchCompleted
6. Clique em Filtros de coluna ...
7. Clique em Duração na lista
8. Expanda Maior que ou igual a e digite o tempo limite que você considera "lento" em milissegundos
9. Clique em OK
10. Clique em Executar

Você pode filtrar por ApplicationName , NTUserName , etc se tiver muitos aplicativos em execução e quiser reduzir o ruído. Você também pode mostrar apenas algumas colunas, por exemplo apenas TextData e Duration .

Aqui está um tratamento muito mais avançado do Profiler.

    

Answer 2

você pode usar isso para obter as 10 principais consultas caras (se você estiver no servidor SQL 2005 e acima):

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

    

Answer 3

Usar um rastreamento do Profiler, particularmente ao importar o rastreamento em um banco de dados, é uma excelente metodologia.

Se você estiver usando o SQL Server 2005 ou posterior, DMVs (Dynamic Management Views) oferecem uma metodologia alternativa:

SELECT TOP 100
(total_logical_reads + total_logical_writes) / qs.execution_count AS average_IO,
(total_logical_reads + total_logical_writes) AS total_IO,
qs.execution_count AS execution_count,
SUBSTRING (qt.text,qs.statement_start_offset/2, 
     (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 indivudual_query,
o.name AS object_name,
DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where qt.dbid = DB_ID()
ORDER BY average_IO DESC;

Uma das coisas a lembrar é que os DMVs são limpos quando o SQL Server é iniciado, portanto, se o seu servidor estiver ativo por 12 minutos, ele pode não informar muito. Além disso, eles são cumulativos - portanto, as janelas de manutenção (checkDB) podem distorcer os dados.