Detect Database Blocking Issue with SQL Server Dynamic Management View
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)).