Ferramenta de monitoramento de deadlocks do SQL Server

Os deadlocks no SQL Server ocorrem quando duas ou mais transações, ou coleções de consultas SQL, bloqueiam uma à outra por uma dependência cíclica. Isso ocorre quando as transações mantêm bloqueios nos recursos requeridos também por outras transações, resultando em um bloqueio do SQL Server em uma tabela. Uma consulta SQL com deadlock não pode ser concluída sem que o bloqueio seja liberado. Também não é possível concluir a consulta SQL em deadlock correspondente.

Quando você recebe uma notificação de deadlock no SQL Server, você encontra uma transação chamada de “vítima” e uma chamada de “sobrevivente”. A vítima é a sessão revertida, e é selecionada automaticamente pelo SQL Server com base em prioridade de deadlock e custo de reversão.

Um projeto inadequado do banco de dados, falta de indexação, níveis de isolamento inapropriados e consultas mal escritas são algumas causas comuns do deadlock. Sempre que um bloqueio do SQL Server ocorrer em uma tabela, as operações serão atrasadas ou até mesmo inteiramente interrompidas. Por fim, os deadlocks podem resultar em uma parada completa do processamento interno do mecanismo do banco de dados.

Resolver deadlocks do SQL Server pode ser um processo frustrante e demorado, especialmente quando se lida com vários cenários complexos de deadlocks do servidor. Uma situação típica de deadlock se desenrola da seguinte forma: um procedimento armazenado impôs um bloqueio no recurso A (como um objeto, página ou linha) e está aguardando outro procedimento antes de poder acessar o recurso B. Porém, o segundo procedimento impôs um bloqueio em B e também está aguardando o primeiro procedimento liberar seu bloqueio em A.

Para quebrar esse ciclo vicioso, é necessário identificar e resolver rapidamente a origem do deadlock.

Há várias formas de resolver deadlocks no SQL Server, dependendo da situação. O SQL Server é capaz de detecção automática por meio de um monitor de bloqueio. Conforme observado acima, assim que um deadlock é detectado, o SQL Server escolhe automaticamente a “vítima” e reverte essa transação, depois cabe a você e à equipe administrativa de TI resolver os deadlocks no SQL Server.

Veja algumas formas comuns para resolver deadlocks no SQL Server:

• Índices de cobertura: Ao remover inteiramente consultas de indicadores, um índice de cobertura pode reduzir os riscos de deadlocks causados por consultas de indicadores.
• Índices de chave estrangeira: Reduza os deadlocks do SQL Server resultantes da integridade referencial em cascata criando índices que correspondam às suas colunas de chave estrangeira. Sem um índice de chave estrangeira, as ações em cascata levarão mais tempo e aumentarão o risco de um deadlock.
• Acesso à ordem de objetos: Para criar deadlocks para fins de teste, acesse objetos de banco de dados em uma ordem diferente. Para evitar esses deadlocks de outra maneira, faça as transações tão curtas quanto possível e mantenha objetos de acesso na mesma ordem lógica.
• Níveis de isolamento de transação: O nível de isolamento de transação padrão no SQL Server, LEITURA CONFIRMADA, pode promover deadlocks entre gravadores e leitores de dados. Evite isso usando INSTANTÂNEO CONFIRMADO ou INSTANTÂNEO, nos quais um leitor não aceita bloqueios em objetos e, em vez disso, usa as versões de linha para isolamento.
• Prioridade do deadlock: Ao especificar a prioridade de uma transação como Alta, Normal (Padrão) ou Baixa, é possível permitir que as sessões atuais continuem sendo processadas se houver deadlock no SQL Server.
• Gerenciamento de erros de aplicativos: Usando a lógica EXPERIMENTAR...CAPTURAR, é possível interceptar o número do deadlock. Também é possível definir o número de novas tentativas permitidas para um número definido, reduzindo o risco de um loop infinito.

A melhor forma de evitar deadlocks no SQL Server é recorrer à ajuda de uma ferramenta de monitoramento de deadlocks do SQL. Um monitor de deadlocks do SQL pode descobrir e resolver deadlocks no SQL Server oferecendo insights e recursos que aprimoram o monitoramento de deadlocks.

Há várias formas de localizar um bloqueio no SQL Server sem usar um monitor de deadlock do SQL. Esses métodos são realizados por meio do próprio SQL Server:

• Sinalizadores de rastreamento: Por meio dos sinalizadores de rastreamento 1204 ou 1222, o SQL Server pode realizar o monitoramento de deadlocks. Os sinalizadores de rastreamento devem sempre estar em execução, dificultando a detecção proativa de deadlocks.
• Contagem de desempenho: É projetada para mostrar o número de deadlocks presentes no SQL Server, mas não oferece detalhes relacionados às transações de sessões envolvidas.
• Criador de perfil do SQL Server: Também conhecido como rastreamento do lado do servidor, ele pode capturar detalhes de deadlock no SQL Server e os exibir em um gráfico XML. Esse método também precisa sempre ser executado, impedindo que você colete métricas de modo proativo.
• Sessão de evento estendida: O SQL Server executa um rastreamento de evento estendido por padrão, mas esse método é demorado, propenso a erros humanos e exige esforço manual.

Embora esses métodos possam oferecer algum alívio, eles não são tão rápidos, fáceis e eficientes quanto usar uma ferramenta de monitoramento de deadlocks para evitar deadlocks no SQL Server. Uma ferramenta de detecção de deadlocks de SQL como o SolarWinds SQL Sentry permite reconhecer rapidamente quando um deadlock ocorreu, compreender suas causas raiz e ver ações que podem ser tomadas para reduzir o risco de repetições do deadlock.

Os recursos de Análise de deadlock no SQL Sentry fornecem métricas detalhadas para que você possa resolver deadlocks do servidor e reduzir o tempo gasto para restaurar o desempenho ideal de seu banco de dados. As métricas incluem:

• SQL Server em que o deadlock ocorreu
• Hora em que o deadlock do banco de dados ocorreu
• SPID da vítima – o ID de processo da sessão da vítima envolvida no deadlock
• Host da vítima – a estação de trabalho que pertence à cadeia da vítima
• Aplicativo da vítima – o nome do aplicativo que pertence à cadeia da vítima
• Banco de dados da vítima
• Dados de texto da vítima
• XML do deadlock – XML do deadlock capturado

A área de detalhes do bloqueio descreve o deadlock do banco de dados por tipos de bloqueio específicos, incluindo proprietários e elementos em espera envolvidos em cada bloqueio. Os detalhes incluem:

• SPID – ID do processo da sessão do proprietário/elemento em espera associado
• Plano – abre uma sessão do Plan Explorer para visualizar o plano de consultas associado
• Host
• Aplicativo
• Banco de dados
• Uso de log – a quantidade de espaço de log usado pelo processo
• Prioridade do deadlock – zero (0) ou normal é a prioridade padrão
• Tempo de espera – tempo em milissegundos gasto na espera do recurso
• Hora de início da transação
• Hora de início do último lote
• Hora de conclusão do último lote
• Modo/tipo de bloqueio do recurso
• Status da tarefa
• Níveis de isolamento da transação atual
• Nome de login da sessão

O SolarWinds^® SQL Sentry foi projetado para capturar e examinar deadlocks de modo rápido e completo no SQL Server. Seus recursos de monitoramento de deadlock do SQL podem ajudar você a detectar e resolver deadlocks antes que eles gerem caos em suas operações de banco de dados e a obter insights práticos sobre como evitar que deadlocks similares ocorram no futuro.

O SQL Sentry foi desenvolvido para usar uma sessão modificada de Evento estendido para capturar detalhes do monitoramento. O SQL Sentry foi projetado para exibir deadlocks detectados no fim da guia Tendências, onde é possível encontrar outras informações críticas do deadlock no SQL Server, como:

• Hora da ocorrência do deadlock
• Número de sessões envolvidas no deadlock
• Qualquer objeto envolvido
• Nome da máquina e nome de usuário

Outra métrica do SQL Sentry é chamada de Impacto sobre a vítima, projetada para mostrar a medida de tempo que uma transação de deadlock estava em execução e consumindo recursos. Como o tempo de transação significa recursos desperdiçados, o tempo de transação da vítima da consulta de deadlock do SQL é um bom indicador do impacto que o deadlock teve sobre os SQL Servers e sobre os usuários finais.

Há outras páginas junto com a guia Tendências, nas quais você pode acessar mais informações e ações de deadlock no SQL Server. Por exemplo, o SQL Sentry permite configurar sessões não padrão usando a página Propriedades avançadas. A página Detalhes dos deadlocks do SQL Sentry foi desenvolvida para exibir o resumo do deadlock, que mostra os carimbos de data e hora da vítima e do sobrevivente, objetos, programas e número de sessões. Aqui você também pode encontrar dados nos recursos em deadlock, incluindo nome de índice, identificação de banco de dados e identificação de bloqueio.

O SQL Sentry permite que você exporte métricas e detalhes-chave que podem ser usados em conjunto com o SQL Server Management Studio (SSMS). O SSMS é um software fornecido pela Microsoft, que tem a capacidade de migrar com o SQL Sentry e outros bancos de dados – por meio do suporte à integração do SSMS, o SQL Sentry pode aprimorar a comunicação e otimizar a resolução de deadlocks. Você também pode enviar os detalhes do deadlock como um e-mail por meio do SQL Sentry e incluir o arquivo exportado como um anexo de e-mail.

Aproveite a capacidade do SQL Sentry de monitorar vários aspectos das operações do SQL Server para obter uma visão ampla dos elementos que afetam o desempenho de banco de dados.