SQL Server – Blocked Sessions with PAGELATCH Wait Types

lockingoptimizationsql serversql server 2014wait-types

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

Best Answer

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.

Related Question