Identify the cause of SQL Server blocking

Problem

In my previous article (Different techniques to identify blocking in SQL Server), I discussed locks and blocks, and presented you with an overview on how to troubleshoot and resolve blocks using dynamic management views and Activity Monitor. After I wrote this article, I received several emails from readers asking how they can use the information returned by these dynamic management views (DMVs) to identify SPIDs and other useful information about the processes that are actually causing blocking on a SQL Server instance. In this tip, I will share the query which I have written using these dynamic management views (DMVs) that will help you to quickly identify SPIDs and other useful information about the processes that are causing blocking on SQL Server instance.

Solution

As discussed in my previous article, SQL Server has a rich set of dynamic management views (DMVs) that helps you to quickly identify locking and blocking in SQL Server. That is why, writing such a query is quite simple in SQL Server. I used the following dynamic management views (DMVs) for my query.

sys.dm_os_waiting_tasks – Returns information about blocked and blocking processes.
sys.dm_exec_sessions – Returns information about authenticated sessions on SQL Server.
sys.dm_exec_requests – Returns the detailed information about the requests currently executing on SQL Server.
sys.dm_tran_locks – Returns the information about the current locks and the processes blocking them.
sys.dm_exec_query_plan – Returns the showplan for the query in XML format.
sys.dm_exec_sql_text – Returns the text of T-SQL batch.

The following is the query, which I have written using these dynamic management views (DMVs) that will help you to quickly identify the SPIDs and other information about the processes that are causing the blocking on SQL Server instance. This query returns the comprehensive information about the blocking and waiting processes, which is useful for troubleshooting SQL Server locking and blocking issues. This query is also a good way to analyze detailed information about locks, and help you to identify the cause of a large number of blocks.

WITH [Blocking]
AS (SELECT w.[session_id]
   ,s.[original_login_name]
   ,s.[login_name]
   ,w.[wait_duration_ms]
   ,w.[wait_type]
   ,r.[status]
   ,r.[wait_resource]
   ,w.[resource_description]
   ,s.[program_name]
   ,w.[blocking_session_id]
   ,s.[host_name]
   ,r.[command]
   ,r.[percent_complete]
   ,r.[cpu_time]
   ,r.[total_elapsed_time]
   ,r.[reads]
   ,r.[writes]
   ,r.[logical_reads]
   ,r.[row_count]
   ,q.[text]
   ,q.[dbid]
   ,p.[query_plan]
   ,r.[plan_handle]
 FROM [sys].[dm_os_waiting_tasks] w
 INNER JOIN [sys].[dm_exec_sessions] s ON w.[session_id] = s.[session_id]
 INNER JOIN [sys].[dm_exec_requests] r ON s.[session_id] = r.[session_id]
 CROSS APPLY [sys].[dm_exec_sql_text](r.[plan_handle]) q
 CROSS APPLY [sys].[dm_exec_query_plan](r.[plan_handle]) p
 WHERE w.[session_id] > 50
  AND w.[wait_type] NOT IN ('DBMIRROR_DBM_EVENT'
      ,'ASYNC_NETWORK_IO'))
SELECT b.[session_id] AS [WaitingSessionID]
      ,b.[blocking_session_id] AS [BlockingSessionID]
      ,b.[login_name] AS [WaitingUserSessionLogin]
      ,s1.[login_name] AS [BlockingUserSessionLogin]
      ,b.[original_login_name] AS [WaitingUserConnectionLogin] 
      ,s1.[original_login_name] AS [BlockingSessionConnectionLogin]
      ,b.[wait_duration_ms] AS [WaitDuration]
      ,b.[wait_type] AS [WaitType]
      ,t.[request_mode] AS [WaitRequestMode]
      ,UPPER(b.[status]) AS [WaitingProcessStatus]
      ,UPPER(s1.[status]) AS [BlockingSessionStatus]
      ,b.[wait_resource] AS [WaitResource]
      ,t.[resource_type] AS [WaitResourceType]
      ,t.[resource_database_id] AS [WaitResourceDatabaseID]
      ,DB_NAME(t.[resource_database_id]) AS [WaitResourceDatabaseName]
      ,b.[resource_description] AS [WaitResourceDescription]
      ,b.[program_name] AS [WaitingSessionProgramName]
      ,s1.[program_name] AS [BlockingSessionProgramName]
      ,b.[host_name] AS [WaitingHost]
      ,s1.[host_name] AS [BlockingHost]
      ,b.[command] AS [WaitingCommandType]
      ,b.[text] AS [WaitingCommandText]
      ,b.[row_count] AS [WaitingCommandRowCount]
      ,b.[percent_complete] AS [WaitingCommandPercentComplete]
      ,b.[cpu_time] AS [WaitingCommandCPUTime]
      ,b.[total_elapsed_time] AS [WaitingCommandTotalElapsedTime]
      ,b.[reads] AS [WaitingCommandReads]
      ,b.[writes] AS [WaitingCommandWrites]
      ,b.[logical_reads] AS [WaitingCommandLogicalReads]
      ,b.[query_plan] AS [WaitingCommandQueryPlan]
      ,b.[plan_handle] AS [WaitingCommandPlanHandle]
FROM [Blocking] b
INNER JOIN [sys].[dm_exec_sessions] s1
ON b.[blocking_session_id] = s1.[session_id]
INNER JOIN [sys].[dm_tran_locks] t
ON t.[request_session_id] = b.[session_id]
WHERE t.[request_status] = 'WAIT'
GO

Sample Output

To examine the results of this query, run it on SQL Server where you are experiencing blocks. For example, when I executed this query on my test SQL Server where I’m deliberately running some code to cause blocking, it brings the following results (Note: To fit the resultset, I’ve split the resultset into seven images):

DMV query to quickly identify the cause of a large number of blocks

SQL Server has rich set of dynamic management view

quickly identify locking and blocking in SQL Server

processes that are causing the blocking on SQL Server instance

troubleshooting SQL Server locking and blocking issues

To examine the results of this query, run it on SQL Server where you are experiencing blocks

The following are the columns returned by this query:

WaitingSessionID – The SPID of the waiting session.
BlockingSessionID – The SPID of the blocking session.
WaitingSessionUserLogin – The user session login name under which waiting session is currently executing.
BlockingSessionUserLogin – The user session login name under which blocking session is currently executing.
WaitingUserConnectionLogin – The login name that the user used to create waiting session.
BlockingSessionConnectionLogin – The login name that the user used to create waiting session.
WaitDuration – Waiting process wait time in milliseconds.
WaitType – Type of wait.
WaitRequestMode – Mode of the wait request.
WaitingProcessStatus – The status of waiting process.
BlockingSessionStatus – The status of blocking process.
WaitResource - The name of the resource request is waiting for.
WaitResourceType – The type of the resource request is waiting for.
WaitResourceDatabaseID – The database id of the database in which the requested resource exists.
WaitResourceDatabaseName – The name of the database in which the requested resource exists.
WaitResourceDescription – The detailed description of the waiting resource.
WaitingSessionProgramName – The name of the program that initiated the waiting session.
BlockingSessionProgramName – The name of the program that initiated the blocking session.
WaitingHost – The name of the workstation that is specific to waiting session.
BlockingHost – The name of the workstation that is specific to blocking session.
WaitingCommandType – The type of waiting session command.
WaitingCommandText – The text of waiting session command.
WaitingCommandRowCount – Expected number of rows return by the waiting session.
WaitingCommandPercentComplete – Percentage of the waiting request client.
WaitingCommandCPUTime – CPU time used by waiting session.
WaitingCommandTotalElapsedTime – The total time elapsed in milliseconds since the waiting request arrived.
WaitingCommandReads – The number of reads performed by the waiting session request.
WaitingCommandWrites – The number of writes performed by the waiting session request.
WaitingCommandLogicalReads – The number of logical reads performed by the waiting session request.
WaitingCommandQueryPlan – Waiting command execution plan.
WaitingCommandPlanHandle – Plan handle of the waiting session command.

As you can see from above resultset, that process 53 listed BlockingSessionID column of row 4 is not blocked by another process, hence identified as the SPID that is the cause of the blocking on my test SQL Server instance.

Next Steps

Transaction locks are the most common cause of blocked processes. The stronger (least concurrent) the isolation level, the more likely it is to cause a blocked process.
Try to use less granular lock for your queries, as the less granular the lock, the more likely a blocked process or deadlock will not occur. For example, if the entire table is locked, there is a higher likelihood of blocks than if only a single row is locked.
Revisit your database design because bad database design could be potential reason for excessive locking and blocking.
Check out these tips to learn more about locking and blocking:

Resources

Community

Subscribe