How to Check Blocking Queries

1. Introduction

In this tutorial, we’ll look at SQL blocking queries in-depth. We’ll use our University schema and SQL Server 2022 as our main SQL dialect.

SQL Server offers two ways to monitor blocking behavior:

1. Using the SP_WHO2 stored procedure
2. Via an SQL query

2. Blocking in Databases

All SQL queries run under a session. We define a blocking session as holding a lock on a particular resource, which prevents other sessions from acquiring a conflicting lock type on that same resource.

Any blocked session must wait for the corresponding blocking session to complete its execution and release its lock on the resource.

Blocking queries can significantly impair database performance and business operations in many ways. For example, they can decrease throughput as queries take more than the normal time to complete. As a result, we see non-realtime user experience, application slowdowns, and poor business flow. Also, as database usage increases, a higher volume of concurrent transactions means longer execution times and increased disk I/O.

Thus, all these reasons motivate us to minimize blocking behavior in our database systems.

3. Causes

Many things can contribute to blocking behavior.

First, long-running queries cause a significant chunk of blocking of a database session. A long-running query holds its locks for the entire duration. Thus, it blocks other processes.

Second, we block a DB session when we cancel or kill a query. This is so because a process gets in a rollback state when we cancel or kill a query (say, a client restart). In such a case, the database must undergo a rollback process to undo any data modifications. During this (potentially time-consuming) rollback, the query’s statements continue to hold locks, which block other processes.

Another cause of blocking is when we set isolation levels for our transactions without thorough consideration. Any transaction isolation level other than Read Uncommitted also adds to blocking. For example, the database internally uses the default Read Committed level during read operations to prevent dirty reads. However, this leads to blocking if multiple transactions attempt to access the same data concurrently.

Higher isolation levels, such as Repeatable Read and Serializable, hold shared locks for the entire transaction duration. Thus, it increases their potential to cause blocking.

4. SP_WHO2

Let’s check how SP_WHO2 helps detect blocking.

In the first session (spid=56), we run an UPDATE query on the Course table to set the credits of a particular course:

BEGIN TRAN;
UPDATE Course
SET credits = 8
WHERE id = 'CS111';

In our second session (spid=58), we run the same query with a different credit value. Since we didn’t commit or rollback the previous query, our second session blocks:

BEGIN TRAN;
UPDATE Course
SET credits = 9
WHERE id = 'CS111';

We now open a third session to execute this stored procedure to determine the source of blocking:

EXEC sp_who2;

And we can see that the second session is in the SUSPENDED state:

SPID  Status          BlkBy   Command         
----- -----------     ------- -----------------
56    sleeping        -        AWAITING COMMAND
58    SUSPENDED       56       UPDATE

The field blkby shows that the first session (spid=56) is its blocker.

5. DMV Query

Now we’ll explore how to solve the same problem using queries.

SQL Server offers a framework called DMV, or dynamic management view. It provides granular information about the sessions, locks, and resources.

Let’s open a fourth session to run this query to know the reason and source of blocking of our second session:

SELECT
    blocked.session_id,
    blocked.status,
    blocked.wait_type,
    blocking.session_id AS blocking_session_id,
    blocking.status AS blocking_status,
    blocked.command
FROM sys.dm_exec_requests AS blocked
INNER JOIN sys.dm_exec_sessions AS blocking ON blocked.blocking_session_id = blocking.session_id
LEFT JOIN sys.dm_exec_connections AS conn_blocked ON blocked.session_id = conn_blocked.session_id
LEFT JOIN sys.dm_exec_connections AS conn_blocking ON blocked.blocking_session_id = conn_blocking.session_id
OUTER APPLY sys.dm_exec_sql_text(conn_blocked.most_recent_sql_handle) AS blocked_sql
OUTER APPLY sys.dm_exec_sql_text(conn_blocking.most_recent_sql_handle) AS blocking_sql
WHERE blocked.blocking_session_id <> 0;

The output shows that our second session (spid=58) is suspended by our first session (56), which holds a lock due to the update command:

session_id  status       wait_type   blocking_session_id   blocking_status   command
----------  -----------  ----------- --------------------  ----------------  --------
58          suspended    LCK_M_X     56                    sleeping          UPDATE

We can find the user who has blocked the session via attribute blocking.login_name AS blocking_login. Further, we can find the blocking command using the construct blocking_sql.text AS blocking_query_text.

6. Blocking Termination

We can resolve the blocking behavior by either:

1. Rolling back the blocking session
2. Committing the blocking session, or
3. Terminating the blocking session

To terminate our blocking session (spid=56), we use the KILL command:

KILL 56;

The SQL Server immediately terminates the specified session and initiates a rollback of any open transactions the killed session was running. As part of the rollback, all locks held by the session will be released, unblocking our second session.

7. Comparison

Let’s compare both solutions:

DMVs are more flexible than SP_WHO2 because of two main reasons.

First, DMVs have customizable queries offering relational views. We can filter, join, and aggregate data in DMVs. On the other hand, SP_WHO2 offers a fixed output with making modifications very difficult.

Second, DMVs offer high level of detail such as such as blocking SQL query text, specific locks held (for example, LCK_M_S and LCK_M_X), lock duration, lock mode (S, X, U) and locked resource type (key, page, object). In contrast, SP_WHO2 offers minimal details (session id of blocker and blocker command).

8. Best Practices

The most effective strategy for managing blocking in SQL Server is proactive prevention. Although identifying and addressing existing blocking is crucial, implementing measures to minimize the occurrence and duration of locks is much more impactful for maintaining a healthy, high-concurrency SQL Server environment.

8.1. Transaction Management

We should minimize the duration of each transaction by keeping them short and simple. We can do this by splitting every long transaction into smaller units. This approach significantly reduces the lock held duration there by decreasing the blocking window.

Another useful practice is to avoid taking user input within a transaction. User can delay and this often increases lock held duration leading to blocking.  

8.2. Indexing Strategies

Our SQL Server should have properly defined indexes.

We should handle related indexes (clustering) and non-related indexes (singleton) indexes separately. This separation reduces logical reads, and minimizes the lock held duration.

All this leads to efficient data access by reducing the need for resource-intensive full table scans that acquire broader, more restrictive locks.

8.3. Query Optimization

We can use the construct sys.dm_exec_query_plan to identify performance bottlenecks and optimize inefficient queries. This reduces the execution time of resource-intensive queries.

Second, we should avoid SELECT * when we don’t really need all columns of a table. This way, we reduce the amount of data read from disk and transferred over the network, thereby minimizing I/O and memory usage.

8.4. Application Design

A good practice for making our application robust and deterministic is to implement retry logic to overcome transient errors, such as deadlocks or lock timeouts.

This allows the application to gracefully handle temporary blocking situations without crashing, improving overall system resilience.

9. Conclusion

In this article, we studied blocking queries in depth by looking at the SP_WHO2 and SQL queries. We further showed that via queries, we can trace complex blocking chains, pinpoint head blockers, and uncover the precise root causes of concurrency issues. Then, we gave best practices to avoid blocking.

By adopting these best practices and maintaining a proactive stance, we can get minimal blocking and maximal concurrency of our database system. Thus, all this can offer a seamless user experience.