Você já se perguntou como encontrar as consultas TSQL com pior desempenho em sua instância do SQL Server? Se sim, você não está sozinho. No SQL Server, as estatísticas de consulta são mantidas à medida que os comandos TSQL são executados. Essas estatísticas de consulta podem ser acessadas usando os Objetos de Gerenciamento Dinâmico fornecidos pelo SQL Server.
O Objeto de Gerenciamento Dinâmico sys.dm_exec_query_stats retorna uma visão das estatísticas de consulta que o SQL Server está mantendo atualmente. Essas estatísticas são armazenadas na memória juntamente com o plano em cache para cada consulta. Enquanto um plano de execução TSQL estiver armazenado no cache de plano, você pode recuperar as estatísticas para esse comando TSQL. No entanto, uma vez que o comando é removido do cache de procedimento, as estatísticas também são descartadas.
Abaixo está um exemplo de um procedimento armazenado que pode ser usado para recuperar as estatísticas de consulta TSQL armazenadas no cache de procedimento:
CREATE PROCEDURE [dbo].[usp_Worst_TSQL]
@DBNAME VARCHAR(128) = '',
@COUNT INT = 999999999,
@ORDERBY VARCHAR(4) = 'TCPU'
AS
BEGIN
-- Verificar se o parâmetro @ORDERBY é válido
IF ((SELECT CASE WHEN @ORDERBY IN ('ACPU', 'TCPU', 'AE', 'TE', 'EC', 'AIO', 'TIO', 'ALR', 'TLR', 'ALW', 'TLW', 'APR', 'TPR') THEN 1 ELSE 0 END) = 0)
BEGIN
-- Abortar se o parâmetro @ORDERBY for inválido
RAISERROR('@ORDERBY parâmetro não é APCU, TCPU, AE, TE, EC, AIO, TIO, ALR, TLR, ALW, TLW, APR ou TPR', 11, 1)
RETURN
END
SELECT TOP (@COUNT)
COALESCE(DB_NAME(st.dbid), DB_NAME(CAST(pa.value AS INT)) + '*', 'Recurso') AS [Nome do Banco de Dados],
SUBSTRING(text, CASE WHEN statement_start_offset = 0 OR statement_start_offset IS NULL THEN 1 ELSE statement_start_offset / 2 + 1 END,
CASE WHEN statement_end_offset = 0 OR statement_end_offset = -1 OR statement_end_offset IS NULL THEN LEN(text) ELSE statement_end_offset / 2 END
- CASE WHEN statement_start_offset = 0 OR statement_start_offset IS NULL THEN 1 ELSE statement_start_offset / 2 END + 1) AS [Instrução],
OBJECT_SCHEMA_NAME(st.objectid, dbid) AS [Nome do Esquema],
OBJECT_NAME(st.objectid, dbid) AS [Nome do Objeto],
objtype AS [Tipo de Plano em Cache],
total_elapsed_time / execution_count AS [Tempo Médio Decorrido],
execution_count AS [Contagem de Execução],
(total_logical_reads + total_logical_writes + total_physical_reads) / execution_count AS [Média de E/S],
total_logical_reads + total_logical_writes + total_physical_reads AS [Total de E/S],
total_logical_reads / execution_count AS [Média de Leituras Lógicas],
total_logical_reads AS [Total de Leituras Lógicas],
total_logical_writes / execution_count AS [Média de Gravações Lógicas],
total_logical_writes AS [Total de Gravações Lógicas],
total_physical_reads / execution_count AS [Média de Leituras Físicas],
total_physical_reads AS [Total de Leituras Físicas],
total_worker_time / execution_count AS [Média de CPU],
total_worker_time AS [Total de CPU],
total_elapsed_time AS [Tempo Total Decorrido],
last_execution_time AS [Último Horário de Execução]
FROM sys.dm_exec_query_stats qs
JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
OUTER APPLY sys.dm_exec_plan_attributes(qs.plan_handle) pa
WHERE attribute = 'dbid'
AND CASE WHEN @DBNAME = '' THEN ''
ELSE COALESCE(DB_NAME(st.dbid), DB_NAME(CAST(pa.value AS INT)) + '*', 'Recurso') END
IN (RTRIM(@DBNAME), RTRIM(@DBNAME) + '*')
ORDER BY CASE WHEN @ORDERBY = 'ACPU' THEN total_worker_time / execution_count
WHEN @ORDERBY = 'TCPU' THEN total_worker_time
WHEN @ORDERBY = 'AE' THEN total_elapsed_time / execution_count
WHEN @ORDERBY = 'TE' THEN total_elapsed_time
WHEN @ORDERBY = 'EC' THEN execution_count
WHEN @ORDERBY = 'AIO' THEN (total_logical_reads + total_logical_writes + total_physical_reads) / execution_count
WHEN @ORDERBY = 'TIO' THEN total_logical_reads + total_logical_writes + total_physical_reads
WHEN @ORDERBY = 'ALR' THEN total_logical_reads / execution_count
WHEN @ORDERBY = 'TLR' THEN total_logical_reads
WHEN @ORDERBY = 'ALW' THEN total_logical_writes / execution_count
WHEN @ORDERBY = 'TLW' THEN total_logical_writes
WHEN @ORDERBY = 'APR' THEN total_physical_reads / execution_count
WHEN @ORDERBY = 'TPR' THEN total_physical_reads
END DESC
END
Este procedimento armazenado pode receber diferentes parâmetros para determinar quais estatísticas são retornadas e como os dados são ordenados. O parâmetro @DBNAME é usado para restringir a saída a um banco de dados específico. Se este parâmetro for definido como um nome de banco de dados específico, apenas as instruções associadas a esse banco de dados serão exibidas. Se o parâmetro @DBNAME não for definido, o procedimento armazenado retornará linhas associadas a qualquer banco de dados.
O parâmetro @COUNT permite controlar o número de linhas retornadas pelo procedimento armazenado. Se este parâmetro for usado, apenas as primeiras x linhas (onde x é igual a @COUNT) serão retornadas, com base no parâmetro @ORDERBY.
O parâmetro @ORDERBY identifica a ordem de classificação das linhas retornadas em ordem decrescente. Ele suporta os seguintes tipos: CPU, AE, TE, EC, AIO, TIO, ALR, TLR, ALW, TLW, APR e TPR. Por exemplo, se você deseja exibir as 6 principais instruções no banco de dados AdventureWorks com base no uso médio de CPU, você pode executar o procedimento armazenado da seguinte forma:
EXEC usp_Worst_TSQL @DBNAME='AdventureWorks', @COUNT=6, @ORDERBY='ACPU';
Da mesma forma, você pode recuperar as 100 principais instruções ordenadas por E/S média:
EXEC usp_Worst_TSQL @COUNT=100, @ORDERBY='AE';
Se você deseja mostrar todas as instruções ordenadas por E/S média, você pode simplesmente executar o procedimento armazenado sem nenhum parâmetro:
EXEC usp_Worst_TSQL;
Ao usar este procedimento armazenado, você poderá identificar e analisar as consultas TSQL com pior desempenho em sua instância ou banco de dados do SQL Server, com base em várias métricas de desempenho, como uso de CPU, contagem de execução, E/S e tempo decorrido.
Veja todos os artigos de Greg Larsen