The blocked column in the sysprocesses table is populated for latch waits after you install SQL Server 2000 SP4 (906344)

MORE INFORMATION

Latches are used to synchronize access to cached data pages and other in-memory objects. Typically, latches are only held briefly, and latch wait times are correspondingly small. SQL Server 2000 SP4 adds diagnostics to help troubleshoot cases in which an SPID waits a long time for a latch. These diagnostics cause the blocked column in the sysprocesses system table to reflect the owner of a latch that is blocking the latch request of another SPID. Before SQL Server 2000 SP4, the blocked column was only populated when a lock wait caused blocking.

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 more information about the possible values in the waittype column, click the following article number to view the article in the Microsoft Knowledge Base: When an SPID is waiting for an I/O page latch, you may notice that the blocked column briefly reports that the SPID is blocking itself. This behavior is a side effect of the way that latches are used for I/O operations on data pages. When a thread issues an I/O request, the SPID that issues the I/O request acquires a latch on the page. All SQL Server 2000 I/O operations are asynchronous. Therefore, the SPID will try to acquire another latch on the same page if the SPID that issued the I/O request must wait for the request to finish. This second latch is blocked by the first latch. Therefore, the blocked column reports that the SPID is blocking itself. When the I/O request finishes, the first latch is released. Then, the second latch request is granted.

For example, the following conditions may occur:

1. SPID 55 wants to read a data page that does not exist in the buffer pool.
2. 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.
3. SPID 55 issues the I/O request to read the page from disk.
4. 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.
5. When the I/O request finishes, the EX latch on the page is released.
6. The release of the EX latch gives the SH latch to SPID 55.
7. SPID 55 can now read the page.

Between step 4 and step 5, the sysprocesses table indicates that SPID 55 is blocked by itself together with a waittype of PAGEIOLATCH_XX. In this waittype, XX may be SH, UP, or EX. This behavior indicates that SPID 55 issued an I/O request and SPID 55 is waiting for the I/O request to finish.