前言：建立效能基準線的關鍵
效能調優的前提是「測量」。SQL Server 提供數百個效能計數器，但真正關鍵、可採取行動的僅有核心 20-30 個。本文整理 29 個必監控指標，附帶臨界值、計算邏輯與排查方向，助您快速定位瓶頸。
一、系統層級指標（OS & Hardware）
| 計數器 | 物件/實例 | 警示閾值 | 含義與對策 |
|---|
| % Processor Time | Processor(_Total) | > 85% 持續 | CPU 繁忙。檢查：高編譯/重編譯、缺失索引、平行度過高、執行計畫劣化 |
| % User Time | Processor(_Total) | > 70% | 使用者模式 CPU（排序、聚合、聯接）。優化：索引、簡化聯接、水平分割 |
| Processor Queue Length | System | > 2 x CPU 核心數 | 等待 CPU 的執行緒佇列長度。持續偏高 = CPU 壓力 |
| Interrupts/sec | Processor(_Total) | > 3000 | 硬體中斷頻率。過高可能為驅動程式或硬體問題 |
| Pages/sec | Memory | > 50 (持續) | 硬分頁率（含讀寫）。> 0 即表示記憶體壓力；配合 Page Reads/sec > 5 更嚴重 |
| Available MBytes | Memory | < 5% 總實體 RAM | 可用記憶體。過低會觸發分頁、緩衝區壓縮 |
| Page Faults/sec | Memory | 持續極高 | 軟+硬分頁。偶爾尖峰正常，持續高 = 記憶體瓶頸 |
| Avg. Disk sec/Read | PhysicalDisk(_Total) | > 25 ms (關鍵任務 > 10 ms) | 平均讀取延遲。超過閾值 = 儲存子系統瓶頸 |
| Avg. Disk sec/Write | PhysicalDisk(_Total) | > 25 ms (關鍵任務 > 10 ms) | 平均寫入延遲。檢查：RAID 卡快取、磁碟壽命、IOPS 上限 |
| Avg. Disk Queue Length | PhysicalDisk(_Total) | > 2 x 磁碟數 | 佇列深度。RAID 群視為單一磁碟計算 |
| Disk Reads/sec / Writes/sec | PhysicalDisk(_Total) | 接近廠商規格上限 | IOPS 使用率。Ultra320 SCSI 約 50-70 IOPS/磁碟；SSD 可達萬級 |
| % Idle Time | PhysicalDisk | < 20% | 磁碟閒置時間。過低 = 磁碟飽和 |
| Bytes Total/sec | Network Interface | > 70% 頻寬 | 網路吞吐。鎖定：資料庫鏡像、AlwaysOn、大量資料搬移 |
二、SQL Server 核心指標
鎖與封鎖
| 計數器 | 警示閾值 | 分析方向 |
|---|
| Lock Waits/sec (_Total) | > 1000 | 封鎖頻率。配合 Average Wait Time (ms) 判斷嚴重度 |
| Average Wait Time (ms) (_Total) | > 50 ms | 平均等待時間。高值 = 激烈鎖爭用 |
| Lock Timeouts/sec (_Total) | > 0 | 鎖逾時。通常伴隨應用程式錯誤 |
| Number of Deadlocks/sec (_Total) | > 0 | 死鎖率。需捕獲死鎖圖 (Trace Flag 1222 / Extended Events) 分析 |
緩衝區與快取
| 計數器 | 理想值 | 說明 |
|---|
| Buffer cache hit ratio | > 95% (OLTP > 99%) | 資料頁在記憶體命中率。低於 90% 需加記憶體或檢查計畫 |
| Page life expectancy (PLE) | > 300 秒 | 頁面在緩衝區存活秒數。持續 < 300 = 記憶體不足或掃描過多 |
| Checkpoint pages/sec | 視工作負載 | 檢查點寫入頁數/秒。尖峰對應 PLE 下降，屬正常 |
| Lazy writes/sec | 接近 0 | 惰性寫入頻率。持續 > 0 = 記憶體壓力迫使髒頁刷盤 |
存取方法與編譯
| 計數器 | 警示閾值 | 對策 |
|---|
| Full Scans/sec | > 5 | 全表掃描頻率。檢查缺失索引、統計資料、查詢寫法 |
| Page Splits/sec | 越低越好 | 頁面分裂。降低 Fill Factor、重建索引、避免隨機 GUID 叢集鍵 |
| Worktables Created/sec | 持續高 | 臨時工作表。可能為排序溢出、雜湊聯接、游標 |
| SQL Compilations/sec | 穩定後應極低 | 編譯頻率。高值 = 參數化不足、Ad-hoc 計畫洩漏 |
| SQL Re-Compilations/sec | < 10% of Compilations | 重編譯率。檢查統計資料更新、結構變更、SET 選項變化 |
| Batch Requests/sec | 越高越好 (吞吐量) | 批次請求率。基準線建立後，驟降 = 阻塞或資源耗盡 |
記憶體管理
| 計數器 | 警示閾值 | 說明 |
|---|
| Memory Grants Pending | > 0 持續 | 等待記憶體授與的查詢數。> 0 = 記憶體不足導致排序/雜湊溢出 |
資料庫檔案
| 計數器 | 用途 |
|---|
| Data File(s) Size (KB) | 監控資料檔成長、規劃容量 |
| Log File(s) Size (KB) | 監控記錄檔大小、預防爆滿 |
| Log File(s) Used Size (KB) | 即時使用量，配合備份策略調整 |
閂鎖
| 計數器 | 警示閾值 | 說明 |
|---|
| Latch Waits/sec | > 100 | 閂鎖等待頻率。高值 = 熱門頁面爭用 (如 PFS、GAM、索引根頁) |
| Average Latch Wait Time (ms) | > 10 ms | 平均閂鎖等待。嚴重資源爭用指標 |
三、實戰監控策略建議
- 建立基準線：連續 7-14 天收集上述指標，記錄 P50/P95/P99 百分位數
- 分層告警：
- L1 (資訊)：單一指標短暫超標
- L2 (警告)：核心指標 (PLE、Disk Latency、Lock Waits) 持續 5 分鐘超標
- L3 (嚴重)：多項核心指標同時異常、或 Deadlocks > 0
- 工具鏈：
- 收集：PerfMon Data Collector Set / Telegraf + InfluxDB
- 視覺化：Grafana / Power BI
- 深度診斷：sp_WhoIsActive, Query Store, Extended Events, Wait Stats 分析
- 定期複盤：每月檢視前 10 大等待類型、前 10 消耗資源查詢、索引使用率報表
四、快速參考卡：Top 5 必看指標
| 優先級 | 指標 | 健康閾值 | 異常首要檢查 |
|---|
| 1 | Page Life Expectancy | > 300 秒 | 記憶體容量、全表掃描、索引碎片 |
| 2 | Avg. Disk sec/Read/Write | < 10-15 ms | 儲存子系統、檔案群佈局、tempdb 壓力 |
| 3 | Buffer Cache Hit Ratio | > 95% | 記憶體壓力、計畫快取污染、參數化 |
| 4 | Lock Waits/sec + Avg Wait Time | < 1000 / < 50ms | 長時間交易、索引缺失、隔離等級 |
| 5 | Batch Requests/sec | 基準線 ± 20% | 阻塞、CPU 飽和、網路延遲 |
將這 29 個指標納入日常監控體系，配合 Query Store 與 Wait Stats 深度分析，即可從「被動救火」轉型為「主動效能治理」，確保 SQL Server 持續在最佳狀態服務業務。
