More on SQL Server Locking
SQL Server Lock Modes
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.)
- Exclusive—An exclusive lock is granted for data modifications to make sure only one process can modify data at a time
- Schema—A schema lock is granted when a process must be able to rely on a consistent schema during its operations
- Shared—A shared lock is granted for read operations and contributes minimally to blocking
- Update—An update lock is granted for updates and tends to be used for updates that happen in multiple steps
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.
SQL Server Lock Compatibility
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
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.
Lock Hierarchy
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
Is SQL Server Blocking Bad?
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.
How Do I Detect Problematic Blocking?
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.
Querying Blocked Requests
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:
- It shows a single moment in time, which won't help you understand blocks you might have missed when you weren't looking.
- It only shows the request SPID and the blocking SPID. The root cause of the problem is at the head of the blocking chain. You'll have a hard time piecing the chain together from these results. The risk of overlooking the actual problematic process is high.
Monitoring Continuously for SQL Server Blocking
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.
Monitoring SQL Server Blocking with SQL Sentry
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.
Ready to start a trial?
