Speak guys!
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:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 | 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 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.
Execution Result:
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.