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

SQL Server – Como identificar locks, blocks e sessões bloqueadoras

Post Views 30,608 views
Reading time 5 minutes

Hey guys!

Nesse artigo, eu gostaria de demonstrar como utilizar DMV’s do SQL Server para identificar locks, blocks e sessões bloqueadoras no seu ambiente. Tive a ideia de escrever esse post, quando estava atendendo um cliente presencialmente em uma demanda de BI e do meu lado vi alguns usuários reclamando de problemas de “lentidão” em seus relatórios, quando o que estava ocorrendo na realidade, eram locks e blocks em algumas tabelas do banco de dados.

Qual a diferença de Lock, Block e Deadlock?

Em resumo, temos 3 situações de bloqueios no SQL Server:

  • Lock: Ocorre quando uma sessão (Ex: sessão 121) está realizando alguma alteração (de dados ou de estrutura) em algum objeto e o SQL Server aplica uma trava nesse objeto para impedir que outras sessões tentem acessar ou modificar esse objeto enquanto a sessão inicial (Ex: sessão 121) ainda não terminou de aplicar as suas modificações. Nenhum evento de wait é gerado nesse estado
  • Block: Cenário parecido com o Lock, mas com a diferença que nesse cenário, existe um lock no objeto e uma ou mais sessões estão tentando ler ou alterar esse objeto, ficando assim, aguardando a liberação do lock aplicado nesse objeto para continuar a execução dos comandos. Nesse estado, são gerados eventos de wait.
  • Deadlock: O cenário de Deadlock é parecido com o cenário do Block, com a diferença que no Deadlock, a sessão bloqueadora também está sendo bloqueada e, caso o SQL Server ou o DBA não tomem alguma ação, esses locks iriam durar infinitamente. Imagine o cenário onde a sessão A está travando a sessão B, a sessão B está travando a sessão C e a sessão C está travando a sessão A. Caso pelo menos uma dessas 3 sessões não seja eliminada, esse lock não iria terminar nunca. Para saber mais detalhes sobre o Deadlock, não deixe de ler esse artigo aqui

Esse cenário de locks e blocks é muito comum em ambientes transacionais, e como identificá-los e tratá-los é uma dúvida bem recorrente de DBA’s que estão iniciando a carreira e começando a entrar no mundo da administração de bancos de dados, me motivando a escrever os artigos abaixo:

Como identificar Locks e Blocks utilizando DMV’s do SQL Server

Apesar de já ter escrito esses artigos, eu fiquei sentindo falta de ter uma “versão F5”, rápida, simples e prática, para mostrar ao DBA uma lista com todos os blocks da instância e quais eram as causas raiz desses bloqueios, facilitando identificar as sessões que estavam originando esses blocks.

Para isso, adaptei o script que desenvolvi no post SQL Server – Como criar um alerta por e-mail de locks e sessões bloqueadas na instância utilizando DMV’s para criar uma Stored Procedure que é tão simples quanto um F5 para executar e analisar os resultados. Incluí mais algumas colunas para trazer mais informações sobre a sessão e ele ficou assim:

Em resumo, utilizei as DMV’s abaixo:
sys.dm_exec_sessions, para obter informações sobre as sessões da instância
sys.dm_exec_requests, para obter informações sobre as requisições, de onde extraio a informação da coluna blocking_session_id (número da sessão que está bloqueando outra) e da blocked_session_count
msdb.dbo.sysjobs, essa view não é necessária para exibir os locks e blocks, mas a utilizo apenas para trazer o nome do Job na coluna program_name, caso a sessão esteja executando o job.
sys.dm_os_waiting_tasks, outra DMV que não é necessária para exibir os locks e blocks, mas a utilizo para trazer o evento de wait da sessão e o tempo que ela está aguardando a liberação do lock
sys.dm_exec_sql_text, DMF que não é necessária para exibir os locks e blocks, mas a utilizo para recuperar o texto da query que está sendo executada.
sys.sysprocesses, outra DMV que não é necessária para exibir os locks e blocks, mas a utilizo para recuperar o texto da query quando não consigo recuperar o sql_handle da sys.dm_exec_sessions.

Resultado da execução:

Dois recursos que ajudam bastante nesse monitoramento, são as colunas nested_level e blocked_session_count:

  • nested_level: nível de hierarquia dos locks. O nível 1 são as sessões que estão travando diretamente as sessões de nível 2 em diante, assim como o nível 2 está travando todas as sessões do nível 3 em diante, e assim sucessivamente.
  • blocked_session_count: Coluna que mostra a quantidade de outras sessões que essa sessão específica está lockando. Ex: Se uma sessão está com essa coluna preenchida com o valor 8, isso quer dizer que existem 8 sessões lockadas aguardando a liberação de recursos dessa sessão.

And that's it, folks!
Um grande abraço e até mais!