SQL Server – Como identificar e coletar informações de consultas demoradas utilizando Extended Events (XE)

Visualizações: 1.865
Esse post é a parte 8 de 8 da série Performance Tuning
Tempo de Leitura: 7 minutos

Fala pessoal!
Nesse artigo eu gostaria de compartilhar com vocês como identificar e coletar informações de consultas demoradas utilizando Extended Events (XE), num artigo bem parecido com o SQL Server – Como identificar e coletar informações de consultas demoradas utilizando Trace (SQL Server Profiler), que utiliza a tecnologia de Profiler (Trace).

O que me motivou a escrever esse artigo foi que o Profiler é um recurso que está marcado como deprecated há bastante tempo, é uma tecnologia bem mais antiga e o código não é nada amigável ou legível. Então, pensando em trazer uma solução mais moderna e intuitiva para vocês, resolvi compartilhar essa solução utilizando o XE.

Se tudo o que eu falei acima ainda não é suficiente para você trocar o seu Profiler pelo XE, saiba que o overhead do XE é MUITO menor que de eventos utilizando Trace, conforme podemos encontrar nos artigos abaixo:

Passo 1 – Ativar o Extended Event no Servidor (SQL 2012+)

A fim de começar a monitorar os comandos que são executados na instância, vamos ativar o Extended Event capturando os eventos de sqlserver.sql_batch_completed. Poderia até ser o sqlserver.sp_statement_completed e/ou sqlserver.sql_statement_completed, mas iria capturar apenas os trechos de uma query ad-hoc ou SP que passaram do tempo limite de duração. Nesse artigo, o meu objetivo é capturar a SP ou batch inteiro que passou do tempo limite e depois analiso para saber qual o statement que está demorando mais.

Para esse monitoramento, defini que o limite de tempo para capturar as consultas é de 3 segundos. Atingiu ou passou desse tempo, a consulta é logada no nosso monitoramento. O destino dos dados coletados eu configurei para “C:\Traces\Query Lenta”, com tamanho máximo de 10 MB de dados e 10 arquivos no máximo. Como o rollout está configurado, os arquivos vão manter sempre os dados mais recentes, então não precisamos nos preocupar com expurgar os dados ou com os arquivos crescendo muito.

Passo 1 – Ativar o Extended Event no Servidor (SQL 2008)

Caso você ainda esteja utilizando o SQL Server 2008 no seu ambiente (já passou da hora de migrar hein.. suporte acaba em julho/2019), irá notar que ao tentar criar o Extended Events descrito acima, você receberá a mensagem de erro abaixo:

Msg 25623, Level 16, State 1, Line 16
The event name, “sqlserver.sql_batch_completed”, is invalid, or the object could not be found

Isso ocorre porque no SQL Server 2008, esse evento ainda não era suportado, conforme podemos consultar utilizando a query abaixo:

Resultado:

Ou seja, se quisermos implementar uma solução de monitoramento de query lenta utilizando Extended Events, teremos que buscar outros tipos de evento, e para este caso, vou utilizar os métodos sp_statement_completed e sql_statement_completed:

Diferente do método que utilizei anteriormente (sql_batch_completed), que mede o tempo de execução de todo o batch, esses 2 tipos de eventos vão capturar trechos de query ou Stored Procedures que ultrapassem o tempo limite, ou seja, se você tem um Stored Procedure que demora 40s para executar e nenhum dos statements ultrapassa os 3 segundos, essa SP não cairá no monitoramento.

Passo 2 – Criar a Tabela de Histórico dos Dados

Para armazenar os dados coletados pelo nosso monitoramento, vamos criar uma tabela física que utilizaremos para inserir os dados e, posteriormente, para consultar os dados coletados sempre que necessário.

SQL Server 2012+

SQL Server 2008:

Passo 3 – Criar a Stored Procedure para Capturar os Dados

Agora que já estamos monitorando as consultas, precisamos ler os dados coletados e armazenar em uma tabela física para consultas, já que configurei o tamanho máximo dos arquivos bem pequenos (10 MB) para que as consultas sejam sempre rápidas. Dependendo da quantidade de consultas pesadas no seu ambiente, você pode aumentar o diminuir o tamanho máximo de acordo com o seu ambiente.

Para capturar os dados no SQL Server 2012+, vamos utilizar a Stored Procedure abaixo:

Caso você esteja utilizando o SQL Server 2008, preparei uma Stored Procedure para você também 🙂

Passo 4 – Criar um job no SQL Agent para automatizar a coleta dos dados

Para que os dados coletados sejam sempre gravados na tabela que criamos, agora você deve criar um Job no SQL Server Agent para automatizar a execução da Stored Procedure criada no Item 3, de acordo com a frequência que você precisa que sejam gravados os dados.

A minha sugestão, é começar com execuções a cada 10 minutos e ir ajustando esse tempo conforme a necessidade.

Abaixo, vou compartilhar um exemplo de código para criação do Job, mas acredito que criar pela interface do SQL Server Management Studio (SSMS) seja bem mais prático e intuitivo, né ?

E por fim, agora que nosso monitoramento está ativado e já temos um job para automatizar a coleta desses dados e gravá-los em uma tabela do banco de dados, podemos consultar os dados dessa tabela sempre que for necessário identificar consultas lentas no nosso ambiente.

Exemplo de execução:

Reparem que a última coluna (Result) nos informa se o comando foi executado com sucesso ou se ele foi interrompido. Bem legal, né ?

Espero que tenham gostado dessa dica, um grande abraço e até o próximo artigo.