运用SQLSERVER2005CLR解决XMLShowplan实例

大家都知道SQL SERVER 2005 CLR（公共语言运行库），而在编程中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 过程一样。

使用 SQL SERVER 2005 CLR 存储过程和进程内数据访问提取查询成本

实现该解决方案
1. .NET Framework 语言（本文中使用 Visual C#）定义存储过程，该过程将从给定查询的 XML Showplan 中获得查询成本。
2.正在运行 SQL Server 的服务器注册此过程。此操作需要两个子步骤：
1） SQL Server 中注册该程序集。
2）建一个引用外部 CLR 方法的存储过程。

 

显示用于创建用户定义的 CLR 存储过程的示意图。

以下步骤循序渐进地介绍该解决方案的过程。

1. A 包含一个 Visual C# 程序 (ShowplanXPath.cs)，该程序从运行 SQL Server 的服务器中提取 XML 格式的 Showplan，然后在获得的 Showplan 上执行 XPath 表达式，以提取估计查询执行成本。***步包括，使用 Visual C# 编译器编译该程序并生成一个 DLL (ShowplanXPath.dll)。可使用以下命令行来进行编译。该命令生成一个名为 ShowplanXPath.dll 的 DLL：

 
 
 
 
    
  
  
  
  
  1. \csc.exe   
    2. 
  
  
  
  
  2. /out:ShowplanXPath.dll  
    3. 
  
  
  
  
  3. /target:library   
    4. 
  
  
  
  
  4. /reference:\System.dll  
    5. 
  
  
  
  
  5. /reference:\System.Data.dll  
    6. 
  
  
  
  
  6. /reference:\sqlaccess.dll  
    7. 
  
  
  
  
  7. ShowplanXPath.cs 
    8.

其中，应该将 替换为指向 Microsoft .NET Framework 位置的正确路径，例如 

 
 
 
 
    
  
  
  
  
  1. C:\WINNT\Microsoft.NET\Framework\v2.0.40607 
    2.

或将其添加到系统环境变量 PATH 中。请注意，您需要根据计算机上安装的 .NET Framework 的版本来修改“v2.0.40607”。将 替换为指向 SQL Server 2005 安装的二进制文件的正确路径，例如 

 
 
 
 
    
  
  
  
  
  1. "C:\Program Files\MicrosoftSQL Server\MSSQL.1\MSSQL\Binn\"  
    2.

如果该路径包含空格，那么就像本示例那样将该路径用引号括起来。

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

 
 
 
 
    
  
  
  
  
  1. use AdventureWorks   
    2. 
  
  
  
  
  2. go   
    3. 
  
  
  
  
  3. CREATE ASSEMBLY ShowplanXPath   
    4. 
  
  
  
  
  4. FROM '\ShowplanXPath.dll'   
    5. 
  
  
  
  
  5. go  
    6.

将 替换为指向***步所编译 DLL 的位置的路径。

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

 
 
 
 
    
  
  
  
  
  1. CREATE PROCEDURE dbo.GetXMLShowplanCost   
    2. 
  
  
  
  
  2. (   
    3. 
  
  
  
  
  3. @tsqlStmt   NVARCHAR(MAX),   
    4. 
  
  
  
  
  4. @queryCost   NVARCHAR(MAX) OUT   
    5. 
  
  
  
  
  5. )   
    6. 
  
  
  
  
  6. AS EXTERNAL NAME ShowplanXPath.xmlshowplanaccess.GetXMLShowplan   
    7. 
  
  
  
  
  7. go  
    8.

请注意，此外部名称的逻辑格式为：assembly_name.class_name.method_name。@tsqlStmt 参数将包含一个查询，而且将使用 OUT 参数 @queryCost 返回查询成本。

4.端使用以下代码调用 CLR 用户定义的存储过程：

 
 
 
 
    
  
  
  
  
  1. DECLARE @query nvarchar(max)-- the query   
    2. 
  
  
  
  
  2. DECLARE @cost nvarchar(max)-- its estimated execution cost   
    3. 
  
  
  
  
  3. -- set this to your query   
    4. 
  
  
  
  
  4. set @query = N'select * from person.address'   
    5. 
  
  
  
  
  5. -- execute the procedure   
    6. 
  
  
  
  
  6. EXECdbo.GetXMLShowplanCost @query, @cost OUTPUT   
    7. 
  
  
  
  
  7. select @cost-- print the cost   
    8. 
  
  
  
  
  8. -- note that @cost is nvarchar, we use explicit comparison in case of an error    
    9. 
  
  
  
  
  9. -- and implicit conversion for actual cost   
    10. 
  
  
  
  
  10. if (@cost != '-1') and (@cost <= 0.5)-- if query is cheap to execute,   
    11. 
  
  
  
  
  11. EXEC(@query)-- execute it; else don't execute   
    12. 
  
  
  
  
  12. -- replace 0.5 with your own threshold   
    13. 
  
  
  
  
  13. go  
    14.

请注意，可以通过 @query 变量提交一组查询（一个批处理），然后返回该批处理的总成本。如果查询或批处理中有错误，则返回“-1”作为其成本。可以修改附录 A 中的异常处理代码，以便在出现错误时能更好地满足您的需要。

5.输出参数 @cost 将该查询的估计执行成本返回到客户端。如步骤 4 中的代码示例所示。

6.端可根据 @cost 的值，选择是否将该查询提交到 SQL Server 来加以执行，如步骤 4 中的代码所示。

显示执行存储过程的主要步骤

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 查询不能使用进程内数据访问。使用就是 SQL SERVER 2005 CLR 存储过程和进程内数据访问提取查询成本的一个案例。

网站题目：运用SQLSERVER2005CLR解决XMLShowplan实例
网站URL：http://www.stwzsj.com/qtweb/news9/2859.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联

猜你还喜欢下面的内容

微信公众号知识

分类信息网

小谭网创广告　　 　定制网站　　 　商城网站　　 　雅安服务器托管　　 　成都网站制作公司　　 　东方电机技改服务有限公司　　 　营销型网站建设　　 　网上商城开发　　 　成都门户网站建设　　 　犀浦封阳台　　 　安防网站建设方案　　 　网站建设公司　　 　小程序开发　　 　成都网站建设　　 　成都app软件开发　　 　软文发布平台　　 　中晴建设　　 　微博营销　　 　成都网络营销　　 　域名ssl证书