Negative Blocking Session Ids (-5 = Latch ANY TASK RELEASOR)

Negative Blocking Session Ids​​ (-5 = Latch ANY TASK RELEASOR)

 

 

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. ​​ 

  • Latch.Acquire

  • Update​​ File Control Block

  • Latch.Release

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. ​​ A common SQL Server pattern is​​ (simplified):

  • 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.

  • If the latch is a sublatch you can use the sys.dm_os_sublatches DMV to obtain additional 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.

 

  • Bob Dorr