Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I am looking at SQL lock wait time perf mon counter and it is indicating high value ranging from 5 to 20 seconds during certain times of the day.
I have setup extended events which gives me the T-SQL statements that are executing. Is there any way to get the list of:
Specific queries that are contributing to high locking time?
Specific queries that are actually blocking other queries?
You can see these articles. It describes the locking process in the SQL server:
Techniques to identify blocking queries and causes of SQL Server blocks
Identifying SQL Server queries contributing to long lock waits and queries with long lock hold times that are risks for long lock waits.
I’ll give you the most frustrating answer ever: it can’t be done.
Or, at least, there isn't a self-contained single way without application logging that i personally would recommend as reliable to do so in a production environment.
In real-time observation, sys.dm_os_waiting tasks can identify long lock waits, the awaited resource, waiting session_id and exec_context_id, blocking_session_id and blocking_exec_context_id. But if the blocking session is executing a query, that's not necessarily the query which was responsible for acquiring the lock. In fact, an idle session might be holding the lock. Consider this somewhat ridiculous example:
BEGIN TRAN
SELECT TOP (1) sv.number
FROM master.dbo.spt_values sv (TABLOCKX)
WHERE sv.number = -9
ORDER BY sv.number;
SELECT 1;
WAITFOR DELAY '00:01:00';
COMMIT TRAN
From another session the following would be in lock wait until the transaction commits...
SELECT TOP (1) sv.number
FROM master.dbo.spt_values sv
WHERE sv.number = -1
ORDER BY sv.number;
And even if you looked back to the previous query in that session, it's the SELECT 1 and it didn't acquire the lock on master.dbo.spt_values.
So it's important to distinguish that a transaction is responsible for the lock, and a session is the owner of the transaction. If explicit transactions are used, any query within the explicit transaction should be considered as the potential lock-acquirer.
The system_health extended events session contains information for lock waits greater than 30 seconds - this information is logged when the wait resolves.
Use the system_health Session - SQL Server | Microsoft Docs
In a non-production SQL Server, investigate using lock:acquired, lock:escalation, and lock:released events.
But on a busy system, there is lots of lock activity so an extended events session like that would have too much observer overhead for me.
So, the easiest case is when a long lock wait is identified by os_waiting_tasks, the blocking_session_id is not in a transaction and the current request in that session is the one that acquired the lock.
In that case, consider the isolation level of the session at that time as it will direct some locking behavior, consider hints in the query as they will direct some locking behavior, the structure of the query can determine locking order, and finally lock escalation might be involved. Lock escalation from row locks to table locks is an efficiency strategy SQL Server attempts after a query has acquired a threshold of row locks. So as the scope of a query increases when data grows over time, table level locks may occur that did not previously occur in a smaller table or with a smaller scope for the query. Lock escalation can also be employed by SQL Server as a memory management facility if shrinking lock memory can relieve internal memory pressure for SQL Server.
And again, if the session holding the lock is within an explicit transaction, any of the queries in the transaction may have acquired the lock. That's where application logging comes in.
Query Store can be helpful... but Query Store doesn't identify session_id values. And you won't know if lock escalation took place within a query based only on information from Query Store.
Lock information is recorded in the transaction log. However, i recommend resorting to transaction log reading in a production SQL Server instance as a last resort only, and only on rare occasion. The fn_dblog() function to read from the transaction log leads to log_reuse_wait_desc = log_scan while it is executing - in development instances i myself have contributed to txlog full conditions by using fn_dblog() to investigate why the txlog was growing so fast :-)
