DEV Community: Carl Walker

How to Detect and Resolve Blocking Sessions in SQL Server

Carl Walker — Tue, 10 Mar 2026 09:59:48 +0000

If you've ever worked with SQL Server in a production environment, you've probably encountered a situation where queries suddenly slow down or applications become unresponsive. One of the most common causes of this issue is blocking.

Blocking occurs when one query holds a lock on a resource and prevents another query from accessing that same resource. While locking is a normal part of how SQL Server maintains data consistency, excessive blocking can seriously affect database performance.

In this article, we’ll explore:

What blocking is in SQL Server
How to detect blocking sessions
How to identify the blocking query
Ways to resolve and prevent blocking issues

What is Blocking in SQL Server?

Blocking happens when a transaction locks a resource (such as a table or row), and another transaction needs access to that same resource.

For example:

Transaction A updates a row in a table
Transaction B tries to read or update the same row
Transaction B must wait until Transaction A completes

If Transaction A takes too long to finish, other queries start piling up behind it.

In production systems, this can cause:

slow application performance
timeouts
long query wait times
frustrated users

How to Detect Blocking Sessions

SQL Server provides several Dynamic Management Views (DMVs) that help identify blocking activity.

One of the easiest ways is to query sys.dm_exec_requests.

SELECT
    session_id,
    blocking_session_id,
    wait_type,
    wait_time,
    wait_resource,
    status
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

What this query shows

session_id → the waiting session
blocking_session_id → the session causing the block
wait_type → the type of wait occurring
wait_time → how long the session has been waiting

If the blocking_session_id column contains a value, that means the session is being blocked by another process.

Identifying the Blocking Query

Once you find the blocking session ID, the next step is to determine which query is causing the block.

You can retrieve the query text using the following query:

SELECT
    r.session_id,
    r.blocking_session_id,
    t.text AS query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id = <blocking_session_id>;

Replace with the session ID returned from the previous query.

This will show the SQL statement responsible for holding the lock.

Using sp_who2 for Quick Monitoring

SQL Server also includes a useful built-in stored procedure called sp_who2 that provides a quick overview of active sessions.

EXEC sp_who2;

Look at the BlkBy column.

If a session shows a value in this column, it means it is being blocked by another session.

Although it's an older diagnostic tool, many DBAs still use it for quick troubleshooting.

Resolving Blocking Sessions

Once you identify the blocking session, you have several options.

1. Investigate the query

Check if the query is performing unnecessary operations such as:

table scans
missing indexes
large transactions

Optimizing the query may resolve the issue without terminating it.

2. Commit or rollback the transaction

Sometimes the blocking occurs because a transaction was left open longer than expected.

Completing the transaction will release the lock.

3. Terminate the blocking session

If the blocking query is causing major disruption, you may need to stop it using the KILL command.

KILL <session_id>;

Be cautious when using this command because SQL Server must roll back the transaction before releasing the locks.

Monitoring Long-Running Queries

In many cases, blocking occurs because a query has been running for too long.

If you need to identify and terminate long-running queries, you can follow this guide: How to Find and Kill Long-Running Queries in SQL Server This helps DBAs quickly detect problematic queries before they start blocking other sessions.

Best Practices to Prevent Blocking

While blocking can't always be avoided, good database practices can reduce its frequency.

Keep transactions short
Long transactions hold locks longer and increase the chance of blocking.

Create proper indexes
Missing indexes often force SQL Server to scan entire tables.

Avoid unnecessary locks
Design queries that access only the data they need.

Monitor database activity regularly
Routine monitoring helps detect performance issues early.

Final Thoughts

Blocking is a normal behavior in SQL Server, but when it becomes excessive, it can significantly affect database performance.

By using SQL Server’s built-in monitoring tools and DMVs, DBAs can quickly:

detect blocking sessions
identify problematic queries
resolve issues before they impact users

Understanding how blocking works and how to troubleshoot it is an essential skill for anyone managing SQL Server environments.

How to Find and Kill Long-Running Queries in SQL Server

Carl Walker — Sun, 08 Mar 2026 14:14:18 +0000

Anyone who works with SQL Server long enough eventually faces the same problem: a query that just keeps running.

Maybe it's blocking other sessions, maybe CPU usage spikes, or maybe an application suddenly becomes slow. Long-running queries are one of the most common causes of SQL Server performance issues.

In this article, we'll walk through how to:

Identify long-running queries
Detect blocking sessions
Investigate the query details
Safely terminate problematic queries

Let’s get started.

Why Long-Running Queries Are a Problem

A query running longer than expected can cause several issues:

Block other queries
Consume excessive CPU or memory
Lock critical database resources
Slow down applications

In high-traffic environments, even a single poorly optimized query can impact multiple users.

That's why knowing how to quickly identify and stop problematic queries is essential.

1. Finding Long-Running Queries

SQL Server provides several Dynamic Management Views (DMVs) that help monitor currently executing queries.

One of the most useful views is sys.dm_exec_requests.

Run the following query to find active sessions and their execution time:

SELECT
    session_id,
    status,
    start_time,
    command,
    cpu_time,
    total_elapsed_time,
    blocking_session_id
FROM sys.dm_exec_requests
WHERE session_id > 50
ORDER BY total_elapsed_time DESC;

What this shows

session_id → the connection ID
total_elapsed_time → how long the query has been running
blocking_session_id → identifies blocking sessions
status → running, suspended, etc.

Queries with high elapsed time may require investigation.

2. Viewing the Actual Query Text

Once you identify a suspicious session, you’ll probably want to see the SQL statement being executed.

You can retrieve the query text using sys.dm_exec_sql_text.

SELECT
    r.session_id,
    r.status,
    r.start_time,
    r.cpu_time,
    r.total_elapsed_time,
    t.text AS query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id > 50
ORDER BY r.total_elapsed_time DESC;

3. Detecting Blocking Queries

Sometimes the problem isn’t just a slow query, it's a blocking query.

Blocking occurs when one query holds a lock that prevents other queries from accessing the same data.

To find blocking sessions:

SELECT
    session_id,
    blocking_session_id,
    wait_type,
    wait_time,
    wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

If you see a session ID in the blocking_session_id column, it means another process is waiting on it.

In these cases, resolving the blocking query becomes a priority.

4. Using sp_who2 for Quick Monitoring

SQL Server also includes a helpful system stored procedure called sp_who2.

EXEC sp_who2;

This shows:

Active sessions
CPU usage
Disk IO
Blocking sessions

Look at the BlkBy column to identify blocking queries.

Although it’s an older tool, it’s still very useful for quick troubleshooting.

5. Killing a Long-Running Query

If a query is causing serious issues, you may need to terminate it.

To stop a session, use the KILL command.

Example:

KILL 78;

Where 78 is the session ID.

Important

When you kill a query, SQL Server rolls back the transaction. If the transaction was large, the rollback may take some time.

You can monitor rollback progress with:

KILL 78 WITH STATUSONLY;

6. Preventing Long-Running Queries

Stopping a query is only a short-term fix. Ideally, you should prevent performance issues before they occur.

Some common strategies include:

Add proper indexes

Missing indexes often lead to table scans and slow queries.

*Avoid SELECT **

Only retrieve the columns you actually need.

Monitor execution plans

Execution plans reveal inefficient query operations.

Use query timeouts

Applications should have limits to prevent runaway queries.

Conclusion

Long-running queries are inevitable in most SQL Server environments, but knowing how to quickly identify and resolve them can prevent serious performance issues.

The key steps are:

Identify the running query
Investigate the SQL statement
Detect blocking sessions
Kill the query if necessary
Optimize the query to prevent recurrence

SQL Server’s DMVs provide powerful insight into what's happening inside your database, and learning to use them effectively can make troubleshooting much easier.