SQL Server 效能調優的核心在於記憶體管理。緩衝區管理器 負責資料頁面快取,快取管理器 負責執行計畫與物件中繼資料快取。本文解析兩大管理器的核心計數器、運作機制與監控實戰。
管理資料庫頁面 (8KB) 在記憶體中的快取,是效能最關鍵的組件。
| 計數器 | 含義 | 健康基準 | 異常處理 |
|---|---|---|---|
| Buffer cache hit ratio | 資料頁面在記憶體中命中率 | > 99% (OLTP),> 90% (OLAP) | 增加記憶體、優化查詢減少掃描、檢查計畫快取汙染 |
| Page life expectancy (PLE) | 頁面在快存活秒數 | > 300 秒 (5 分鐘) | < 300 秒表示記憶體壓力,檢查大掃描查詢、索引碎片 |
| Free pages | 可用空閒頁面數 | > 總頁面 10% | 過低觸發 Lazy Writer 強制刷盤,影響效能 |
| Database pages | 目前快取資料庫頁面總數 | 隨資料庫大小變化 | 配合 PLE 判斷快取熱度 |
| Lazy writes/sec | Lazy Writer 每秒寫入磁碟頁數 | 接近 0 | 持續 > 0 表示記憶體嚴重不足 |
| Checkpoint pages/sec | 檢查點每秒刷新頁數 | 視復原間隔而定 | 尖峰過高影響 IO,調整 recovery interval |
管理預存程序、臨時 SQL、觸發器等編譯後的執行計畫。
各類別 (Adhoc SQL、Prepared SQL、Procedures、Triggers 等) 均有專屬計數器:
大量不重用的動態 SQL 佔滿計畫快取:
-- 檢查 Adhoc 計畫佔用
SELECT objtype, COUNT(*) AS plan_count,
SUM(size_in_bytes)/1024/1024 AS size_mb
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY size_mb DESC;
sp_configure 'optimize for ad hoc workloads', 1; RECONFIGURE — 首次執行只存存根,第二次才存完整計畫ALTER DATABASE ... SET PARAMETERIZATION FORCED (謹慎測試)以下 SET 選項不同會導致產生不同執行計畫 (相同查詢文本但選項不同 = 不同計畫):
ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGSARITHABORT (極關鍵,SSMS 預設 ON,應用連線常為 OFF)QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULLNUMERIC_ROUNDABORT最佳實踐:應用程式連線字串統一設定標準選項組合,確保計畫重用。
CREATE PROCEDURE 權限:sysadmin、db_owner、db_ddladmindbo.TableName-- 綜合快取健康度檢查
SELECT
'Buffer Cache Hit Ratio' AS Metric,
CAST(cntr_value AS FLOAT) / NULLIF(CAST(cntr_value AS FLOAT), 0) * 100 AS Value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio' AND instance_name = ''
UNION ALL
SELECT
'Page Life Expectancy (sec)',
cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'
UNION ALL
SELECT
'Plan Cache Hit Ratio',
CAST(cntr_value AS FLOAT) / NULLIF(CAST(cntr_value AS FLOAT), 0) * 100
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Cache Hit Ratio' AND instance_name = '_Total';
SQL Server 快取雙核心:**Buffer Manager 管資料,Cache Manager 管計畫**。
監控三大黃金指標:Buffer Hit Ratio > 99%、PLE > 300s、Plan Cache Hit Ratio > 90%。
記憶體壓力首選優化:索引優化減少掃描 > 參數化減少計畫膨脹 > 增加物理記憶體。