我們日常編寫的 SQL 語句本質上只是描述「獲取什麼數據」的邏輯,而非「如何獲取」的物理路徑。當 SQL 陳述式送達 SQL Server 後,查詢分析器會依序經過解析、綁定、查詢優化與執行四個階段。前三個階段完成後即產生執行計劃——也就是 SQL Server 依循的物理存取路徑。本文重點不在查詢優化器本身,而是探討執行計劃生成後的緩存機制與常見問題。
生成執行計劃的過程極為耗費 CPU 與記憶體資源。查詢優化器的工作大致分三步:
以 SELECT * FROM A INNER JOIN B ON a.a=b.b INNER JOIN C ON c.c=a.a 為例,聯結順序不同皆能得出相同結果,產生多個候選計劃。SQL Server 依據基數估算與 CPU/IO 成本選擇計劃,高度依賴統計資訊的準確性。由於編譯成本高昂,將計劃緩存以便重用,便能大幅降低重複編譯開銷,這就是執行計劃緩存存在的初衷。
透過 sys.dm_exec_cached_plans DMV 可檢視已緩存的計劃;sys.dm_os_memory_cache_counters 則能查看各類物件佔用的記憶體大小。
執行計劃緩存屬於緩存的一種,物件會依演算法被替換。主要依據為記憶體壓力,分為:
最佳實務提醒:緩存鍵值為查詢文本本身,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,此法無額外負擔且更輕量。但有幾項限制:
執行計劃選擇高度依賴統計資訊,相同語句因參數不同可能產生截然不同的最佳計劃。但若查詢包裝在預存程序中,首次執行時的參數值會被「嗅探」並鎖定計劃,後續執行即便參數大幅變化仍復用舊計劃,導致效能災難。
例如:參數值 1 只回傳 1 筆資料(適合索引搜尋),參數值 4 回傳 5,000 筆(適合索引掃描)。若首次以參數 1 執行,緩存了「索引搜尋」計劃,後續以參數 4 執行時仍沿用該計劃,造成 5,000 次 Key Lookup,效能極差。且緩存內容不可控、隨時失效,讓 DBA 難以排查。
這揭示了核心矛盾:查詢優化器追求當下最佳計劃,執行計劃緩存追求最大程度重用,兩者在參數敏感查詢上產生衝突。下篇將續探常見問題與解決方案。
本文梳理了查詢優化器生成計劃的流程、緩存的四類物件、淘汰機制、調優應用,並點出參數嗅探導致的計劃重用衝突。下篇將深入探討編譯/重編譯問題與解決策略。