使用 SQL Server 2005 中的 SQLCLR 处理 XML Showplan(2)
[color=Blue][b]使用 SQL Server 2005 中的 SQLCLR 处理 XML Showplan(2)[/b][/color][b]编者按：[/b]
《[url=http://bbs.51cto.com/viewthread.php?tid=3648&extra=page%3D1]使用 SQL Server 2005 中的 SQLCLR 处理 XML Showplan(1)[/url]》
《[url=http://bbs.51cto.com/viewthread.php?tid=3649&extra=page%3D1]使用 SQL Server 2005 中的 SQLCLR 处理 XML Showplan(2)[/url]》
[b]下面我们详述第二部分内容：[/b]
[b]解决方案 2：使用 CLR 存储过程和 XQuery 表达式提取查询成[/b]本
该解决方案循序渐进的过程与前面的解决方案（解决方案 1）类似，但还存在一些重要区别。在解决方案 2 中，CLR 存储过程以 XML 格式为给定的查询返回 showplan，而无需做进一步的处理。客户端使用 XQuery 表达式从返回的 XML showplan 中提取估计查询成本。
[b]实现该解决方案[/b]
1.附录 B 包含一个 Visual C# 程序，该程序以 XML 格式从 SQL Server 提取 showplan，然后将提取的 showplan 返回客户端。与解决方案 1 的第一步类似，以下命令行可用于将该程序编译为 DLL。该命令生成一个名为 ReturnShowplanXML.dll 的 DLL。
[color=maroon]
<path-to-.NET-framework>\csc.exe
/out:ReturnShowplanXML.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
ReturnShowplanXML.cs
[/color]
与前面解决方案的第一步类似，应将 和 分别替换为指向 Microsoft .NET Framework 位置和 SQL Server 2005 安装位置的二进制文件的正确路径。
2.下一步，使用由客户端（如 SQL Server 2005 Management Studio）发布的以下 Transact-SQL 命令，让 SQL Server 2005 知道该程序集 (ReturnShowplanXML.dll)。
[color=maroon]
use AdventureWorks
go
CREATE ASSEMBLY ReturnShowplanXML
FROM '<path-to-compiled-DLL>\ReturnShowplanXML.dll'
go
[/color]
将 替换为指向您在该过程的步骤 1 中编译 DLL 的位置的路径。
3.在已注册程序集 (ReturnShowplanXML.dll) 中创建引用外部 CLR 方法的用户定义存储过程。
[color=maroon]
CREATE PROCEDURE dbo.ReturnXMLShowplan
(
@tsqlStmt NVARCHAR(MAX),
@retPlanXML NVARCHAR(MAX) OUT
)
AS EXTERNAL NAME ReturnShowplanXML.xmlshowplanaccess.GetXMLShowplan
go
[/color]
@tsqlStmt 参数将包含一个查询，并且将使用 OUT 参数 @retPlanXML 以 XML 格式返回 showplan。
4.客户端使用如下代码来调用 CLR 用户定义的过程：
[color=maroon]
-- @shplan will contain the showplan in XMLformat
DECLARE @shplan nvarchar(max)
-- @query will contain the query whose cost is to be estimated
DECLARE @query nvarchar(max)
-- set this to your query
set @query = N'select * from person.address'
EXECdbo.ReturnXMLShowplan @query, @shplan OUTPUT
DECLARE @querycost float
DECLARE @threshold float
set @threshold = 0.5
-- extract query cost using XQuery
select @querycost = cast(@shplan as xml).value
('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
(//p:RelOp)[1]/@EstimatedTotalSubtreeCost', 'float')
select @querycost
if ( @querycost <= @threshold ) -- if the cost is within limit,
EXEC(@query) -- execute the query; else don't
go
[/color]
如果该查询包含错误，则将返回 XML 块 text of the exception 而不是 showplan。您可能希望修改附录 B 中该代码的异常处理部分，以更好地满足您的需要。
5.通过 OUTPUT 参数 @shplan 将 showplan 以 XML 格式返回到客户端。然后，客户端将 showplan 和从 showplan 提取的估计执行成本的 XQuery 表达式发送到 SQL Server。
6.服务器通过以变量 @querycost 形式返回该查询成本做出响应。
7.如果成本低于阈值，则客户端会将该查询发送给该服务器来执行。
[align=center][img]http://www.microsoft.com/china/msdn/library/data/sqlserver/art/xmlshowplans_03.gif[/img]
图 3.第二个解决方案的示意处理步骤[/align]
图 3 概述该解决方案的处理步骤。在此方法中应强调两个重点：
◆在进程内执行对 XML showplan 的提取，与解决方案 1 相同。
[b]注：[/b]
使用 XQuery 表达式的查询成本提取并没有使用进程内数据访问，这是由于已将 showplan 发送到客户端进程，并且该客户端已重新发送 showplan，并且从 showplan 中提取查询成本的 XQuery 表达式。
◆对于进程外数据访问，SQL Server 还支持 XQuery 查询，而不仅仅是 XPath 查询。因此，可以使用更多的表述性查询来处理 XML showplan。此方法不如解决方案 1 中所使用的方法有效，因为 showplan 通过连接发送了两次。
[b]小结[/b]
通过 SQL Server 2005 SQLCLR 功能，可使用 XPath 或 XQuery 语言处理 XML 格式的 Showplan。由于 XPath 和 XQuery 引擎内置于 SQL Server 2005，因此可以在它们和 Transact-SQL 之间形成无缝集成。实现 CLR 用户定义的过程以作为 Transact-SQL 和 XPath 或 XQuery 之间链接的 Visual C# 代码相对简单。SQLCLR 极大地扩展了 Transact-SQL 的功能，并且可以使用诸如 Visual C# 和 Visual Basic .NET 之类的过程性语言有效地实现 CPU 密集的计算。
[b]附录 B：“ReturnShowplanXML.cs”的代码清单（解决方案 2）using System;[/b]
using System.IO;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
public class xmlshowplanaccess
{
public static void GetXMLShowplan(string tsqlStmt, ref string tsqlStmtShowplan)
{
// tsqlStmt contains the statement whose showplan needs to be returned
// tsqlStmtShowplan will return the showplan of tsqlStmt in XMLformat
// Open a connection and create a command
SqlConnection conn = new SqlConnection("context connection = true");
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "set showplan_xml on";
cmd.ExecuteNonQuery(); // turn the showplan_xml mode on
cmd.CommandText = tsqlStmt;
try
{
// thePlan will contain the showplan in XMLformat
String thePlan = String.Empty;
SqlDataReader sdr = cmd.ExecuteReader();
// In case the result set is chunked, concatenate
while (sdr.Read())
thePlan += sdr.GetSqlString(0).ToString();
sdr.Close();
cmd.CommandText = "set showplan_xml off" ;
cmd.ExecuteNonQuery(); // turn the showplan_xml mode off
tsqlStmtShowplan = thePlan; // return the showplan in XMLformat
}
catch (SqlException e) // return well formed xml document with the text of exception
{
tsqlStmtShowplan = "" + e.ToString() + "";
}
} // GetXMLShowplan ends
} // xmlshowplanaccess ends
【完】
[[i] 本帖最后由 黑袜子 于 2005-12-7 05:34 PM 编辑 [/i]] 2005的确有些不同哦。
页:
[1]