9

Edited : Why are sessions reporting being blocked but waiting with PAGELATCH_*, and not LCK_M_ related wait types?

I previously assumed that SQL server would only report blocking sessions in the blocking_session_Id column. If the blocked sessions were waiting for a logical lock and not anything else such as a PAGELATCH_*.

enter image description here

  • Looks like many concurrent sessions are inserting data into the table. Can you post the table structure along with indexes ? – Kin Shah Oct 1 '15 at 16:23
  • @Kin the table begin inserted to by SPIDs 69, 82 and 84 does have a clustered index defined with a IDENTITY(1,1) property so it could be a case of last page insert contention but at this stage I'm more interested in why the blocked sessions are waiting with a non-locking related wait type. Also interestingly, SPID 93 is attempting to put an XLOCK on a totally unrelated table to the table being selected from by 64. – Pixelated Oct 1 '15 at 16:32
  • 2
    I would also look at my tempdb configuration. I have often seen pagelatch boiling down to incorrectly configured tempdb. – Shanky Oct 1 '15 at 17:34
6

I previously assumed that SQL Server would only report a blocking session in the blocking_session_id column, if the blocked sessions were waiting for a logical lock and not anything else such as a PAGELATCH_*.

You are using Adam Machanic's sp_WhoIsActive procedure, not a built-in SQL Server facility. Adam's procedure reports all 'interesting' causes of blocking, not just lock blocking. The underlying information comes from various sources, including sysprocesses, sys.dm_exec_requests, and sys.dm_os_waiting_tasks.

A task can wait for other things besides a lock. It may be able, for example, to acquire an exclusive lock on a row on a page, but then still have to wait to acquire an exclusive page latch (because other tasks have latched the same page in an incompatible mode).

In your example, the PAGELATCH_EX exclusive page latches are on regular data or index pages, not PFS, GAM, SGAM, DCM, or BCM pages, as indicated by the (*) at the end of the wait_info.

You can find the documentation (blog entries) for sp_WhoIsActive here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.