您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 2005 CPU 性能優化：編譯與重編譯深度解析

編輯：關於SqlServer

前言：識別 CPU 瓶頸的關鍵指標

當 SQL Server 突然出現 CPU 瓶頸時，往往源於查詢未優化、資料庫設定錯誤、設計缺陷或硬體資源不足。在決定增加 CPU 數量或升級更快的處理器前，應優先檢查消耗最多 CPU 資源的操作是否可優化。

效能計數器 Processor: % Processor Time 若持續超過 80%，即可視為 CPU 瓶頸。此外，透過 sys.dm_os_schedulers 檢視 runnable_task_count 是否為非零值，非零代表任務被迫等待時間片，數值越高瓶頸越嚴重。

SELECT scheduler_id, current_task_count, runnable_task_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255;

找出高 CPU 耗用的查詢計畫

以下查詢彙總相同查詢控制代碼的所有陳述式 CPU 耗用，列出前 50 筆最耗資源的批次或程序：

SELECT TOP 50
    SUM(qs.total_worker_time) AS total_cpu_time,
    SUM(qs.execution_count) AS total_execution_count,
    COUNT(*) AS number_of_statements,
    qs.plan_handle
FROM sys.dm_exec_query_stats qs
GROUP BY qs.plan_handle
ORDER BY SUM(qs.total_worker_time) DESC;

過度編譯與重編譯的成因與影響

批次或 RPC 提交前，系統會驗證查詢計畫的有效性。驗證失敗會觸發重編譯，產生新計畫。重編譯屬 CPU 敏感操作，過度發生會導致效能下降。

SQL Server 2000 vs 2005 差異

SQL Server 2000 重編譯整個預存程序；SQL Server 2005 引入陳述式層級重編譯，僅重編譯觸發語句，減少 CPU 頻寬與編譯鎖爭用。

常見重編譯原因

結構描述變更
統計資料變更
延遲編譯
SET 選項變更
暫存表變更
以 WITH RECOMPILE 建立的預存程序

偵測工具與方法

系統監視器

監控 SQL Statistics 物件的三個關鍵計數器：

Batch Requests/sec
SQL Compilations/sec
SQL Recompilations/sec

理想狀態下，Recompilations/sec 與 Batch Requests/sec 比率極低（即席查詢除外）。

SQL Server Profiler 追蹤

使用事件類別：

SP:Recompile（事件類別 37）
CursorRecompile（75）
SQL:StmtRecompile（166）

EventSubClass 欄位揭示重編譯原因。查詢追蹤檔：

SELECT spid, starttime, textdata, eventsubclass, objectid, databaseid, sqlhandle
FROM fn_trace_gettable('filepath.trc', 1)
WHERE EventClass IN (37, 75, 166);

Showplan XML For Query Compile

編譯/重編譯時觸發，包含查詢計畫與物件 ID，協助找出頻繁重編譯的陳述式。

動態管理檢視

sys.dm_exec_query_optimizer_info 顯示優化耗用時間（ElASPsed time 接近 CPU 時間）。

sys.dm_exec_query_stats 中的 plan_generation_num 表示編譯次數。查詢前 25 筆重編譯最多的程序：

SELECT TOP 25
    sql_text.text, sql_handle, plan_generation_num,
    execution_count, dbid, objectid
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sql_text
WHERE plan_generation_num > 1
ORDER BY plan_generation_num DESC;

解決策略

SET 選項導致重編譯：用 Profiler 確認變更的 SET 選項，避免在預存程序內修改，改在連接層級設定並保持不變。
暫存表統計變更：考慮改用資料表變數（無統計資料，可能影響優化），或使用 KEEP PLAN 提示將暫存表臨界值提升至永久表等級。
統計資料變更導致重編譯：指定 KEEPFIXED PLAN 提示，僅在正確性相關變更（結構異動、sp_recompile）時重編譯。
關閉自動更新統計資料：非推薦，可能導致非最佳計畫。
使用完整物件名稱（如 dbo.table1）避免重編譯與模稜兩可。
避免延遲編譯：勿在條件結構（IF）中混用 DML/DDL。
執行 DTA (Database Engine Tuning Advisor) 分析編譯與執行時間優化建議。
檢查 WITH RECOMPILE：SQL Server 2005 改用陳述式層級 OPTION (RECOMPILE)，避免整個程序強制重編譯。