Troubleshoot SQL Server Blocking and Identify Root Causes

Stop guessing and start solving. SolarWinds® SQL Sentry® provides a complete, actionable view of blocking chains and deadlocks to help you find the head blocker and resolve performance problems fast.

Download Free Trial Email Link To Trial

Fully functional for 14 days

Learn More

Monitor SQL Server Blocking

Resolve Bottlenecks

Run Blocking Reports

Gain Visibility

Identify Root Blockers

Find the Root Blocker in Seconds, Not Hours

SQL Server Blocking 0 Features Array Item - features item image

When you've got a performance problem, you first check for blocking. This means manually running Transact-SQL (T-SQL) queries against dynamic management views (DMVs) such as sys.dm_exec_requests or constantly refreshing Activity Monitor in SQL Server Management Studio (SSMS). You're trying to find a blocking_session_id and trace it back. It's like searching for the start of a traffic jam from a helicopter with no radio: you can see a car has stopped, but you don't know why.

SQL Sentry scraps the manual process. It continuously monitors blocking transactions and displays the entire blocking chain in a simple, hierarchical, and color-coded view. You don't only see one server process ID (SPID); you see the whole picture. The head blocker (the root cause) is clearly shown as an orange running node, and all the victim processes blocked by it are in red. You can instantly see the relationships between all SPIDs in the chain, so you can pinpoint the true source of the block in seconds.

Download Free Trial Email Link To Trial

Fully functional for 14 days

Learn More

Analyze the “Why” With Deep Blocking Details

SQL Server Blocking 1 Features Array Item - features item image

Finding the head-blocking SPID is only step one. The real questions are what is it doing and why? Without a tool, you'd have to grab the SPID and then run Database Console Commands (DBCC) INPUTBUFFER or query sys.dm_exec_sql_text to get the SQL statement.

SQL Sentry puts all this vital context in one place. For any block, you can see the full SQL statement text, how long it's been running, the total wait time, the specific wait type (e.g., LCK_M_IX), and the exact wait resource (e.g., a table, page, or key). The grid view also shows you the host, application, database, and login for both the blocker and the blocked sessions. This context is critical. You can immediately see whether a long-running query from a specific application is the culprit or whether a transaction was left open by a user. This data, combined with SQL Sentry Top SQL view for high-impact queries, gives you all you need to understand the root cause, not only the symptom.

Download Free Trial Email Link To Trial

Fully functional for 14 days

Learn More

Go Beyond Real Time With Historical Blocking Analysis

SQL Server Blocking 2 Features Array Item - features item image

The biggest headache with SQL Server blocking is when the problem is intermittent. A user complains, but by the time you log in to SSMS, the block is gone. Activity Monitor and sp_who2 only show you what's happening right now.

SQL Sentry records blocking data, letting you troubleshoot issues after the fact. You can switch the “Blocking SQL” tab to “History” mode, select any time range, and see a complete replay of blocks that happened hours or days ago. This is a game changer for a database administrator (DBA). You can analyze trends using the “Blocked by” column to see if one application, wait resource, or wait type is consistently causing the most pain. This historical data, retained for 15 days by default, lets you move from firefighting to proactively tuning the queries and indexes responsible for chronic concurrency issues.

Download Free Trial Email Link To Trial

Fully functional for 14 days

Learn More

Stop Reacting by Using Proactive Blocking and Deadlock Alerts

SQL Server Blocking 3 Features Array Item - features item image

Don't wait for users to tell you the database is slow. SQL Sentry comes with powerful proactive alerting. You can configure the SQL Server: Blocking SQL condition to fire an alert when any block lasts longer than your specified threshold (the Minimum Block Duration, which defaults to 15 seconds). This is far more advanced than a basic blocked_process report. You can also configure automated responses to specific conditions to prevent alert fatigue.

SQL Sentry excels at capturing and alerting on deadlocks. A deadlock is a deadly embrace in which two processes are waiting on each other, forcing Microsoft SQL Server to kill one of them (the victim) to resolve the issue. SQL Sentry captures the complete deadlock graph XML, shows you the victim and survivor queries, and lets you analyze the chain of events so you can fix the underlying logic and prevent it from happening again.

Download Free Trial Email Link To Trial

Fully functional for 14 days

Learn More

Prevent Recurrence by Optimizing Bad Queries

Analyze MySQL - Analytics Tools for MySQL Database Use case type 1 2 Features Array Item - features item image

Killing a blocking SPID is a temporary fix. To solve the problem permanently, you need to fix the underlying query. When you spot a blocking query in SQL Sentry, you can jump directly into the integrated Plan Explorer to analyze the execution plan. You can see exactly which operators are causing the slowdown, view index scoring, and identify the missing indexes forcing scans and holding locks.

Once you identify the bottleneck, you don't need to waste hours on trial-and-error tuning. The new AI Query Assist helps you instantly generate optimized versions of your SQL. It analyzes your query and provides a side-by-side comparison of the original code and the improved rewrite, allowing you to deploy a permanent fix in minutes, not days.

Download Free Trial Email Link To Trial

Fully functional for 14 days

Learn More

Get More on SQL Server Blocking

Do you find yourself asking…

What's the difference between SQL Server blocking and a deadlock?
How do I manually check for blocking in SSMS?
What are the most common causes of blocking?
How can I troubleshoot SQL Server blocking?
Why is finding blocking in SQL Server important?

Blocking is normal and happens all the time in SQL Server. It occurs when one process (a SPID) is waiting for a resource (such as a row or table) another SPID has locked. Blocking becomes a performance problem only when the wait time is long.

A deadlock is a specific terminal type of blocking. It's a deadly embrace in which SPID 1 is waiting for a resource held by SPID 2, while SPID 2 is waiting for a resource held by SPID 1. Neither can move forward. Microsoft SQL Server automatically detects this, chooses one SPID as the victim, and kills its process, forcing the transaction to roll back.

Once you have identified a problematic block, it’s time to troubleshoot. Common blocking scenarios and solutions you may come across include:

A query with a long execution time: If a query has an excessively long execution time and aggressively locks to prevent other processes from reading or modifying the same data, it can result in problematic SQL Server blocking. To resolve this problem, you’ll need to first locate the SQL Server blocking query by analyzing DMVs or checking your monitoring platform. Then, you can optimize your query and execution plans.
An application using a complex transaction that performs statements or batches before committing: If an application performs multiple statements or batches before committing (saving the data), other processes won’t be able to access the resource during that period. Here, too, you’ll need to identify the query at the head of the blocking chain and then tune its performance.
A transaction is abandoned and isn’t releasing locks: If an application has a query timeout or a cancel, the SPID will terminate the query and batch. However, it won’t automatically roll back or commit the transaction, which could cause blocking. To troubleshoot this problem, you need to identify which SPID is holding the transaction open and either issue a rollback or use the T-SQL kill command.
An SPID whose client application didn’t fetch all result rows to completion: When an application doesn’t immediately fetch all result rows to completion, the locks will be left on the tables, leading to blocking. To fix this problem, you’ll need to rewrite the application to fetch all rows of the result to completion. To avoid this problem in the first place, restrict any poorly behaved applications to a separate reporting or a decision-support database.

The good news is that SQL Sentry can help, no matter what type of blocking scenario you’re up against. SQL Sentry collects and retains blocking and deadlock data. Not only does this give you a comprehensive view of present performance, but it also allows you to see into the past. Plus, you can see blocking issues in real-time and kill blocking processes from SQL Sentry with one click. With SQL Sentry’s help, you can understand why blocks are occurring and redesign your queries and transactions to prevent them in the future.

SQL Sentry can even take automatic action against blocking SQL conditions for times when you aren’t around. All you need to do is pre-configure SQL Sentry to perform specific actions when conditions are met.

Blocking is normal and happens all the time in SQL Server. It occurs when one process (a SPID) is waiting for a resource (such as a row or table) another SPID has locked. Blocking becomes a performance problem only when the wait time is long.
A deadlock is a specific terminal type of blocking. It's a deadly embrace in which SPID 1 is waiting for a resource held by SPID 2, while SPID 2 is waiting for a resource held by SPID 1. Neither can move forward. Microsoft SQL Server automatically detects this, chooses one SPID as the victim, and kills its process, forcing the transaction to roll back.
Problematic blocking is usually caused by locks being held for too long. Common reasons include:

Long-running queries: A big reporting query or a complex stored procedure running for minutes can hold locks, blocking dozens of faster queries
Open transactions: An application (or user) starts a transaction and modifies data but never issues a COMMIT or rollback, so the SPID goes idle while continuing to hold its locks, blocking any other process requiring the same data
Poor indexing: When queries can't efficiently find data, they have to scan entire tables, causing more locks to be held for longer durations
Lock escalation: To save memory, SQL Server sometimes escalates many granular row- or page-level locks into a single exclusive table lock, unexpectedly blocking many other users
Inappropriate transaction isolation level: Using an overly strict level such as SERIALIZABLE when it's not needed can drastically increase locking and reduce concurrency
Once you have identified a problematic block, it’s time to troubleshoot. Common blocking scenarios and solutions you may come across include:
A query with a long execution time: If a query has an excessively long execution time and aggressively locks to prevent other processes from reading or modifying the same data, it can result in problematic SQL Server blocking. To resolve this problem, you’ll need to first locate the SQL Server blocking query by analyzing DMVs or checking your monitoring platform. Then, you can optimize your query and execution plans.
An application using a complex transaction that performs statements or batches before committing: If an application performs multiple statements or batches before committing (saving the data), other processes won’t be able to access the resource during that period. Here, too, you’ll need to identify the query at the head of the blocking chain and then tune its performance.
A transaction is abandoned and isn’t releasing locks: If an application has a query timeout or a cancel, the SPID will terminate the query and batch. However, it won’t automatically roll back or commit the transaction, which could cause blocking. To troubleshoot this problem, you need to identify which SPID is holding the transaction open and either issue a rollback or use the T-SQL kill command.
An SPID whose client application didn’t fetch all result rows to completion: When an application doesn’t immediately fetch all result rows to completion, the locks will be left on the tables, leading to blocking. To fix this problem, you’ll need to rewrite the application to fetch all rows of the result to completion. To avoid this problem in the first place, restrict any poorly behaved applications to a separate reporting or a decision-support database.

The good news is that SQL Sentry can help, no matter what type of blocking scenario you’re up against. SQL Sentry collects and retains blocking and deadlock data. Not only does this give you a comprehensive view of present performance, but it also allows you to see into the past. Plus, you can see blocking issues in real-time and kill blocking processes from SQL Sentry with one click. With SQL Sentry’s help, you can understand why blocks are occurring and redesign your queries and transactions to prevent them in the future.
SQL Sentry can even take automatic action against blocking SQL conditions for times when you aren’t around. All you need to do is pre-configure SQL Sentry to perform specific actions when conditions are met.
It’s essential for database administrators to know how to find blocking in SQL Server because blocking can cut DBAs off from business-critical resources and prevent them from managing their databases efficiently. However, blocking can be complex and time-consuming to troubleshoot. Here are five of the most common blocking causes:
Inefficient SQL statements executed when the database session was locked
Sleeping sessions lost at the transaction nesting level
Applications failing to retrieve all result rows
Sessions rolling back data
Sessions abandoned because the client application crashed or was disconnected

Still, in a large enterprise with even larger databases, there are many more reasons why a session could be blocked. It would be incredibly challenging for a database administrator to get to the top of the blocking chain and uncover these reasons manually.
Standard SQL Server monitoring tools will display database locking and blocking waits, but they won’t identify the root blocker responsible for the blocking chain. Increased blocking equals slower apps, but how can a database administrator determine whether increased blocking is a sign of database inefficiency or a reason for slow app response times?
Tooling like SQL Sentry can help DBAs get to the bottom of a SQL Server blocking chain, resolve blocks quickly, and get back to business.
Finding blocking isn’t only important when blockers are actively affecting database performance. Since locks and blocks are necessary byproducts of working with SQL Server, DBAs must do all they can to proactively monitor blocking issues or fix the code causing idle blockers. A key part of database management best practices is using software designed to help you find blockers even in their beginning stages.

Start Troubleshooting SQL Server Blocking Today

SolarWinds SQL Sentry

Get full visibility into blocking chains and deadlocks with the intuitive SQL Sentry dashboard
Analyze high-impact queries and historical performance data to find the root cause of performance bottlenecks
Set up proactive alerts and automated responses to stop blocking problems before they impact your users

Download Free Trial Email Link To Trial

Fully functional for 14 days

Let's talk it over.

Contact our team. Anytime.