SQL Server等待事件—PAGEIOLATCH_EX

什么是PAGEioLATCH_EX等待事件？ 下面我们将对PAGEIOLATCH_EX等待事件的相关资料做一个简单的归纳、整理。关于PAGEIOLATCH_EX，官方文档的简单介绍如下：

PAGEIOLATCH_EX：

Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem.

  在任务等待 I/O 请求中缓冲区的闩锁时发生。 闩锁请求处于“独占”模式。 长时间的等待可能指示磁盘子系统出现问题。

In SQL Server, a latch is a short-term lightweight synchronization object.

Buffer latches including the PAGEIOLATCH_EX wait type are used to synchronize access to BUF structures and associated pages in the sql Server database. The most frequently occurring buffer latching situation is when serialization is required on a buffer page. These buffer latches are not held for the complete period of the transaction. The PAGEIO latches are a subset of BUF latches used when the buffer and associated data page or the index page is in the middle of an IO operation. PAGEIOLATCH wait types are used for disk-to-memory transfers.

在SQL Server中，闩锁是短暂的轻量级同步对象。

缓冲区闩锁（包括PAGEIOLATCH_EX等待类型用于同步访问SQL Server 数据库中的 BUF 结构和关联页。最常见的缓冲区闩锁情况是在缓冲区页面上需要序列化操作时。这些缓冲区闩锁不会在事务的整个期间内保持不变。PAGEIO闩锁是缓冲区和相关数据页或索引页处于 IO 操作中间时使用的 BUF闩锁的子集。PAGEIOLATCH 等待类型用于磁盘到内存的传输。

PAGEIOLATCH_EX (exclusive mode page IO latch request)

When a SQL Server user needs a page that is not in buffer cache, the database must first allocate a buffer page, and then puts an exclusive PAGEIOLATCH_EX latch on the buffer while the page is transferred from disk to cache. During this operation SQL Server puts a PAGEIOLATCH_EX request on the buffer on behalf of the user. After the write to cache is complete, the PAGEIOLATCH_EX latch is released.

当SQL Server用户需要不在缓冲区高速缓存中的相关页面时，数据库必须首先分配一个缓冲区页面，然后在页面从磁盘传输到高速缓存时，在缓冲区上放置一个独占PAGEIOLATCH_EX闩锁。 在此操作过程中，SQL Server 代表用户在缓冲区上发出PAGEIOLATCH_EX请求。写入高速缓存完成后，将释放PAGEIOLATCH_EX闩锁。

Problem indication

Excessive PAGEIOLATCH_EX waits occur when data is transfered from disk to memory for update operations and these transfers take time to complete.

This may be an indication of disk contention or other disk subsystem bottlenecks.

当数据从磁盘传输到内存中以进行更新操作时，将出现大量的PAGEIOLATCH_EX等待，并且这些传输（磁盘IO操作）需要时间才能完成。这可能表明存在磁盘争用或其他磁盘子系统瓶颈。

PS：其实当一个大表发生大量数据删除操作时，也会出现大量的PAGEIOLATCH_EX等待事件。

Description:

This wait type is when a thread is waiting for the read of a data file page from disk to complete, and the thread is Going to modify the page structure once it is in memory (EX = EXclusive mode). The Latches Whitepaper in the sidebar on the right has a description of all latch modes and their compatibility with other latch modes.

(Books Online description: “Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem.”)

这种等待类型是线程正在等待从磁盘读取数据文件的相关页面到内存的完成，并且一旦线程将修改内存中的页面结构（EX = EXclusive模式）。 右侧边栏中的“闩锁白皮书”介绍了所有闩锁模式及其与其他闩锁模式的兼容性。

（在线丛书描述：“当任务正在等待I / O请求中的缓冲区的闩锁上时发生。闩锁请求处于互斥模式。长时间等待可能表明磁盘子系统有问题。”）

PAGEIOLATCH_EX

A query is waiting for exclusive write access to a page in order to add data to the page, but the page is not currently in memory and has to be loaded from disk.

Page latches are lightweight, non-configurable locks used by internal processes within SQL Server to manage access to the page buffer in memory.  When SQL Server has to read pages from the disk into the memory buffer or from the buffer out to disk, it must place latches on the buffer pages while the processes take place.

It’s nORMal to see some PAGEIOLATCH_* waits, but if you’re frequently seeing them with wait times consistently above 10 milliseconds and you’re experiencing some type of latency, it suggests that the I/O subsystem is under pressure. Conversely, if you see many brief waits, you may be affected by increased I/O activity.

查询正在等待对页面的独占写入访问，以便将数据添加到页面，但是该页面当前不在内存中，必须从磁盘加载。

闩锁是SQL Server 内部进程用于管理对内存中页面缓冲区的访问的轻量级、不可配置的锁。当SQL Server必须将磁盘中的页面读取到内存缓冲区中或从缓冲区中读取到磁盘时，它必须在进程进行时将闩锁放在缓冲区页面上。

看到一些PAGEIOLATCH_ *等待是很正常的，但是如果您经常看到它们的等待时间始终超过10毫秒，并且遇到某种类型的延迟，则表明I/O子系统面临压力。 相反，如果您看到许多短暂的等待，则可能会受到I/O活动增加的影响。

参考资料：

https://logicalread.com/2012/11/12/sql-server-pageiolatch_ex-wait-type/#.XkH2KCIzaHs

Https://www.sqlskills.com/help/waits/pageiolatch_ex/

https://documentation.red-gate.com/sm4/working-with-overviews/using-performance-diagnostics/list-of-common-wait-types/pageiolatch_ex

https://docs.microsoft.com/zh-cn/sql/relational-databases/system-dynamic-management-views/sys-dm-os-wait-stats-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15