What Is SQL Server Blocking?

SQL Server blocking is a normal trait of SQL Server database systems. Blocking occurs when SQL Server is servicing requests from multiple clients and different processes request the same resources at the same time. If a resource is in use, it is locked and unavailable to subsequent requests, so the process is delayed until the resource is released and becomes available again. SQL Server blocks normally resolve themselves without a noticeable effect on performance. However, on occasion, blocks cause performance to degrade either because the initial process locks the resource for an extended period of time before releasing it or because the initial process doesn’t release the resource at all, which makes the resources inaccessible until the block is resolved manually. 

When a set of resources is blocked for a significant amount of time, performance quality decreases to the point that system users begin to notice issues. These issues include things such as queries taking a long time to run or timing out or users being unable to save screens they have modified. 

How to Fix SQL Server Blocks

The short-term solution to SQL Server blocking is to identify and manually unblock the transaction when you notice performance is being negatively impacted by a block or blocks. 

Once you have confirmed the presence of a SQL Server block, check the SQL Server activity monitor and kill the queries that are causing the most trouble. You can also use SQL scripts to continuously monitor the state of locking and blocking on SQL Server. Brent Ozar suggests adding some blocking-specific performance counters so SQL Server will notify you when you have exceeded your designated blocking thresholds.

How to Prevent SQL Server Blocks

It is useful for a database administrator to know how to fix a SQL Server block. But for the sake of maintaining a high-performing system, it’s even better to know how to prevent—or at least minimize—the occurrence of blocking. Here are four tips to minimize some common types of SQL Server blocking:

1. Use indexes

Using clustered indexes in high-usage tables helps reduce blocking by prompting the query optimizer to run an index seek instead of an index scan. When only the record being updated is locked, subsequent processes can circumvent the lock and can be completed.

It’s also important to make sure that UPDATE and DELETE statements use indexes. Indexes help UPDATE and DELETE statements reduce the number of modification locks they acquire using either nonclustered indexes or index hints to remove locks. 

2. Clean up your queries

One best practice for avoiding SQL Server blocks is to break up long transactions into multiple shorter transactions. Long-running queries hold a lock for the duration of the transaction, making the resource unavailable to other processes. Breaking up long transactions into short ones keeps the data moving.

Consider using SQL Server tuning to make queries as efficient as possible. A good query optimizer will write and rewrite queries until they are configured to reduce the number of blocks that can slow performance.

3. Identify the root cause of SQL Server blocking

Tracking performance metrics over time is a great way to avoid SQL Server blocking. By locating which sessions are frequently blocked and what statements are involved, you can determine a course of action to remove the root causes of recurring blocks through indexing, query optimization, or other means.

Another place to look for the root cause of blocking is the system’s workload. Run a workload analysis to determine whether there has been an increase. Too heavy of a workload will cause SQL Server blocking as the growing number of queries pile up waiting for access to locked resources. You may need to add indexes or distribute workload across multiple databases to resolve heavy workload performance issues.

4. Keep your stats up to date

Outdated statistics may trigger inefficient query plans to run because the plan wasn’t updated to account for changes. SQL Server will launch search crawls to look for moved or missing data, which will in turn create blocks.

The most effective way to keep stats current is to automate as many updates as possible. Where you aren’t able to schedule auto updates, be sure to add manual updates to your scheduled maintenance checklist to ensure the updates are done regularly.

SQL Server blocking may be a fact of DBA life, but there are ways to mitigate its impact on your SQL Server database performance. Break the block-unblock cycle by implementing some of the strategies discussed above. With a little planning, optimization, and automation, it’s possible to reduce the occurence of SQL Server blocking—and, in some circumstances, to eradicate it altogether.