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:
- SQL Server – Como criar um alerta por e-mail de locks e sessões bloqueadas na instância utilizando DMV’s
- SQL Server – Como gerar um histórico de deadlocks para análise de falhas em rotinas
- SQL Server – Utilizando a SP WhoIsActive para identificar Locks, Blocks, Queries Lentas, Queries em Execução e muito mais
- SQL Server – Query para retornar as consultas em execução (sp_WhoIsActive sem consumir TempDB)
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 |
IF (OBJECT_ID('dbo.stpVerifica_Locks') IS NULL) EXEC('CREATE PROCEDURE stpVerifica_Locks AS SELECT 1') GO ALTER PROCEDURE [dbo].[stpVerifica_Locks] AS BEGIN SET NOCOUNT ON DECLARE @Monitoramento_Locks TABLE ( [nested_level] INT, [session_id] SMALLINT, [wait_info] NVARCHAR(4000), [wait_time_ms] BIGINT, [blocking_session_id] SMALLINT, [blocked_session_count] INT, [open_transaction_count] INT, [sql_text] XML, [sql_command] XML, [total_elapsed_time] INT, [deadlock_priority] INT, [transaction_isolation_level] VARCHAR(50), [last_request_start_time] DATETIME, [login_name] NVARCHAR(128), [nt_user_name] NVARCHAR(128), [original_login_name] NVARCHAR(128), [host_name] NVARCHAR(128), [program_name] NVARCHAR(128) ) INSERT INTO @Monitoramento_Locks SELECT NULL AS nested_level, A.session_id AS session_id, '(' + CAST(COALESCE(E.wait_duration_ms, B.wait_time) AS VARCHAR(20)) + 'ms)' + COALESCE(E.wait_type, B.wait_type) + COALESCE((CASE WHEN COALESCE(E.wait_type, B.wait_type) LIKE 'PAGE%LATCH%' THEN ':' + DB_NAME(LEFT(E.resource_description, CHARINDEX(':', E.resource_description) - 1)) + ':' + SUBSTRING(E.resource_description, CHARINDEX(':', E.resource_description) + 1, 999) WHEN COALESCE(E.wait_type, B.wait_type) = 'OLEDB' THEN '[' + REPLACE(REPLACE(E.resource_description, ' (SPID=', ':'), ')', '') + ']' ELSE '' END), '') AS wait_info, COALESCE(E.wait_duration_ms, B.wait_time) AS wait_time_ms, NULLIF(B.blocking_session_id, 0) AS blocking_session_id, COALESCE(F.blocked_session_count, 0) AS blocked_session_count, A.open_transaction_count, CAST('<?query --' + CHAR(10) + ( SELECT TOP 1 SUBSTRING(X.[text], B.statement_start_offset / 2 + 1, ((CASE WHEN B.statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), X.[text])) * 2) ELSE B.statement_end_offset END ) - B.statement_start_offset ) / 2 + 1 ) ) + CHAR(10) + '--?>' AS XML) AS sql_text, CAST('<?query --' + CHAR(10) + X.[text] + CHAR(10) + '--?>' AS XML) AS sql_command, A.total_elapsed_time, A.[deadlock_priority], (CASE B.transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'ReadUncommitted' WHEN 2 THEN 'ReadCommitted' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END) AS transaction_isolation_level, A.last_request_start_time, A.login_name, A.nt_user_name, A.original_login_name, A.[host_name], (CASE WHEN D.name IS NOT NULL THEN 'SQLAgent - TSQL Job (' + D.[name] + ' - ' + SUBSTRING(A.[program_name], 67, LEN(A.[program_name]) - 67) + ')' ELSE A.[program_name] END) AS [program_name] FROM sys.dm_exec_sessions AS A WITH (NOLOCK) LEFT JOIN sys.dm_exec_requests AS B WITH (NOLOCK) ON A.session_id = B.session_id LEFT JOIN msdb.dbo.sysjobs AS D ON RIGHT(D.job_id, 10) = RIGHT(SUBSTRING(A.[program_name], 30, 34), 10) LEFT JOIN ( SELECT session_id, wait_type, wait_duration_ms, resource_description, ROW_NUMBER() OVER(PARTITION BY session_id ORDER BY (CASE WHEN wait_type LIKE 'PAGE%LATCH%' THEN 0 ELSE 1 END), wait_duration_ms) AS Ranking FROM sys.dm_os_waiting_tasks ) E ON A.session_id = E.session_id AND E.Ranking = 1 LEFT JOIN ( SELECT blocking_session_id, COUNT(*) AS blocked_session_count FROM sys.dm_exec_requests WHERE blocking_session_id <> 0 GROUP BY blocking_session_id ) F ON A.session_id = F.blocking_session_id LEFT JOIN sys.sysprocesses AS G WITH(NOLOCK) ON A.session_id = G.spid OUTER APPLY sys.dm_exec_sql_text(COALESCE(B.[sql_handle], G.[sql_handle])) AS X WHERE A.session_id > 50 AND A.session_id <> @@SPID AND ( (NULLIF(B.blocking_session_id, 0) IS NOT NULL OR COALESCE(F.blocked_session_count, 0) > 0) OR (A.session_id IN (SELECT NULLIF(blocking_session_id, 0) FROM sys.dm_exec_requests)) ) ------------------------------------------------ -- Gera o nível dos locks ------------------------------------------------ UPDATE @Monitoramento_Locks SET nested_level = 1 WHERE blocking_session_id IS NULL DECLARE @Contador INT = 2 WHILE(EXISTS(SELECT NULL FROM @Monitoramento_Locks WHERE nested_level IS NULL) AND @Contador < 50) BEGIN UPDATE A SET A.nested_level = @Contador FROM @Monitoramento_Locks A JOIN @Monitoramento_Locks B ON A.blocking_session_id = B.session_id WHERE A.nested_level IS NULL AND B.nested_level = (@Contador - 1) SET @Contador += 1 END UPDATE @Monitoramento_Locks SET nested_level = @Contador WHERE nested_level IS NULL SELECT * FROM @Monitoramento_Locks ORDER BY nested_level, blocked_session_count DESC, blocking_session_id, wait_time_ms DESC END |
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!
Portuguese 
English
Parabéns pelo excelente post. Obrigado por contribuir com a comunidade de administradores e novos dbas.