构建一个应用程序以从其 xml showplan 中提取查询的估计执行成本。用户只能将成本低于预定阈值的那些查询提交到运行 sql server 2005 的服务器，从而确保服务器不会由于成本高、长时间运行的查询而超载。
一、简介
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 语句。
|||图 1. 在sqlclr中实现和注册用户定义的存储过程的步骤
图 1 显示用于创建用户定义的 clr 存储过程的示意图。以下步骤循序渐进地介绍该解决方案的过程。
1.
附录 a 包含一个 visual c# 程序 (showplanxpath.cs)，该程序从运行 sql server 的服务器中提取 xml 格式的 showplan，然后在获得的 showplan 上执行 xpath 表达式，以提取估计查询执行成本。第一步包括，使用 visual c# 编译器编译该程序并生成一个 dll (showplanxpath.dll)。可使用以下命令行来进行编译。该命令生成一个名为 showplanxpath.dll 的 dll：
csc.exe
/out:showplanxpath.dll
/target:library
/reference:system.dll
/reference:system.data.dll
/reference:sqlaccess.dll
showplanxpath.cs其中，应该将 替换为指向 microsoft .net framework 位置的正确路径，例如
c:winntmicrosoft.netframeworkv2.0.40607
或将其添加到系统环境变量 path 中。请注意，您需要根据计算机上安装的 .net framework 的版本来修改“v2.0.40607”。将 替换为指向 sql server 2005 安装的二进制文件的正确路径，例如
"c:program filesmicrosoftsql servermssql.1mssqlbinn"
如果该路径包含空格，那么就像本示例那样将该路径用引号括起来。
2.
下一步，使用客户端（例如 sql server 2005 management studio）发布的以下 transact-sql 命令来让 sql server 2005 知道该程序集 (showplanxpath.dll)：
use adventureworks
go
create assembly showplanxpath
from '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 中的代码所示。
图 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.
如果成本低于阈值，则客户端会将该查询发送给该服务器来执行。
图 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
新闻热点
疑难解答
