SQL server: Blocking issue

Different techniques to identify blocking in SQL Server

Problem

SQL Server is able to service requests from a large number of concurrent users. When SQL Server is servicing requests from many clients, there is a strong possibility that conflicts will occur because different processes request access to the same resources at the same time. A conflict in which one process is waiting for another to release a resource is called a block. Although in SQL Server a blocked process usually resolves itself when the first process releases the resource but there are times when a process holds a transaction lock and doesn’t release it. In this tip, we will learn different techniques to troubleshoot and resolve blocks in SQL Server.

Solution

In order to resolve a blocked process, we first need to determine which process is the blocking process and then if possible kill the blocking process. There are many different ways in SQL Server to identify a blocks and blocking process that are listed as follow:

• Activity Monitor
• SQLServer:Locks Performance Object
• DMVs
  • sys.dm_exec_requests
  • sys.dm_tran_locks
  • sys.dm_os_waiting_tasks
• SQL Server Profiler Locks Event Category

Each of these tools reports different information which is helpful in resolving blocks quickly. Let’s have a look at these tools in details:

1) Using SQL Script

Method 1:

Get List of Blocked SID from the SP:
1.SP_who2 Active

or
2.SELECT spid FROM master..SYSPROCESSES WHERE blocked0

Let’s say 56 is the spid blocking

Details about Blocked SID:
DBCC INPUTBUFFER(56) — Will give you the Event Info
KILL 56

Method 2:

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

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.

1) Activity Monitor

Activity Monitor is a tool in SQL Server Management Studio that gives you a view of current connections on SQL Server. You can use Activity Monitor to view information about the current processes and locks held on SQL Server resources. To open Activity Monitor in SQL Server Management Studio, right-click the SQL Server instance name in Object Explorer and then select Activity Monitor:

Launch Activity Monitor

To find blocked process with Activity Monitor, first click on Processes in Activity Monitor to open the Process Info page:

Process Info Page and Locating Blocking Process

Then locate the process that is waiting, and then scroll over to the Blocked By column and note the Process ID in that column. Find that Process ID in Process Info page. and

If you want to terminate the blocking process, right-click it and choose Kill Process:

2) The SQLServer:Locks performance object

You use SQLServer:Locks object counter in Performance Monitor to view current statistics or create a log or alert to monitor locks. For example, you can monitor Average Wait Time, Number of deadlocks/sec and Lock Timeouts/secstatistics to determine whether there is a problem with resource contention on SQL Server. However, you will need additional information to determine the exact cause of the problem. Follow the steps below to monitor the SQLServer: Locks performance counter:

On the Start menu, point to Run, type perfmon in the Run dialog box, and then click OK to launch Performance Monitor.

Launching Performance Monitor

Right-click anywhere on the screen and then choose Add Counters.
 

Add counters

Scroll down to locate SQL Server lock counters and add these three counters and then click OK.

• Average Wait Time
• Number of deadlocks/sec
• Lock Timeouts/sec

3) DMVs (Dynamic Management Views)

sys.dm_exec_requests

You can use the sys.dm_exec_requests dynamic management view to obtain detailed information about the requests currently executing on SQL Server. The dynamic management view includes detailed information about the query and query plan, status of request and information about the amount of time it has been executing. The columns you are most likely to use when troubleshooting a block or deadlock are as follow:

1. blocking_session_id - The SPID of the blocking session.
2. wait_type - Type of wait.
3. wait_time - Length of time request has been waiting (in milliseconds).
4. last_wait_type - If a wait has ended, its type is listed here.
5. wait_resource - Name of resource the request is waiting for.
6. transaction_isolation_level - Isolation level for the transaction.
7. lock_timeout - Length of time a lock can exist before timing out

To view blocked process execute the following query:

USE [master]
GO
SELECT  session_id
 ,blocking_session_id
 ,wait_time
 ,wait_type
 ,last_wait_type
 ,wait_resource
 ,transaction_isolation_level
 ,lock_timeout
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0
GO

sys.dm_tran_locks

You can view information about current locks and the processes blocking them using the sys.dm_tran_locks dynamic management view. This column has one of three values: GRANT, WAIT or CONVERT. The value of CONVERT means that the requestor has been granted a request but is waiting to upgrade to the initial request to be granted. To locate information about all locks with a request status of CONVERT, you execute the following:

USE [master]
GO
SELECT * from sys.dm_tran_locks
WHERE request_status = 'CONVERT'
GO

The request_session_id column contains the Process ID for the process. To view locking in the particular database, execute the following query that joins sys.dm_tran_locks with sys.partitions:

USE [master]
GO
SELECT   tl.resource_type
 ,tl.resource_associated_entity_id
 ,OBJECT_NAME(p.object_id) AS object_name
 ,tl.request_status
 ,tl.request_mode
 ,tl.request_session_id
 ,tl.resource_description
FROM sys.dm_tran_locks tl
LEFT JOIN sys.partitions p 
ON p.hobt_id = tl.resource_associated_entity_id
WHERE tl.resource_database_id = DB_ID()
GO

sys.dm_os_waiting_tasks

The sys.dm_os_waiting_tasks dynamic management view reports information about the blocked and blocking processes. The blocked process is listed in the session_id column. The blocking is listed in the blocking_session_id column.
Execute the following to view wait stats for all block processes on SQL Server:

USE [master]
GO
SELECT   w.session_id
 ,w.wait_duration_ms
 ,w.wait_type
 ,w.blocking_session_id
 ,w.resource_description
 ,s.program_name
 ,t.text
 ,t.dbid
 ,s.cpu_time
 ,s.memory_usage
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
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
WHERE s.is_user_process = 1
GO

This detail is good for a big picture, or to get a quick idea of the types of waits occurring, but most of the real diagnostics and tuning will occur at a statement level.

4) SQL Server Profiler

You use the SQL Server Profiler Locks event category to create a trace of events related to locks and deadlocks. You can choose one or more of these event classes:

1. Deadlock_Graph_Event_Class — Creates an XML description of deadlocks.
2. Lock:Acquired — Use in conjunction with Lock:Released to determine the types of locks being requested and the length of time they are retained.
3. Lock:Cancel — Use to determine which locks are cancelled.
4. Lock:Deadlock Chain — Use to determine the objects involved in a deadlock.
5. Lock:Deadlock — Use to determine the objects and applications involved in a deadlock.
6. Lock:Escalation — Reports information about locks that have been escalated to cover a larger resource. For example, when a row lock becomes a table lock.
7. Lock:Released — Use in conjunction with Lock:Acquired.
8. Lock:Timeout(timeout>0) — Provides information about locks that have timed out due to blocking issues.
9. Lock:Timeout — Provides the same information as Lock:Timeout (timeout>0), but includes timeouts where the duration was 0.

5) sp_who/sp_who2

Both sp_who/sp_who2 return information about all the sessions that are currently established in the database and these are denoted as spid's. Both these store procedures accepts parameters. The blk column of sp_who and blkbycolumn of sp_who2 contains the spid for blocking process. Running sp_who and sp_who2 is easy, for example following call of these procedures returns all process that are currently active on SQL Server:

USE master;
GO
EXEC sp_who 'active';
GO
EXEC sp_who2 'active';
GO

6) Use KILL statement to terminate blocked process

You use the KILL statement to view the status of a process or kill the process. The KILL statement has the syntax: KILL spid | UOW [WITH STATUSONLY]

USE master;
GO
KILL spid | UOW [WITH STATUSONLY]
GO

You must pass either a spid or, if the process belongs to a Distributed Transaction Coordination (DTC) transaction, you must provide a Unit of Work (UOW). You must be a member of sysadmin or processadmin to kill a process. You can obtain the spid for the current session by running @@spid. You can obtain the spid for the sessions associated with a login by running sp_who2. If you don’t specify a login, sp_who2 returns information for all current connections. If you can’t kill a blocking process, you might have to restart the SQL Server service. Doing so will cause all current connections to close, so you should avoid restarting the service if possible.

When to use each tool?

The dynamic management views and Activity Monitor are good tools for learning the current state of a process and for resolving a current blocked process. System Monitor counters are ideal for identifying trends that indicate a stored procedure, application, or database configuration setting is causes a large number of blocked processes or timeouts due to blocking. Running a SQL Server Profiler trace is a good way to analyse detailed information and identify the cause of a large number of blocks.

SQL server: Blocking issue

When I was working with couple of frequent blocking issue in our live server, I have noticed different types of wait resources which are not meaningful unless they mapped to a table or an index. In this post I will explain how to map the wait resources to table/index.

Key Wait Resource 

Below is a sample of blocked process report with Key as wait resource .

The first part of the key wait resource is the database id and second part is called as Hobt_Id. Hobt is an acronym for Heap Or B Tree. The hobt_id can be mapped to sys.indexes and sys.objects through sys.partitions. Below script will map the key wait resource to corresponding index and table.

SELECT 
 o.name AS TableName, 
i.name AS IndexName,
SCHEMA_NAME(o.schema_id) AS SchemaName
FROM sys.partitions p JOIN sys.objects o ON p.OBJECT_ID = o.OBJECT_ID 
JOIN sys.indexes i ON p.OBJECT_ID = i.OBJECT_ID  AND p.index_id = i.index_id 
WHERE p.hobt_id = 72057594040811520

Page Wait Resource 

Below is a sample of blocked process report with Page as wait resource .

As everyone knows, the first part of the wait resource is the database id (68)  , second part is file id (1)  and third part is the page number(492478). To find out the object id associated with this page follow the below steps.

SELECT DB_NAME(68)

Select the database based on the output above statement. Then execute the below statement

DBCC traceon (3604)

GO
DBCC page (68, 1, 492478) --Database_id,file_id,page_id


This will give a result as given below.

The object id can be mapped to a table using  the system function  object_name()

Object Wait Resource 

Below is a sample of blocked process report with Object as wait resource .

Here also the first part of the wait resource is the database id(68) ,second part is the object id and third part is known as lock partition id. The object id part can be mapped to an object using the object_name() system function . The lock partition id is not very useful in troubleshooting the blocking issue.This will have positive value only when the server has more than 16 CPU. I will explain more about the lock partition in my  future post.