sql server 性能調優資源等待之PAGEIOLATCH

一.概念在介紹資源等待PAGEIOLATCH之前，先來瞭解下從實例級別來分析的各種資源等待的dmv視圖sys.dm_os_wait_stats。它是返回執行的線程所遇到的所有等待的相關信息，該視圖是從一個實際級別來分析的各種等待,它包括200多種類型的等待，需要關註的包括PageIoLatch（磁 ...

一.概念

　　在介紹資源等待PAGEIOLATCH之前，先來瞭解下從實例級別來分析的各種資源等待的dmv視圖sys.dm_os_wait_stats。它是返回執行的線程所遇到的所有等待的相關信息，該視圖是從一個實際級別來分析的各種等待,它包括200多種類型的等待，需要關註的包括PageIoLatch（磁碟I/O讀寫的等待時間）,LCK_xx（鎖的等待時間），WriteLog（日誌寫入等待），PageLatch（頁上閂鎖）Cxpacket（並行等待）等以及其它資源等待排前的。

　　1. 下麵根據總耗時排序來觀察，這裡分析的等待的wait_type 不包括以下

SELECT  wait_type ,
        waiting_tasks_count,
        signal_wait_time_ms ,
        wait_time_ms,
        max_wait_time_ms
FROM    sys.dm_os_wait_stats
WHERE   wait_time_ms > 0
        AND wait_type NOT IN ( 'CLR_SEMAPHORE', 'CLR_AUTO_EVENT',
                               'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE',
                               'SLEEP_TASK', 'SLEEP_SYSTEMTASK',
                               'SQLTRACE_BUFFER_FLUSH', 'WAITFOR',
                               'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE',
                               'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT',
                               'BROKER_TO_FLUSH', 'BROKER_TASK_STOP',
                               'CLR_MANUAL_EVENT',
                               'DISPATCHER_QUEUE_SEMAPHORE',
                               'FT_IFTS_SCHEDULER_IDLE_WAIT',
                               'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN',
                               'SQLTRACE_INCREMENTAL_FLUSH_SLEEP' )
ORDER BY signal_wait_time_ms DESC

下圖排名在前的資源等待是重點需要去關註分析：

　　通過上面的查詢就能找到PAGEIOLATCH_x類型的資源等待，由於是實例級別的統計，想要獲得有意義數據，就需要查看感興趣的時間間隔。如果要間隔來分析，不需要重啟服務，可通過以下命令來重置

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);

　　wait_type:等待類型
　　waiting_tasks_count:該等待類型的等待數
　　wait_time_ms:該等待類型的總等待時間(包括一個進程懸掛狀態(Suspend)和可運行狀態(Runnable)花費的總時間)
　　max_wait_time_ms:該等待類型的最長等待時間
　　signal_wait_time_ms:正在等待的線程從收到信號通知到其開始運行之間的時差(一個進程可運行狀態(Runnable)花費的總時間)
　　io等待時間==wait_time_ms - signal_wait_time_ms

二. PAGEIOLATCH_x

　　2.1 什麼是Latch

　　　　在sql server里latch是輕量級鎖，不同於lock。latch是用來同步sqlserver的內部對象(同步資源訪問)，而lock是用來對於用戶對象包括(表，行，索引等)進行同步，簡單概括：Latch用來保護SQL server內部的一些資源（如page）的物理訪問，可以認為是一個同步對象。而lock則強調邏輯訪問。比如一個table，就是個邏輯上的概念。關於lock鎖這塊在"sql server 鎖與事務撥雲見日"中有詳細說明。

　　2.2 什麼是PageIOLatch　

　　當查詢的數據頁如果在Buffer pool里找到了，則沒有任何等待。否則就會發出一個非同步io操作，將頁面讀入到buffer pool,沒做完之前，連接會保持在PageIoLatch_ex(寫)或PageIoLatch_sh(讀)的等待狀態，是Buffer pool與磁碟之間的等待。它反映了查詢磁碟i/o讀寫的等待時間。
　　當sql server將數據頁面從數據文件里讀入記憶體時，為了防止其他用戶對記憶體里的同一個數據頁面進行訪問，sql server會在記憶體的數據頁同上加一個排它鎖latch,而當任務要讀取緩存在記憶體里的頁面時，會申請一個共用鎖，像是lock一樣，latch也會出現阻塞，根據不同的等待資源，等待狀態有如下：PAGEIOLATCH_DT，PAGEIOLATCH_EX，PAGEIOLATCH_KP，PAGEIOLATCH_SH，PAGEIOLATCH_UP。重點關註PAGEIOLATCH_EX（寫入）和PAGEIOLATCH_SH(讀取)二種等待。

2.1 AGEIOLATCH流程圖

　　有時我們分析當前活動用戶狀態下時，一個有趣的現象是，有時候你發現某個SPID被自己阻塞住了(通過sys.sysprocesses了查看) 為什麼會自己等待自己呢？這個得從SQL server讀取頁的過程說起。SQL server從磁碟讀取一個page的過程如下：

　　(1)：由一個用戶請求，獲取掃描X表,由Worker x去執行。

　　(2)：在掃描過程中找到了它需要的數據頁同1:100。

　　(3)：發麵頁面1:100並不在記憶體中的數據緩存里。

　　(4)：sql server在緩衝池裡找到一個可以存放的頁面空間，在上面加EX的LATCH鎖，防止數據從磁碟里讀出來之前，別人也來讀取或修改這個頁面。

　　(5)：worker x發起一個非同步i/o請求,要求從數據文件里讀出頁面1:100。

　　(6)：由於是非同步i/o(可以理解為一個task子線程)，worker x可以接著做它下麵要做的事情，就是讀出記憶體中的頁面1:100,讀取的動作需要申請一個sh的latch。

　　(7)：由於worker x之前申請了一個EX的LATCH鎖還沒有釋放，所以這個sh的latch將被阻塞住，worker x被自己阻塞住了，等待的資源就是PAGEIOLATCH_SH。

　　最後當非同步i/o結束後，系統會通知worker x，你要的數據已經寫入記憶體了。接著EX的LATCH鎖釋放，worker x申請得到了sh的latch鎖。

總結：首先說worker是一個執行單元,下麵有多個task關聯Worker上， task是運行的最小任務單元，可以這麼理解worker產生了第一個x的task任務，再第5步發起一個非同步i/o請求是第二個task任務。二個task屬於一個worker，worker x被自己阻塞住了。關於任務調度瞭解查看sql server 任務調度與CPU。

2.2 具體分析

　　通過上面瞭解到如果磁碟的速度不能滿足sql server的需要，它就會成為一個瓶頸，通常PAGEIOLATCH_SH 從磁碟讀數據到記憶體，如果記憶體不夠大，當有記憶體壓力時候它會釋放掉緩存數據，數據頁就不會在記憶體的數據緩存里,這樣記憶體問題就導致了磁碟的瓶頸。PAGEIOLATCH_EX是寫入數據，這一般是磁碟的寫入速度明顯跟不上，與記憶體沒有直接關係。

下麵是查詢PAGEIOLATCH_x的資源等待時間：

select wait_type,
waiting_tasks_count,
wait_time_ms ,
max_wait_time_ms,
signal_wait_time_ms
from sys.dm_os_wait_stats
where wait_type like 'PAGEIOLATCH%' 
order by wait_type

下麵是查詢出來的等待信息：

PageIOLatch_SH 總等待時間是(7166603.0-15891)/1000.0/60.0=119.17分鐘，平均耗時是(7166603.0-15891)/297813.0=24.01毫秒,最大等待時間是3159秒。

PageIOLatch_EX 總等待時間是(3002776.0-5727)/1000.0/60.0=49.95分鐘，平均耗時是(3002776.0-5727)/317143.0=9.45毫秒，最大等待時間是1915秒。

關於I/O磁碟 sys.dm_io_virtual_file_stats 函數也做個參考

SELECT  
       MAX(io_stall_read_ms) AS read_ms,
         MAX(num_of_reads) AS read_count,
       MAX(io_stall_read_ms) / MAX(num_of_reads) AS 'Avg Read ms',
         MAX(io_stall_write_ms) AS write_ms,
        MAX(num_of_writes) AS write_count,
         MAX(io_stall_write_ms) /  MAX(num_of_writes) AS 'Avg Write ms'
FROM    sys.dm_io_virtual_file_stats(null, null)
WHERE   num_of_reads > 0 AND num_of_writes > 0

　　總結：PageIOLatch_EX(寫入)跟磁碟的寫入速度有關係。PageIOLatch_SH(讀取)跟記憶體中的數據緩存有關係。通過上面的sql統計查詢，從等待的時間上看，並沒有清晰的評估磁碟性能的標準，但可以做評估基準數據，定期重置，做性能分析。要確定磁碟的壓力，還需要從windows系統性能監視器方面來分析。關於記憶體原理查看”sql server 記憶體初探“磁碟查看"sql server I/O硬碟交互" 。

sql server 性能調優 資源等待之PAGEIOLATCH

一.概念

二. PAGEIOLATCH_x

sql server 性能調優資源等待之PAGEIOLATCH