SQL Server may report a blocking session id as a negative integer value. SQL Server uses negative sessions ids to indicate special conditions.
|
Value
|
Description
|
|
-1
|
Orphaned lock, commonly a bug in SQL Server where lock ownership has been incorrectly lost.
|
|
-2
|
Pending DTC transaction. The client connection(s) associated with the DTC transaction have disconnected and the SQL Server DTC transaction object is waiting for the MSDTC Manager, state change notification. The client application needs to invoke commit or rollback on the transaction interface to complete the DTC transaction.
|
|
-3
|
Deferred recovery. The lock is actively held by a deferred transaction.
|
|
-4
|
Latch transition occurring. Indicates a latch release is in progress.
|
|
-5
|
Any task/session can release the latch. I/O latches are the most common user.
|
Any Task/Session (-5)
I recently added the (-5 blocking session id) to improve latch visibility. A latch can be acquired and released by the same session or acquired and released on different sessions.
Acquired and Released By Same Session
Used in a common locking pattern, the latch is held by the same session, could block other sessions, and is released by the same session.
Acquire and Released by Any Session
Used for asynchronous operations, commonly I/O. The latch is acquired to protect the I/O buffer and released when the I/O is complete. Here is a simplified SQL Server usage example:
-
Session 1: Latch.Acquire, Exclusive
-
Session 1: Read/Write file async (I/O put in motion.) At this point the latch is deemed owned by ‘ANY TASK.’ The acquiring session may not perform the release activities, as any session can.
-
Session 1: Attempt to acquire same latch, shared. This will block until the latch is released. The blocking allows session 1 to yield the CPU, allowing other sessions to execute.
-
Every time the scheduler context switch occurs the pending I/Os are checked. When I/O completion occurs, the active session releases the exclusive latch.
-
Session 100: Detects I/O is complete, calls Latch.Release, places Session 1 on the runnable queue to continue processing.
SQL Server performs activities, such as read-ahead, bookmark lookup and other asynchronous operations. These operations are designed to leverage the asynchronous capabilities of the operating system while performing parallel activities. For example, a session may perform a read-ahead for a large data sort. While sorting rows from the first page subsequent pages are asynchronously read by the operating system. The design allows the sort to operate in parallel with the I/O activities to improve CPU utilization and query performance.
A blocking session id of -5 alone does not indicate a performance problem. The addition of -5 is just an indication that the session is waiting on an asynchronous action to complete where-as prior to the addition, the same session wait would have showed blocking session = 0 but was still in a wait state.
Latch Wait Description
Along with the blocking session id of -5 the wait description was also extended.
LATCH 0x%p: KP: %d SH: %d UP: %d EX: %d DT: %d Sublatch: %d HasWaiters: %d Task: 0x%p AnyReleasor: %d
The wait description is output in the error log and DMVs, such as dm_exec_requests. For example:
-
If the latch is I/O based you may be able to cross reference to sys.dm_os_buffer_descriptors. Note, the DMV can be large and owned latches my be skipped to avoid DMV stalls.
-
If the latch is I/O based the file statistics are a great source of information.
|
Latch Type
|
Description
|
|
KP
|
Stabilization count owners. Used to make sure the latch cannot be destroyed (DT.)
|
|
SH
|
Count of shared latch owners.
|
|
UP
|
Update latch is held. Used to serialize modifications to the protected resource.
|
|
EX
|
Exclusive latch is held. Used to serialize modifications to the protected resource.
|
|
DT
|
Acquired to destroy the latch object.
|
|
Sublatch
|
The latch has been promoted to a sub/super latch to improve performance.
|
|
HasWaiters
|
Number of tasks waiting to acquire latch ownership.
|
|
AnyReleasor
|
Latch can be released by any session/task.
|
