SQL Server - How to identify locks, blocks, and blocking sessions

Views: 9.977
Reading Time: 5 minutes

Hey 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:

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:

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!