Hey guys!

In this article, I would like to demonstrate how to use SQL Server DMVs to identify locks, blocks and blocking sessions in your environment. I had the idea of ​​writing this post when I was assisting a client in person on a BI demand and on my side I saw some users complaining about “slow” problems in their reports, when what was actually happening were locks and blocks in some database tables.

What is the difference between Lock, Block and Deadlock?

In summary, we have 3 blocking situations in SQL Server:

  • Lock: It occurs when a session (Ex: session 121) is making some change (data or structure) to some object and SQL Server applies a lock to that object to prevent other sessions from trying to access or modify this object while the initial session (Ex: session 121) has not yet finished applying its modifications. No wait events are generated in this state
  • Block: Similar scenario to Lock, but with the difference that in this scenario, there is a lock on the object and one or more sessions are trying to read or change this object, thus waiting for the lock applied to this object to be released to continue executing the commands. In this state, wait events are generated.
  • Deadlock: The Deadlock scenario is similar to the Block scenario, with the difference that in Deadlock, the blocking session is also being blocked and, if the SQL Server or the DBA does not take any action, these locks would last infinitely. Imagine the scenario where session A is locking session B, session B is locking session C and session C is locking session A. If at least one of these 3 sessions is not eliminated, this lock would never end. To find out more details about Deadlock, be sure to read this article here

This scenario of locks and blocks is very common in transactional environments, and how to identify and deal with them is a frequently asked question for DBAs who are starting their careers and beginning to enter the world of database administration, motivating me to write the articles below:

How to identify Locks and Blocks using SQL Server DMV’s

Despite having already written these articles, I missed having a quick, simple and practical “F5 version”, to show the DBA a list of all the blocks in the instance and what the root causes of these blocks were, making it easier to identify the sessions that were originating these blocks.

To do this, I adapted the script I developed in the post SQL Server – How to create an email alert of locks and blocked sessions on the instance using DMV’s to create a Stored Procedure that is as simple as F5 to execute and analyze the results. I included a few more columns to provide more information about the session and it looked like this:

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

In summary, I used the DMV’s below:
– sys.dm_exec_sessions, for information about the instance's sessions
– sys.dm_exec_requests, to obtain information about requests, from which I extract information from the blocking_session_id column (number of the session that is blocking another) and blocked_session_count
– msdb.dbo.sysjobs, this view is not necessary to display the locks and blocks, but I only use it to display the Job name in the program_name column, if the session is executing the job.
– sys.dm_os_waiting_tasks, another DMV that is not necessary to display locks and blocks, but I use it to bring the session's wait event and the time it is waiting for the lock to be released
– sys.dm_exec_sql_text, DMF which is not necessary to display locks and blocks, but I use it to retrieve the text of the query being executed.
– sys.sysprocesses, another DMV that is not necessary to display locks and blocks, but I use it to retrieve the query text when I cannot retrieve the sql_handle from sys.dm_exec_sessions.

Execution result:

Two resources that help a lot with this monitoring are the nested_level and blocked_session_count columns:

  • nested_level: lock hierarchy level. Level 1 are the sessions that are directly blocking sessions from level 2 onwards, just as level 2 is blocking all sessions from level 3 onwards, and so on.
  • blocked_session_count: Column that shows the number of other sessions that this specific session is blocking. Ex: If a session has this column filled with the value 8, this means that there are 8 locked sessions awaiting the release of resources from that session.

That's it, folks!
A big hug and see you later!