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

SQL Server: Identificando índices "inúteis"

Após 2 meses sem novas postagens, hoje vou mostrar um script muito útil para nós DBAs SQL Server. Usei como “base” o script do artigo Unused Indexes in your databases (Portal SQL Server Central)

O Script

/*

Parte(A) identifica índices sem entrada na DMV 'dm_db_index_usage_stats',

isto indica que o índice nunca foi utilizado desde a inicialização do SQL Server

*/

SELECT DB_NAME(), OBJECT_NAME(i.object_id) AS 'Table', ISNULL(i.name, 'heap') AS 'Index', x.used_page_count AS 'SizeKB'

FROM sys.objects o

INNER JOIN sys.indexes i

ON i.[object_id] = o.[object_id]

LEFT JOIN sys.dm_db_index_usage_stats s

ON i.index_id = s.index_id and s.object_id = i.object_id

LEFT JOIN sys.dm_db_partition_stats x

ON i.[object_id] = x.[object_id] AND i.index_id = x.index_id

WHERE OBJECT_NAME(o.object_id) IS NOT NULL AND OBJECT_NAME(s.object_id) IS NULL

AND o.[type] = 'U' AND ISNULL(i.name, 'heap') <> 'heap'

UNION ALL

/*

Parte(B) identifica índices que não são mais utilizados desde a inicialização

da instância do SQL Server

*/

SELECT DB_NAME(), OBJECT_NAME(i.object_id) AS 'Table', ISNULL(i.name, 'heap') AS 'Index', x.used_page_count AS 'SizeKB'

FROM sys.objects o

INNER JOIN sys.indexes i

ON i.[object_id] = o.[object_id]

LEFT JOIN sys.dm_db_index_usage_stats s

ON i.index_id = s.index_id and s.object_id = i.object_id

LEFT JOIN sys.dm_db_partition_stats x

ON i.[object_id] = x.[object_id] AND i.index_id = x.index_id

WHERE user_seeks = 0 AND user_scans = 0 AND user_lookups = 0

AND o.[type] = 'U' AND ISNULL(i.name, 'heap') <> 'heap'

ORDER BY 2 ASC

Para o script foram utilizadas as seguintes referências:

sys.objects
http://msdn.microsoft.com/pt-br/library/ms177596.aspx

sys.indexes
http://msdn.microsoft.com/pt-br/library/ms190283.aspx

sys.dm_db_index_usage_stats
http://msdn.microsoft.com/pt-br/library/ms188755.aspx

sys.dm_db_partition_stats
http://msdn.microsoft.com/pt-br/library/ms187737.aspx

Unused Indexes in your databases
http://www.sqlservercentral.com/scripts/Indexing/72486/

Aproveitei o script e criei um report dinâmico, possibilitando escolher a instância e databases que serão analisados. Na próxima semana crio um post com o passo a passo de como criar este report.

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

Até mais,

Bruno Santos