How to identify blocking in SQL Server


SQL Server is a powerful relational database management system used by businesses of all sizes. However, as your database grows, it is not uncommon to experience SQL blocking, which can significantly impact your database's performance. In this blog post, we will discuss what SQL blocking is, how to identify and analyse it, and how to resolve it to keep your database running smoothly.

What is SQL Blocking?

SQL blocking occurs when one query blocks another query from accessing a shared resource, such as a table or page. When a query is blocked, it can't proceed until the resource it needs is available, leading to delays and slowdowns in your database.

Identifying SQL Blocking:

The first step in resolving SQL blocking is identifying it. SQL Server Management Studio (SSMS) provides several tools for identifying blocking, such as the Activity Monitor and the Blocked Process Report.

There are number of ways to find out the details of the spid's involved in blocking

  • sp_who2
  • sys.sysprocesses
  • sys.dm_exec_requests
  • sys.dm_tran_locks and sys.dm_os_waiting_tasks
  • SQL Server Management Studio Activity Monitor
  • SQL Server Management Studio Reports
  • SQL Server Profiler
  • SQL Server Extended Events

in this post we are focusing on TSQL methods

sp_who2 is a system stored procedure that returns information about the current SQL Server processes and sessions, including details such as session ID, login name, database being used, and the command being executed. It is commonly used for troubleshooting and identifying blocking issues in a SQL Server instance.

Below is sample code and a screen shot

USE master
EXEC sp_who2

We can check if there is any server blocking by querying sys.sysprocesses. If the results return no rows, this indicates that there is no active blocking on the server.

Below is sample code and a screen shot

select * from sys.sysprocesses where blocked<>0
blocked statements

Identifying and resolving head blocking in SQL Server is important because it can significantly impact database performance and user experience. Head blocking occurs when a transaction is blocking other transactions, causing them to wait in a queue for resources that are being held by the blocking transaction.

Below is sample code and a screen shot

select loginame, cpu, memusage, physical_io, * 
  from  master..sysprocesses a
 where  exists ( select b.*
    from master..sysprocesses b
    where b.blocked > 0 and
   b.blocked = a.spid ) and not
 exists ( select b.*
     from master..sysprocesses b
    where b.blocked > 0 and
   b.spid = a.spid ) 
order by spid
The blocking tree script is an important tool in SQL Server for troubleshooting and identifying blocking issues. When there is blocking in SQL Server, it is often not a simple one-to-one relationship between the blocked and blocking sessions. Rather, there can be a complex chain of blocking sessions that can be difficult to unravel. Click here to view raw code

Below is sample code and a screen shot

Analysing SQL Blocking:

Once you've identified blocking, you need to analyze it to determine the root cause. This may involve identifying the queries causing the blocking, checking indexes and statistics, or checking for lock escalation.

Resolving SQL Blocking:

There are several ways to resolve SQL blocking, depending on the cause. In some cases, killing the blocking process may be necessary, but this can have unintended consequences, such as rolling back transactions. Other methods for resolving SQL blocking include optimizing queries, using indexes, adjusting transaction isolation levels, and redistributing data.

Preventing SQL Blocking:

The best way to deal with SQL blocking is to prevent it from occurring in the first place. Strategies for preventing SQL blocking include optimizing queries, creating appropriate indexes, and setting transaction isolation levels appropriately.


SQL blocking can be a frustrating issue that impacts your database's performance. However, by understanding how to identify, analyse, and resolve SQL blocking, you can keep your database running smoothly and ensure your users have a positive experience. Remember to also focus on prevention, so you can avoid SQL blocking in the future.

Lince Sebastian

Indian MSSQL DBA thriving in database management - ensuring efficiency and smooth operations. Devoted father of two and avid Clash of Clans player, driven by strategic thinking. Football fuels my passion - cheering Kerala Blasters and Arsenal. I share my professional and life insights through my blog.


إرسال تعليق (0)
أحدث أقدم