|
使用 SQL Server 2005 中的 SQLCLR 处理 XML Showplan
简介
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 有时会遇到这种情况，即用户在工作高峰期向服务器提交长时间运行的查询，因而降低了服务器的响应速度。有两种方法可以防止这一情况的发生：
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 过程一样。
解决方案 1：使用 CLR 存储过程和进程内数据访问提取查询成本
实现该解决方案
1.
使用 .NET Framework 语言（本文中使用 Visual C#）定义存储过程，该过程将从给定查询的 XML Showplan 中获得查询成本。
2.
使用正在运行 SQL Server 的服务器注册此过程。此操作需要两个子步骤：
1.
在 SQL Server 中注册该程序集。
2.
创建一个引用外部 CLR 方法的存储过程。
图 1. 在 SQLCLR 中实现和注册用户定义的存储过程的步骤
图 1 显示用于创建用户定义的 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
其中，应该将<path-to-.NET-framework> 替换为指向 Microsoft .NET Framework 位置的正确路径，例如
C:\WINNT\Microsoft.NET\Framework\v2.0.40607
或将其添加到系统环境变量 PATH 中。请注意，您需要根据计算机上安装的 .NET Framework 的版本来修改“v2.0.40607”。将 <path-to-SQL-Server-installation>替换为指向 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
将<path-to-SQL-Server-installation> 替换为指向第一步所编译 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 中的代码所示。
图 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。
<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
与前面解决方案的第一步类似，应将 <path-to-.NET-framework>和 <path-to-SQL-Server-installation>分别替换为指向 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 '<path-to-compiled-DLL>\ReturnShowplanXML.dll'
go
将<path-to-compiled-DLL> 替换为指向您在该过程的步骤 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 块<error> text of the exception </error> 而不是 showplan。您可能希望修改附录 B 中该代码的异常处理部分，以更好地满足您的需要。
5.
通过 OUTPUT 参数 @shplan 将 showplan 以 XML 格式返回到客户端。然后，客户端将 showplan 和从 showplan 提取的估计执行成本的 XQuery 表达式发送到 SQL Server。
6.
服务器通过以变量 @querycost 形式返回该查询成本做出响应。
7.
如果成本低于阈值，则客户端会将该查询发送给该服务器来执行。
图 3. 第二个解决方案的示意处理步骤
图 3 概述该解决方案的处理步骤。在此方法中应强调两个重点：
•
在进程内执行对 XML showplan 的提取，与解决方案 1 相同。
注 使用 XQuery 表达式的查询成本提取并没有使用进程内数据访问，这是由于已将 showplan 发送到客户端进程，并且该客户端已重新发送 showplan，并且从 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 之间形成无缝集成。实现 CLR 用户定义的过程以作为 Transact-SQL 和 XPath 或 XQuery 之间链接的 Visual C# 代码相对简单。SQLCLR 极大地扩展了 Transact-SQL 的功能，并且可以使用诸如 Visual C# 和 Visual Basic .NET 之类的过程性语言有效地实现 CPU 密集的计算。
附录 A：“ShowplanXPath.cs”的代码清单（解决方案 1）
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
附录 B：“ReturnShowplanXML.cs”的代码清单（解决方案 2）
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
查看上一页
返回分类首页 返回96PC首页
查看下一页 |
