This change in SQL Server 2000 SP4 does not change the situations in which a latch is requested. Additionally, this change does not change the situations in which an SPID is blocked by a latch. This change only affects the way in which latch waits are reported in the sysprocesses system table.
Latch ownership is only tracked for latches that are in exclusive (EX) or update (UP) latch mode. Ownership is not tracked for latches that are in shared (SH) latch mode. This means that the blocked column will not be populated for some latch requests, even after SQL Server 2000 SP4 has been installed.
Most of the time, you can ignore the value in the blocked column if the following conditions are true:
- The value in the waittime column is low.
- The waittype of the SPID is a latch waittype.
For example, the following conditions may occur:
- SPID 55 wants to read a data page that does not exist in the buffer pool.
- SPID 55 acquires an EX latch on the page. Because the page does not exist yet in memory, the requested latch mode is EX. The EX latch mode forces other SPIDs that may also want to access the page to wait for the I/O request to finish. The EX latch mode also prevents other SPIDs from issuing a duplicate I/O request for the same page.
- SPID 55 issues the I/O request to read the page from disk.
- Because SPID 55 wants to read the page, SPID 55 must wait for the I/O request to finish. To wait for the I/O request to finish, SPID 55 tries to acquire another latch that has the shared (SH) latch mode on the same page. Because an EX latch has already been acquired, the SH latch request is blocked, and the SPID is suspended. Because the EX latch that blocks the SH latch request was also acquired by SPID 55, the SPID is temporarily reported as blocking itself.
- When the I/O request finishes, the EX latch on the page is released.
- The release of the EX latch gives the SH latch to SPID 55.
- SPID 55 can now read the page.
Article ID: 906344 - Last Review: Jun 20, 2014 - Revision: 1