萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 執行計劃緩存深度解析(上)

SQL Server 執行計劃緩存深度解析(上)

我們日常編寫的 SQL 語句本質上只是描述「獲取什麼數據」的邏輯,而非「如何獲取」的物理路徑。當 SQL 陳述式送達 SQL Server 後,查詢分析器會依序經過解析、綁定、查詢優化與執行四個階段。前三個階段完成後即產生執行計劃——也就是 SQL Server 依循的物理存取路徑。本文重點不在查詢優化器本身,而是探討執行計劃生成後的緩存機制與常見問題。

為何需要執行計劃緩存?

生成執行計劃的過程極為耗費 CPU 與記憶體資源。查詢優化器的工作大致分三步:

  1. 解析查詢文本中的資料表、預存程序、檢視表名稱,並建立代表查詢邏輯的樹狀結構。
  2. 優化與簡化:將子查詢轉為等效聯結、優先套用篩選條件、移除不必要的聯結(如有索引可覆蓋查詢則無需存取基底資料表)。
  3. 根據統計資訊進行基於成本的評估,從多個候選計劃中挑選「夠好」的計劃(不一定是最佳),以降低評估本身的開銷。

以 SELECT * FROM A INNER JOIN B ON a.a=b.b INNER JOIN C ON c.c=a.a 為例,聯結順序不同皆能得出相同結果,產生多個候選計劃。SQL Server 依據基數估算與 CPU/IO 成本選擇計劃,高度依賴統計資訊的準確性。由於編譯成本高昂,將計劃緩存以便重用,便能大幅降低重複編譯開銷,這就是執行計劃緩存存在的初衷。

執行計劃緩存的四類物件

  1. 編譯後的計劃:類似 MSIL 之於 C#,為可重用的邏輯結構。
  2. 執行上下文:包含 SET 選項、區域變數值等會話專屬資訊,多使用者可共用同一編譯計劃,但各自擁有獨立的執行上下文。
  3. 游標:儲存游標狀態,游標本身專屬於單一連線,但其關聯的執行計劃可共用。
  4. 代數樹:代表查詢文本的解析樹。因檢視表、DEFAULT、約束等可能被多查詢重用,緩存代數樹可省去重複解析。

透過 sys.dm_exec_cached_plans DMV 可檢視已緩存的計劃;sys.dm_os_memory_cache_counters 則能查看各類物件佔用的記憶體大小。

緩存淘汰機制與內存壓力

執行計劃緩存屬於緩存的一種,物件會依演算法被替換。主要依據為記憶體壓力,分為:

  • 外部壓力:Buffer Pool 可用空間降至臨界值(視實體記憶體或 max server memory 設定而定)。
  • 內部壓力:計劃緩存物件數超過閾值(32 位元為 40,000;64 位元提升至 160,000)。

最佳實務提醒:緩存鍵值為查詢文本本身,SELECT * FROM SchemaName.TableName 與 SELECT * FROM TableName 雖語意相同,卻會產生兩份計劃。建議引用物件時一律加上結構描述名稱,避免計劃膨脹。

善用緩存計劃進行調優

緩存計劃蘊含豐富統計資訊(CPU 時間、等待時間、執行次數等),但僅統計執行時間,不含編譯時間。以下查詢可找出總耗時最長的 20 陳述式:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
  CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28,2)) AS [Total Duration (s)],
  CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time AS DECIMAL(28,2)) AS [% CPU],
  CAST((qs.total_elapsed_time - qs.total_worker_time) * 100.0 / qs.total_elapsed_time AS DECIMAL(28,2)) AS [% Waiting],
  qs.execution_count,
  CAST(qs.total_elapsed_time / 1000000.0 / qs.execution_count AS DECIMAL(28,2)) AS [Average Duration (s)],
  SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
    ((CASE WHEN qs.statement_end_offset=-1 THEN LEN(CONVERT(NVARCHAR(MAX),qt.text))*2 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS [Individual Query],
  qt.text AS [Parent Query],
  DB_NAME(qt.dbid) AS DatabaseName,
  qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qs.total_elapsed_time > 0
ORDER BY qs.total_elapsed_time DESC;

相比伺服器端 Trace 或 Profiler,此法無額外負擔且更輕量。但有幾項限制:

  • 索引重建、更新統計等 DDL 不緩存,卻可能極耗資源。
  • 緩存隨時可能被淘汰,無法看到已失效的計劃。
  • 無法觀測編譯成本。
  • 未參數化的查詢可能產生多份計劃,導致統計無法正確累加。

執行計劃緩存與查詢優化器的衝突:參數嗅探問題

執行計劃選擇高度依賴統計資訊,相同語句因參數不同可能產生截然不同的最佳計劃。但若查詢包裝在預存程序中,首次執行時的參數值會被「嗅探」並鎖定計劃,後續執行即便參數大幅變化仍復用舊計劃,導致效能災難。

例如:參數值 1 只回傳 1 筆資料(適合索引搜尋),參數值 4 回傳 5,000 筆(適合索引掃描)。若首次以參數 1 執行,緩存了「索引搜尋」計劃,後續以參數 4 執行時仍沿用該計劃,造成 5,000 次 Key Lookup,效能極差。且緩存內容不可控、隨時失效,讓 DBA 難以排查。

這揭示了核心矛盾:查詢優化器追求當下最佳計劃,執行計劃緩存追求最大程度重用,兩者在參數敏感查詢上產生衝突。下篇將續探常見問題與解決方案。

小結

本文梳理了查詢優化器生成計劃的流程、緩存的四類物件、淘汰機制、調優應用,並點出參數嗅探導致的計劃重用衝突。下篇將深入探討編譯/重編譯問題與解決策略。

copyright © 萬盛學電腦網 all rights reserved