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

SQL Server 2005 SQLCLR 實戰：解析 XML Showplan 實現查詢成本管控（上）

編輯：關於SqlServer

一、背景痛點：查詢總督功能的局限

SQL Server 提供兩種查詢成本限制機制：

伺服器級：`sp_configure 'query governor cost limit', ` — 全域生效，粒度粗
會話級：`SET QUERY_GOVERNOR_COST_LIMIT ` — 僅影響當前連線

兩者皆為靜態閾值，無法支援：多查詢自動選擇最低成本方案、依業務邏輯動態攔截、客戶端預估成本決策。本文展示利用 SQLCLR + XPath/XQuery 以程式化方式提取 XML Showplan 中的 `EstimatedTotalSubtreeCost`，構建細粒度查詢管控框架。

二、解決方案架構

+------------------+     +------------------+     +------------------+
|  Client App      |     |  SQL Server      |     |  CLR Assembly    |
|  (C#/VB.NET)     |     |  (T-SQL)         |     |  (C# DLL)        |
+------------------+     +------------------+     +------------------+
        |                          |                          |
        | 1. Submit Query Text     |                          |
        |------------------------->|                          |
        |                          | 2. SET SHOWPLAN_XML ON   |
        |                          |------------------------->|
        |                          |                          | 3. Execute Query
        |                          |                          |    (no execution)
        |                          |                          | 4. Capture XML Showplan
        |                          |<-------------------------|
        |                          | 5. Call CLR UDF/USP      |
        |                          |------------------------->|
        |                          |                          | 6. XPath/XQuery Extract Cost
        |                          |                          | 7. Return Cost (float)
        |                          |<-------------------------|
        | 8. Cost Returned         |                          |
        |<-------------------------|                          |
        | 9. Decision: Execute /   |                          |
        |     Reject / Rewrite     |                          |

三、CLR 組件開發步驟

1. C# 類別庫專案 (目標 .NET Framework 2.0/3.5)

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml.XPath;
using System.Xml;

public partial class ShowplanAnalyzer
{
    [SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlDouble GetEstimatedCost(SqlXml queryXml)
    {
        if (queryXml.IsNull) return SqlDouble.Null;

        // 使用 XPathNavigator 高效流式查詢
        XPathNavigator nav = queryXml.CreateNavigator();
        XmlNamespaceManager nsmgr = new XmlNamespaceManager(nav.NameTable);
        nsmgr.AddNamespace("sp", "https://schemas.microsoft.com/sqlserver/2004/07/showplan");

        // 定位第一個 StmtSimple 的 EstimatedTotalSubtreeCost
        string xpath = "//sp:StmtSimple/@EstimatedTotalSubtreeCost";
        XPathNavigator costNav = nav.SelectSingleNode(xpath, nsmgr);

        if (costNav != null)
        {
            double cost;
            if (Double.TryParse(costNav.Value, out cost))
                return new SqlDouble(cost);
        }
        return SqlDouble.Null;
    }
}

2. 部署至 SQL Server

-- 1. 啟用 CLR (需 sysadmin)
EXEC sp_configure 'clr enabled', 1; RECONFIGURE;

-- 2. 建立組件 (PERMISSION_SET = SAFE 足夠，無外部存取)
CREATE ASSEMBLY ShowplanAnalyzer
FROM 'C:\Deploy\ShowplanAnalyzer.dll'
WITH PERMISSION_SET = SAFE;
GO

-- 3. 建立使用者定義函數
CREATE FUNCTION dbo.fn_GetEstimatedCost(@QueryXml XML)
RETURNS FLOAT
AS EXTERNAL NAME ShowplanAnalyzer.[ShowplanAnalyzer].GetEstimatedCost;
GO

四、T-SQL 呼叫範例：動態查詢成本評估

DECLARE @Sql NVARCHAR(MAX) = N'
    SELECT o.OrderID, c.CustomerName, SUM(od.Quantity * od.UnitPrice) AS TotalAmt
    FROM Sales.Orders o
    JOIN Sales.Customers c ON o.CustomerID = c.CustomerID
    JOIN Sales.OrderDetails od ON o.OrderID = od.OrderID
    WHERE o.OrderDate >= DATEADD(MONTH, -3, GETUTCDATE())
    GROUP BY o.OrderID, c.CustomerName;';

-- 1. 取得 XML Showplan (不執行查詢)
SET SHOWPLAN_XML ON;
EXEC sp_executesql @Sql;
SET SHOWPLAN_XML OFF;
-- 結果在「結果」面板為 XML，實際應用中需用 SqlCommand.ExecuteXmlReader 捕獲

-- 2. 假設已將 XML 存入變數 @PlanXml
DECLARE @PlanXml XML = (SELECT * FROM OPENROWSET(...)); -- 略

-- 3. 呼叫 CLR 函數提取成本
DECLARE @EstCost FLOAT = dbo.fn_GetEstimatedCost(@PlanXml);

-- 4. 業務決策邏輯
IF @EstCost < 50.0
BEGIN
    PRINT '成本可接受，執行查詢';
    EXEC sp_executesql @Sql;
END
ELSE
BEGIN
    PRINT '成本過高 (' + CAST(@EstCost AS VARCHAR(20)) + ')，拒絕執行或重寫';
    -- 記錄審計、通知開發人員、嘗試替代查詢等
END;

五、進階 XPath/XQuery 技巧

1. 多語句批次處理

-- Showplan 根節點包含多個 Batch，每個 Batch 多個 StmtSimple
//sp:Batch/sp:Statements/sp:StmtSimple/@EstimatedTotalSubtreeCost

2. 針對特定陳述式類型

-- 僅 SELECT 陳述式
//sp:StmtSimple[@StatementType = 'SELECT']/@EstimatedTotalSubtreeCost

-- 含 Parallelism 運算子的查詢
//sp:StmtSimple[.//sp:RelOp[@PhysicalOp = 'Parallelism']]/@EstimatedTotalSubtreeCost

3. XQuery 替代方案 (在 CLR 中使用 XmlReader + XQuery)

// 在 C# 中使用 XQuery (需 .NET 3.5+ System.Xml.Linq)
XDocument doc = XDocument.Load(queryXml.CreateReader());
XNamespace sp = "https://schemas.microsoft.com/sqlserver/2004/07/showplan";
var costs = doc.Descendants(sp + "StmtSimple")
               .Select(e => (double?)e.Attribute("EstimatedTotalSubtreeCost"))
               .Where(c => c.HasValue)
               .ToList();

六、安全性與權限模型

權限層級	適用場景	限制
SAFE (預設)	純運算、XML 解析、無外部存取	無檔案/網路/登錄存取，記憶體受限
EXTERNAL_ACCESS	需讀取組態檔、呼叫 Web Service	需資料庫設定 TRUSTWORTHY 或簽署非對稱金鑰
UNSAFE	非受控碼、P/Invoke、靜態變數共享	極高風險，僅限 sysadmin 部署，嚴格審計