SQL Server PageIOLatch和PageLatch

標籤：ade   pen   先來   eem   dsd   調整   資料結構   資料頁面   ddl   

Latch是輕量級的鎖，它是SQL Server內部用來同步資源訪問的一個資料結構，使資料的訪問同步有序，這意味著，當一個線程獲得資源R的Latch的獨佔使用權時，如果其他的線程也想訪問這個Latch時，那麼它必須等待該Latch的獨佔使用權釋放。

一，Latch介紹

Latch主要分為兩種：Buffer Latch，I/O Latch，分別用於保護記憶體中的資料頁和硬碟上的資料頁。Latch的類型受到Buffer的一個位元的控制，bit:BUF_IO，該位元位表示Latch當前是否在做buffer的IO操作。當SQL Server從硬碟中讀取一個Page時，會在Buffer Pool中預留一個Buffer（記憶體中的Page），並且為該Buffer的位元BUF_IO 設為1。如果Page從硬碟中讀取，寫入到Cache中，那麼該bit設定為0。當BUF_IO=1時，對該Page申請的Latch是PageIOLatch；當BUF_IO=0時，對該Page申請的Latch是PageLatch。

我們先來簡短介紹一下I/O Latch。當SQL Server從硬碟上讀取一個頁時，會先在記憶體預留該頁的空間。並且在該預留空間的某一個位BUF_IO設為1。如果資料從硬碟讀寫完成，則該位設為0. 從硬碟讀取頁的期間，其他也需要訪問該頁的線程當然要等待，等待類型為PAGEIOLATCH_SH，直到讀寫完成，BUF_IO被設為0為止。另外一種Latch則稱為Buffer Latch，用來保護記憶體裡的資料結構，當進程需要讀取一個記憶體裡的資料頁時，該進程要先擷取該資料頁上的Buffer Latch。

1，PageIOLatch

如果資料頁不存在於Buffer Pool中，SQL Server必須從硬碟中把資料載入到記憶體，此時，SQL Server需要在Buffer Pool中預留一個Buffer，並為該Buffer申請一個PageIOLatch_EX，表示正在把資料頁（Data Page）或索引葉（Index Page）載入到記憶體；如果資料頁已經載入到記憶體，那麼 PageIOLatch_EX 釋放。在從硬碟讀取到緩衝的期間，需要訪問該資料頁的所有線程都需要等待，等待類型是：PageIOLatch_SH，如果我們看到大量PAGEIOLATCH_SH等待，則基本可以斷定問題是出在磁碟效能上面。

PageIOLatch_EX 和 PageIOLatch_SH是不相容的，直到Disk的讀取過程完成，PageIOLatch_EX釋放，線程申請到PageIOLatch_SH，表示資料頁已經存在於Buffer Pool中。如果Disk IO緩慢，一個會話（Session）可能會對同一個Page同時申請PageIOLatch_EX 和 PageIOLatch_SH，自己把自己阻塞（block），畢竟請求資料寫入的目的，就是為了讀取資料。

SQL Server 從硬碟讀取一個Page的過程如下：

1. Acquires an EX latch on the page that does not exist in the buffer pool (EX mode: blocking others, and no duplicate same IO will be issued)  資料頁不在記憶體中，SQL server 申請並得到該頁的EX獨佔類型的latch。
2. Issues the I/O request to read the page from disk  發出I/O請求，將該Page從Disk讀取到Buffer pool。
3. 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, so temporarily it seems the same SPID was blocking itself  試圖在該頁上面獲得另外一個共用類型latch。因為該頁的latch EX已經獲得，而EX和SH不相容，所以SH必須等待。看起來就像自己等待自己。
4. When the I/O request finishes, the EX latch on the page is released 當頁讀取完畢，EX latch釋放。
5. Release of the EX latch gives the SH latch to the same thread 因為Latch EX 釋放，Latch SH就成功獲得。
6. The thread can now read the page 現在該線程成功獲得latch SH，可以讀取該頁了（該頁已經在記憶體裡面了）。

2，PageLatch

在訪問資料庫的資料頁（Data Page或Index Page）時，如果相應的buffer已經存在於Buffer Pool中，那麼SQL Server先擷取buffer的latch，這個Latch就是 PageLatch，然後讀取Buffer中的資料。

PageLatch是Buffer Latch， 用來保護記憶體中的資料結構，當進程需要讀取一個記憶體裡的資料頁時，該進程要先擷取該資料頁上的Buffer Latch。

3，PageLatch和PageIOLatch都是加在記憶體page上的Latch

如果要讀取資料庫某一個資料頁中的資料，那麼這個資料頁必須存在於記憶體中。如果不存在於記憶體中，SQL Server 發出一個IO請求，將該Page載入到記憶體中，然後從記憶體中讀取該資料頁中的資料。在資料讀取的過程中，SQL Server建立Latch結構使該過程順利進行。在訪問一個Data Page之前，必須首先擷取該page的Latch。如果Page不在記憶體中，擷取的是PageIOLatch；如果page存在於記憶體中，擷取的是PageLatch。

PageIOLatch_XX：當資料頁不在記憶體裡時，SQL Server 先在記憶體中預留一個Page，然後從Disk讀取，載入到記憶體Page，此時，SQL Server申請並擷取的latch類型是PAGEIOLATCH，PageIOLatch表示進行中IO操作。PageIOLatch_EX表示正在將disk中的資料頁載入到記憶體，PageIOLatch_SH表示在載入資料頁到記憶體期間，試圖讀取記憶體中的資料頁，此時載入資料頁的過程沒有完成，處於loading狀態。如果經常出現PageIOLatch_SH，表明Loading資料頁的時間太長，可能出現IO bottleneck。

PageLatch_XX：資料頁已經存在於記憶體中，對記憶體資料頁加的latch是PageLatch。此時從disk載入資料頁的operation已經完成，處於Loaded狀態，一個資料庫page同時存在於記憶體和disk，在訪問記憶體頁之前，添加的latch，就是PageLatch。

 

----讀後感 Buffer Latch Timeout的解析

IO Latch表示SQL Server正在將disk中的資料頁載入到buffer Pool，一旦資料頁載入完成，IO Latch就會釋放。在載入資料頁到buffer pool時，SQL Server Engine先在記憶體中預留一個page的空間，將位BUF_IO設定為1。記憶體中的Page，叫做Buffer。當BUF_IO=1時，對該Page加的Latch是PageIOLatch。當資料載入完成，該Page的位BUF_IO設定為0。當BUF_IO設定為0時，對該Page加的Latch是PageLatch。IO Latch和Buffer Latch都是對記憶體中的Page加的Latch，只不過，IOLatch是在Data Loading期間，BUF_IO位為1時，加在記憶體page上的latch；Buffer Latch是Data 載入完成， BUF_IO為0時，加在記憶體page上的latch。

 

當SQL Server從硬碟上讀取一個頁時，會先在記憶體預留該頁的空間，並且將該預留空間的位BUF_IO設為1。如果資料從硬碟讀寫完成，則該位設為0。從硬碟讀取頁的期間，其他也需要訪問該頁的線程當然要等待，等待類型為PAGEIOLATCH_SH，直到讀寫完成，BUF_IO被設為0為止。因此，如果我們看到大量PAGEIOLATCH_SH等待，則基本可以斷定問題是出在磁碟效能上面。

 二，Latch和效能

1，資料的IO操作

SQL Server訪問的任何一個Page必須存在於記憶體中，如果不存在於記憶體中，那麼SQL Server發出 Disk IO請求，將資料頁從Disk讀取到記憶體中，然後SQL Server從記憶體中讀取該Page的內容。在訪問任何一個記憶體page之前，必須申請和擷取該Page上的Latch。

在資料讀取的過程中，SQL Server先在記憶體中預留一個Page的空間，並設定該Page的位BUF_IO=1，並發出Disk IO請求，此時，在該Page上加的Latch是PageIOLatch_EX，表示正在將資料頁從Disk讀取到記憶體。

在資料頁載入的過程中，任何一個讀取該Page的Thread，在該Page上加的Latch是PAGEIOLATCH_SH，表示在SQL Server Engine從Disk讀取資料頁，寫入記憶體時，Thread試圖讀取該Page，由於PAGEIOLATCH_SH和PageIOLatch_EX不相容，讀取該Page的Thread會被Block，直到Page被讀取到記憶體中。

一旦資料頁被寫入到記憶體中，PageIOLatch_EX會立即釋放，並設定該Page的位BUF_IO=0。由於資料頁存在於記憶體中，對該Page申請的Latch是PageLatch。

如果發生PAGEIOLATCH類型的等待，那麼SQL Server一定是在等待Disk I/O操作的完成。如果經常出現這類等待，說明磁碟速度不能滿足要求，已經成為SQL Server的瓶頸。PAGEIOLATCH_XX主要分為兩大類：PAGEIOLATCH_SH和PAGEIOLATCH_EX。

PAGEIOLATCH_SH 發生在使用者訪問一個資料頁，同時SQL Server正在將資料頁從磁碟寫入記憶體，經常發生PAGEIOLATCH_SH等待，說明記憶體不夠大，導致SQL Server需要做很多頁面讀取的操作，磁碟IO是記憶體壓力的副作用。

PAGEIOLATCH_EX：發生在從Disk page讀取到記憶體buffer中，經常發生PAGEIOLATCH_EX等待，說明Disk讀取速度慢，這和記憶體沒直接關係。

2，資料頁成為HotPage

當一個Task要修改頁面時，它必須先申請一個EX的Latch，即PageLatch_EX。只有擷取到Latch，才能修改頁面的內容。由於資料頁的修改都是在記憶體中完成，所以時間應該非常短，可以忽略不計。而PAGELATCH_EX只是在修改過程中才出現，所以生存周期應該很短，如果出現了，說明：1、SQL Server沒有明顯的記憶體和磁碟瓶頸。2、應用程式發出大量的並發語句修改同一張表，而修改操作集中在同一個頁面，或者數量不多的幾個頁面上，成為Hot Page。3、這種瓶頸無法通過提高硬體設定解決，只能修改 Table的儲存，使修改分散在更多的Page上，提高並發修改的能力。

Hot page的緩解方法：

（1）、換一個資料列建叢集索引，而不要在Identity欄位上，同一時間插入有機會分散到不同的頁面上。

（2）、如果一定要在Identity的欄位上建叢集索引，建議在其他某個列上建立若干個分區。

（3）、為FileGroup增加File，盡量將Files分布在不同的Physical Disk上。

3，在tempdb中，系統Page成為Hot Page

SQL Server不僅在資料頁面上加Latch，而且在資料檔案的系統頁面上，例如SGAM、PFS和GAM頁面等，也會加Latch。系統Page有時候也會成為系統瓶頸。

在建立新表時，SQL Server 需要分配空間。分配儲存空間需要同時修改SGAM、PFS和GAM頁面，設定標識位，由於一個系統Page的標誌位管理很多資料頁，當頻繁的建立和刪除表結構時，而這些資料頁都發生在同一個系統Page，或者數量不多的幾個系統Page上，會使該系統Page過熱，成為Hot Page。在tempdb中，建立和刪除table結構的操作會並發、反覆發生，容易使系統頁過熱，產生hot Page。系統page過熱，可以通過調整表設計來緩解。對此的解決方案：

• 建立與cpu數量相同的tempdb檔案，保持File Size相同，使得每個File承擔的IO壓力相對平均
• 禁止 tempdb 檔案自動成長，避免單個檔案不平均增長

 

引用《Q&A on Latches in the SQL Server Engine》：

Anytime you talk about latching and database pages, think in terms of BUF (buffer) latches. So to read a page from disk and put this into cache, the worker thread will take out a EX (Exclusive) latch on the BUF associated with the page. Then the read is issues for the page. Then a SH latch is taken out by this same thread on this BUF structure. Since these are not compatible, the thread will wait. Makes sense since we need to wait for the page to be read in from disk before trying to look at it. Any other thread needing to read this page will also wait since you need a SH latch on a BUF to read the page in cache. When the read has completed, the EX latch is released, and now the SH latch is acquired and the thread(s) can read the page in cache.

When a thread has to wait on a BUF latch, the engine will look at the BUF structure to see if a special bit is turned on called BUF_IO. This is set when a page associated with the BUF is currently being read from or written to disk. This is how the engine knows that if your thread is waiting on a latch, that it is waiting on an “IO Latch”. The wait_type will therefore look something like PAGEIOLATCH_SH as opposed to PAGELATCH_SH.

 

A latch is a short-term lightweight synchronization object. The following list describes the different types of latches:

• Non-buffer (Non-BUF) latch: The non-buffer latches provide synchronization services to in-memory data structures or provide re-entrancy protection for concurrency-sensitive code lines. These latches can be used for a variety of things, but they are not used to synchronize access to buffer pages.
• Buffer (BUF) latch: The buffer latches are used to synchronize access to BUF structures and their associated database pages. The typical buffer latching occurs during operations that require serialization on a buffer page, (during a page split or during the allocation of a new page, for example). These latches are not held for the duration of a transaction. These are indicated in the master.dbo.sysprocesses table by the PAGELATCH waittypes.
• IO latch: The IO latches are a subset of BUF latches that are used when the buffer and associated data page or the index page is in the middle of an IO operation. PAGEIOLATCH waittypes are used for disk-to-memory transfers and a significant waittime for these waittypes suggests disk I/O subsystem issues.

 

參考文檔：

Buffer Latch Timeout的解析

什麼是PAGELATCH和PAGEIOLATCH

Q&A on Latches in the SQL Server Engine….

SQL Server PageIOLatch和PageLatch