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.
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.