In this article, I would like to demonstrate how to use SQL Server DMV's to identify locks, blocks and blocking sessions in your environment. I had the idea of writing this post, when I was attending a customer in person in a BI demand and on my side I saw some users complaining about “slowness” problems in their reports, when what was really happening were locks and blocks in some database tables.
What is the difference between Lock, Block and Deadlock?
In summary, we have 3 lock situations in SQL Server:
- Lock: Occurs when a session (Ex: 121 session) is making any changes (data or structure) to any object and SQL Server locks that object to prevent other sessions from trying to access or modify that object while the initial session (Ex : session 121) has not yet applied its changes. No wait events are raised 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, waiting for the release of the lock applied to this object to continue the execution of the commands. In this state, wait events are generated.
- Deadlock: The Deadlock scenario is similar to the Block scenario, except that in Deadlock, the blocking session is also being blocked, and if SQL Server or DBA do not take 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 dropped, this lock would never end. For more details on 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 treat them is a very recurring question of DBA's who are starting their careers and starting to enter the world of database administration, motivating me to write the articles below:
- SQL Server - How to create an email alert of locks and sessions locked on instance using DMV's
- SQL Server - How to generate a deadlock history for failure analysis in routines
- SQL Server - Using SP WhoIsActive to identify Locks, Blocks, Slow Queries, Running Queries, and more
- SQL Server - Query to return running queries (sp_WhoIsActive without consuming TempDB)
How to identify Locks and Blocks using SQL Server DMV's
Despite having already written these articles, I was missing a “F5 version”, quick, simple and practical, to show the DBA a list with all the blocks of the instance and what were the root causes of these blocks, making it easier to identify the sessions that were originating these blocks.
For that, I adapted the script I developed in the post. SQL Server - How to create an email alert of locks and sessions locked on instance using DMV's to create a Stored Procedure that is as simple as an F5 to execute and analyze the results. I added a few more columns to bring 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')
ALTER PROCEDURE [dbo].[stpVerifica_Locks]
SET NOCOUNT ON
DECLARE @Monitoramento_Locks TABLE
INSERT INTO @Monitoramento_Locks
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=', ':'), ')', '') + ']'
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,
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)
) - 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,
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,
(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]
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 (
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
) E ON A.session_id = E.session_id AND E.Ranking = 1
LEFT JOIN (
COUNT(*) AS blocked_session_count
blocking_session_id <> 0
) 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
A.session_id > 50
AND A.session_id <> @@SPID
(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
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)
A.nested_level = @Contador
JOIN @Monitoramento_Locks B ON A.blocking_session_id = B.session_id
A.nested_level IS NULL
AND B.nested_level = (@Contador - 1)
SET @Contador += 1
SET nested_level = @Contador
WHERE nested_level IS NULL
ORDER BY nested_level, blocked_session_count DESC, blocking_session_id, wait_time_ms DESC
In summary, I used the DMV's below:
- sys.dm_exec_sessions, for information about instance sessions
- sys.dm_exec_requests, for information about requests, from where I extract information from the blocking_session_id column (session number blocking another) and blocked_session_count
- msdb.dbo.sysjobs, this view is not required to display locks and blocks, but I only use it to bring up the Job name in the program_name column if the session is running the job.
- sys.dm_os_waiting_tasks, another DMV that is not required to display locks and blocks, but I use it to bring the session wait event and the time it is waiting for the lock to be released
- sys.dm_exec_sql_text, DMF which is not required to display locks and blocks, but I use it to retrieve the text of the query that is being executed.
- sys.sysprocesses, another DMV that is not required to display locks and blocks, but I use it to retrieve query text when I cannot retrieve sql_handle from sys.dm_exec_sessions.
Two features that greatly help with this monitoring are the nested_level and blocked_session_count columns:
- nested_level: level of hierarchy of locks. The 1 level is the sessions that are directly crashing the 2 level sessions onwards, just as the 2 level is locking all sessions from the 3 level onwards, and so on.
- blocked_session_count: Column that shows how many other sessions this particular session is locking. Ex: If a session has this column filled with the value 8, it means that there are 8 locked sessions waiting for resources to be released from that session.
That's it folks!
A big hug and even more!
Congratulations on the excellent post. Thanks for contributing to the admin community and new dbas.