Search code examples
sql-servertransactionssql-server-2016database-locking

Detect the cause of SQL Server update lock

Problem:

A .NET application during business transaction executes a query like 

UPDATE Order 
SET Description = 'some new description` 
WHERE OrderId = @p1 AND RowVersion = @p2

This query hangs until timeout (several minutes) and then I get an exception:

SqlException: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

It is reproduced, when database is under heavy load (several times per day).
I need to detect the cause of the lock of the query.

What I've tried:

  1. Exploring activity monitor - it shows that the query is hanging by lock. Filtering by headblocker does not give much, it is frequently changing.

  2. Analyze SQL script, that gives similar to activity monitor data - almost same result as looking to activity monitor. Chasing blocking_session_id results in some session, that awaits for command or executing some SQL, I can't reason a relation to Order table. Executing the same script in a second gives other session. I also tried a some other queries/stored procedures from this atritcle with no result.

  3. Building standard SQL Server report for locked/problem transactions results in errors like Max recursion exhausted or Local OutOfMemory Exception (I have 16 Gb RAM).

Database Details

  • Version: SQL Server 2016
  • Approximate number of concurrent queries per second by apps to database: 400
  • Database size: 1.5 Tb
  • Transaction isolation level: ReadUncommited for readonly transactions, Serializable for transactions with modifications

I'm absolutely new to this kind of problems, so I have missed a lot for sure.
Any help or direction would be great!

Solution

  • Try to use sys.dm_exec_requests view, and filter by columns blocking_session_id, wait_time