¡Hola, chicos!

En este artículo, me gustaría demostrar cómo usar DMV de SQL Server para identificar bloqueos, bloqueos y sesiones de bloqueo en su entorno. Tuve la idea de escribir este post cuando estaba atendiendo personalmente a un cliente en una demanda de BI y de mi lado vi a algunos usuarios quejándose de problemas “lentos” en sus reportes, cuando en realidad lo que estaba pasando eran bloqueos y bloqueos en algunas tablas de la base de datos.

¿Cuál es la diferencia entre bloquear, bloquear y punto muerto?

En resumen, tenemos 3 situaciones de bloqueo en SQL Server:

  • Cerrar con llave: Ocurre cuando una sesión (Ej: sesión 121) está realizando algún cambio (datos o estructura) a algún objeto y SQL Server aplica un bloqueo a ese objeto para evitar que otras sesiones intenten acceder o modificar este objeto mientras la sesión inicial (Ej: sesión 121) aún no ha terminado de aplicar sus modificaciones. No se generan eventos de espera en este estado.
  • Bloquear: Escenario similar a Lock, pero con la diferencia que en este escenario, hay un bloqueo en el objeto y una o más sesiones están intentando leer o cambiar este objeto, esperando así que se libere el bloqueo aplicado a este objeto para continuar ejecutando los comandos. En este estado, se generan eventos de espera.
  • Punto muerto: El escenario Deadlock es similar al escenario Block, con la diferencia de que en Deadlock, la sesión de bloqueo también está siendo bloqueada y, si el SQL Server o el DBA no realiza ninguna acción, estos bloqueos durarían infinitamente. Imagine el escenario en el que la sesión A bloquea la sesión B, la sesión B bloquea la sesión C y la sesión C bloquea la sesión A. Si al menos una de estas 3 sesiones no se elimina, este bloqueo nunca terminaría. Para conocer más detalles sobre Deadlock, asegúrese de leer este artículo aquí

Este escenario de bloqueos y bloqueos es muy común en entornos transaccionales, y cómo identificarlos y lidiar con ellos es una pregunta frecuente para los DBA que están iniciando su carrera y comenzando a ingresar al mundo de la administración de bases de datos, motivándome a escribir los siguientes artículos:

Cómo identificar bloqueos y bloqueos utilizando el DMV de SQL Server

A pesar de haber escrito ya estos artículos, echaba de menos tener una “versión F5” rápida, sencilla y práctica, para mostrarle al DBA una lista de todos los bloqueos de la instancia y cuáles eran las causas raíz de estos bloqueos, facilitando la identificación de las sesiones que estaban originando estos bloques.

Para hacer esto, adapté el guión que desarrollé en la publicación. SQL Server: cómo crear una alerta por correo electrónico de bloqueos y sesiones bloqueadas en la instancia utilizando DMV para crear un procedimiento almacenado que sea tan simple como F5 para ejecutar y analizar los resultados. Incluí algunas columnas más para proporcionar más información sobre la sesión y se veía así:

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

En resumen, utilicé los siguientes del DMV:
– sys.dm_exec_sessions, para obtener información sobre las sesiones de la instancia.
– sys.dm_exec_requests, para obtener información sobre solicitudes, de las cuales extraigo información de la columna blocking_session_id (número de la sesión que está bloqueando a otra) y block_session_count
– msdb.dbo.sysjobs, esta vista no es necesaria para mostrar los bloqueos y bloqueos, pero solo la uso para mostrar el nombre del trabajo en la columna nombre_programa, si la sesión está ejecutando el trabajo.
– sys.dm_os_waiting_tasks, otro DMV que no es necesario para mostrar bloqueos y bloqueos, pero lo uso para traer el evento de espera de la sesión y el tiempo que está esperando que se libere el bloqueo.
– sys.dm_exec_sql_text, DMF que no es necesario para mostrar bloqueos y bloqueos, pero lo uso para recuperar el texto de la consulta que se está ejecutando.
– sys.sysprocesos, otro DMV que no es necesario para mostrar bloqueos y bloqueos, pero lo uso para recuperar el texto de la consulta cuando no puedo recuperar el sql_handle de sys.dm_exec_sessions.

Resultado de la ejecución:

Dos recursos que ayudan mucho con este monitoreo son las columnas nested_level y block_session_count:

  • nivel_anidado: nivel de jerarquía de bloqueo. El nivel 1 son las sesiones que bloquean directamente las sesiones del nivel 2 en adelante, así como el nivel 2 bloquea todas las sesiones del nivel 3 en adelante, y así sucesivamente.
  • recuento_de_sesiones_bloqueadas: Columna que muestra el número de otras sesiones que esta sesión específica está bloqueando. Ej: si una sesión tiene esta columna llena con el valor 8, esto significa que hay 8 sesiones bloqueadas esperando la liberación de recursos de esa sesión.

¡Eso es todo, amigos!
¡Un fuerte abrazo y hasta luego!