[SQL SERVER]SQL Server Perfmon Counters
我自己常透過Performance Counter來檢視SQL Server相關效能水準是否如預期
錄製時間範圍我大概抓2~5個禮拜，也可以透過sys.dm_os_performance_counters 查看相關效能計數器
這篇來整理一下自己常用的SQL Server Perfmon Counters門檻值。
note:硬體部分可參考[SQL SERVER][Performance]找出硬體Bottleneck
| 名稱 | 建議值 | 說明 |
SQLServer: Buffer Manager: Buffer cache hit ratio |
>95% 優:99.999% |
從buffer讀取data page擊中率，該值越高表示資料都是從記憶體中讀取 |
SQLServer: Buffer Manager: Page life expectancy |
>300 停留超過5分鐘 |
data page留在buffer的時間，停留時間越長，表示SQL Server不需要從disk讀取資料到memory，停留時間越短，表示Server可能需要增加記憶體 |
SQLServer: Buffer Manager: Checkpoint Pages / Sec |
將記憶體中的Dirty Page 和log page寫入disk，checkpoint頻率和 recovery interval設定有關聯，如果該值太高可能表示記憶體不足或是 recovery interval設定太高(預設0表示每分鐘處理) | |
|
SQL Server: Databases:Log Flushes/sec |
log page每秒寫入transaction log file頻率，該值太高可能是交易處理邏輯不良導致交易次數太高，如1000筆資料每一次commit該值就會增加1000，如果批次分成2個交易(每500筆commit)該值只會增加2 | |
|
SQL Server: Databases:Log Growths |
0 | transaction log file被擴大次數，每一次當transaction log file被擴大時，所有使用者活動必須被停止，直到transaction log file成長完畢，這通常表示transaction log file空間沒有被reuse或是初始值設定不良所導致，要避免使用者請求處理期間被擴大影響效能 |
| SQLServer: SQL Statistics: Batch Requests/Sec | 每一秒處理請求的數量，該值通常和硬體等級有關，也通常表示SQL Server吞吐量，基本上透過該值需和其他值互相搭配參考。 | |
| SQLServer: SQL Statistics: SQL Compilations/Sec | < Batch Requests/Sec *10% | 每秒TSQL發生編譯次數(包含重新編譯)，編譯是一個昂貴操作，基本上該值越低越好，越高可能 ad hoc查詢過多，或使用不必要的recompile提示，可能須適當改寫TSQL降低編譯次數。 |
| SQLServer: SQL Statistics: SQL Re-Compilations/Sec | <Compilations/Sec *10% | 每秒發生重新編譯次數，該值應該要接近0，因為重新編譯可能導致deadlock和exclusive compile locks，該值需參考Batch Requests/Sec和SQL Compilations/Sec |
| SQLServer: Access Methods: Page Splits / Sec | < 100 Batch Requests/Sec *20% | 每秒發生頁面分割次數，頁面分割是昂貴操作，要減少頁面分割次數，需要查看資料表和索引設計，避免非順序資料新增或是針對高寫資料表調整索引填充因子。 |
SQLServer: General Statistic: Processes Block |
process blocked another process數量，理想情況下該值應該接近0，但實務上短暫封鎖是可接受的，如果該值太高你應該詳細調查是那些因素造成，以我個人經驗大多都是不良的平行處理所造成(參考SQL SERVER]找出封鎖的處理序) | |
| SQLServer: Locks:
Lock Wait Time (ms) |
<10 ms | thread請求lock等待時間，該值過高可能是blocking、不必要的鎖定擴大或是TSQL不佳所造成，也有可能是I/O問題(硬碟壞一顆)，透過 sys.dm_os_waiting_tasks 查看鎖定資源，調整TSQL、設計正確索引或是最小化鎖定(nolock) |
| SQLServer: Locks: Lock Waits / Sec | 0 | thread請求lock等待時間。 整體lock時間應該要越短越好，例如應該盡量縮短交易處理時間，避免大交易處理(可拆多個小交易) |
| SQLServer: Locks: Avg Wait Time (ms) | <500 ms | 每個lock請求等待時間，一般來說平均超過500ms表示有過多的封鎖，該值需參考Lock Waits/sec 取得一個平衡。 |
| SQLServer: Locks: Deadlock/s | <1 | 每秒死結次數，該值應該等於0，該值太高表示交易處理邏輯不良，或是沒有使用正確索引導致都是full scan，需重新調整邏輯處理或是改善TSQL效能。 |
| SQL Server: Latches : Latch Waits/sec |
(Total Latch Wait Time) / (Latch Waits/ Sec) < 10 |
該數值表示無法立即授予Latch的請求。 Latch是SQL Serve內部用來同步資源一種資料結構，和操作系統的read/write lock類似，Latch保護資源使得資料存取井然有序，例如說當某一個session取得某個資源latch獨佔時，其他session如果也需要該資源latch時，則必須等待該latch釋放才可存取。 大方向來說，latch分兩大類，一buffer latch、另一I/O latch，buffer latch一般<1 ms，I/O latch一般<10 ms。 note:PAGELATCH_SH wait for PFS or SGAM pages in tempdb，表示tempdb有嚴重的資源競爭，透過切割tempdb datafile和啟用TF1118可獲得改善(參考[SQL SERVER]了解制式範圍和混和範圍) |
| SQL Server: Latches :
Avg Latch Wait Time (ms) |
<1 ms | 請求latch所等待時間。 |
| SQL Server: Latches :
Total Latch Wait Time (ms) |
(Total Latch Wait Time) / (Latch Waits/ Sec) < 10 |
該數值表示最後Latch請求的總等待時間，該值可提供其他計數器參考使用 |
參考
[SQL SERVER][Performance]找出硬體Bottleneck
SQL Server Performance, Best Practices, & Productivity