什麼是PAGEIOLATCH_EX等待事件？ 下麵我們將對PAGEIOLATCH_EX等待事件的相關資料做一個簡單的歸納、整理。關於PAGEIOLATCH_EX，官方文檔的簡單介紹如下： PAGEIOLATCH_EX： Occurs when a task is waiting on a latch... ...
什麼是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/
