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




Orphaned lock,​​ commonly​​ a bug in SQL Server​​ where lock ownership has been incorrectly​​ lost.



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.



Deferred recovery. ​​ The lock is actively held by a​​ deferred transaction.



Latch transition​​ occurring. ​​ Indicates a latch release is in progress.



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



Stabilization count owners. ​​ Used to make sure the latch cannot be destroyed (DT.)


Count of shared latch owners.


Update latch is held. ​​​​ Used to serialize modifications​​ to the protected resource.


Exclusive latch is held.​​ ​​ Used to serialize modifications​​ to the protected resource.


Acquired to destroy the latch object.


The latch has been promoted to a sub/super latch to improve performance.


Number of tasks waiting to acquire latch ownership.


Latch can be released by any session/task.


  • Bob Dorr

Y) { return 1 * ordBy; } return 0; } }); $.each(rows, function(index, row) { node.children(‘tbody’).append(row); }); } function removeTableCellDecoration() { $(“td[data-sorting], th[data-sorting]”).removeClass(‘sortDecorator’); } //unbind the click event just in case we are loading more than one document $(“td[data-sorting], th[data-sorting]”).off(‘click’); $(“td[data-sorting], th[data-sorting]”).click(function(){ removeTableCellDecoration(); $(this).addClass(‘sortDecorator’); //get desired ordering var ordering = $(this).attr(‘data-sortOrdering’); if (ordering == ‘ASC’){ var ordBy = -1; $(this).attr(‘data-sortOrdering’, ‘DESC’); } else { var ordBy = 1; $(this).attr(‘data-sortOrdering’, ‘ASC’); } var cellPosition = $(this).prevAll().length; var table = $(this).parents(‘table:first’); sortingTable(table, cellPosition, ordBy); }); function convert2number(str) { str = str.trim(); if (str.match(/^d+/)) { //we have to take special care when the thousand separators are ‘.’ var result_0 = str.replace(/./g, ‘_@_’); var result_1 = result_0.replace(window.numFormat[0], ‘.’); var result = result_1.replace(/_@_/g, ”); result .replace(/ /g, ”); return result; } else { return str; } } }(jQuery));]]> Locking and Latching File I/OPerformance

( No ratings yet )