Esat Erkec
Usage of the sys.dm_tran_locks

Monitoring SQL Server blocking problems

October 12, 2021 by

In this article, we will learn how to monitor SQL Server blocking issues with different methods.

How does a blocking occur in SQL Server?

In relational databases, numerous user transactions are processed concurrently. Under this circumstance, different transactions accessing the same table records simultaneously is an inevitable situation. SQL Server uses the lock mechanism to protect the data integrity because a resource can be modified by different transactions at the same time. The locking mechanism isn’t a problem, it is a methodology to provide data integrity. However, the locks can cause a problematic situation when a resource is locked and other processes start waiting for that resource for a long time.

The database engine decides which resources should be accessed during the execution of a statement. After this decision, the Lock Manager enters the game and decides the appropriate lock type and granularity of locks (row, page, and table) according to the type of operation being performed and the amount of data that will be affected. Based on the executed statement and isolation level, the SQL Server Lock Manager can choose different types of locks for the resources. Blocking is a state of wait that starts when a resource or set of resources has acquired lock by one process and then another process wants to lock the same resources. In a case like this, the second process starts to wait until the release of the locked objects. Assume that in the default isolation level the User-Alfa updates some rows of the SalesPerson table of the Adventureworks database. User-Alfa’s update statement acquires an exclusive lock to the changing resources.

After executing this query, we can check the locked resources in the Adventureworks database with help of the following query. This query includes sys.dm_tran_locks dynamic management view and this view returns information about the currently active lock manager resources in SQL Server.

Usage of the sys.dm_tran_locks

In the default isolation mode (read committed), the read operations acquire shared (S) lock to the resources. Now, User-Beta wants to read the entire table, so the read operation will need to read the updated rows as well but these rows have already been locked by the User-Alfa. In this case, the User-Beta has to wait 40 seconds to release the resources by the User-Alfa because exclusive and shared lock types conflict.

After this simple example, let’s look at the different methods that help to monitor the SQL Server blocking issues.

Using SQL Server Lock Wait Types to monitor SQL Server blocking problems

Wait types are one of the main performance indicators of SQL Server and are very helpful to determine the reason for the performance issues.

The wait types which are starting with the LCK* prefix indicate the blocking problems in SQL Server. The lock wait type can be changed according to isolation levels or locked resources. For example, if we execute our sample queries in the serializable isolation level the wait type indicates the LCK_M_RX_S wait type. At first, we will execute the update query.

As second, we will execute the select query in a separate query window.

We will use sp_whoisactive to monitor the details of the active transactions.

Using wait types to monitor SQL Server blocking problems.

The serializable is the strictest SQL isolation level because this isolation level prevents the dirty read, non-repeatable read, and phantom read. The following table gives a short description of lock wait types.

Wait Type

Description

LCK_M_IX

Intent-Exclusive

LCK_M_IU

Intent-Update

LCK_M_IS

Intent-Share

LCK_M_X

Exclusive

LCK_M_UIX

Update-Intent-Exclusive

LCK_M_U

Update

LCK_M_SIX

Share-Intent-Exclusive

LCK_M_SIU

Shared intent to update

LCK_M_SCH_S

Schema stability

LCK_M_SCH_M

Schema modification

LCK_M_S

Share

LCK_M_RI_X

Range-Insert-Exclusive

LCK_M_RI_U

Range-Insert-Update

LCK_M_RI_S

Range-Insert-Shared

LCK_M_RI_NL

Range-Insert-NULL

LCK_M_RX_X

Range-exclusive-exclusive

LCK_M_RX_U

Range-exclusive-update

LCK_M_RX_S

Range-exclusive-Shared

LCK_M_RS_U

Range-share-Update

LCK_M_RS_S

Range-share-share

LCK_M_BU

Bulk Update

As a result, the lock wait types guide to determine SQL Server blocking problems but do not directly point to the historical problematic queries.

Using system_health extended event to monitor SQL Server blocking problems

The system_health is the default extended event session of the SQL Server. It is started automatically when the database engine is started. The system_health captures any session that has waited in the blocked status for over 30 seconds. We can report the blocked queries which are over 30 seconds with the help of the following query. This query finds the system_health file stored location and then parses this XML data only for the lock wait types.

Querying extended event to monitor SQL Server blocking issues

The advantage of this option is to see the problematic queries and lock wait types without extra effort. Despite that, the drawback of system_health is that it only captures the blocking issues that take longer than 30 seconds.

Using All Blocking Transactions report to monitor SQL Server blocking problems

We can find various standard reports in the SQL Server Management Studio (SSMS) that help to obtain detail about the database engine performance metrics. All Blocking Transactions show the report that offers the current blocking and blocked transaction details. In order to find out this report, we need to right-click on any database and then navigate as shown below:

Reports > Standart Reports > All Blocking Transactions

All Blocking Transactions report

This report shows the blocking and blocked sessions and this detail provides an advantage to find out the head blockers of the SQL Server blocking chain issues. In blocking chains, one query locks a resource, and then multiple queries start waiting for each other due to the resource locked by the first query. Therefore, we need to find out the head blocker query in the blocking chain problems. This report can help to resolve which query is the root cause of the blocking chain.

All transaction blocking report in SSMS, how to monitor SQL Server

Using blocked process threshold option to monitor SQL Server blocking problems

We can use the blocked process threshold option to report a blocked query wait time over the specified value. By default, this option is disabled, and to enable this option we need to use the sp_configure system procedure. Microsoft recommends setting this value to at least 5 seconds. With the help of the following query, we can enable this option and set it 10 seconds.

Changing  blocked process threshold option of the SQL Server

After enabling the blocked process threshold option, we need to create an extended event session that reports the blocked process. To do this we need to use the blocked_process_session event. Firstly, we right-click on the Sessions folder that is located under the Management node and then click New Session Wizard.

Create an extended event session

On the Set Session Properties window, we will give a name to our session and then click Next.

Set session properties window in the extended event

We chose the “Do not use a template” option and skip the Choose Template screen.

Chose template window in the extended event

We filter the blocked_process_report event and add it to the Selected events. To quickly create the extended event session, we will click the Finish button. We can see a captured event on the Watch Live Data screen when we simulate a blocked process.

How to use blocked_process_report in the extended event

On the last screen of the extended event, we click the Start the event session immediately and Watch live data on screen as it is captured. So that, the session starts immediately and the Watch Live Data screen will appear.

Successfully create an extended event

The blocked processes will be captured by this session but their wait time must be over the blocked process threshold.

Blocked process report in SQL Server

The blocked_process field shows an XML report. This report includes all details about the blocking and blocked processes.

Details of the blocked process report - monitor SQL Server

Conclusion

In this article, we have learned some different methods to monitor SQL Server blocking issues. Sometimes the blocking problems can be annoying for the database administrators, therefore monitoring this type of problem will help to detect the problematic queries.

Esat Erkec
168 Views