大家都知道SQL SERVER 2005 CLR（公共語言運行庫），而在編程中SQL Server 開發人員和數據庫管理員 (DBA) 有時會遇到這種情況，即用戶在工作高峰期向服務器提交長時間運行的查詢，因而降低了服務器的響應速度。在這里給大家將介紹兩種方法可以防止這一情況的發生：
1.DBA 可使用 sp_configure 將 query governor cost limit 選項設置為特定閾值。（這是一個高級選項。）該閾值在整個服務器內是有效的。
2.要影響連接的閾值，DBA 可以使用 SET QUERY_GOVERNOR_COST_LIMIT 語句。

可以想像一下需要更細粒度控制的情況。例如，用戶可能有三個等效但語法結構不同的查詢，并希望以執行速度盡可能最快的形式自動提交該查詢。此外，用戶還可能希望不執行任何估計執行成本超過特定閾值的查詢。以編程方式訪問查詢成本，將允許用戶通過控制基于估計執行成本的查詢提交過程來構建服務器友好的應用程序。本文中描述的技術允許使用 SQLCLR 用戶定義的過程、XPath、XQuery 以及 Visual C# 技術，以編程方式訪問查詢的估計執行成本。如本文所述，通過用戶定義的過程使用 SQLCLR 來訪問 SQL Server 2005 的基本技術也可用于其他應用程序。

在 SQL Server 2005 中，可以使用 .NET Framework 中可用的任何編程語言（例如 Microsoft Visual Basic .NET 或 Visual C#）來定義用戶定義的類型、函數、過程以及聚合。從概念上講，在定義了用戶定義的實體后，就可以在 SQL Server 中使用該實體，就像是由 SQL Server 本身提供的實體一樣。例如，定義用戶定義的 T 類型之后，還可以定義帶有一列 T 類型的關系型表。定義了用戶定義的 P 過程后，就可以使用 EXEC P 調用該過程，就像 Transact-SQL 過程一樣。

使用 SQL SERVER 2005 CLR 存儲過程和進程內數據訪問提取查詢成本

實現該解決方案
1. .NET Framework 語言（本文中使用 Visual C#）定義存儲過程，該過程將從給定查詢的 XML Showplan 中獲得查詢成本。
2.正在運行 SQL Server 的服務器注冊此過程。此操作需要兩個子步驟：
1） SQL Server 中注冊該程序集。
2）建一個引用外部 CLR 方法的存儲過程。

顯示用于創建用戶定義的 CLR 存儲過程的示意圖。

以下步驟循序漸進地介紹該解決方案的過程。

1. A 包含一個 Visual C# 程序 (ShowplanXPath.cs)，該程序從運行 SQL Server 的服務器中提取 XML 格式的 Showplan，然后在獲得的 Showplan 上執行 XPath 表達式，以提取估計查詢執行成本。***步包括，使用 Visual C# 編譯器編譯該程序并生成一個 DLL (ShowplanXPath.dll)。可使用以下命令行來進行編譯。該命令生成一個名為 ShowplanXPath.dll 的 DLL：

<path-to-.NET-framework>\csc.exe   
/out:ShowplanXPath.dll  
/target:library   
/reference:<path-to-.NET-framework>\System.dll  
/reference:<path-to-.NET-framework>\System.Data.dll  
/reference:<path-to-SQL-Server-installation>\sqlaccess.dll  
ShowplanXPath.cs 

其中，應該將 替換為指向 Microsoft .NET Framework 位置的正確路徑，例如

C:\WINNT\Microsoft.NET\Framework\v2.0.40607 

或將其添加到系統環境變量 PATH 中。請注意，您需要根據計算機上安裝的 .NET Framework 的版本來修改“v2.0.40607”。將 替換為指向 SQL Server 2005 安裝的二進制文件的正確路徑，例如

"C:\Program Files\MicrosoftSQL Server\MSSQL.1\MSSQL\Binn\"  

如果該路徑包含空格，那么就像本示例那樣將該路徑用引號括起來。

2.下一步，使用客戶端（例如 SQL Server 2005 Management Studio）發布的以下 Transact-SQL 命令來讓 SQL Server 2005 知道該程序集 (ShowplanXPath.dll)：

use AdventureWorks   
go   
CREATE ASSEMBLY ShowplanXPath   
FROM '<path-to-compiled-DLL>\ShowplanXPath.dll'   
go  

將 替換為指向***步所編譯 DLL 的位置的路徑。

3.注冊的程序集 (ShowplanXPath.dll) 中創建引用外部 CLR 方法的用戶定義的存儲過程。

CREATE PROCEDURE dbo.GetXMLShowplanCost   
(   
@tsqlStmt   NVARCHAR(MAX),   
@queryCost   NVARCHAR(MAX) OUT   
)   
AS EXTERNAL NAME ShowplanXPath.xmlshowplanaccess.GetXMLShowplan   
go  

請注意，此外部名稱的邏輯格式為：assembly_name.class_name.method_name。@tsqlStmt 參數將包含一個查詢，而且將使用 OUT 參數 @queryCost 返回查詢成本。

4.端使用以下代碼調用 CLR 用戶定義的存儲過程：

DECLARE @query nvarchar(max)-- the query   
DECLARE @cost nvarchar(max)-- its estimated execution cost   
-- set this to your query   
set @query = N'select * from person.address'   
-- execute the procedure   
EXECdbo.GetXMLShowplanCost @query, @cost OUTPUT   
select @cost-- print the cost   
-- note that @cost is nvarchar, we use explicit comparison in case of an error    
-- and implicit conversion for actual cost   
if (@cost != '-1') and (@cost <= 0.5)-- if query is cheap to execute,   
EXEC(@query)-- execute it; else don't execute   
-- replace 0.5 with your own threshold   
go  

請注意，可以通過 @query 變量提交一組查詢（一個批處理），然后返回該批處理的總成本。如果查詢或批處理中有錯誤，則返回“-1”作為其成本。可以修改附錄 A 中的異常處理代碼，以便在出現錯誤時能更好地滿足您的需要。

5.輸出參數 @cost 將該查詢的估計執行成本返回到客戶端。如步驟 4 中的代碼示例所示。

6.端可根據 @cost 的值，選擇是否將該查詢提交到 SQL Server 來加以執行，如步驟 4 中的代碼所示。

顯示執行存儲過程的主要步驟

1.一旦調用該過程，它就會接收到一個成本有待估計的查詢。
2.CLR 存儲過程將 SHOWPLAN_XML 模式設置為 ON。不執行提交到該連接的任何語句；然而，將為這些語句生成 showplan。將該查詢本身發送到 SQL Server。
3.該服務器以 XML 格式逐段返回此 showplan，然后 Visual C# 程序將這些片段整理在一起。
4.該過程將 SHOWPLAN_XML 模式設置為 OFF。
5. CLR 存儲過程準備并以 XML 格式在 showplan 上執行一個 XPath 表達式，以提取查詢成本。該批處理中每條語句中的每個查詢計劃的成本均被提取并總計。
6.估計查詢執行成本返回到調用程序中。如果 SQL 代碼中出現錯誤，則返回“-1”作為成本。

注 DLL 與 SQL Server 之間的通信稱為進程內數據訪問，這是因為已將該 DLL 鏈接到 SQL Server 進程。由于已將 DLL 動態鏈接到該 SQL Server 進程，因此交換數據并不跨越 SQL Server 進程邊界。當執行進程內數據訪問時，只能將 XPath 查詢發送到 SQL Server；XQuery 查詢不能使用進程內數據訪問。使用就是 SQL SERVER 2005 CLR 存儲過程和進程內數據訪問提取查詢成本的一個案例。

【編輯推薦】

1. 全面概括SQL SERVER調用CLR類庫實現步驟
2. CLR函數壓縮NTEXT類型字段實例講解
3. CLR函數實現字符串排序七步通
4. CLR線程池教程四大功能詳解
5. CLR程序集教程新手上路