Detect Database Blocking Issue with SQL Server Dynamic Management View

Great example how to create a block and then query for it.    (original article)

Detect Database Blocking Issue with SQL Server Dynamic Management View

By : Kasim Wirama, MCITP, MCDBA

DBA has responsibility to maintain database from backup-restore, data integrity until smooth database daily operation. Most common problem regarding to database operation especially busy database activity is blocking and deadlocking. For this posting, I show you how to detect blocking on your SQL Server by using DMV (Dynamic Management View).

DMV has been introduced since SQL Server 2005. DMV for detecting blocking is sys.dm_tran_locks. Many advantage you use the DMV over sp_lock, sp_who2, sp_who and the like. Its advantages are that the DMV could show you resource location and you could export resultset for further analysis.

Back to sys.dm_tran_locks, you can detect blocking by joining to the DMV itself as query shown below
SELECT
l.*
FROM sys.dm_tran_locks as l
JOIN sys.dm_tran_locks as l1
ON l.resource_associated_entity_id = l1.resource_associated_entity_id
WHERE l.request_status <> l1.request_status
AND
( l.resource_description = l1.resource_description OR (l.resource_description IS NULL AND l1.resource_description IS NULL))

To see blocking scenario you can create sample table below :

USE tempdb
GO
CREATE TABLE test (cola varchar(10));
GO
INSERT INTO test VALUES (‘a’);

Open first connection (in SSMS open new window designer), and issue then execute query below :
USE tempdb
GO
BEGIN TRAN
UPDATE test SET cola = ‘b’;
WAITFOR DELAY ’00:10:00’;
ROLLBACK TRAN

Open second connection, issue the below :

USE tempdb
GO
SELECT * FROM test;

Execute the DMV then it display 2 records, to display object name getting involved in blocking, you can issue query below :

SELECT
db_name(l.resource_database_id) as [database name],
CASE l.resource_type
  WHEN ‘object’ then object_name(l.resource_associated_entity_id)
  WHEN ‘database’ then ‘databae’
  ELSE
    CASE WHEN l.resource_database_id = db_id() THEN
       (SELECT object_Name(object_id) FROM sys.partitions WHERE hobt_id = l.resource_associated_entity_id)
    ELSE NULL
    END
END,
l.*
FROM sys.dm_tran_locks as l
JOIN sys.dm_tran_locks as l1
ON l.resource_associated_entity_id = l1.resource_associated_entity_id
WHERE l.request_status <> l1.request_status
AND
( l.resource_description = l1.resource_description OR (l.resource_description IS NULL AND l1.resource_description IS NULL));

If you need to know what queries getting involved with blocking you can get query information from sys.dm_exec_requests and DMV function : sys.dm_exec_sql_text. Here is the DMV query :

SELECT
db_name(l.resource_database_id) as [database name],
CASE l.resource_type
  WHEN ‘object’ then object_name(l.resource_associated_entity_id)
  WHEN ‘database’ then ‘databae’
  ELSE
    CASE WHEN l.resource_database_id = db_id() THEN
       (SELECT object_Name(object_id) FROM sys.partitions WHERE hobt_id = l.resource_associated_entity_id)
    ELSE NULL
    END
END,
( SELECT t.[text]
  FROM sys.dm_exec_requests r
  CROSS APPLY sys.dm_exec_sql_text (r.sql_handle) AS t
  WHERE r.session_id = l.request_session_id
),
l.*
FROM sys.dm_tran_locks as l
JOIN sys.dm_tran_locks as l1
ON l.resource_associated_entity_id = l1.resource_associated_entity_id
WHERE l.request_status <> l1.request_status
AND
( l.resource_description = l1.resource_description OR (l.resource_description IS NULL AND l1.resource_description IS NULL)).