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

Published Jan 21 2022 04:17 PM 3,480 Views
Microsoft

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

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

%3CLINGO-SUB%20id%3D%22lingo-sub-3061302%22%20slang%3D%22en-US%22%3ENegative%20Blocking%20Session%20Ids%20(-5%20%3D%20Latch%20ANY%20TASK%20RELEASOR)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3061302%22%20slang%3D%22en-US%22%3E%3CP%20class%3D%22Normal%22%3E%3CSPAN%3ESQL%20Server%20may%20report%20a%20blocking%20session%20id%20as%20a%20negative%20integer%20value.%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3ESQL%20Server%20uses%20negative%20sessions%20ids%20to%20indicate%20special%20conditions.%3C%2FSPAN%3E%20%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CDIV%20class%3D%22dxoResponsiveTable%22%3E%0A%3CTABLE%20class%3D%22Table2%22%3E%3CCOLGROUP%3E%3CCOL%20class%3D%22%20TableColumn3%22%20data-colwidth%3D%220.6215%22%20data-colwidthunits%3D%22in%22%20%2F%3E%3CCOL%20class%3D%22%20TableColumn4%22%20data-colwidth%3D%225.8715%22%20data-colwidthunits%3D%22in%22%20%2F%3E%3C%2FCOLGROUP%3E%0A%3CTBODY%3E%0A%3CTR%20class%3D%22TableRow5%22%3E%0A%3CTD%20class%3D%22TableCell6%22%3E%3CP%20class%3D%22P7%22%3E%3CSTRONG%3EValue%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20class%3D%22TableCell8%22%3E%3CP%20class%3D%22P9%22%3E%3CSTRONG%3EDescription%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%20class%3D%22TableRow10%22%3E%0A%3CTD%20class%3D%22TableCell11%22%3E%3CP%20class%3D%22P12%22%3E%3CSTRONG%3E-1%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20class%3D%22TableCell13%22%3E%3CP%20class%3D%22P14%22%3E%3CSPAN%3EOrphaned%20lock%2C%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Ecommonly%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Ea%20bug%20in%20SQL%20Server%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Ewhere%20lock%20ownership%20has%20been%20incorrectly%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Elost.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22P15%22%3E%26nbsp%3B%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%20class%3D%22TableRow16%22%3E%0A%3CTD%20class%3D%22TableCell17%22%3E%3CP%20class%3D%22P18%22%3E%3CSTRONG%3E-2%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20class%3D%22TableCell19%22%3E%3CP%20class%3D%22P20%22%3E%3CA%20title%3D%22%22%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fsql-server-support-blog%2Fhow-it-works-orphan-dtc-transaction-session-spid-2%2Fba-p%2F316061%22%20target%3D%22_top%22%3E%3CSPAN%20class%3D%22T21%22%3EPending%20DTC%3C%2FSPAN%3E%3CSPAN%20class%3D%22T22%22%3E%20%26nbsp%3Btransaction%3C%2FSPAN%3E%3C%2FA%3E%3CSPAN%20class%3D%22T23%22%3E.%20%26nbsp%3BThe%20client%20connection(s)%20associated%20with%20the%20DTC%20transaction%20have%20disconnected%20and%20the%20SQL%20Server%20DTC%20transaction%20object%20is%20waiting%20for%20the%20MSDTC%20Manager%3C%2FSPAN%3E%3CSPAN%20class%3D%22T24%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22T25%22%3E%20%26nbsp%3Bstate%20change%3C%2FSPAN%3E%3CSPAN%20class%3D%22T26%22%3E%20%26nbsp%3Bnotification%3C%2FSPAN%3E%3CSPAN%20class%3D%22T27%22%3E.%20%26nbsp%3BThe%20client%20application%20%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22T28%22%3Eneeds%20to%20invoke%20%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22T29%22%3Ecommit%20or%20rollback%20%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22T30%22%3Eo%3C%2FSPAN%3E%3CSPAN%20class%3D%22T31%22%3En%20the%20transaction%20interface%20to%20complete%20the%20DTC%20transaction.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22P32%22%3E%26nbsp%3B%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%20class%3D%22TableRow33%22%3E%0A%3CTD%20class%3D%22TableCell34%22%3E%3CP%20class%3D%22P35%22%3E%3CSTRONG%3E-3%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20class%3D%22TableCell36%22%3E%3CP%20class%3D%22P37%22%3E%3CSPAN%20class%3D%22T38%22%3EDeferred%20recovery%3C%2FSPAN%3E%3CSPAN%20class%3D%22T39%22%3E.%20%26nbsp%3BThe%20lock%20is%20actively%20held%20by%20a%20%26nbsp%3B%3C%2FSPAN%3E%3CA%20title%3D%22%22%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fbackup-restore%2Fdeferred-transactions-sql-server%3Fview%3Dsql-server-ver15%22%20target%3D%22_top%22%20rel%3D%22noopener%20noreferrer%22%3E%3CSPAN%20class%3D%22T40%22%3Edeferred%20transaction%3C%2FSPAN%3E%3C%2FA%3E%3CSPAN%20class%3D%22T41%22%3E.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22P42%22%3E%26nbsp%3B%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%20class%3D%22TableRow43%22%3E%0A%3CTD%20class%3D%22TableCell44%22%3E%3CP%20class%3D%22P45%22%3E%3CSTRONG%3E-4%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20class%3D%22TableCell46%22%3E%3CP%20class%3D%22P47%22%3E%3CSPAN%3ELatch%20transition%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Eoccurring.%26nbsp%3B%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3EIndicates%20a%20latch%20release%20is%20in%20progress.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22P48%22%3E%26nbsp%3B%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%20class%3D%22TableRow49%22%3E%0A%3CTD%20class%3D%22TableCell50%22%3E%3CP%20class%3D%22P51%22%3E%3CSTRONG%3E-5%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20class%3D%22TableCell52%22%3E%3CP%20class%3D%22P53%22%3E%3CSPAN%3EAny%20task%2Fsession%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Ecan%20release%20the%20latch.%26nbsp%3B%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3EI%2FO%20latches%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Eare%20the%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Emost%20common%20user.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22P54%22%3E%26nbsp%3B%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3C%2FDIV%3E%0A%3CP%20class%3D%22P55%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22P56%22%3E%3CSTRONG%3EAny%20Task%2FSession%20(-5)%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%20class%3D%22P57%22%3E%3CSPAN%3EI%20recently%20added%20the%20(-5%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Eblocking%20session%20id)%20to%20improve%20latch%20visibility.%26nbsp%3B%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3EA%20latch%20can%20be%20acquired%20and%20released%20by%20the%20same%20session%20or%20acquired%20and%20released%20on%20different%20sessions.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22P58%22%3E%3CSTRONG%3EAcquired%20and%20Released%20By%20Same%20Session%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%20class%3D%22Normal%22%3E%3CSPAN%20class%3D%22T59%22%3EUsed%20in%20a%20common%20locking%20pattern%2C%20the%20latch%20is%20held%20by%20the%20same%20session%2C%20could%20block%20other%20sessions%2C%20and%20is%20released%20by%20the%20same%20session.%20%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CUL%20class%3D%22LFO3%22%3E%0A%3CLI%3E%3CP%20class%3D%22P60%22%3E%3CSPAN%3ELatch.Acquire%3C%2FSPAN%3E%3C%2FP%3E%0A%3C%2FLI%3E%0A%3CLI%3E%3CP%20class%3D%22P61%22%3E%3CSPAN%3EUpdate%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3EFile%20Control%20Block%3C%2FSPAN%3E%3C%2FP%3E%0A%3C%2FLI%3E%0A%3CLI%3E%3CP%20class%3D%22P62%22%3E%3CSPAN%3ELatch.Release%3C%2FSPAN%3E%3C%2FP%3E%0A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%20class%3D%22P63%22%3E%3CSTRONG%3EAcquire%20and%20Released%20%26nbsp%3Bby%20%26nbsp%3BAny%20Session%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%20class%3D%22Normal%22%3E%3CSPAN%20class%3D%22T64%22%3EUsed%20for%20asynchronous%20operations%2C%20%26nbsp%3B%3C%2FSPAN%3E%3CA%20title%3D%22%22%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fsql-server-support-blog%2Fhow-it-works-bob-dorr-s-sql-server-i-o-presentation%2Fba-p%2F316031%22%20target%3D%22_top%22%3E%3CSPAN%20class%3D%22T65%22%3Ecommonly%20I%2FO%3C%2FSPAN%3E%3C%2FA%3E%3CSPAN%20class%3D%22T66%22%3E.%20%26nbsp%3BThe%20latch%20is%20acquired%20to%20protect%20the%20I%2FO%20buffer%20%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22T67%22%3Eand%20released%20when%20the%20I%2FO%20is%20complete.%20Here%20is%20a%20simplified%20SQL%20Server%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22T68%22%3Eusage%20example%3C%2FSPAN%3E%3CSPAN%20class%3D%22T69%22%3E%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CUL%20class%3D%22LFO4%22%3E%0A%3CLI%3E%3CP%20class%3D%22P70%22%3E%3CSPAN%3ESession%201%3A%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3ELatch.Acquire%2C%20Exclusive%3C%2FSPAN%3E%3C%2FP%3E%0A%3C%2FLI%3E%0A%3CLI%3E%3CP%20class%3D%22P71%22%3E%3CSPAN%20class%3D%22T72%22%3ESession%201%3A%20%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22T73%22%3ERead%2FWrite%20file%20async%20(I%3C%2FSPAN%3E%3CSPAN%20class%3D%22T74%22%3E%2F%3C%2FSPAN%3E%3CSPAN%20class%3D%22T75%22%3EO%20put%20in%20motion%3C%2FSPAN%3E%3CSPAN%20class%3D%22T76%22%3E.)%20%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22T77%22%3EAt%20this%20point%20the%20latch%20is%20deemed%20owned%20by%20%E2%80%98ANY%20TASK.%E2%80%99%20%26nbsp%3BThe%20%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22T78%22%3Eacquiring%3C%2FSPAN%3E%3CSPAN%20class%3D%22T79%22%3E%20%26nbsp%3Bsession%20may%20not%20perform%20the%20release%20activities%3C%2FSPAN%3E%3CSPAN%20class%3D%22T80%22%3E%2C%20%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22T81%22%3Eas%20%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22T82%22%3Eany%20session%20can.%3C%2FSPAN%3E%3C%2FP%3E%0A%3C%2FLI%3E%0A%3CLI%3E%3CP%20class%3D%22P83%22%3E%3CSPAN%3ESession%201%3A%20Attempt%20to%20acquire%20same%20latch%2C%20shared.%26nbsp%3B%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3EThis%20will%20block%20until%20the%20latch%20is%20released.%26nbsp%3B%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3EThe%20blocking%20allows%20session%201%20to%20yield%20the%20CPU%2C%20allowing%20other%20sessions%20to%20execute.%3C%2FSPAN%3E%3C%2FP%3E%0A%3C%2FLI%3E%0A%3CLI%3E%3CP%20class%3D%22P84%22%3E%3CSPAN%20class%3D%22T85%22%3EEvery%20time%20the%20scheduler%20context%20switch%3C%2FSPAN%3E%3CSPAN%20class%3D%22T86%22%3E%20%26nbsp%3Boccurs%20%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22T87%22%3Ethe%20pending%20I%2FOs%20are%20checked.%20%26nbsp%3BWhen%20%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22T88%22%3EI%2FO%20%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22T89%22%3Ecompletion%20occur%3C%2FSPAN%3E%3CSPAN%20class%3D%22T90%22%3Es%3C%2FSPAN%3E%3CSPAN%20class%3D%22T91%22%3E%2C%20the%20%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22T92%22%3Eactive%3C%2FSPAN%3E%3CSPAN%20class%3D%22T93%22%3E%20%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22T94%22%3Esession%20releases%20the%20exclusive%20latch.%3C%2FSPAN%3E%3C%2FP%3E%0A%3C%2FLI%3E%0A%3CLI%3E%3CP%20class%3D%22P95%22%3E%3CSPAN%3ESession%20100%3A%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3EDetects%20I%2FO%20is%20complete%2C%20calls%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3ELatch.Release%2C%20places%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3ESession%201%20on%20the%20runnable%20queue%20to%20continue%20processing.%3C%2FSPAN%3E%3C%2FP%3E%0A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%20class%3D%22P96%22%3E%3CSPAN%3ESQL%20Server%20performs%20activities%2C%20such%20as%20read-ahead%2C%20bookmark%20lookup%20and%20other%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Easynchronous%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Eoperations.%26nbsp%3B%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3EThese%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Eoperations%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Eare%20designed%20to%20leverage%20the%20asynchronous%20capabilities%20of%20the%20operating%20system%20while%20performing%20parallel%20activities.%26nbsp%3B%3C%2FSPAN%3E%26nbsp%3B%20%26nbsp%3B%3CSPAN%3EFor%20example%2C%20a%20session%20may%20perform%20a%20read-ahead%20for%20a%20large%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Edata%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Esort.%26nbsp%3B%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3EWhile%20sorting%20rows%20from%20the%20first%20page%20subsequent%20pages%20are%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Easynchronously%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Eread%20by%20the%20operating%20system.%26nbsp%3B%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3EThe%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Edesign%20allows%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Ethe%20sort%20to%20operate%20in%20parallel%20with%20the%20I%2FO%20activities%20to%20improve%20CPU%20utilization%20and%20query%20performance.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22P96%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22P97%22%3E%3CSPAN%3EA%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Eblocking%20session%20id%20of%20-5%20alone%20does%20not%20indicate%20a%20performance%20problem.%26nbsp%3B%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3EThe%20addition%20of%20-5%20is%20just%20an%20indication%20that%20the%20session%20is%20waiting%20on%20an%20asynchronous%20action%20to%20complete%20where-as%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Eprior%20to%20the%20addition%2C%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Ethe%20same%20session%20wait%20would%20have%20showed%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Eblocking%20session%20%3D%200%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Ebut%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Ewas%20still%20in%20a%20wait%20state.%26nbsp%3B%3C%2FSPAN%3E%20%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22P97%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22Normal%22%3E%3CSTRONG%3E%3CSPAN%20class%3D%22T98%22%3ELatch%20Wait%20Description%3C%2FSPAN%3E%3CSPAN%20class%3D%22T99%22%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FSTRONG%3E%3CSPAN%20class%3D%22T101%22%3EAlong%20with%20the%20blocking%20session%20id%20of%20-5%20the%20wait%20%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22T102%22%3Edescription%3C%2FSPAN%3E%3CSPAN%20class%3D%22T103%22%3E%20%26nbsp%3Bwas%20also%20extended.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22P104%22%3E%3CSTRONG%3E%3CSPAN%20class%3D%22T105%22%3ELATCH%26nbsp%3B0x%25p%3A%26nbsp%3BKP%3A%26nbsp%3B%25d%26nbsp%3BSH%3A%26nbsp%3B%25d%26nbsp%3BUP%3A%26nbsp%3B%25d%26nbsp%3BEX%3A%26nbsp%3B%25d%26nbsp%3BDT%3A%26nbsp%3B%25d%26nbsp%3BSublatch%3A%26nbsp%3B%25d%26nbsp%3BHasWaiters%3A%26nbsp%3B%25d%26nbsp%3BTask%3A%26nbsp%3B0x%25p%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22T107%22%3EAnyReleasor%3A%26nbsp%3B%25d%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%20class%3D%22P108%22%3E%3CSPAN%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22P109%22%3E%3CSPAN%3EThe%20wait%20description%20is%20output%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Ein%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Ethe%20error%20log%20and%20DMVs%2C%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Esuch%20as%20dm_exec_requests.%26nbsp%3B%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3EFor%20example%3A%3C%2FSPAN%3E%20%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CUL%20class%3D%22LFO5%22%3E%0A%3CLI%3E%3CP%20class%3D%22P110%22%3E%3CSPAN%3EIf%20the%20latch%20is%20I%2FO%20based%20you%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Emay%20be%20able%20to%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Ecross%20reference%20to%20sys.dm_os_buffer_descriptors.%26nbsp%3B%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3ENote%2C%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Ethe%20DMV%20can%20be%20large%20and%20owned%20latches%20my%20be%20skipped%20to%20avoid%20DMV%20stalls.%3C%2FSPAN%3E%3C%2FP%3E%0A%3C%2FLI%3E%0A%3CLI%3E%3CP%20class%3D%22P111%22%3E%3CSPAN%3EIf%20the%20latch%20is%20I%2FO%20based%20the%20file%20statistics%20are%20a%20great%20source%20of%20information.%3C%2FSPAN%3E%3C%2FP%3E%0A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CUL%20class%3D%22LFO2%22%3E%0A%3CLI%3E%3CP%20class%3D%22P112%22%3E%3CSPAN%3EIf%20the%20latch%20is%20a%20sublatch%20you%20can%20use%20the%20sys.dm_os_sublatches%20DMV%20to%20obtain%20additional%20information.%26nbsp%3B%3C%2FSPAN%3E%20%26nbsp%3B%3C%2FP%3E%0A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CDIV%20class%3D%22dxoResponsiveTable%22%3E%0A%3CTABLE%20class%3D%22Table113%22%3E%3CCOLGROUP%3E%3CCOL%20class%3D%22%20TableColumn114%22%20data-colwidth%3D%221.184%22%20data-colwidthunits%3D%22in%22%20%2F%3E%3CCOL%20class%3D%22%20TableColumn115%22%20data-colwidth%3D%225.309%22%20data-colwidthunits%3D%22in%22%20%2F%3E%3C%2FCOLGROUP%3E%0A%3CTBODY%3E%0A%3CTR%20class%3D%22TableRow116%22%3E%0A%3CTD%20class%3D%22TableCell117%22%3E%3CP%20class%3D%22P118%22%3E%3CSTRONG%3ELatch%20Type%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20class%3D%22TableCell119%22%3E%3CP%20class%3D%22P120%22%3E%3CSTRONG%3EDescription%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%20class%3D%22TableRow121%22%3E%0A%3CTD%20class%3D%22TableCell122%22%3E%3CP%20class%3D%22P123%22%3E%3CSTRONG%3EKP%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20class%3D%22TableCell124%22%3E%3CP%20class%3D%22P125%22%3E%3CSPAN%3EStabilization%20count%20owners.%26nbsp%3B%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3EUsed%20to%20make%20sure%20the%20latch%20cannot%20be%20destroyed%20(DT.)%3C%2FSPAN%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%20class%3D%22TableRow126%22%3E%0A%3CTD%20class%3D%22TableCell127%22%3E%3CP%20class%3D%22P128%22%3E%3CSTRONG%3ESH%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20class%3D%22TableCell129%22%3E%3CP%20class%3D%22P130%22%3E%3CSPAN%3ECount%20of%20shared%20latch%20owners.%3C%2FSPAN%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%20class%3D%22TableRow131%22%3E%0A%3CTD%20class%3D%22TableCell132%22%3E%3CP%20class%3D%22P133%22%3E%3CSTRONG%3EUP%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20class%3D%22TableCell134%22%3E%3CP%20class%3D%22P135%22%3E%3CSPAN%3EUpdate%20latch%20is%20held.%3C%2FSPAN%3E%26nbsp%3B%20%26nbsp%3B%3CSPAN%3EUsed%20to%20serialize%20modifications%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Eto%20the%20protected%20resource.%3C%2FSPAN%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%20class%3D%22TableRow136%22%3E%0A%3CTD%20class%3D%22TableCell137%22%3E%3CP%20class%3D%22P138%22%3E%3CSTRONG%3EEX%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20class%3D%22TableCell139%22%3E%3CP%20class%3D%22P140%22%3E%3CSPAN%3EExclusive%20latch%20is%20held.%3C%2FSPAN%3E%20%26nbsp%3B%20%26nbsp%3B%3CSPAN%3EUsed%20to%20serialize%20modifications%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Eto%20the%20protected%20resource.%3C%2FSPAN%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%20class%3D%22TableRow141%22%3E%0A%3CTD%20class%3D%22TableCell142%22%3E%3CP%20class%3D%22P143%22%3E%3CSTRONG%3EDT%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20class%3D%22TableCell144%22%3E%3CP%20class%3D%22P145%22%3E%3CSPAN%3EAcquired%20to%20destroy%20the%20latch%20object.%3C%2FSPAN%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%20class%3D%22TableRow146%22%3E%0A%3CTD%20class%3D%22TableCell147%22%3E%3CP%20class%3D%22P148%22%3E%3CSTRONG%3ESublatch%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20class%3D%22TableCell149%22%3E%3CP%20class%3D%22P150%22%3E%3CSPAN%3EThe%20latch%20has%20been%20promoted%20to%20a%20sub%2Fsuper%20latch%20to%20improve%20performance.%3C%2FSPAN%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%20class%3D%22TableRow151%22%3E%0A%3CTD%20class%3D%22TableCell152%22%3E%3CP%20class%3D%22P153%22%3E%3CSTRONG%3EHasWaiters%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20class%3D%22TableCell154%22%3E%3CP%20class%3D%22P155%22%3E%3CSPAN%3ENumber%20of%20tasks%20waiting%20to%20acquire%20latch%20ownership.%3C%2FSPAN%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%20class%3D%22TableRow156%22%3E%0A%3CTD%20class%3D%22TableCell157%22%3E%3CP%20class%3D%22P158%22%3E%3CSTRONG%3EAnyReleasor%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20class%3D%22TableCell159%22%3E%3CP%20class%3D%22P160%22%3E%3CSPAN%3ELatch%20can%20be%20released%20by%20any%20session%2Ftask.%3C%2FSPAN%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-3061302%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3ESQL%20Server%20may%20report%20a%20blocking%20session%20id%20as%20a%20negative%20integer%20value.%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3ESQL%20Server%20uses%20negative%20sessions%20ids%20to%20indicate%20special%20conditions.%3C%2FSPAN%3E%20%26nbsp%3B%3C%2FP%3E%0A%3CTABLE%20class%3D%22Table2%22%3E%3CCOLGROUP%3E%3CCOL%20class%3D%22%20TableColumn3%22%20data-colwidth%3D%220.6215%22%20data-colwidthunits%3D%22in%22%20%2F%3E%3CCOL%20class%3D%22%20TableColumn4%22%20data-colwidth%3D%225.8715%22%20data-colwidthunits%3D%22in%22%20%2F%3E%3C%2FCOLGROUP%3E%0A%3CTBODY%3E%0A%3CTR%20class%3D%22TableRow49%22%3E%0A%3CTD%20class%3D%22TableCell50%22%3E%3CP%20class%3D%22P51%22%3E%3CSPAN%3E-5%3C%2FSPAN%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20class%3D%22TableCell52%22%3E%3CP%20class%3D%22P53%22%3E%3CSPAN%3EAny%20task%2Fsession%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Ecan%20release%20the%20latch.%26nbsp%3B%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3EI%2FO%20latches%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Eare%20the%3C%2FSPAN%3E%20%26nbsp%3B%3CSPAN%3Emost%20common%20user.%3C%2FSPAN%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3061302%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBobSQL%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Co-Authors
Version history
Last update:
‎Jan 13 2022 02:17 PM
Updated by: