SQL blocking chains in SQL Server

SQL blocking means that one or multiple SQL queries are waiting for resources that are locked by another SQL Query. SQL blocking can affect data throughput and SQL Server responsiveness significantly. SQL Server response time is a measure that shows how long the end user that has executed a query must wait until he gets a response. So, in case that a query is doing nothing while waiting on another query to release the lock on the target resource, the response time increases thus making the SQL Server less responsive.

At the other hand, when talking about the data throughput, the measure is the number of simultaneous users who are logged and execute their queries in parallel without slowing down the SQL Server. Moreover, this is the SQL Server performance parameter that could be severely affected when blocking chain occurs. In scenarios were complex blocking chains occurs where one SQL query blocks another, and that query then blocks the third one, and the third one blocks the fourth one, and so on the blocking of the critical SQL Server resources for a prolonged time could occur. That creates a sort of artificial serialization in accessing key resources to the level where only one or eventually two users are effectively using SQL Server at one time.

Both of these scenarios are something that should be avoided and prevented.

SQL Server blocking is hard to identify and troubleshoot as it is hard to predict such behavior during development when only a limited number of users, quite often only one or two, are executing and testing the queries against the SQL Server at the same time. It is even worse to predict the situations where a single rarely executed query can cause a lengthy and complex blocking chain, making the troubleshooting process of bad SQL Server performance caused by such a rare and complex blocking chain annoying and often without results.

As it can be seen in the article, establishing the SQL Server blocks monitoring and tracking of even simple SQL Server blocks is not an easy and straightforward process. Even when the significant time is invested, results could be vague in most cases.

The situation where blocking chain is the cause of the issue is even more complicated for monitoring and troubleshooting. It’s quite tough to pinpoint the head blocker of the blocking chain when there are a number of processes involved. Moreover, if we add the possibility that deadlocks could be involved in the blocking chain as well, there could be quite a mess that has to be resolved and tracked.

Just as an example of the blocking chain, the scenario where a chain of the three SQL blocking processes will be simulated. Simulating such a blocking chain requires four separate connections to the SQL Server database to be created. That means that each query must be executed from a separate query tab in the SQL Server Management Studio. The Adventure Works database will be used in the blocking chain example.

SSMS tab1:

In this window, the query will start a transaction that will select from the database table and then for the purpose of easier simulation the waitfor delay command will be executed to create the 15 seconds wait for more comfortable work and unambiguous results presentation. The query will force the locks just for the purpose of the presentation. Otherwise, that is not recommended without a very particular reason.

begin tran

select * from [Person].[Person] with (tablock, holdlock, xlock)
waitfor delay '00:00:15'

commit tran

SSMS tab2:

The query in tab 2 should be executed immediately after the previous query in tab 1. The transaction will try to select from the same table, and that will conflict with the transaction in the tab1 that holds the lock.

select * from [Person].[Person]

SSMS tab3:

Finally, in tab 3 the select query will be executed after the tab 2 query, and it will conflict with the query in the tab 2.

select * from [Person].[Person]

SSMS tab4:

This query in tab 4 is designed to display the active SQL blocking and should be executed after the query in tab 3 is executed. In our example, SQL Server used for this example had a very few active processes, so we were able to get insight straight into results.

Select Session_ID,Blocking_Session_ID
 From Sys.DM_Exec_Requests
 Where Session_ID > 50
 And Blocking_Session_Id <> 0

Results displayed in the SSMS

What we can see from this result is that session 65 is waiting for session 56 to release the lock it holds on the resource, and it can be seen that session 56 is waiting for session 55 to release the acquired lock. Moreover, it is evident that session 52 is not waiting for anything, so one could assume that session 52 is the root cause of the SQL blocking or the so-called head blocker.

While this could look like a simple to troubleshoot, it is not the case in the real world. Production servers might have hundreds or thousands processes where numerous blocks could occur and blocking chains could reach tens of levels. Analyzing such results manually and figuring out relations and connections between processes cod take a lot of time and are prone to mistakes or misinterpretations.

At the top of that, this is not the information that is stored somewhere, so it doesn’t allow any insight into the history and frequency of SQL blockings and blocking chains.

Use ApexSQL Monitor to resolve SQL blocking

ApexSQL Monitor SQL blocking and blocking chain tracks are parts of the Query waits feature, or to better say the sub-features of the Query wait feature.

To access the blocking chain section of the Query waits feature:

  1. Select the SQL Server that should be checked for the blocking chain in the server explorer pane on the left
  2. From the Instance dashboard, select the Waits link in the Query section

    Open the Query waits page via the ApexSQL Monitor instance dashboard

  3. In the Query waits page, select the Blocking chain radio button

    Select the Blocking chain radio button in the Qurt waits page to see the all head blockers

  4. If SQL blocking exists in the selected SQL Server, the information about the blocking chains displays

ApexSQL Monitor displays each SQL Server block in a hierarchical form that allows displaying the relations between all blocked and blockers SPIDs in a SQL blocking chain. The head blockers are displayed by default as they consist of the top nodes in the blocking chain hierarchy. The blocked SPIDs are displayed as parts of the sub-nodes in the blocking chain.

To check the blocked SPIDs, expand the blocking chain using the arrow icon left of the head blocker grid row:

Expand the head blocker to see the related queries that are blocked

The blocked SPIDs are nested underneath the head blocker, and any subsequent SPID is nested beneath its blocker in the chain, and it can be drilled down the chain by expanding any new subsequent SQL blocking where exists. Such an approach ensures the complete insight in the blocking chain at a first glance.

For each blocker and blocked query, ApexSQL Monitor allows reviewing details about that particular query by expanding the table via the arrow icon on the far right of the row.

Check the detailed description of head blocker and their blocked queries

The information in the Query details tab contains information about the query involved in the blocking chain that includes the wait type involved, wait time as well as the wait resource.

As SQL blocking is a situation where a process is forced to wait another process to release the acquired lock, SQL Server logs that via the LCK_M_XXX wait type. The adequate wait type will be displayed, in accordance with what lock type blocked queries are waiting to acquire. Click the wait type link to display the helper pop up window with details related to that wait type as well as advice on potential resolution and links to external resources where some additional information about the involved wait type could be researched.

The helper window with detailed description of the selected query wait type, suggested solutions and links for additional research

The SQL Text tab contains the full T-SQL of the query.

View the full T-SQL for the query that is part of the blocking chain

An additional ability of the block chain feature is that it allows the user to decide whether to display or not the blocking chains where the deadlock as a unique form of SQL blocking is involved. Unticking the Deadlock check box displays only the blocking chains where deadlocks are not involved. By default, the ApexSQL monitor displays all blocking chains including the ones where the deadlock is involved. In addition, each such blocking chain is marked with red DEADLOCK marking, as all blocking chains where the deadlocks are involved requires the special attention.

Turn on/off displaying of blocking chains that contain deadlocks

Quick tip:

The information about the deadlock involvement and ability to filter out blocking chains where deadlock is involved is possible only if the deadlock monitoring is turned on. To learn how to enable deadlock monitoring and track the deadlocks, read How to configure and use deadlock monitoring

Another way to access the blocking chain of interest is via the Blocked section of the Query waits page. While analyzing the blocked queries, if there is a need for a particular query to get details about its involvement in the blocking chain and how it is involved, use the Blocking chain link in the details of that blocked query details.

Use the Blocking chain linlk from the Blocked section query details to display the specific blocking chain related to that particular query execution

That will drill down to the specific blocking chain in the Blocking chain section that involves that particular SQL blocking situation.

The specific blocking chain displays when opened via the Blocking details link in the blocked query details section

In this way, only the particular blocking chain related to that particular SQL blocking will be displayed. That could be very useful for reducing the noise that other blocking chains could cause when analysis of the particular blocking chain situation is required.

 

March 4, 2019