Everything You Need to Know About SQL Server Blocking
Blocking occurs in lock-based concurrency systems when processes require access to resources. Depending on the type of access needed, a logical "lock" is granted to a process. This lock tells competing processes to wait. The process or processes that are waiting for the lock are "blocked."
SQL Server is a lock-based concurrency system. Therefore, SQL Server blocking is by design, integral to maintaining data integrity and process concurrency, and completely normal and expected.
Locks in SQL Server have a sophisticated design. The design allows for greater process concurrency because not all types of lock will block all other types of lock. Locks designate a mode that tells a process the type of access it has to the resource. The lock mode determines how restrictive the lock is to processes and, ultimately, how sensitive the process might be to causing blocks.
Common lock modes are listed in the following table. (See Microsoft's documentation for a complete listing.)
Intent is a modifier lock mode. You will often see a lock mode of IX or IS, for example. A lock mode modified with "I" indicates an intent lock. This is a performance optimization allowing SQL Server to evaluate potential lock compatibility issues faster. A more detailed explanation of intent locks can be found in this blog post.
Lock compatibility refers to whether one lock mode can be granted in relation to another lock mode that is already granted. Lock modes that are not compatible will result in blocking.
The table below can help you determine which lock types might block other lock types. It doesn't cover all lock modes, but it does include lock modes you will encounter regularly.
Credit: Microsoft SQL Server Documentation
Lock escalation is a mechanism used by SQL Server. It takes lots of low-level locks and moves them to fewer locks higher in the lock hierarchy. Lock escalation conserves memory used by the lock manager. It reduces memory requirements by tracking fewer locks on larger scope objects versus many locks smaller scope objects.
The lock hierarchy in SQL Server is fairly simple. At the highest level, we have database locks. At the lowest level, we have row locks. If you've heard of latches, put that out of your mind for the moment. Locks and latches serve a distantly similar purpose, but they are far from being the same.
The lock hierarchy for SQL Server from top to bottom
SQL Server will default to low-level row or page locks. Once the lock manager has numerous locks (~5,000) on the same object, lock escalation consolidates the low-level locks to a table lock. Row locks will not escalate to page locks. Also, note that locks will escalate if they are exclusive (X) or intent exclusive (IX).
Lock escalation can be modified for a server using trace flag 1211. This disables lock escalation for the server. A better option for modifying lock escalation was provided for the ALTER TABLE statement:
ALTER TABLE <table_name> SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )
Modifying lock escalation should be done cautiously and with explicit purpose. Disabling lock escalation could result in a lot of wasted memory, especially for high-throughput transactional applications.
Blocking is completely normal in SQL Server. You will typically be unaware of the many short blocks happening all the time. Sometimes, blocks take longer than expected to resolve. Blocks of longer duration can create chains, where a blocked process blocks additional processes and so on. This type of blocking scenario is problematic. So, the answer is yes and no. Blocking is expected, but it can also become a problem.
The first step is determining what you consider to be a problematic block. Basing this on block duration is typical. If you are unsure, somewhere between 5 and 15 seconds is a good starting point. Going over 30 seconds is not recommended because the default command timeout for many client frameworks is 30 seconds. If the command times out before the block is detected, you might not know whether the block under it has impacted application users.
Once you've decided on a duration filter, you can look for blocks with higher duration using SQL Server DMVs. Below is a simple query using the sys.dm_exec_requests DMV:
declare @durationInSeconds float = 5;
select
der.session_id,
der.blocking_session_id,
der.wait_type,
der.wait_time
from
sys.dm_exec_requests der
where
der.wait_time >= (@durationInSeconds * 1000)
and der.blocking_session_id != 0;
This query provides a list of blocked SPIDs, the SPID that is blocking, the current wait type, and the wait duration in MS. It filters results by a duration you set in the parameter defined at the beginning.
This query falls short in two major ways:
Solving the shortcomings of the DMV query would lead to continuous monitoring for blocks. This could take a simple form, where you schedule a job to save the results of a DMV query into a table. It could also be a third-party database performance monitoring solution, such as SolarWinds SQL Sentry. For instance, SQL Sentry automatically collects problematic blocking information, maintains ongoing history of blocking details, and presents blocking chains visually for analysis.
There are a few common reasons often found at the root of problematic SQL Server blocking.
To address the first two reasons, you need to start by discovering the queries at the head of the blocking chain. You'll get this from your monitoring platform or analyzing DMVs. Once you have the queries involved, you can improve the blocking situation by performance tuning those queries. Performance tuning queries is outside the scope of this article, but SQL Sentry Plan Explorer is a good place to start.
For abandoned open transactions, you'll need to discover the SPID holding the transaction open. Querying the DMV sys.dm_tran_active_transactions can help with that. From there, your options are limited. If you are able to track down and take control of the query, you can issue a rollback. If not, you will likely need to use the T-SQL kill command to end the process.
SQL Server blocking doesn’t have to be difficult to track down and resolve. SQL Sentry offers block analysis that captures all SQL blocking details based on the configured blocking duration thresholds. You can view blocking chains either in real-time or historically. Information such as the executed statement, login, host, and database are provided for every process involved. You can also see wait types as well as wait resources. View blocks right when they occur and kill blocking processes directly from the SQL Sentry client with the click of a button.
SQL Sentry maintains history for Top SQL, blocking SQL, and deadlock data. Accessing readily available historic performance data makes troubleshooting blocking easier by painting a clear picture of what has occurred. You can even configure actions to respond to various blocking SQL conditions. When problematic blocking occurs, you are alerted to the situation, and a specified action is taken based on your preferences.
SQL Sentry displays blocks in a hierarchical view that shows the relationships of all blocking and blocked SPIDs in a SQL Server blocking chain. Color-coded nodes displayed with the blocking statement let you know if a statement is still running, has completed, or if it is blocked.
You can also view blocks in the SQL Sentry Event Calendar side-by-side with other events. SQL Sentry offers incredible functionality to help resolve SQL Server blocking issues and can help you get to the root of problems quickly and efficiently.