sql server中PAGELATCH_x和PAGEIOLATCH_x解析

文章目錄

• 1.1什麼是LATCH呢？
• 1.2什麼是PAGELATCH呢？
• 1.3什麼是PAGEIOLATCH呢？
• 過程解析

0.參考文獻

Microsoft SQL Server企業級平台管理實踐 第11章

Buffer Latch Timeout的解析

什麼是PAGELATCH和PAGEIOLATCH

1.PAGELATCH_x和PAGEIOLATCH_x介紹

 在分析SQL server 效能的時候你可能經常看到 PAGELATCH和PAGEIOLATCH。比方說執行如下TSQL語句

Select * from sys.dm_os_wait_stats

它輸出結果裡面就有Latch的有關資訊，如所示：

1.1什麼是LATCH呢？

Latch是SQL server內部用來同步資源訪問的一個資料結構，和作業系統的critical section 或 ReaderWriterLock類似。Latch保護了那些想保護的資源，使得訪問同步有序。比方說，當某個線程獲得某個資源的latch的獨佔使用權的時候，別的線程如果也需要訪問這個latch則它必須等待。那麼又有新的疑問，latch和lock有什麼區別呢？主要是使用的地方和目的不一樣。Latch用來保護SQL server內部的一些資源（如page）的物理訪問，可以認為是一個同步對象。而lock則強調邏輯訪問。比如一個table，就是個邏輯上的概念，物理上一個表是有很多頁組成的。訪問一個表的記錄的時候，首先可能需要獲得表的共用鎖定，然後獲得某個頁的latch，然後就可以讀取該頁的記錄。Lock是全域性的，由統一的lock manager管理。而latch沒有統一的manager管理的。

1.2什麼是PAGELATCH呢？

PAGELATCH_x類型的latch是SQL Server在緩衝池裡的資料頁面上經常加的一類latch。它用來同步訪問buff pool中的資料頁。SQL server中的Buff pool裡每個page都有一個對應的LATCH。 要訪問某個PAGE必須首先獲得這個PAGE的LATCH。PAGELATCH有很多種，如共用的PAGELATCH_SH，獨佔的PAGELATCH_EX等。獨佔的意思是排他性訪問，只有一個線程可以訪問，一般使用者update，insert和delete操作。共用的意思是可以有多個線程同時獲得這個latch。下面以在同一個data page中插入輸入資料來說明pagelatch。可以參考部落格：Buffer Latch Timeout的解析。

每個SQL Server的資料頁面大致分成3個部分：頁頭、頁尾位移量和資料存放區部分。假設現在有一個表格的結構是：

CREATE TABLE test (    a int,     b int)

它在1:100這個頁面上儲存資料。那麼這個頁面結構大致如所示：

在頁頭部分，會記錄頁面屬性，包括頁面編號等，還會記錄當前頁面空閑部分的起始位置在哪裡(m_freedata)。這樣SQL Server在要插入新資料的時候，就能夠很快地找到開始插入的位置。而頁尾部的位移記錄了每一條資料行的起始位置。這樣SQL Server在找每一條記錄的時候，就能很快找到，不會把前一條記錄和後一條搞混。在中page:100裡現在有兩條記錄，(1,100)和(2,200)。第一條記錄的開始位置是96，第二條的開始位置是111。從126開始，是閒置空間。這頁表明一列的長度是15。當頁面裡的資料行發生變化的時候，SQL Server不但要去修改資料本身，還要修改這些位移量的值，以保證SQL Server能夠繼續準確地管理資料頁面裡的每一行。

現在假設有兩個使用者 同時要向這張表裡插入資料，一個人插入(3,300)，另一個插入(4,400)。那麼這兩個使用者會讀取相同的 (m_freedata=126)。假如沒有latch，這兩條插入語句可以同時運行。在事務邏輯上，這兩條語句插入的是兩條不相干的記錄，所以不應該相互阻塞，這樣處理是正確的。某進程在頁面100上，插入如下資料：INSERT VALUES(3, 300),其結果如下：

這時，另外一個進程要在頁面100上，插入如下資料: INSERT VALUES(4, 400), 因為沒有Latch鎖，所以會覆蓋之前的資料。導致資料插入出問題。如所示：

在邏輯層面上，插入兩條資料是互不干擾的，但是在實體儲存體層面上，就出現了問題。要插入的兩條資料讀取的空閑空間位置m_freedata=126，都要往這個位置上面插入，那麼總有一條資料會被另外一條資料所覆蓋，從而導致插入錯誤。要想辦法解決這個衝突，一定要定義出一個先後順序。SQL Server為瞭解決這類問題，引入了另一類頁面上的latch:PAGELATCH。當一個任務要修改頁面時，它必須先申請一個EX的latch。只有得到這個latch，才能修改頁面裡的內容。所以這裡的兩個插入任務，不僅申請了頁面上的鎖，還要申請頁面上的排他latch。假設(3,300)這個插入任務先申請到了，那(4,400)這個任務就會被阻塞住。所以，(3,300)這條記錄能夠被先插入，如所示。

當(3,300)插入完成後，它申請的latch被釋放，m_freedata的資料被更新。此時(4,400)就能得到latch資源，這是才讀取m_freedata，然後往m_freedata位置插入資料。(4,400)被插在了(3,300)的後面。這樣，兩個插入都正確地完成了，如所示。

由於資料頁的修改都是在記憶體中完成的，所以每次修改的時間都應該非常短，幾乎可以忽略不計。而PAGELATCH只是在修改的過程中才會出現，所以PAGELATCH的生存周期應該也非常短。如果這個資源成為了SQL Server經常等待的資源，可說明以下問題。

1. SQL Server沒有明顯的記憶體和磁碟瓶頸（恭喜你！）。
2. 應用程式發來大量的並發語句在修改同一張表格裡的記錄，而表格架構設計以及使用者商務邏輯使得這些修改都集中在同一個頁面，或者數量不多的幾個頁面上。這些頁面有的時候也被稱為Hot Page。這樣的瓶頸通常只會發生在並發使用者比較多的、典型的OLTP系統上。
3. 這種瓶頸是無法通過提高硬體設定解決的，只有通過修改表格設計或者商務邏輯，讓修改分散到儘可能多的頁面上，才能提高並發效能。
4. 要在修改表格的設計，從而引入了partition的概念，要解決上述問題，可以通過分區的方式分擔hot page上面的壓力。具體解決方案參考另外一篇部落格：sql server中filegroup與partition解析 。

1.3什麼是PAGEIOLATCH呢？

當緩衝在記憶體緩衝池地區裡的資料頁面，和磁碟上資料檔案裡的資料頁面進行互動時（就是data page不在記憶體中），為了保證不會有多個使用者同時讀取/修改記憶體裡的資料頁面，SQL Server會像對待表格裡的資料一樣，對記憶體中的頁面實行加鎖的機制，以同步多使用者並發處理。不同的是，在這裡，SQL Server加的是latch（輕量級的鎖），而不是lock。

例如，當SQL Server將資料頁面從資料檔案裡讀入記憶體時，為了防止其他使用者對記憶體裡的同一個資料頁面進行訪問，SQL Server會在記憶體的資料頁面上加一個排他的latch。而當有任務要讀緩衝在記憶體裡的頁面時，會申請一個共用的latch。像lock一樣，latch也會出現阻塞的現象。根據不同的等待資源，在SQL Server裡等待的狀態會是：

PAGEIOLATCH_DT : Destroy buffer page I/O latchPAGEIOLATCH_EX : Exclusive buffer page I/O latchPAGEIOLATCH_KP : Keep buffer page I/O latchPAGEIOLATCH_NL : Null buffer page I/O latchPAGEIOLATCH_SH : Shared buffer page I/O latchPAGEIOLATCH_UP : Update buffer page I/O latch

這裡來舉一個最容易發生的等待“PAGEIOLATCH_SH”，以它做例子，看看這種等待是怎麼發生的。如所示：

過程解析

1. 有一個使用者請求，須讀取整張X表，由Worker X執行。
2. Worker X在執行表掃描的過程中發現它要讀取資料頁面1:100。一張表的資料量可能比一個page大，也可能比一個page小。如果進行table scan的話，那麼就需要讀取所有包含該表資料的page。
3. SQL Server發現頁面1:100並不在記憶體中的資料緩衝裡。
4. SQL Server在緩衝池裡找到一個頁面的空間，在上面申請一個EX的latch，防止資料從磁碟裡讀出來之前，有別人也來讀取或修改這個頁面。對長個page加鎖相當於是在記憶體中預定了一片空間用於存放需要從磁碟中physical read來的page。
5. Worker X發起一個非同步(Asynchronous)I/O請求，要求從資料檔案(database file)裡讀出頁面1:100。
6. 由於是個非同步I/O，Worker X可以接著做它下面要做的事情。而下面要做的，就是要讀出記憶體中的頁面1:100。讀取的動作需要申請一個SH的latch。
7. 由於Worker X之前已經對這個page1:100申請了一個EX latch還沒釋放，所以這個SH latch將被阻塞住。Worker X被自己阻塞住了，等待的資源就是PAGEIOLATCH_SH。
8. 當非同步I/O結束後，系統會通知Worker X，你要的資料已經寫入記憶體了，如所示：

9. 這時候EX latch就被釋放。接著Worker X得到了它申請的SH latch。
10. 資料頁1:100終於被Worker X讀到，讀取工作結束，Worker X可以繼續下面的操作。

由此可以看到，在發生PAGEIOLATCH類型的等待時，SQL Server一定是在等待某個I/O動作的完成。所以如果一個SQL Server經常出現這一類的等待，說明磁碟的速度不能滿足SQL Server的需要，它已經成為了SQL Server的一個瓶頸。

要強調的是，PAGEIOLATCH_x類型等待最常見的是兩大類，PAGEIOLATCH_SH和PAGEIOLATCH_EX。PAGEIOLATCH_SH經常發生在使用者正想要去訪問一個資料頁面，而同時SQL Server卻要把這個頁面從磁碟讀往記憶體。如果這個頁面是使用者經常有可能訪問到的，那麼說到底，問題是因為記憶體不夠大，沒能夠將資料頁面始終緩衝在記憶體裡。所以，往往是先有記憶體壓力，觸發SQL Server做了很多讀取頁面的工作，才引發了磁碟讀的瓶頸。這裡的磁碟瓶頸常常是記憶體瓶頸的副產品。

而PAGEIOLATCH_EX常常是發生在使用者對資料頁面做了修改，SQL Server要向磁碟回寫的時候，基本意味著磁碟的寫入速度明顯跟不上。這裡和記憶體瓶頸沒有直接的聯絡。

和磁碟有關的另一個等待狀態是WRITELOG，說明任務當前正在等待將日誌記錄寫入記錄檔。出現這個等待狀態，也意味著磁碟的寫入速度明顯跟不上。

1.4pagelatch和pageiolatch對比

1. pagelatch是為了保護在buff pool中page的正確讀寫，比如說有兩個線程想同時往一個page中插入資料，如果沒有latch控制的話，插入位置存在衝突。所以引入了pagelatch_ex，一次只有一個線程能夠得到pagelatch_ex，只有得到pagelatch_ex的線程才能夠修改page。
2. 而pageiolatch是為了資料的非同步訪問。比如說我們想讀取一個page，但是它不記憶體中，那麼sql server會首先在記憶體中為這個page空出一塊空間，並且加上ex_latch，然後在這個page真正從disk讀取到記憶體當中之前，其他線程不能對這片記憶體進行操作。因為非同步作業，所以這個線程會去訪問這個page，此時申請sh_latch，但是與之前的ex_latch，最終導致自己被自己阻塞了。這就是pageiolatch_sh。