SQL Server: Queries com piores performance (TOP 10)

Hoje concluí mais um script bastante útil para o dia a dia de um DBA, o objetivo é identificar (em tempo real) as queries (TOP 10) com as piores performance de execução para uma instância do SQL Server.
O script
--O Objetivo do script é analisar quais as queries com os maiores tempos de execução

SELECT [Row], [Name], [Query], [Compilacao], [Execucao], [Execucoes], [TempoCPU]/1000000 AS 'TempoCPU',

[TempoCPUMaximo]/1000000 AS 'TempoCPUMaximo', [LeituraFisica], [LeituraFisicaMaxima], [GravacaoLogica], [GravacaoLogicaMaxima],

[LeituraLogica], [LeituraLogicaMaxima], [Tempo]/1000000 AS 'Tempo', [TempoMaximo]/1000000 AS 'TempoMaximo'

FROM sys.databases AS Databases

INNER JOIN

--Este resultset é usado para classificar as queries com maiores tempo para cada base de dados

(

SELECT ROW_NUMBER() OVER(PARTITION BY [databases].name ORDER BY [stats].max_elapsed_time DESC) AS 'Row',

[databases].name AS 'Base', [stats].creation_time AS 'Compilacao', [stats].last_execution_time AS 'Execucao',

[stats].execution_count AS 'Execucoes', ltrim(rtrim([plan].text)) AS 'Query',

[stats].last_worker_time AS 'TempoCPU',

[stats].max_worker_time AS 'TempoCPUMaximo', [stats].last_physical_reads AS 'LeituraFisica',

[stats].max_physical_reads AS 'LeituraFisicaMaxima', [stats].last_logical_writes AS 'GravacaoLogica',

[stats].max_logical_writes AS 'GravacaoLogicaMaxima', [stats].last_logical_reads AS 'LeituraLogica',

[stats].max_logical_reads AS 'LeituraLogicaMaxima', [stats].last_elapsed_time AS 'Tempo',

[stats].max_elapsed_time AS 'TempoMaximo'

FROM sys.dm_exec_query_stats [stats](NOLOCK)

CROSS APPLY sys.dm_exec_sql_text([stats].plan_handle) [plan]

INNER JOIN sys.databases [databases](NOLOCK)

ON [plan].dbid = [databases].database_id

WHERE [databases].name NOT IN ('Master', 'MSDB', 'TempDB', 'Distribution')

) [TOP]

ON Databases.[Name] = [TOP].Base

--O filtro abaixo é usado para permitir que apenas as 10 execuções com maiores tempos sejam exibidas por base

WHERE [TOP].[Row] <= 10 AND [TempoMaximo]/1000000 >= 3

ORDER BY 1, 15 DESC

Para o script foram utilizadas as seguintes referências:

sys.databases
http://msdn.microsoft.com/pt-br/library/ms178534.aspx

sys.dm_exec_query_stats
http://msdn.microsoft.com/pt-br/library/ms189741.aspx

sys.dm_exec_sql_text

http://msdn.microsoft.com/pt-br/library/ms181929.aspx

Aproveitei o script e criei um report dinâmico, possibilitando escolher a instância que será analisada.

Espero que o script seja útil para você amigo(a) DBA.

Até mais,

Bruno Santos