Different techniques to identify blocking in SQL Server

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 blkby column 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.

Next Steps

• If you are experiencing significant amount of blocking in your system then you need revisit your database design and change the way how the data in the database is accessed. You may also need to consider the correct ISOLATION LEVEL for your SQL queries, etc.
• Check out these tips to learn more about locking and blocking:
  • Understanding SQL Server Locking
  • Understanding SQL Server Blocking
  • Tip category: Locking and Blocking Tips