Performance Monitor4：監控SQL Server的IO效能

標籤：時間間隔   取數   釋放   操作   重建   檔案的   gif   world   載入   

SQL Server的IO效能受到物理Disk的IO延遲和SQL Server內部執行的IO操作的影響。在監控Disk效能時，最主要的度量值（metric）是IO延遲，IO延遲是指從Application建立IO請求，到Disk完成IO請求的時間延遲。如果物理Disk不能及時完成IO請求，跟不上請求負載的速度，那麼SQL Server就容易出現效能問題。SQL Server內部在執行一些特定的操作時，會和Disk做讀寫互動，這也會影響物理硬碟響應SQL Server的IO請求的效能，使查詢進程處於PageIOLatch或WriteLog等待。

一，在系統層級監控物理Disk的IO效能

1，監控物理Disk的IO延遲

在Windows層級上對Physical Disk的IO延遲進行分析，主要依賴於Performance Monitor的計數器，衡量物理Disk的IO延遲的計數器主要有三個：

• Avg. Disk sec/Transfer：Disk每一次讀寫操作所用的平均時間
• Avg. Disk sec/Read：Disk每一次讀操作所用的平均時間 
• Avg. Disk sec/Write：Disk每一次寫操作所用的平均時間

avg.Disk sec/(Transfer,Read,Write)，能夠很好的反映Disk的IO速度，推薦的衡量Disk的IO速度的基準（baseline）:

• 很快：<10ms
• 一般：10-20ms
• 有點慢：20-50ms
• 非常慢：>50ms

2，分析Data Collector收集的計數器數值

是產品環境中一台Server的計數器數值圖表，將IO延遲的度量值按比例放大1000倍，這樣圖表顯示的單位就是ms。

• %Idle Time：在60%左右浮動，說明Disk不是很忙碌
• Avg.Disk sec/Write：大多數情況下都是10ms以下，偶爾波動，說明Disk的寫延遲比較低
• Avg.Disk sec/Read：讀延遲大多數情況下都是在40ms以上，鮮有低於40ms，偶爾達到峰值，說明Disk的讀延遲非常高
• Avg.Disk sec/Transfer：讀寫延遲的均值在30ms左右，時有波動，在%Idle Time曲線不波動時，Disk的讀寫延遲也有波動，說明Disk的讀寫延遲不穩定

初步判斷，Disk的讀寫延遲非常高，Disk的IO效能較差，IO速度慢

3，監控物理Disk的IO次數

根據Disk的IO次數來界定Disk效能，沒有統一的閾值，一般通過監控計數值來擷取一個趨勢，設定一個基準，如果在Disk比較忙碌時，遇到異常的穀值，那麼就需要查看是否出現參數嗅探問題和Disk IO密集的查詢，異常的穀值一般是由查詢語句請求的資料量太多造成的，需要對查詢語句進行效能調優。

系統級經常用到的Disk效能計數器是PhysicalDisk計數器：

• Avg. Disk Queue Length ：提供Disk阻塞程度的主要度量值，表示在 sample interval期間，Disk等待處理的IO請求隊列的平均長度，即等待被Disk處理的IO請求的數量
• % Idle Time：Disk的空閑程度，可以反推出Disk的忙碌程度
• Disk (Reads/Writes/Transfers)/sec：每秒Disk執行讀寫運算元量

隊列長度波動很大，在%Idle Time 升高時，IO數量降低，沒有發現明顯的異常穀值。

4，監控物理Disk讀寫的資料量

這幾個計數值，對監控物理Disk的讀寫效能，意義不大，僅僅作為參考。

• Avg.Disk Bytes/(Read,Write,Transfer)表示：在物理Disk執行讀寫操作時，物理Disk從Disk讀取到記憶體的位元組數量，從記憶體寫入到Disk的位元組數量，以及兩者的總位元組數量
• Disk Bytes/sec：在物理Disk執行讀寫操作時，資料從Disk傳輸到記憶體，或從記憶體寫入到Disk的位元組速度，好的Disk，其值在20-40MB之間，一般Disk，其值在20MB以下

二，SQL Server內部操作對Disk IO效能的影響

SQL Server能夠緩衝從Disk載入的資料頁，正常情況下，大部分操作不需要任何物理讀操作，不需要Disk的物理IO參與就能完成，但是，有一些操作，必須和物理Disk進行IO操作，才能完成。SQL Server和物理Disk進行IO互動的操作：

• 對於記憶體中沒有緩衝的資料，第一次訪問時，需要將資料從資料檔案讀取到記憶體中，SQL Server訪問的任何資料必須緩衝到記憶體中，如果不在記憶體中，SQL Server發送讀請求，將資料頁從物理Disk讀取到記憶體中，這個過程叫做物理讀；如果資料存在於記憶體中，SQL Server直接存取，這個過程叫做邏輯讀。
• 在任何修改操作提交之前，預寫交易記錄記錄到記錄檔，在CheckPoint和LazyWriter運行時，資料被寫入資料檔案。
• 執行CheckPoint時，將緩衝中的髒頁寫入資料檔案，髒頁是指載入記憶體之後被修改過的資料頁，記憶體中的資料和資料檔案中的資料不一致，由CheckPoint觸發的物理寫操作和內壓沒有關係，和使用者修改的資料量有關，用於控制還原的時間間隔。
• 當Buffer Pool空間不足，Free Buffer List減少到臨界值時，LazyWriter進程主動將記憶體中的一些很久沒有訪問過資料頁面和執行計畫清空，如果資料頁面是髒頁，那麼將其寫入到資料檔案，LazyWriter和記憶體壓力有關，由於記憶體可用的Free Buffer不足導致LazyWriter進程執行清理操作。
• IO密集型操作，比如檢查資料庫的一致性（DBCC CheckDB），重建索引，更新統計資料，Database Backup等，會帶來大量的Disk IO操作

SQL Server只會讀取資料檔案，只要資料緩衝在記憶體中，理想情況下，SQL Server不會執行任何物理讀操作，也不需要從物理Disk載入資料到記憶體，SQL Server執行讀取操作效能和記憶體的緩衝能力有直接關係，也和使用者讀取的資料量有關。

SQL Server的寫操作分為寫資料檔案和寫記錄檔。寫入記錄檔的資料量，完全由資料修改量決定，和記憶體壓力沒有關係；寫入資料檔案的數量，主要和修改量有關。LazyWriter和記憶體壓力有關係，一旦記憶體有壓力，LazyWriter自動啟動，負責清理最久未被訪問的緩衝，釋放記憶體，增加可用的Free buffer數量。

因此，SQL Server請求的物理Disk的讀運算元量和記憶體有直接關係，記憶體越充足，緩衝的資料量越多，物理Disk的讀操作的數量就會越少，邏輯讀的數量不會減少；SQL Server請求的物理Disk的寫運算元量和使用者執行的資料修改量有直接關係，和記憶體是否存在壓力關係很微小。在執行物理disk的讀寫請求時，SQL Server的查詢進程產生PageIOLatch等待，表示進程正在執行物理讀操作，該等待可以從DMV：sys.dm_exec_requests 查看到：

 View Code

PageIOLatch 等待：表示進程正在從物理Disk載入資料到記憶體，即進程在進行物理讀操作，從Reads欄位能夠看到物理讀的數量

WriteLog 等待：表示事務正在修改資料，SQL Server將預先將交易記錄記錄寫入到交易記錄檔

 

參考文檔：

Memory - Lazy Writer and Checkpoint

SQL Server disk performance metrics – Part 1 – the most important disk performance metrics

Measuring Disk Latency with Windows Performance Monitor (Perfmon)

Performance Monitor4：監控SQL Server的IO效能