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

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.

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

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