Fala pessoal!

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:

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.

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