How to show blocking information using sys.dm_tran_locks & sys.dm_os_waiting_tasks and Linking session information to operating system threads

How to Returns information about currently active lock manager resources in SQL Server 2017 

Q:-Columns that are returned by sys.dm_tran_locks relate to either the resource being blocked or:

1. The request causing the block

2. The session causing the block

3. The session waiting on the block

4. The user causing the block

Ans:-  Request causing the block

Using sys.dm_tran_locks

The following example works with a scenario in which an update operation is blocked by another transaction. By using sys.dm_tran_locks and other tools, information about locking resources is provided.

SQL

USE tempdb;  
GO  

-- Create test table and index.  
CREATE TABLE t_lock  
    (  
    c1 int, c2 int  
    );  
GO  

CREATE INDEX t_lock_ci on t_lock(c1);  
GO  

-- Insert values into test table  
INSERT INTO t_lock VALUES (1, 1);  
INSERT INTO t_lock VALUES (2,2);  
INSERT INTO t_lock VALUES (3,3);  
INSERT INTO t_lock VALUES (4,4);  
INSERT INTO t_lock VALUES (5,5);  
INSERT INTO t_lock VALUES (6,6);  
GO  

-- Session 1  
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  

BEGIN TRAN  
    SELECT c1  
        FROM t_lock  
        WITH(holdlock, rowlock);  

-- Session 2  
BEGIN TRAN  
    UPDATE t_lock SET c1 = 10  

The following query will display lock information. The value for <dbid> should be replaced with the database_id from sys.databases.

SQL

SELECT resource_type, resource_associated_entity_id,  
    request_status, request_mode,request_session_id,  
    resource_description   
    FROM sys.dm_tran_locks  
    WHERE resource_database_id = <dbid>  

The following query returns object information by using resource_associated_entity_id from the previous query. This query must be executed while you are connected to the database that contains the object.

SELECT object_name(object_id), *  
    FROM sys.partitions  
    WHERE hobt_id=<resource_associated_entity_id>  

The following query will show blocking information.

SQL

SELECT   
        t1.resource_type,  
        t1.resource_database_id,  
        t1.resource_associated_entity_id,  
        t1.request_mode,  
        t1.request_session_id,  
        t2.blocking_session_id  
    FROM sys.dm_tran_locks as t1  
    INNER JOIN sys.dm_os_waiting_tasks as t2  
        ON t1.lock_owner_address = t2.resource_address;  

Release the resources by rolling back the transactions.

SQL

-- Session 1  
ROLLBACK;  
GO  

-- Session 2  
ROLLBACK;  
GO  

B. Linking session information to operating system threads

The following example returns information that associates a session ID with a Windows thread ID. The performance of the thread can be monitored in the Windows Performance Monitor. This query does not return session IDs that are currently sleeping.

SQL

SELECT STasks.session_id, SThreads.os_thread_id  
    FROM sys.dm_os_tasks AS STasks  
    INNER JOIN sys.dm_os_threads AS SThreads  
        ON STasks.worker_address = SThreads.worker_address  
    WHERE STasks.session_id IS NOT NULL  
    ORDER BY STasks.session_id;  
GO