大网数据广告活动
数据库与应用安全

The blocked column is populated for latch waits

· 52CE 我爱测

This article describes that the blocked column in the sysprocesses table is populated for latch waits.

This article introduces that the blocked column in the sysprocesses table is populated for latch waits.

Original product version:   SQL Server
Original KB number:   906344

Summary

In SQL Server, you may notice that the blocked column in the sysprocesses system table is populated for latch waits in addition to lock waits. Sometimes, you may notice brief periods of time when a single server process ID (SPID) is reported as blocking itself. This behavior is expected.

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

Latch ownership is only tracked for latches that are in exclusive (EX) or update (UP) latch mode. Ownership isn't tracked for latches that are in shared (SH) latch mode. This means that the blocked column won't be populated for some latch requests.

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, see sys.dm_os_wait_stats (Transact-SQL).

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 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 doesn't exist in the buffer pool.

  2. SPID 55 acquires an EX latch on the page. Because the page doesn't 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.

Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors. Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.

资料来源与适用范围

资料来源
Microsoft Learn / IT Pro troubleshooting
适用范围
See source applicability · Source date 01/20/2021
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY 4.0 (documentation), MIT (code samples)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。