Análise de bloqueio do SQL Server

Bloqueios ocorrem quando dois ou mais processos precisam acessar o mesmo recurso ao mesmo tempo. Quando um processo solicita acesso a um recurso, ele recebe uma trava que força outros processos que precisam do mesmo recurso a esperarem até que o primeiro processo seja concluído. Na maioria dos casos, o segundo processo esperará o primeiro processo liberar a trava. A partir daí, tudo continua a funcionar normalmente. Em geral, esse processo demora alguns segundos.

No entanto, às vezes o primeiro processo pode levar mais tempo do que o esperado, fazendo com que o segundo processo (e potencialmente outros processos) fique preso atrás dele enquanto aguarda por esse recurso, o que poderia impactar o desempenho do aplicativo e a experiência do usuário final. A duração de uma consulta e o contexto da transação determinam quanto tempo duram as travas.

É importante observar que nem todo bloqueio do SQL Server é ruim. Na verdade, bloqueios são normais. Afinal, o SQL Server é um sistema de concorrência baseado em trava e seus recursos só podem ser acessados por um processo por vez. É claro que, eventualmente, processos concorrentes irão ocorrer. O problema reside nos bloqueios que demoram demais para se resolver e criam cadeias de bloqueios.

Além disso, nem todos os tipos de travas bloqueiam automaticamente todas as outras travas. Alguns tipos de travas são mais restritivas e, assim, mais sujeitas a bloquear outras travas. Alguns tipos de travas incluem:

• Travas exclusivas: Essas travas são atribuídas quando um processo precisa modificar dados. Elas garantes que dois processos não consigam alterar dados simultaneamente.
• Travas de esquemas: Travas de esquemas são dadas a processos que dependem de um esquema consistente durante as operações.
• Travas compartilhadas: Essas travas são atribuídas a operações de leitura. São menos restritivas que outros tipos de travas e, por isso, raramente resultam em bloqueios.
• Travas de atualização: Travas de atualização são concedidas a atualizações e geralmente são usadas para atualizações que ocorrem em várias etapas.

Alguns tipos de travas são mais compatíveis com outras. Por exemplo, as travas exclusivas não são compatíveis com nenhum outro tipo de trava e resultarão em um bloqueio se outro processo precisar do recurso; travas compartilhadas e de atualização podem estar no mesmo recurso ao mesmo tempo.

Não é difícil confundir deadlocks com bloqueios, uma vez que ambos ocorrem quando dois processos precisam de recursos específicos, mas não podem acessá-los para concluir uma consulta. No entanto, bloqueios e deadlocks não são a mesma coisa.

Um bloqueio no SQL Server ocorre quando dois ou mais processos precisam do mesmo recurso. Afinal, apenas um processo por vez pode acessar um recurso. Isso significa que, se um processo usa um recurso, o processo seguinte não será capaz de acessar esse recurso até que o primeiro processo termine de usá-lo.

Já os deadlocks ocorrem quando os processos tentam acessar diferentes recursos. Por exemplo: o primeiro processo pode travar o Recurso A, enquanto o segundo processo trava o Recurso B. Se o primeiro processo precisar colocar uma trava no Recurso B para ser concluído e o segundo precisa colocar uma trava no Recurso A, ambas as operações serão interrompidas, já que ficam paralisadas, uma à espera da outra. Trata-se basicamente de um impasse.

Felizmente, o SQL Server geralmente resolve deadlocks automaticamente revertendo um dos processos para que o outro possa ser concluído. Na sequência, o SQL Server reinicia o segundo processo assim que o primeiro tiver terminado de usar o recurso.

É indiscutível que dois processos eventualmente precisarão do mesmo recurso, e o bloqueio geralmente se resolve assim que o primeiro processo é concluído. No entanto, há instâncias em que os bloqueios podem criar problemas.

Para verificar bloqueios no SQL Server, você pode usar uma consulta para identificar o SPID do bloqueio. Você só precisa usar os DMVs do SQL Server e executar uma consulta usando o DMV sys.dm_exec_requests. Esse procedimento retornará uma lista de SPIDs de bloqueios, quaisquer SPIDs bloqueados, além do tipo e duração da espera.

Você também pode usar o SQL Server Management Studio para verificar bloqueios no SQL Server e identificar o princípio de uma cadeia de bloqueios. Você precisará selecionar Objeto do servidor, Relatórios, Relatórios padrão e, em seguida, Atividade – Todas as transações bloqueadas. O SQL Server Management Studio retornará um relatório que mostra a transação no topo da cadeia de bloqueios e qualquer transação que esteja bloqueada no momento pela primeira transação da lista.

O SQL Sentry facilita ainda mais a identificação de bloqueios problemáticos, já que monitora de modo contínuo os bloqueios do SQL Server. Com o Calendário de eventos do SQL Sentry, você pode visualizar bloqueios junto com outros eventos. Além disso, o SQL Sentry apresenta uma análise robusta de bloqueios e pode capturar seus detalhes com base em limites de duração específicos. Por exemplo, se você deseja informações sobre cada bloqueio que dure mais de dez segundos, o SQL Sentry automaticamente registrará dados (como a instrução executada, host, dados de login, banco de dados e tipos e recursos de espera) nos bloqueios que durarem mais de dez segundos e lhe dará a opção de visualizar esses detalhes em tempo real ou posteriormente.

Com sua visualização hierárquica e nós coloridos, o SQL Sentry torna possível entender uma cadeia de bloqueios com apenas um olhar. Você saberá na hora se uma instrução está sendo executada, foi concluída ou está bloqueada. Você também pode ver as relações entre os bloqueios e SPIDs bloqueados na cadeia, facilitando sua análise.

Uma vez que um bloqueio problemático tenha sido identificado, é hora de solucioná-lo. Os cenários e soluções de bloqueios mais comuns que se pode encontrar incluem:

• Uma consulta com longo tempo de execução: Podem ocorrer bloqueios problemáticos no SQL Server se uma consulta tiver um tempo de execução excessivamente longo e travar agressivamente para evitar que outros processos leiam ou modifiquem os mesmos dados. Para solucionar esse problema, primeiro é preciso localizar a consulta que está bloqueando o SQL Server por meio da análise de DMVs ou consultando sua plataforma de monitoramento. Em seguida, você pode otimizar sua consulta e planos de execução.
• Um aplicativo que esteja usando uma transação complexa que executa instruções ou lotes antes de ser confirmada: Se um aplicativo executa múltiplas instruções ou lotes antes da confirmação (salvar os dados), outros processos não poderão acessar o recurso durante esse período. Neste caso, também é necessário identificar a consulta que se encontra no topo da cadeia de bloqueios e otimizar seu desempenho.
• Uma transação é abandonada e não libera as travas: Se um aplicativo atingir o tempo limite de uma consulta ou se ela for cancelada, o SPID encerrará a consulta e o lote. No entanto, ele não reverterá ou confirmará automaticamente a transação, o que pode levar a um bloqueio. Para solucionar esse problema, é preciso identificar qual SPID está mantendo a transação aberta e iniciar uma reversão ou usar o comando de encerramento do T-SQL.
• Um SPID cujo aplicativo cliente não retorna todas as linhas de resultados para conclusão: Quando um aplicativo não traz imediatamente todas as linhas de resultados para conclusão, as travas serão mantidas nas tabelas, levando a bloqueios. Para solucionar esse problema, você precisará reescrever o aplicativo para que ele traga todas as linhas de resultados para conclusão. Para evitar esse problema, isole qualquer aplicativo que apresente problemas em um relatório ou num banco de dados de suporte de decisão separado.

A boa notícia é que o SQL Sentry pode ajudar em qualquer cenário de bloqueio que você encontre. O SQL Sentry coleta e retém os dados de bloqueios e deadlocks. Esse processo não só proporciona uma visão abrangente do desempenho atual, mas também permite que você veja o que já ocorreu no passado. Além disso, você pode visualizar problemas de bloqueio em tempo real e encerrar processos a partir do SQL Sentry com apenas um clique. Com a ajuda do SQL Sentry, é possível entender quais bloqueios estão em curso e reprojetar suas consultas e transações de modo a evitá-los no futuro.

O SQL Sentry ainda pode agir de modo automático contra condições de bloqueio do SQL quando você não está por perto. Você só precisa configurar o SQL Sentry para praticar ações específicas quando determinadas condições forem atendidas.

É essencial para os administradores de banco de dados saberem como encontrar bloqueios no SQL Server, pois os bloqueios podem impedir o acesso dos DBAs a recursos críticos para o negócio e prejudicar sua capacidade de gerenciar os bancos de dados de modo eficiente. No entanto, bloqueios podem ser difíceis e demorados de solucionar. As cinco causas de bloqueios mais comuns são:

1. Instruções SQL ineficientes executadas quando a sessão do banco de dados estava travada
2. Sessões dormentes perdidas no nível de aninhamento das transações
3. Aplicativos que falham na recuperação de todas as linhas de resultados
4. Sessões que revertem dados
5. Sessões abandonadas porque o aplicativo cliente deu problemas ou foi desconectado

Ainda assim, em uma grande empresa com bancos de dados ainda maiores, há muitos outros motivos pelos quais uma sessão pode ser bloqueada. Seria extremamente desafiador para um administrador de banco de dados chegar ao topo da cadeia de bloqueios e descobrir esses motivos manualmente

As ferramentas de monitoramento padrão do SQL Server exibirão as esperas de travas e bloqueios do banco de dados, mas não identificarão o bloqueador raiz responsável pela cadeia de bloqueios. O aumento dos bloqueios resulta em aplicativos mais lentos, mas como um administrador de banco de dados pode determinar se o aumento dos bloqueios é um sinal de ineficiência do banco de dados ou uma razão para os tempos de resposta lentos do aplicativo?

Ferramentas como o SQL Sentry podem ajudar DBAs a analisar uma cadeia de bloqueios do SQL Server, resolver bloqueios rapidamente e voltar a suas atividades.

A localização de bloqueios não é importante apenas quando as razões do bloqueio estão ativamente afetando o desempenho do banco de dados. Como as travas e bloqueios são subprodutos intrínsecos de se trabalhar com o SQL Server, os DBAs devem fazer tudo o que podem para monitorar proativamente problemas de bloqueio ou corrigir o código que causa bloqueios ociosos. Uma parte fundamental das práticas recomendadas de gerenciamento de banco de dados é usar software projetado para ajudar você a encontrar bloqueios mesmo em seus estágios iniciais.