Como ativar o Slow Query log no SQL Server?

Como Capturar Queries Lentas no SQL Server?

Se você vem do mundo MySQL, PostgreSQL ou até mesmo Oracle, pode já ter se perguntado: como capturar queries lentas no SQL Server?

No MySQL, temos o slow_query_log , no PostgreSQL, o pg_stat_statements/ slowquery e Oracle AWR/ASH* podem ajudar. Mas e no SQL Server?

Podemos analisar queries em execução no momento utilizando sys.dm_exec_requests, mas e se quisermos consultar o histórico?

Err.. Dai habilitamos o Query Store!

Uma boa solução o Query Store. Ele permite armazenar e analisar estatísticas de execução de queries ao longo do tempo. No entanto, dependendo do volume de dados e da granularidade desejada, ele pode não ser a melhor opção para um monitoramento detalhado.

Foi nesse contexto que pesquisei alternativas e encontrei uma solução eficiente: o uso de Extended Events (XEvents). Se você também busca uma forma confiável de capturar queries lentas no SQL Server, este artigo é para você.

Referências:

Documentação oficial da Microsoft sobre Query Store
Blog do Dirceu Resende

Monitorando Queries Lentas com Extended Events

A partir do SQL Server 2012, os Extended Events (XEvents) se tornaram uma das melhores formas de capturar eventos internos do SQL Server. Eles funcionam como um sistema de auditoria leve e personalizável, permitindo registrar queries lentas, deadlocks, commits e por ai vai..

Podemos compará-los a triggers, mas ao invés de serem executados em nível de tabela, eles capturam eventos no nível da instância do SQL Server.

Extended Events (XEvents)

1º Criar o Evento de Captura

Antes de tudo, precisamos criar um evento de captura para registrar as queries demoradas. Para isso, utilizaremos o Extended Events, garantindo que os arquivos de saída sejam gravados em um diretório onde o SQL Server tenha permissão de escrita, no meu caso.

K:\SLOW_QUERIES

CREATE EVENT SESSION [SLOW_QUERIES] ON SERVER  
ADD EVENT sqlserver.sql_batch_completed (  
    ACTION (  
        sqlserver.session_id,  
        sqlserver.client_app_name,  
        sqlserver.client_hostname,  
        sqlserver.database_name,  
        sqlserver.username,  
        sqlserver.session_nt_username,  
        sqlserver.session_server_principal_name,  
        sqlserver.sql_text  
    )  
    WHERE duration > 5000000  -- AJUSTE O TEMPO CONFORME SUA NECESSIDADE
)  
ADD TARGET package0.event_file (  
    SET filename = N'K:\SLOW_QUERIES\slow.xel',   -- AQUI TAMBEM, AJUSTE SEU DIRETORIO
        max_file_size = 10,  
        max_rollover_files = 10  
)  
WITH (STARTUP_STATE = ON);  
GO  

-- Ativa o Extended Event
ALTER EVENT SESSION [SLOW_QUERIES] ON SERVER STATE = START
GO

neste caso iremos coletar tudo que for acima de 5 segundos , com 10 arquivos de no maximo 10MB, em D:\SLOW_QUERIES\.

2º Criar uma Tabela para salvar estes dados

Agora, precisamos de um local para armazenar os dados coletados pelo XEvents. Criamos uma tabela que receberá as informações capturadas.

CREATE TABLE dbo.Historico_Query_Lenta (
    [Dt_Evento] DATETIME,
    [session_id] INT,
    [database_name] VARCHAR(128),
    [username] VARCHAR(128),
    [session_server_principal_name] VARCHAR(128),
    [session_nt_username] VARCHAR(128),
    [client_hostname] VARCHAR(128),
    [client_app_name] VARCHAR(128),
    [duration] DECIMAL(18, 2),
    [cpu_time] DECIMAL(18, 2),
    [logical_reads] BIGINT,
    [physical_reads] BIGINT,
    [writes] BIGINT,
    [row_count] BIGINT,
    [sql_text] XML,
    [batch_text] XML,
    [result] VARCHAR(100)
) WITH(DATA_COMPRESSION=PAGE)
GO

3º Criar uma Procedure para Captura dos Dados

O próximo passo é criar uma Stored Procedure para extrair os dados do Extended Events e armazená-los na tabela criada no Passo 2, crie um database de sua escolha , recomendo a ter sempre um database administrativo para tais acoes.

 
CREATE PROCEDURE dbo.stpCarga_Query_Lenta
AS
BEGIN
    
    
    DECLARE 
        @TimeZone INT = DATEDIFF(HOUR, GETUTCDATE(), GETDATE()),
        @Dt_Ultimo_Registro DATETIME = ISNULL((SELECT MAX(Dt_Evento) FROM dbo.Historico_Query_Lenta), '1900-01-01')
 
 
    IF (OBJECT_ID('tempdb..#Eventos') IS NOT NULL) DROP TABLE #Eventos
    ;WITH CTE AS (
        SELECT CONVERT(XML, event_data) AS event_data
        FROM sys.fn_xe_file_target_read_file(N'K:\SLOW_QUERIES\slow*.xel', NULL, NULL, NULL)
    )
    SELECT
        DATEADD(HOUR, @TimeZone, CTE.event_data.value('(//event/@timestamp)[1]', 'datetime')) AS Dt_Evento,
        CTE.event_data
    INTO
        #Eventos
    FROM
        CTE
    WHERE
        DATEADD(HOUR, @TimeZone, CTE.event_data.value('(//event/@timestamp)[1]', 'datetime')) > @Dt_Ultimo_Registro
    
 
    INSERT INTO dbo.Historico_Query_Lenta
    SELECT
        A.Dt_Evento,
        xed.event_data.value('(action[@name="session_id"]/value)[1]', 'int') AS session_id,
        xed.event_data.value('(action[@name="database_name"]/value)[1]', 'varchar(128)') AS [database_name],
        xed.event_data.value('(action[@name="username"]/value)[1]', 'varchar(128)') AS username,
        xed.event_data.value('(action[@name="session_server_principal_name"]/value)[1]', 'varchar(128)') AS session_server_principal_name,
        xed.event_data.value('(action[@name="session_nt_username"]/value)[1]', 'varchar(128)') AS [session_nt_username],
        xed.event_data.value('(action[@name="client_hostname"]/value)[1]', 'varchar(128)') AS [client_hostname],
        xed.event_data.value('(action[@name="client_app_name"]/value)[1]', 'varchar(128)') AS [client_app_name],
        CAST(xed.event_data.value('(//data[@name="duration"]/value)[1]', 'bigint') / 1000000.0 AS NUMERIC(18, 2)) AS duration,
        CAST(xed.event_data.value('(//data[@name="cpu_time"]/value)[1]', 'bigint') / 1000000.0 AS NUMERIC(18, 2)) AS cpu_time,
        xed.event_data.value('(//data[@name="logical_reads"]/value)[1]', 'bigint') AS logical_reads,
        xed.event_data.value('(//data[@name="physical_reads"]/value)[1]', 'bigint') AS physical_reads,
        xed.event_data.value('(//data[@name="writes"]/value)[1]', 'bigint') AS writes,
        xed.event_data.value('(//data[@name="row_count"]/value)[1]', 'bigint') AS row_count,
        TRY_CAST(xed.event_data.value('(//action[@name="sql_text"]/value)[1]', 'varchar(max)') AS XML) AS sql_text,
        TRY_CAST(xed.event_data.value('(//data[@name="batch_text"]/value)[1]', 'varchar(max)') AS XML) AS batch_text,
        xed.event_data.value('(//data[@name="result"]/text)[1]', 'varchar(100)') AS result
    FROM
        #Eventos A
        CROSS APPLY A.event_data.nodes('//event') AS xed (event_data)
 
 
END

4º Criar um Job para coletas frequentes.

Para garantir que a coleta de queries lentas ocorra de forma contínua, podemos configurar um SQL Server Agent Job para executar a stored procedure periodicamente.

Então podemos consultar nossa tabela Historico_Query_Lenta onde estarão nossos dados.