SQL Server 2005 利用 SQLCLR 處理 XML Showplan 實現查詢成本管控

前言

SQL Server 2005 引入 XML 格式的查詢執行計畫，並支援 XPath、XQuery、XSLT 等 XML 技術處理。本文示範如何結合 SQLCLR、Visual C# 与 XPath/XQuery，以程式化方式提取查詢預估執行成本，實現細粒度的查詢提交管控，防止高成本查詢拖垮伺服器效能。

問題動機

DBA 常面臨用戶在尖峰時段提交長時間查詢導致伺服器回應變慢。傳統手段僅能透過 sp_configure 'query governor cost limit' 或 SET QUERY_GOVERNOR_COST_LIMIT 設定全域或連線層級閾值，缺乏針對單一查詢的精細控制。例如用戶可能有多個語法不同但邏輯等價的查詢，希望自動選擇成本最低者執行，或直接攔截超標查詢——這都需要程式化存取查詢成本。

解決方案架構

核心技術棧

• SQLCLR：在 SQL Server 內部執行 .NET 組件，定義使用者定義函數/預存程序
• Visual C#：編寫邏輯處理組件
• XPath / XQuery：從 XML Showplan 中解析查詢成本
• Transact-SQL：呼叫 CLR 程序並整合業務邏輯

實作方案一：CLR 預存程序 + 進程內資料存取

1. 使用 Visual C# 定義 CLR 預存程序，接收查詢文本，產生 XML Showplan，透過 XPath/XQuery 提取 StatementSubTreeCost 屬性值。
2. 將組件編譯為 DLL，在 SQL Server 中註冊組件（CREATE ASSEMBLY）。
3. 建立對應的 T-SQL 預存程序封裝 CLR 方法（CREATE PROCEDURE ... AS EXTERNAL NAME）。
4. 應用層呼叫此預存程序取得成本，依業務規則決定是否提交正式查詢。

關鍵程式碼片段

// C# CLR 預存程序核心邏輯
[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetEstimatedCost(string queryText, out double cost) {
    using (SqlConnection conn = new SqlConnection("context connection=true")) {
        conn.Open();
        // 取得 XML Showplan
        SqlCommand cmd = new SqlCommand(
            "SET SHOWPLAN_XML ON; " + queryText + "; SET SHOWPLAN_XML OFF;", conn);
        XmlReader reader = cmd.ExecuteXmlReader();
        // XPath 提取成本
        XPathDocument doc = new XPathDocument(reader);
        XPathNavigator nav = doc.CreateNavigator();
        XmlNamespaceManager nsmgr = new XmlNamespaceManager(nav.NameTable);
        nsmgr.AddNamespace("sm", "https://schemas.microsoft.com/sqlserver/2004/07/showplan");
        string xpath = "//sm:StmtSimple/@StatementSubTreeCost";
        XPathNodeIterator iter = nav.Select(xpath, nsmgr);
        cost = iter.MoveNext() ? double.Parse(iter.Current.Value) : 0;
    }
}

進階應用場景

• 多查詢自動選優：對多個等價查詢分別取得成本，自動提交最低成本者。
• 動態閾值攔截：根據時間段、業務優先級動態調整成本上限。
• 審計與報表：記錄所有查詢預估成本，事後分析效能瓶頸。

部署注意事項

• 資料庫需設定 TRUSTWORTHY ON 或簽署組件憑證以允許 EXTERNAL_ACCESS/UNSAFE 權限組件。
• CLR 程式碼應妥善處理例外，避免導致 SQL Server 背景工作執行緒中止。
• 大量併發呼叫時注意連接池與執行緒資源消耗。

結語

SQLCLR 賦予 SQL Server 2005 強大的擴展能力，結合 XML Showplan 與 XPath/XQuery，開發者可輕鬆實現查詢成本的程式化存取與管控，構建更智慧、伺服器友善的應用程式。此技術模式同樣適用於其他需解析 XML 計畫的場景（如索引建議、平行度分析等）。