运用SQL SERVER 2005 CLR解决.ppt

1、每个人都知道SQL SERVER 2005 CLR(公共语言运行时库)，通过使用SQL SERVER 2005 CLR来解决XML显示计划实例教程。但是，SQL Server开发人员和数据库管理员在编程时有时会遇到这种情况，即用户在高峰时间向服务器提交长时间运行的查询，这会降低服务器的响应速度。这里我们将介绍两种防止这种情况发生的方法：1 .数据库管理员可以使用sp_configure将查询调控器成本限制选项设置为特定的阈值。(这是一个高级选项。)此阈值在整个服务器上有效。2。为了影响连接的阈值，数据库管理员可以使用设置查询控制器成本限制语句。您可以想象需要更细粒度控制的情况。例如，用户可能有

2、三个具有不同语法结构的等价查询，并且希望以最快的执行模式自动提交查询。此外，用户可能不希望执行任何估计执行成本超过某个阈值的查询。对查询成本的编程访问将允许用户通过基于估计的执行成本控制查询提交过程来构建对服务器友好的应用程序。本文描述的技术允许您使用SQLCLR用户定义的过程、XPath、XQuery和Visual C#技术以编程方式访问查询的估计执行成本。如本文所述，通过用户定义的过程使用SQLCLR访问SQL Server 2005的基本技术也可以用于其他应用程序。在SQL Server 2005中，您可以使用中可用的任何编程语言来定义用户定义的类型、函数、过程和聚合。NET框架，如微软

3、的Visual Basic。NET或Visual C# .从概念上讲，在定义了一个用户定义的实体之后，它可以像由SQL Server本身提供的实体一样在SQL Server中使用。例如，在定义了用户定义的测试类型之后，您还可以定义一个包含测试类型列表的关系表。一旦定义了一个用户定义的过程，就可以使用执行过程调用它，就像Transact-SQL过程一样。使用SQL SERVER 2005 CLR存储过程和进程内数据访问提取查询开销，并实现该解决方案。1.NET框架语言(本文使用Visual C#语言)定义了存储过程，存储过程将从给定查询的XML显示计划中获取查询开销。2.运行SQL Server

4、的服务器会注册此进程。此操作需要两个子步骤：1)在SQL Server中注册程序集。2)创建引用外部CLR方法的存储过程。一个，显示创建用户定义的CLR存储过程的示意图。以下步骤逐步介绍了解决方案的过程。1.包含一个Visual C#程序(ShowplanXPath.cs)，该程序从运行SQL Server的服务器中提取XML格式的显示计划，然后对获得的显示计划执行XPath表达式，以提取估计的查询执行成本。第一步包括用Visual C#编译器编译程序并生成一个动态链接库。您可以使用以下命令行进行编译。此命令生成一个名为ShowplanXPath.dll的DLL:1。CSC . exe 2/o

5、ut : show planxpath . dll 3。/target:library 4。/reference:System.dll 5。/reference:System。Data.dll 6。7 Reference:sqlaccess.dll。其中应该替换为指向微软的正确路径。NET框架位置，例如，微软的c:netframework 2 . 0 . 40607或添加到系统环境变量路径。请注意，您需要根据的版本修改“v2.0.40607”。安装在您的计算机上。将被替换为指向SQL Server 2005安装的二进制文件的正确路径，例如1 . c : programfilesmicrosof

6、tsqlservermssql . 1 msqlbin。如果该路径包含空格，则像本例中那样用引号将该路径括起来。2.下一步，使用以下由客户端(如SQL Server 2005 Management Studio)发出的Transact-SQL命令，让SQL Server 2005知道程序集(ShowplanXPath.dll):1。将使用指向在第一步中编译的dll的位置的路径来替换。，3。在注册的程序集中创建引用外部CLR方法的用户定义存储过程。1。CREATEPROCEDUREdbo。GetXMLShowplanCost 2。(3 .tsqlStmtNVARCHAR(最大值)，4。Query

7、Costnvarchar(最大值)out5。)6 .请注意，此外部名称的逻辑格式为：assembly _ name . class _ name . method _ name。tsqlStmt参数将包含一个查询，并且将使用OUT参数queryCost返回querycost。四号。用下面的代码调用CLR用户定义的存储过程。declarrequerynvarchar(max)-查询2。declarreconstnvarchar(max)-其估计的执行成本为3。-设置您的查询4。setquery=选择* fromperson.address 5。-执行过程6。EXECdbo。成本输出7。选择成本-

8、打印成本8。-notethocastsnvarchar，weuseexplicitcomparisonincaseoferror 9。-andimplicitconversionforactucost 10。如果(成本！=-1)和(成本=0.5)-ifqueryiscopetexecute，11。EXEC(查询)-execute it；Elsedonexecute12。请注意，您可以通过查询变量提交一组查询(一个批处理)，然后返回该批处理的总成本。如果在查询或批处理中出现错误，将返回“-1”作为其成本。您可以修改附录A中的异常处理代码，以便在出现错误时更好地满足您的需求。5。输出参数cost将

9、查询的估计执行成本返回给客户端。如步骤4中的代码示例所示。6.终端可以根据成本值选择是否将查询提交给SQL Server执行，如步骤4中的代码所示。它显示了执行存储过程的主要步骤。1.一旦调用该过程，它将收到一个需要估计其成本的查询。2.CLR存储过程将显示计划模式设置为打开。不要执行提交给连接的任何语句；但是，将为这些语句生成showplan。将查询本身发送到SQL Server。3.服务器以XML格式返回这个显示计划，然后Visual C#程序将这些片段排序在一起。4.该过程将显示计划模式设置为关闭。5.CLR存储过程以XML格式准备并执行showplan上的XPath表达式，以提取查询开销。提取并合计批处理中每个语句中每个查询计划的成本。6.估计的查询执行成本被返回给调用程序。如果SQL代码中有错误，将返回“-1”作为成本。注意动态链接库和服务器之间的通信称为进程内数据访问，因为动态链接库已经链接到服务器进程。因为动态链接库