面對萬張表索引優化的工程挑戰,本文分享一套基於**執行計畫 XML 解析**的自動化索引重構工具設計思路:從計畫快取收集 → XML 解析謂詞統計 → 智能索引組合生成 → 覆蓋/合並/剔除決策 → 安全發布,附核心 XSD 節點映射表。
| 謂詞類型 | Showplan XML 節點特徵 | 提取邏輯 |
|---|---|---|
| Seek Key (Where/Join 等值) | <SeekKeys><SeekKey><ColumnReference Database="..." Schema="..." Table="..." Column="..."/>...</SeekKey></SeekKeys> | ColumnReference 非參數/常數 → Seek Key;參數 (`@`) 或常數 (`Const`) → Where 條件值 |
| Join 條件 | 出現在 ` | 成對出現,識別左右表欄位 |
| Where 條件 (非 Seek) | <Predicate><ScalarOperator>...<ColumnReference.../>...</ScalarOperator></Predicate> (在 Filter/Scan 節點) | 提取 ColumnReference,標記為 Where 欄位 |
| Output List (Select) | 第一個 ` | 提取所有輸出欄位,候選 INCLUDE |
| 系統表/內部欄位 | ColumnReference 缺少 Database/Schema/Table 或 物件為系統表 | **過濾剔除** |
// 命名空間管理
var ns = new XmlNamespaceManager(doc.NameTable);
ns.AddNamespace("sp", "https://schemas.microsoft.com/sqlserver/2004/07/showplan");
// 提取所有 StmtSimple 節點 (每個查詢陳述式)
var stmts = doc.SelectNodes("//sp:StmtSimple", ns);
foreach (XmlNode stmt in stmts)
{
// 1. 取得執行次數 (從 QueryStats 關聯獲取,此處略)
// 2. 提取 OutputList (Select 欄位)
var outputCols = stmt.SelectNodes(".//sp:OutputList/sp:ColumnReference", ns);
// 3. 提取 SeekKeys (Where/Join Key)
var seekKeys = stmt.SelectNodes(".//sp:SeekKeys/sp:SeekKey/sp:ColumnReference", ns);
// 4. 提取 Predicate 中的 ColumnReference (Where 非 Seek)
var predicates = stmt.SelectNodes(".//sp:Predicate//sp:ColumnReference", ns);
// 5. 識別 Join (簡化邏輯:檢查 PhysicalOp 為 Join 類型且涉及多表)
// ... 統計聚合邏輯 ...
}
原型開發完成,但遭 DBA 團隊強烈抵制 (原因未透露,可能涉及變更管控流程、風險厭惡、工具信任度)、最終無限期擱置。
**教訓**:自動化工具再強,**組織變更管理、審核機制、責任歸屬** 是落地前提。建議採用「輔助決策」模式 (輸出建議報表供 DBA 審核) 而非「全自動執行」。
基於執行計畫 XML 的自動化索引優化,技術路徑清晰、理論紮實、工程可行。核心在於 **「從查詢模式反推索引需求」而非「審計現有索引」**,配合統計加權、智能合並、風險分級發布。若能解決組織阻力,配合 Query Store 長期數據,將是大規模 SQL Server 索引治理的終極武器。