Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

SQL Server – Como identificar timeout ou conexões interrompidas utilizando Extended Events (XE) ou SQL Profiler (Trace)

Visualizações: 12.585 views
Tempo de Leitura: 10 minutos

Olá pessoal!
O tema do post de hoje é timeout, que consiste em um tempo limite (geralmente, medido em segundos) em que uma operação irá aguardar até que ela encerre a execução de forma forçada, caso esse tempo limite seja atingido.

O objetivo desse post é mostrar como identificar timeout ou conexões interrompidas utilizando Extended Events (XE) ou SQL Profiler (Trace) e facilitar na análise desse tipo de problema tão comum no dia a dia.

Depois de identificar a causa raiz do timeout, a solução para resolver isso é tentar otimizar as consultas através de técnicas de Performance Tuning para fazer com que o tempo de execução seja reduzido. Dependendo do processo, se ele realmente for algo que envolva muito processamento e ele realmente é demorado, vale a pena aumentar o tempo de timeout desse processo em específico, mas isso é assunto para a minha série de Performance Tuning no SQL Server.

No SQL Server, temos vários tipos de timeout, onde eu gostaria de destacar:

  • Remote Query Timeout (sp_configure), que define o tempo máximo que consultas remotas (utilizando linked server) podem ser executadas (tempo padrão: 600 segundos)
  • lock_timeout (comando SET), que é o tempo que o client que está executando um comando SQL irá aguardar para que um lock seja liberado para continuar a execução desse comando (tempo padrão: -1 = infinito = vai aguardar indefinidamente)
  • Command Timeout é uma propriedade de drivers de conexão a banco de dados, como ADO.NET, JDBC e outros, que permite definir um limite máximo de tempo que uma instrução SQL poderá executar no banco de dados. Esse valor pode ser alterado dinamicamente na aplicação, podendo ter tempos limites diferentes, dependendo do contexto. (tempo padrão: 30 segundos)
  • Connection Timeout é uma propriedade da string de conexão que permite definir um limite de tempo máximo em que o driver de conexão irá aguardar para estabelecer uma conexão com o banco de dados (tempo padrão: 30 segundos)
Importante
É muito importante deixar claro que os timeouts de lock_timeout, Command Timeout e Connection Timeout são limites impostos pelo client/driver de conexão e não pelo banco de dados.

Quando um timeout ocorre, ele vai gerar uma exceção na aplicação, mas do ponto de vista do banco de dados, não houve nenhum erro ou problema na execução (ou conexão), justamente porque o timeout não ocorre no banco, ou seja, não é o banco que está interrompendo a instrução SQL e sim, a aplicação.

Observações
Caso você queira saber mais sobre os comandos SET do SQL Server, acesse o artigo Os comandos SET do SQL Server.

Recomendo também, a leitura do meu artigo Timeout ao executar Queries via Linked Server no SQL Server, caso esteja enfrentando algum problema relacionado à isso.

Não podemos nos esquecer também do timeout da ferramenta de desenvolvimento (IDE) que o DBA ou desenvolvedor usa pra acessar o banco de dados.

O SQL Server Management Studio (SSMS), por exemplo, possui uma propriedade chamada “Execution time-out”, que define quanto tempo o SSMS deve esperar para executar uma query (internamente, isso é a propriedade Command Timeout do driver de conexão).

O valor padrão dessa propriedade no SSMS é 0 (infinito), ou seja, não possui limite de tempo.

Como gerar um timeout utilizando o SQLCMD

Para poder criar alguns registros de testes e demonstrar como identificar timeouts no banco de dados, vou forçar a ocorrência de timeouts no banco de dados.

Para isso, vou utilizar o utilitário SQLCMD e a propriedade -t 1 (query timeout de 1 segundo), e vou utilizar o comando WAITFOR DELAY ’00:00:05′, que fica 5 segundos aguardando, ou seja, sempre que eu executar esse comando, irei gerar um evento de timeout.

Para conhecer um pouco mais sobre o utilitário SQLCMD, sugiro a leitura do artigo SQLCMD – O utilitário de linha de comando do SQL Server.

Resultado da execução:

Timeouts gerados.

Como identificar timeouts no banco de dados utilizando Extended Events (XE)

Para identificar timeouts no banco de dados, eu já vi muitas pessoas utilizando o tempo de execução da query para definir se houve um timeout ou não. Como o padrão são 30 segundos, a pessoa supõe que se a query demorou exatos 30 segundos, é porque ocorreu um timeout. Entretanto, nem sempre isso é verdade, até porque, como eu já mencionei no começo do post, para cada contexto ou tela do sistema, o desenvolvedor pode definir tempos de timeout diferentes.

Uma das formas de se conseguir identificar se realmente ocorreu um timeout, é utilizando o Extended Events (XE), analisando a classe de evento attention, disponível desde o SQL Server 2012, que indica que ocorreu um evento que requer atenção, como cancelamentos, solicitações de interrupção de cliente e perda de conexões com clientes. Operações de cancelamento também podem ser vistas como parte da implementação de tempos limite de acesso a dados do driver (timeouts)

Vou compartilhar o script já pronto para implementar o monitoramento e também prints da interface do Extended Events, caso você queira criar por contra própria, via tela.

Script para monitorar timeout no SQL Server utilizando Extended Events (XE)

Interface para monitorar timeout no SQL Server utilizando Extended Events (XE)
Clique para visualizar os screenshots

Screenshot 1 – Abrir o Wizard do XE pelo Object Explorer do SSMS

Screenshot 2 – Tela inicial

Screenshot 3 – Definir o nome do Extended Event e marcar o checkbox para habilitar o evento ao final da configuração

Screenshot 4 – Marcar que não quero usar nenhum template

Screenshot 5 – Selecionar o evento “attention” e adicionar na lista de eventos selecionados

Screenshot 6 – Confirmar a seleção e avançar para próxima tela

Screenshot 7 – Selecionar os campos de retorno que você quer que o evento retorne

Screenshot 8 – Avançar sem aplicar filtros

Screenshot 9 – Definir o local para salvar o arquivo .xel, tamanho máximo e rollover dos arquivos

Screenshot 10 – Tela final e resumo das operações

Script para ler os dados gravados pelo Extended Event (XE)
Clique para visualizar o script

Exemplo de dados retornados:

Como identificar timeouts no banco de dados utilizando SQL Profiler (Trace)

Uma outra forma de identificar timeouts no banco de dados é utilizando o SQL Profiler (Trace), especialmente se você está utilizando uma versão anterior à 2012, que não possui suporte ao evento attention do Extended Events.

Para atingir esse objetivo com o SQL Profiler, vamos monitorar os eventos RPC:Completed, SP:StmtCompleted e SQL:BatchCompleted e filtrar apenas os resultados que atendam ao filtro Error = 2.

Importante
Mesmo aplicando esse filtro, você deve acompanhar de perto se o workload do seu servidor vai aumentar muito após ativar esse trace, porque tudo o que for executado, será analisado por esse trace.

Sempre que possível, tente utilizar Extended Events (XE) ao invés de Profiler (Trace), especialmente para essa situação.

Script para monitorar timeout no SQL Server utilizando SQL Profiler (Trace)

Interface para monitorar timeout no SQL Server utilizando SQL Profiler (Trace)
Clique para visualizar os screenshots

Screenshot 1 – Abrindo o SQL Profiler

Screenshot 2 – Conectando na base de dados

Screenshot 3 – Selecionar o template Tuning e clicar na aba “Event Selection”

Screenshot 4 – Marcar o checkbox “Show all columns”, marcar a coluna “Error” (e as outras que você queira retornar) e clicar no botão “Column Filters”

Screenshot 5 – Aplicar um filtro na coluna Error e usar a medida Error = 2. Marcar o checkbox “Exclude rows that do not contain values”

Screenshot 6 – Clicar no botão de Run para iniciar o trace no servidor

Screenshot 7 – Analisar os resultados encontrados

Screenshot 8 – Resultados coletados e já armazenados numa tabela de histórico

Trecho de uma live onde o Márcio Júnior demonstra como identificar timeout no SQL Server

E é isso aí, pessoal!
Um grande abraço e até mais.