什么是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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/11338.html
標籤：SQL Server
上一篇：BOS只讀狀態修改
下一篇：資料庫多行轉換為單一列