SQL Server等待事件—PAGEIOLATCH_EX

悅光陰 2022-04-13

展開全文

什么是PAGEIOLATCH_EX等待事件？下面我們將對PAGEIOLATCH_EX等待事件的相關(guān)資料做一個簡單的歸納、整理。關(guān)于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.

在任務(wù)等待 I/O 請求中緩沖區(qū)的閂鎖時發(fā)生。閂鎖請求處于“獨占”模式。長時間的等待可能指示磁盤子系統(tǒng)出現(xiàn)問題。

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中，閂鎖是短暫的輕量級同步對象。

緩沖區(qū)閂鎖（包括PAGEIOLATCH_EX等待類型用于同步訪問SQL Server 數(shù)據(jù)庫中的 BUF 結(jié)構(gòu)和關(guān)聯(lián)頁。最常見的緩沖區(qū)閂鎖情況是在緩沖區(qū)頁面上需要序列化操作時。這些緩沖區(qū)閂鎖不會在事務(wù)的整個期間內(nèi)保持不變。PAGEIO閂鎖是緩沖區(qū)和相關(guān)數(shù)據(jù)頁或索引頁處于 IO 操作中間時使用的 BUF閂鎖的子集。PAGEIOLATCH 等待類型用于磁盤到內(nèi)存的傳輸。

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用戶需要不在緩沖區(qū)高速緩存中的相關(guān)頁面時，數(shù)據(jù)庫必須首先分配一個緩沖區(qū)頁面，然后在頁面從磁盤傳輸?shù)礁咚倬彺鏁r，在緩沖區(qū)上放置一個獨占PAGEIOLATCH_EX閂鎖。在此操作過程中，SQL Server 代表用戶在緩沖區(qū)上發(fā)出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.

當數(shù)據(jù)從磁盤傳輸?shù)絻?nèi)存中以進行更新操作時，將出現(xiàn)大量的PAGEIOLATCH_EX等待，并且這些傳輸（磁盤IO操作）需要時間才能完成。這可能表明存在磁盤爭用或其他磁盤子系統(tǒng)瓶頸。

PS：其實當一個大表發(fā)生大量數(shù)據(jù)刪除操作時，也會出現(xiàn)大量的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.”)

這種等待類型是線程正在等待從磁盤讀取數(shù)據(jù)文件的相關(guān)頁面到內(nèi)存的完成，并且一旦線程將修改內(nèi)存中的頁面結(jié)構(gòu)（EX = EXclusive模式）。右側(cè)邊欄中的“閂鎖白皮書”介紹了所有閂鎖模式及其與其他閂鎖模式的兼容性。

（在線叢書描述：“當任務(wù)正在等待I / O請求中的緩沖區(qū)的閂鎖上時發(fā)生。閂鎖請求處于互斥模式。長時間等待可能表明磁盤子系統(tǒng)有問題。”）

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.

查詢正在等待對頁面的獨占寫入訪問，以便將數(shù)據(jù)添加到頁面，但是該頁面當前不在內(nèi)存中，必須從磁盤加載。

閂鎖是SQL Server 內(nèi)部進程用于管理對內(nèi)存中頁面緩沖區(qū)的訪問的輕量級、不可配置的鎖。當SQL Server必須將磁盤中的頁面讀取到內(nèi)存緩沖區(qū)中或從緩沖區(qū)中讀取到磁盤時，它必須在進程進行時將閂鎖放在緩沖區(qū)頁面上。

看到一些PAGEIOLATCH_ *等待是很正常的，但是如果您經(jīng)常看到它們的等待時間始終超過10毫秒，并且遇到某種類型的延遲，則表明I/O子系統(tǒng)面臨壓力。相反，如果您看到許多短暫的等待，則可能會受到I/O活動增加的影響。