使用 SQL Server 2005 中的 SQLCLR 处理 XML Showplan(1)

附录 B 包含一个 Visual C# 程序，该程序以 XML 格式从 SQL Server 提取 showplan，然后将提取的 showplan 返回客户端。与解决方案 1 的第一步类似，以下命令行可用于将该程序编译为 DLL。该命令生成一个名为 ReturnShowplanXML.dll 的 DLL。

csc.exe 
/out:ReturnShowplanXML.dll
/target:library 
/reference:System.dll
/reference:System.Data.dll
/reference:sqlaccess.dll
ReturnShowplanXML.cs

与前面解决方案的第一步类似，应将 和 分别替换为指向 Microsoft .NET Framework 位置和 SQL Server 2005 安装位置的二进制文件的正确路径。

下一步，使用由客户端（如 SQL Server 2005 Management Studio）发布的以下 Transact-SQL 命令，让 SQL Server 2005 知道该程序集 (ReturnShowplanXML.dll)。

use AdventureWorks
go

CREATE ASSEMBLY ReturnShowplanXML 
FROM 'ReturnShowplanXML.dll'
go

将 替换为指向您在该过程的步骤 1 中编译 DLL 的位置的路径。

在已注册程序集 (ReturnShowplanXML.dll) 中创建引用外部 CLR 方法的用户定义存储过程。

CREATE PROCEDURE dbo.ReturnXMLShowplan 
( 
 @tsqlStmt   NVARCHAR(MAX),  
 @retPlanXML  NVARCHAR(MAX) OUT 
) 
AS EXTERNAL NAME ReturnShowplanXML.xmlshowplanaccess.GetXMLShowplan 
go 

@tsqlStmt 参数将包含一个查询，并且将使用 OUT 参数 @retPlanXML 以 XML 格式返回 showplan。

客户端使用如下代码来调用 CLR 用户定义的过程：

-- @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 

假如该查询包含错误，则将返回 XML 块 text of the exception 而不是 showplan。您可能希望修改附录 B 中该代码的异常处理部分，以更好地满足您的需要。

通过 OUTPUT 参数 @shplan 将 showplan 以 XML 格式返回到客户端。然后，客户端将 showplan 和从 showplan 提取的估计执行成本的 XQuery 表达式发送到 SQL Server。

服务器通过以变量 @querycost 形式返回该查询成本做出响应。

假如成本低于阈值，则客户端会将该查询发送给该服务器来执行。

小结

通过 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 密集的计算。

using System; 
using System.IO; 
using System.Data; 
using System.Data.Sql; 
using System.Data.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 tsqlStmt's cost 
      // 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();  // turns 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, 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 
          // 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, XmlSpace.Preserve); 
          System.Xml.XPath.XPathNavigator navigator = doc.CreateNavigator(); 
          XmlNamespaceManager nsmgr = new XmlNamespaceManager(navigator.NameTable); 
          nsmgr.AddNamespace("sql", "http://schemas.microsoft.com/sqlserver/2004/07/showplan"); 
          // The exact namespace will depend on the showplan's version. 
          // Please modify the year and month appropriately. 
          XPathExpression xpression; 
          // The XPaththat points to the estimated execution cost of the query 
          xpression =  
              navigator.Compile("//sql:Batch/sql:Statements/sql:StmtSimple/"  
              + "sql:QueryPlan[1]/sql:RelOp[1]/@EstimatedTotalSubtreeCost"); 
          xpression.SetContext(nsmgr); 
          XPathNodeIterator iterator = navigator.Select(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 any errors in SQL code 
          tsqlStmtCost = "-1"; 
      } 
  }  // GetXMLShowplan ends 
}  // xmlshowplanaccess class ends 

using System; 
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 

一、简介

Microsoft SQL Server 2005 使其查询执行计划（又称作 Showplan）可以采用 XML 格式。而且可以使用任何一种 XML 技术（例如 XPath、XQuery 或 XSLT）来处理 XML Showplan。本文描述从其 XML Showplan 中提取查询的估计执行成本的应用程序。Transact-SQL 所提取的成本可用于 Transact-SQL 窗口。使用该技术，用户只能将那些成本低于预定阈值的查询提交到运行 SQL Server 的服务器。这确保该服务器不会由于成本高、长时间运行的查询而超载。

二、目标和受众

本文的目标是 SQL Server 开发人员和数据库管理员 (DBA)。它向数据库管理员简要介绍了 SQLCLR（公共语言运行库）。用于提取 Showplan 的应用程序使用两个 Microsoft Visual C# .NET 小程序，本文还详细阐述 SQL Server 如何调用通过编译这些程序生成的 DLL。该应用程序还使用了可用于查询和提取 XML 数据中信息的 XPath 和 XQuery 技术。SQL Server 2005 为这两种查询语言提供内置支持。本文演示这两种语言如何与 Transact-SQL 进行无缝地交互操作。

SQL Server DBA 有时会遇到这种情况，即用户在工作高峰期向服务器提交长时间运行的查询，因而降低了服务器的响应速度。有两种方法可以防止这一情况的发生：

可以想像一下需要更细粒度控制的情况。例如，用户可能有三个等效但语法结构不同的查询，并希望以执行速度尽可能最快的形式自动提交该查询。此外，用户还可能希望不执行任何估计执行成本超过特定阈值的查询。以编程方式访问查询成本，将允许用户通过控制基于估计执行成本的查询提交过程来构建服务器友好的应用程序。

本文中描述的技术允许使用 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 过程一样。

实现该解决方案

1.

使用 .NET Framework 语言（本文中使用 Visual C#）定义存储过程，该过程将从给定查询的 XML Showplan 中获得查询成本。

2.

使用正在运行 SQL Server 的服务器注册此过程。此操作需要两个子步骤： 1. 在 SQL Server 中注册该程序集。 2. 创建一个引用外部 CLR 方法的存储过程。 图 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 上执行一个 XPath 表达式，以提取查询成本。该批处理中每条语句中的每个查询计划的成本均被提取并总计。 6. 估计查询执行成本返回到调用程序中。假如 SQL 代码中出现错误，则返回“-1”作为成本。 注 DLL 与 SQL Server 之间的通信称为进程内数据访问，这是因为已将该 DLL 链接到 SQL Server 进程。由于已将 DLL 动态链接到该 SQL Server 进程，因此交换数据并不跨越 SQL Server 进程边界。当执行进程内数据访问时，只能将 XPath 查询发送到 SQL Server；XQuery 查询不能使用进程内数据访问。 解决方案 2：使用 CLR 存储过程和 XQuery 表达式提取查询成本 该解决方案循序渐进的过程与前面的解决方案（解决方案 1）类似，但还存在一些重要区别。在解决方案 2 中，CLR 存储过程以 XML 格式为给定的查询返回 showplan，而无需做进一步的处理。客户端使用 XQuery 表达式从返回的 XML showplan 中提取估计查询成本。 实现该解决方案 1. 附录 B 包含一个 Visual C# 程序，该程序以 XML 格式从 SQL Server 提取 showplan，然后将提取的 showplan 返回客户端。与解决方案 1 的第一步类似，以下命令行可用于将该程序编译为 DLL。该命令生成一个名为 ReturnShowplanXML.dll 的 DLL。 csc.exe /out:ReturnShowplanXML.dll /target:library /reference:System.dll /reference:System.Data.dll /reference:sqlaccess.dll ReturnShowplanXML.cs 与前面解决方案的第一步类似，应将 和 分别替换为指向 Microsoft .NET Framework 位置和 SQL Server 2005 安装位置的二进制文件的正确路径。 2. 下一步，使用由客户端（如 SQL Server 2005 Management Studio）发布的以下 Transact-SQL 命令，让 SQL Server 2005 知道该程序集 (ReturnShowplanXML.dll)。 use AdventureWorks go CREATE ASSEMBLY ReturnShowplanXML FROM 'ReturnShowplanXML.dll' go 将 替换为指向您在该过程的步骤 1 中编译 DLL 的位置的路径。 3. 在已注册程序集 (ReturnShowplanXML.dll) 中创建引用外部 CLR 方法的用户定义存储过程。 CREATE PROCEDURE dbo.ReturnXMLShowplan ( @tsqlStmt NVARCHAR(MAX), @retPlanXML NVARCHAR(MAX) OUT ) AS EXTERNAL NAME ReturnShowplanXML.xmlshowplanaccess.GetXMLShowplan go @tsqlStmt 参数将包含一个查询，并且将使用 OUT 参数 @retPlanXML 以 XML 格式返回 showplan。 4. 客户端使用如下代码来调用 CLR 用户定义的过程： -- @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 假如该查询包含错误，则将返回 XML 块 text of the exception 而不是 showplan。您可能希望修改附录 B 中该代码的异常处理部分，以更好地满足您的需要。 5. 通过 OUTPUT 参数 @shplan 将 showplan 以 XML 格式返回到客户端。然后，客户端将 showplan 和从 showplan 提取的估计执行成本的 XQuery 表达式发送到 SQL Server。 6. 服务器通过以变量 @querycost 形式返回该查询成本做出响应。 7. 假如成本低于阈值，则客户端会将该查询发送给该服务器来执行。 上一篇返回首页 下一篇 声明： 此文观点不代表本站立场；转载务必保留本文链接；版权疑问请联系我们。 别人在看 抖音安全与信任开放日：揭秘推荐算法，告别单一标签依赖 ultraedit编辑器打开文件时，总是提示是否转换为DOS格式，如何关闭？ Cornell大神Kleinberg的经典教材《算法设计》是最好入门的算法教材 从 Microsoft 下载中心安装 Windows 7 SP1 和 Windows Server 2008 R2 SP1 之前要执行的步骤 Llama 2基于UCloud UK8S的创新应用 火山引擎DataTester：如何使用A/B测试优化全域营销效果 腾讯云、移动云继阿里云降价后宣布大幅度降价 字节跳动数据平台论文被ICDE2023国际顶会收录，将通过火山引擎开放相关成果 这个话题被围观超10000次，火山引擎VeDI如此解答 误删库怎么办？火山引擎DataLeap“3招”守护数据安全 在上方输入关键词后，回车键 开始搜索。Esc键 取消该搜索窗口。