Identifica as querys com custo elevado de processamento

Este script retorna as querys que mais consomem tempo de execução pelo SQL Server.

 1 SELECT TOP 15
 2 
 3     SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
 4     MIN(query_stats.statement_text) AS "Statement Text"
 5 FROM
 6     (SELECT QS.*,
 7     SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
 8     ((CASE statement_end_offset
 9         WHEN -1 THEN DATALENGTH(st.text)
10         ELSE QS.statement_end_offset END     
11              - QS.statement_start_offset)/2) + 1) AS statement_text
12      FROM sys.dm_exec_query_stats AS QS
13      CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
14 GROUP BY query_stats.query_hash
15 ORDER BY 1 DESC;