1、使用 SQL Server2005 中的 SQLCLR 处理 XML ShowplanArun MaratheBoris BaryshnikovMicrosoft Corporation适用于：Microsoft SQL Server 2005Microsoft Visual C# .NET摘要：构建一个应用程序以从其 XML Showplan 中提取查询的估计执行成本。用户只能将成本低于预定阈值的那些查询提交到运行 SQL Server 2005 的服务器，从而确保服务器不会由于成本高、长时间运行的查询而超载。本页内容简介 目标和受众 问题声明 解决方案 1：使用 CLR 存储过程和进程内数
2、据访问提取查询成本 解决方案 2：使用 CLR 存储过程和 XQuery 表达式提取查询成本 小结 附录 A：“ShowplanXPath.cs” 的代码清单（解决方案 1） 附录 B：“ReturnShowplanXML.cs”的代码清单（解决方案 2） 简介Microsoft SQL Server 2005 使其查询执行计划（又称作 Showplan）可以采用 XML 格式。而且可以使用任何一种 XML 技术（例如 XPath、XQuery 或 XSLT）来处理 XML Showplan。本文描述从其 XML Showplan 中提取查询的估计执行成本的应用程序。Transact-SQL
3、所提取的成本可用于 Transact-SQL 窗口。使用该技术，用户只能将那些成本低于预定阈值的查询提交到运行 SQL Server 的服务器。这确保该服务器不会由于成本高、长时间运行的查询而超载。 返回页首目标和受众本文的目标是 SQL Server 开发人员和数据库管理员 (DBA)。它向数据库管理员简要介绍了 SQLCLR（公共语言运行库）。用于提取 Showplan 的应用程序使用两个 Microsoft Visual C# .NET 小程序，本文还详细阐述 SQL Server 如何调用通过编译这些程序生成的 DLL。该应用程序还使用了可用于查询和提取 XML 数据中信息的 XPat
4、h 和 XQuery 技术。SQL Server 2005 为这两种查询语言提供内置支持。本文演示这两种语言如何与 Transact-SQL 进行无缝地交互操作。返回页首问题声明SQL Server DBA 有时会遇到这种情况，即用户在工作高峰期向服务器提交长时间运行的查询，因而降低了服务器的响应速度。有两种方法可以防止这一情况的发生： 1.DBA 可使用 sp_configure 将 query governor cost limit 选项设置为特定阈值。（这是一个高级选项。）该阈值在整个服务器内是有效的。 2.要影响连接的阈值，DBA 可以使用 SET QUERY_GOVERNOR_COS
5、T_LIMIT 语句。 可以想像一下需要更细粒度控制的情况。例如，用户可能有三个等效但语法结构不同的查询，并希望以执行速度尽可能最快的形式自动提交该查询。此外，用户还可能希望不执行任何估计执行成本超过特定阈值的查询。以编程方式访问查询成本，将允许用户通过控制基于估计执行成本的查询提交过程来构建服务器友好的应用程序。本文中描述的技术允许使用 SQLCLR 用户定义的过程、XPath 、XQuery 以及 Visual C# 技术，以编程方式访问查询的估计执行成本。如本文所述，通过用户定义的过程使用 SQLCLR 来访问 SQL Server 2005 的基本技术也可用于其他应用程序。在 SQL
6、Server 2005 中，可以使用 .NET Framework 中可用的任何编程语言（例如 Microsoft Visual Basic .NET 或 Visual C#）来定义用户定义的类型、函数、过程以及聚合。从概念上讲，在定义了用户定义的实体后，就可以在 SQL Server 中使用该实体，就像是由 SQL Server 本身提供的实体一样。例如，定义用户定义的 T 类型之后，还可以定义带有一列 T 类型的关系型表。定义了用户定义的 P 过程后，就可以使用 EXEC P 调用该过程，就像 Transact-SQL 过程一样。返回页首解决方案 1：使用 CLR 存储过程和进程内数据访问
7、提取查询成本实现该解决方案1.使用 .NET Framework 语言（本文中使用 Visual C#）定义存储过程，该过程将从给定查询的 XML Showplan 中获得查询成本。 2.使用正在运行 SQL Server 的服务器注册此过程。此操作需要两个子步骤： 1.在 SQL Server 中注册该程序集。 2.创建一个引用外部 CLR 方法的存储过程。 图 1 显示用于创建用户定义的 CLR 存储过程的示意图。以下步骤循序渐进地介绍该解决方案的过程。 1.附录 A 包含一个 Visual C# 程序 (ShowplanXPath.cs)，该程序从运行 SQL Server 的服务器中提
8、取 XML 格式的 Showplan，然后在获得的 Showplan 上执行 XPath 表达式，以提取估计查询执行成本。第一步包括，使用 Visual C# 编译器编译该程序并生成一个 DLL (ShowplanXPath.dll)。可使用以下命令行来进行编译。该命令生成一个名为 ShowplanXPath.dll 的 DLL： Code:csc.exe /out:ShowplanXPath.dll/target:library /reference:System.dll/reference:System.Data.dll/reference:sqlaccess.dllShowplanXPa
9、th.cs其中，应该将 替换为指向 Microsoft .NET Framework 位置的正确路径，例如 C:WINNTMicrosoft.NETFrameworkv2.0.40607 或将其添加到系统环境变量 PATH 中。请注意，您需要根据计算机上安装的 .NET Framework 的版本来修改“v2.0.40607” 。将 替换为指向 SQL Server 2005 安装的二进制文件的正确路径，例如 “C:Program FilesMicrosoftSQL ServerMSSQL.1MSSQLBinn“ 如果该路径包含空格，那么就像本示例那样将该路径用引号括起来。 2.下一步，使用客
10、户端（例如 SQL Server 2005 Management Studio）发布的以下 Transact-SQL 命令来让 SQL Server 2005 知道该程序集 (ShowplanXPath.dll)： Code:use AdventureWorks go CREATE ASSEMBLY ShowplanXPath FROM ShowplanXPath.dll go 将 替换为指向第一步所编译 DLL 的位置的路径。 3.在已注册的程序集 (ShowplanXPath.dll) 中创建引用外部 CLR 方法的用户定义的存储过程。 Code:CREATE PROCEDURE dbo.
11、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 用户定义的存储过程： Code:DECLARE query nvarcha
12、r(max) - the query DECLARE cost nvarchar(max) - its estimated execution cost - set this to your query set query = Nselect * 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
13、in case of an error - and implicit conversion for actual cost if (cost != -1) and (cost 请注意，可以通过 query 变量提交一组查询（一个批处理），然后返回该批处理的总成本。如果查询或批处理中有错误，则返回“-1”作为其成本。可以修改附录 A 中的异常处理代码，以便在出现错误时能更好地满足您的需要。 5.通过输出参数 cost 将该查询的估计执行成本返回到客户端。如步骤 4 中的代码示例所示。 6.客户端可根据 cost 的值，选择是否将该查询提交到 SQL Server 来加以执行，如步骤 4 中的代码
14、所示。图 2. 执行 CLR 存储过程的示意处理步骤图 2 显示执行存储过程的主要步骤，详细描述如下： 1.一旦调用该过程，它就会接收到一个成本有待估计的查询。 2.CLR 存储过程将 SHOWPLAN_XML 模式设置为 ON。不执行提交到该连接的任何语句；然而，将为这些语句生成 showplan。将该查询本身发送到 SQL Server。 3.该服务器以 XML 格式逐段返回此 showplan，然后 Visual C# 程序将这些片段整理在一起。 4.该过程将 SHOWPLAN_XML 模式设置为 OFF。 5.CLR 存储过程准备并以 XML 格式在 showplan 上执行一个 XP
15、ath 表达式，以提取查询成本。该批处理中每条语句中的每个查询计划的成本均被提取并总计。 6.估计查询执行成本返回到调用程序中。如果 SQL 代码中出现错误，则返回“-1”作为成本。 注 DLL 与 SQL Server 之间的通信称为进程内数据访问，这是因为已将该 DLL 链接到 SQL Server 进程。由于已将 DLL 动态链接到该 SQL Server 进程，因此交换数据并不跨越 SQL Server 进程边界。当执行进程内数据访问时，只能将 XPath 查询发送到 SQL Server；XQuery 查询不能使用进程内数据访问。返回页首解决方案 2：使用 CLR 存储过程和 XQu
16、ery 表达式提取查询成本该解决方案循序渐进的过程与前面的解决方案（解决方案 1）类似，但还存在一些重要区别。在解决方案 2 中，CLR 存储过程以 XML 格式为给定的查询返回 showplan，而无需做进一步的处理。客户端使用 XQuery 表达式从返回的 XML showplan 中提取估计查询成本。实现该解决方案1.附录 B 包含一个 Visual C# 程序，该程序以 XML 格式从 SQL Server 提取 showplan，然后将提取的 showplan 返回客户端。与解决方案 1 的第一步类似，以下命令行可用于将该程序编译为 DLL。该命令生成一个名为 ReturnShowp
17、lanXML.dll 的 DLL。 Code:csc.exe /out:ReturnShowplanXML.dll/target:library /reference:System.dll/reference:System.Data.dll/reference:sqlaccess.dllReturnShowplanXML.cs与前面解决方案的第一步类似，应将 和 分别替换为指向 Microsoft .NET Framework 位置和 SQL Server 2005 安装位置的二进制文件的正确路径。 2.下一步，使用由客户端（如 SQL Server 2005 Management Studi
18、o）发布的以下 Transact-SQL 命令，让 SQL Server 2005 知道该程序集 (ReturnShowplanXML.dll)。 Code:use AdventureWorksgoCREATE ASSEMBLY ReturnShowplanXML FROM ReturnShowplanXML.dllgo将 替换为指向您在该过程的步骤 1 中编译 DLL 的位置的路径。 3.在已注册程序集 (ReturnShowplanXML.dll) 中创建引用外部 CLR 方法的用户定义存储过程。 Code:CREATE PROCEDURE dbo.ReturnXMLShowplan (
19、tsqlStmt NVARCHAR(MAX), retPlanXML NVARCHAR(MAX) OUT ) AS EXTERNAL NAME ReturnShowplanXML.xmlshowplanaccess.GetXMLShowplan go tsqlStmt 参数将包含一个查询，并且将使用 OUT 参数 retPlanXML 以 XML 格式返回 showplan。 4.客户端使用如下代码来调用 CLR 用户定义的过程： Code:- shplan will contain the showplan in XMLformat DECLARE shplan nvarchar(max)
20、- query will contain the query whose cost is to be estimated DECLARE query nvarchar(max) - set this to your query set query = Nselect * from person.address EXECdbo.ReturnXMLShowplan query, shplan OUTPUT DECLARE querycost float DECLARE threshold float set threshold = 0.5 - extract query cost using XQ
21、uery select querycost = cast(shplan as xml).value (declare namespace p=“http:/ (/p:RelOp)1/EstimatedTotalSubtreeCost, float) select querycost if ( querycost 如果该查询包含错误，则将返回 XML 块 text of the exception 而不是 showplan。您可能希望修改附录 B 中该代码的异常处理部分，以更好地满足您的需要。 5.通过 OUTPUT 参数 shplan 将 showplan 以 XML 格式返回到客户端。然后，
22、客户端将 showplan 和从 showplan 提取的估计执行成本的 XQuery 表达式发送到 SQL Server。 6.服务器通过以变量 querycost 形式返回该查询成本做出响应。 7.如果成本低于阈值，则客户端会将该查询发送给该服务器来执行。 图 3. 第二个解决方案的示意处理步骤图 3 概述该解决方案的处理步骤。在此方法中应强调两个重点： 在进程内执行对 XML showplan 的提取，与解决方案 1 相同。 注 使用 XQuery 表达式的查询成本提取并没有使用进程内数据访问，这是由于已将 showplan 发送到客户端进程，并且该客户端已重新发送 showplan，并
23、且从 showplan 中提取查询成本的 XQuery 表达式。 对于进程外数据访问，SQL Server 还支持 XQuery 查询，而不仅仅是 XPath 查询。因此，可以使用更多的表述性查询来处理 XML showplan。此方法不如解决方案 1 中所使用的方法有效，因为 showplan 通过连接发送了两次。 小结通过 SQL Server 2005 SQLCLR 功能，可使用 XPath 或 XQuery 语言处理 XML 格式的 Showplan。由于 XPath 和 XQuery 引擎内置于 SQL Server 2005，因此可以在它们和 Transact-SQL 之间形成无缝
24、集成。实现 CLR 用户定义的过程以作为 Transact-SQL 和 XPath 或 XQuery 之间链接的 Visual C# 代码相对简单。SQLCLR 极大地扩展了 Transact-SQL 的功能，并且可以使用诸如 Visual C# 和 Visual Basic .NET 之类的过程性语言有效地实现 CPU 密集的计算。附录 A：“ShowplanXPath.cs” 的代码清单（解决方案 1）Code:using System; using System.IO; using System.Data; using System.Data.Sql; using System.Data
25、.SqlClient; using System.Xml; using System.Xml.XPath; public class xmlshowplanaccess public static void GetXMLShowplan(string tsqlStmt, ref string tsqlStmtCost) / tsqlStmt contains the query whose cost needs to be calculated / tsqlStmtCost will contain the tsqlStmts cost / Open a connection and crea
26、te a command SqlConnection conn = new SqlConnection(“context connection = true“); conn.Open(); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = “set showplan_xml on“; cmd.ExecuteNonQuery(); / turns showplan_xml mode on cmd.CommandText = tsqlStmt; try / thePlan will contain the showplan in XM
27、Lformat String thePlan = String.Empty; SqlDataReader sdr = cmd.ExecuteReader(); / In case the result set is chunked, we concatenate while (sdr.Read() thePlan += sdr.GetSqlString(0).ToString(); sdr.Close(); cmd.CommandText = “set showplan_xml off“; cmd.ExecuteNonQuery(); / turns showplan_xml mode off
28、 / Now the showplan in XMLformat is contained in thePlan. / We shall now evaluate an XPathexpression against the showplan. StringReader strReader = new StringReader(thePlan); System.Xml.XmlTextReader xreader = new System.Xml.XmlTextReader(strReader); XPathDocument doc = new XPathDocument(xreader, Xm
29、lSpace.Preserve); System.Xml.XPath.XPathNavigator navigator = doc.CreateNavigator(); XmlNamespaceManager nsmgr = new XmlNamespaceManager(navigator.NameTable); nsmgr.AddNamespace(“sql“, “http:/ / The exact namespace will depend on the showplans version. / Please modify the year and month appropriatel
30、y. XPathExpression xpression; / The XPaththat points to the estimated execution cost of the query xpression = navigator.Compile(“/sql:Batch/sql:Statements/sql:StmtSimple/“ + “sql:QueryPlan1/sql:RelOp1/EstimatedTotalSubtreeCost“); xpression.SetContext(nsmgr); XPathNodeIterator iterator = navigator.Se
31、lect(xpression); String val = String.Empty; System.Single totalCost = 0; / sum costs of all query plans in this batch while(iterator.MoveNext() totalCost += Single.Parse(iterator.Current.Value); tsqlStmtCost = totalCost.ToString(); / set the return value catch (SqlException) / return -1 if there are
32、 any errors in SQL code tsqlStmtCost = “-1“; / GetXMLShowplan ends / xmlshowplanaccess class ends 附录 B：“ReturnShowplanXML.cs”的代码清单（解决方案 2）Code:using System; using System.IO; using System.Data; using System.Data.Sql; using System.Data.SqlClient; public class xmlshowplanaccess public static void GetXM
33、LShowplan(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 = tru
34、e“); 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.ExecuteRead
35、er(); / 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
