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.
|
1 2 3 4 5 |
BEGIN TRAN UPDATE Sales.SalesPerson SET ModifiedDate =GETDATE() WHERE BusinessEntityID BETWEEN 274 AND 280 WAITFOR DELAY '00:00:40' ROLLBACK TRAN |
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.
|
1 2 3 4 5 |
SELECT * FROM sys.dm_tran_locks WHERE resource_database_id = (SELECT db.database_id FROM sys.databases AS db WHERE name = 'Adventureworks2017'); |
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.
|
1 2 |
SELECT * FROM Sales.SalesPerson; |
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.
- Note: You can see this article, Boost SQL Server Performance with Wait Statistics to find more details about the SQL Server wait types
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.
|
1 2 3 4 5 6 7 |
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRANSACTION; UPDATE Sales.SalesPerson SET ModifiedDate = GETDATE() WHERE BusinessEntityID BETWEEN 274 AND 280; WAITFOR DELAY '00:00:40'; ROLLBACK TRAN; |
As second, we will execute the select query in a separate query window.
|
1 2 3 4 5 |
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRAN; SELECT * FROM Sales.SalesPerson; COMMIT TRAN; |
We will use sp_whoisactive to monitor the details of the active transactions.
|
1 |
EXECUTE sp_whoIsActive @get_locks = 1 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
SELECT s.name, CAST(t.target_data AS XML).value( '(EventFileTarget/File/@name)[1]', 'VARCHAR(MAX)' ) AS fileName INTO #EX_FilePath FROM sys.dm_xe_sessions AS s INNER JOIN sys.dm_xe_session_targets AS t ON s.address = t.event_session_address WHERE t.target_name = 'event_file'; DECLARE @EventFileTarget AS NVARCHAR(500); SELECT @EventFileTarget = fileName FROM #EX_FilePath WHERE name = 'system_health'; SELECT * FROM(SELECT n.value( '(@name)[1]', 'varchar(50)' ) AS event_name, n.value( '(@package)[1]', 'varchar(50)' ) AS package_name, n.value( '(@timestamp)[1]', 'datetime2' ) AS [utc_timestamp], n.value( '(data[@name="wait_type"]/text)[1]', 'nvarchar(max)' ) AS wait_type, n.value( '(action[@name="session_id"]/value)[1]', 'bigint' ) AS session_id, n.value( '(data[@name="duration"]/value)[1]', 'bigint' ) / 1000 AS duration_ms, n.value( '(action[@name="sql_text"]/value)[1]', 'nvarchar(max)' ) AS sql_text FROM ( SELECT CAST(event_data AS XML) AS event_data FROM sys.fn_xe_file_target_read_file( @EventFileTarget, NULL, NULL, NULL ) ) AS ed CROSS APPLY ed.event_data.nodes( 'event' ) AS q(n)) AS TMP_TBL WHERE event_name = 'wait_info' AND wait_type LIKE 'LCK%' ORDER BY utc_timestamp DESC; |
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
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.
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.
|
1 2 3 4 5 6 7 8 9 |
EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'blocked process threshold', 10; GO RECONFIGURE; GO EXEC sp_configure 'blocked process threshold' |
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.
On the Set Session Properties window, we will give a name to our session and then click Next.
We chose the “Do not use a template” option and skip the Choose Template screen.
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.
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.
The blocked processes will be captured by this session but their wait time must be over the blocked process threshold.
The blocked_process field shows an XML report. This report includes all details about the blocking and blocked processes.
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.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023