Performance Monitor4：監控SQL Server的IO性能

SQL Server的IO性能受到物理Disk的IO延遲和SQL Server內部執行的IO操作的影響。在監控Disk性能時，最主要的度量值（metric）是IO延遲，IO延遲是指從Application創建IO請求，到Disk完成IO請求的時間延遲。如果物理Disk不能及時完成IO請求，跟不上請求 ...

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），重建索引，更新統計信息，資料庫備份等，會帶來大量的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 查看到：

select  r.session_id,
        r.blocking_session_id as blocking,
        r.wait_type as Current_Wait_Type,
        r.wait_resource, 
        r.last_wait_type,
        r.wait_time,
        r.status,
        r.command,
        r.cpu_time,r.reads,r.writes,r.logical_reads,
        r.total_elapsed_time,
        r.start_time,
        db_name(r.database_id) as database_name,
        SUBSTRING(  st.text, 
                    r.statement_start_offset/2+1,
                    (    CASE WHEN r.statement_end_offset = -1 
                                THEN LEN(CONVERT(NVARCHAR(MAX), st.text))
                            ELSE (r.statement_end_offset - r.statement_start_offset)/2
                        END 
                    )
                ) as IndividualQueryStatement
from sys.dm_exec_requests r
outer APPLY sys.dm_exec_sql_text(r.sql_handle) as st
where (r.wait_type<>'MISCELLANEOUS' or r.wait_type is null)
    and r.session_id>50
    and r.session_id<>@@spid