SQL Server 性能調優(性能基線)

在寫這篇東西的時候我也不是很清楚性能基線，到底要檢查點什麼，dmv要不要檢查，perfmon要檢測那先。

是以我決定，對我發的《sql server 性能調優》文章内的 perfmon和dmv做一個總結。來建立自己的性能基線。

在io中我們要注意哪些性能名額呢？

1. physical disk\disk reads/sec

  --這個應該很清楚 一看就就知道 這個名額是指什麼的

2. physical

disk\ disk writes/sec

一打開文章就看到這2個值，而卻有閥值，看到閥值很開心，因為不用你去收集值了。

• Less than 10 ms = good performance

• Between 10 ms and 20 ms = slow

performance

• Between 20 ms and 50 ms = poor

• Greater than 50 ms = significant

performance problem.

接下來就是 sys.dm_os_wait_stats

中的幾個wait type

3.  PAGEIOLATCH_* 

 PAGEIOLATCH_* 系列的wait type 一共有

PAGEIOLATCH_DT   -- 破壞，什麼是破壞，就是把記憶體中資料頁釋放掉

PAGEIOLATCH_EX   -- x鎖，可以怎麼了解，就是排他占用這個鎖

PAGEIOLATCH_KP   -- 保持，就是保持這個頁不被破壞

PAGEIOLATCH_NL   -- 沒有定義，保留

PAGEIOLATCH_SH   -- 在讀，資料頁的時候就配置設定這個闩

PAGEIOLATCH_UP   -- 在更新的時候配置設定這個            

根據onlinebook的解釋：在任務等待 I/O 請求中緩沖區的闩鎖時發生。闩鎖請求處于“XX”模式。長時間的等待可能訓示磁盤子系統出現問題。

講的直白一點就是系統在io，入讀或寫的時候配置設定的。等待io請求

4. ASYNC_IO_COMPLETION

根據onlinebook的解釋：當某任務正在等待 I/O 完成時出現

這個是等待異步io完成，那麼和上面有沒有關系呢？答案是沒有，上面等待的是io讀取出來，或者寫入。這個是等待系統的異步io完成是不一樣的概念。

5. IO_COMPLETION

根據onlinebook的解釋：在等待 I/O 操作完成時出現。通常，該等待類型表示非資料頁 I/O。資料頁 I/O 完成等待顯示為 PAGEIOLATCH_* waits。

這個就不解釋了說的很明白了就是等待非資料頁的io完成

6. WRITELOG

根據onlinebook的解釋：等待日志重新整理完成時出現。導緻日志重新整理的常見操作是檢查點和事務送出。

這個也不多解釋，就是寫入日志時候等待的時間。

7.Processor/ %Privileged Time  

 --核心級别的cpu使用率

8.Processor/ %User Time    

        --使用者幾倍的cpu使用率

9.Process (sqlservr.exe)/ %Processor

Time    --某個程序的cpu使用率

10.SQLServer:SQL Statistics/Auto-Param Attempts/sec    --試圖運作自動參數化次數

11. SQLServer:SQL

Statistics/Failed Auto-params/sec    

  -- 自動參數化失敗

12. SQLServer:SQL

Statistics/Batch Requests/sec             -- 批處理量

13. SQLServer:SQL

Statistics/SQL Compilations/sec          -- 編譯次數

14.  SQLServer:SQL

Statistics/SQL Re-Compilations/sec    -- 反編譯次數

15.  SQLServer:Plan

Cache/Cache hit Ratio                

           -- 執行計劃，cache命中率

接下來還是 wait event的

16.signal_wait_time_ms --從發出信号到開始運作的時間差，時間花費在等待運作隊列中，是單純的cpu等待。

下面代碼量化的像是signal_wait_time_ms占的比重

SELECT SUM(signal_wait_time_ms) AS TotalSignalWaitTime ,

( SUM(CAST(signal_wait_time_ms AS NUMERIC(20, 2)))

/ SUM(CAST(wait_time_ms AS NUMERIC(20, 2))) * 100 )

AS PercentageSignalWaitsOfTotalTime

FROM sys.dm_os_wait_stats

在建立baseline 的時候 完全可以 按這個sql來擷取值。

17.SOS_SCHEDULER_YIELD等待

onlinebook的解釋:在任務自願為要執行的其他任務生成計劃程式時出現。在該等待期間任務正在等待其量程更新。

完全看不懂，啥叫量程。

直白的說就是：當查詢自動放棄cpu，并且等待恢複執行，這個等待就叫做SOS_SCHEDULER_YIELD。

18.CXPACKET等待

onlinebook:當嘗試同步查詢處理器交換疊代器時出現。如果針對該等待類型的争用成為問題時，可以考慮降低并行度。

直白點就是：處理器之間的一種同步，一般出現在 并發查詢，為啥？因為隻有并發查詢才用多個處理器。

接下來是 sys.dm_os_schedulers 

SELECT scheduler_id ,

current_tasks_count ,

runnable_tasks_count

FROM sys.dm_os_schedulers

WHERE scheduler_id < 255

19.主要是查每個處理器上的任務數和可運作的任務數。

20.SQL

Server :Buffer Manager

又很多有用的計數器都是這 buffer manager 對象下面，可以幫助發現buffer pool滾筒的問題。

21.buffer

cache hit ratio

buffer cache hit ratio一般情況下在oltp中要高于95%，在olap中要高于90%。可惜的是沒有關于這個性能名額相關的解釋，和這個值是如何影響預讀機制的。如果這個名額的值有巨大的下降那麼就說明有問題。這個不能說明記憶體壓力和sql server 健康指數。

22.page life

expectancy

page life expectancy是頁生命周期，也就是一個資料頁在記憶體中的時間。在以前sql server 2000 4g的記憶體已經很大了，sql server buffer

pool的大小是1.6g，如果sql server 從磁盤上讀取1.6g的資料也隻要5分鐘，但是今天64g的記憶體是主流，如果從磁盤一下子讀取50g的記憶體，會嚴重的沖擊io。當存在大量的查詢掃描表，讀入新的資料頁，導緻生命周期值下降也不是不正常的。這個值必須長期的監視來分析問題。

23.Free

Pages

free pages是記憶體中空頁的數量，不要接近于0。這個值說明查詢能否在其他查詢不是放記憶體的情況下，快速的配置設定記憶體的主要依據。如果free pages 很少，頁生命周期很短，并且伴随着空頁争用（free list

stalls/sec）的情況那麼很有可能導緻記憶體壓力。

24.Free list

stalls/sec

Free list stalls/sec每秒空頁等待的數量，如果一段時間内都在0以上那麼說明可能存在記憶體壓力。

25.lazy

write/sec

lazy write/sec 就是每秒寫入磁盤的次數。如果發生量很大并且生命周期很短，free page 很少，但是 free list stall/sec 量很大，那麼就是發生記憶體壓力了。

SQL Server:memory Manager

SQL Server:memory

Manager對象内對記憶體的消費和記憶體管理的問題提供了很重要參考

26.total server memory 和 target server memory

這2個計數器代表了目前sql server 使用的總共記憶體和sql server 想要用的記憶體。如果 target server memory超過了total server memory，也是記憶體壓力的重要标志。sql server 會減少記憶體的需求來接近服務的可用記憶體，或者通過最大伺服器記憶體配置，是以當記憶體出現壓力問題的時候不應該第一時間去檢視這2個計數器

28.memory

grants outstanding

該值是現實多少程序已經成功的擷取了記憶體的授權。在一段時間内，業務高峰期，如果該值過低，那麼标志可能存在記憶體壓力，特别是 memory grants pending 也比較高的情況下。

29. memory

grants pending

該值是有過少程序正在等待記憶體的授權。如果為非0，那麼說明需要調整或者優化負載或者增加記憶體。

每個需要跟蹤的東西我都簡單的解釋了一下。關于 wait event 是累計計數的，在計算的時候需要相減。

這樣跟蹤個一天，設定好頻率，就能得出性能基線了，可以做成圖示，這樣通過圖形就更容易看出問題了。