Shulou(Shulou.com)06/02 Report--

Use SQL SERVER 2005 CLR to solve XML instance analysis, many novices are not very clear about this, in order to help you solve this problem, the following small series will be explained in detail for everyone, there are people who need this to learn, I hope you can gain something.

SQL Server 2005 CLR (Common Language Runtime) is well known, and SQL Server developers and database administrators (DBAs) sometimes encounter situations in programming where users submit long-running queries to the server during peak hours, thus slowing down the server's response time. Here are two ways to prevent this from happening:

1. DBAs can use sp_configure to set the query governor cost limit option to a specific threshold. (This is an advanced option.) This threshold is valid throughout the server.

2. To influence the threshold for a connection, DBAs can use the SET QUERY_GOVERNOR_COST_LIMIT statement.

Imagine a situation where finer grained control is needed. For example, a user may have three queries that are equivalent but syntactically different and want to automatically submit the query in the fastest possible form. Additionally, users may also wish not to execute any queries for which the estimated execution cost exceeds a particular threshold. Programmatically accessing query costs will allow users to build server-friendly applications by controlling the query submission process based on estimated execution costs. The techniques described in this article allow programmatic access to the estimated execution cost of a query using SQL CLR user-defined procedures, XPath, XQuery, and Visual C#technologies. As described in this article, the basic techniques for using SQL CLR to access SQL Server 2005 through user-defined procedures can also be used for other applications.

In SQL Server 2005, user-defined types, functions, procedures, and aggregates can be defined using any programming language available in the. NET Framework, such as Microsoft Visual Basic. NET or Visual C#. Conceptually, once a user-defined entity is defined, it can be used in SQL Server as if it were provided by SQL Server itself. For example, after defining a user-defined T type, you can define a relational table with a column of T types. Once a user-defined P procedure is defined, it can be invoked using EXEC P, just like a Transact-SQL procedure.

Extract query costs using SQL SERVER 2005 CLR stored procedures and in-process data access

the achievement of that solution

1. The. NET Framework language (Visual C#is used in this article) defines stored procedures that derive query costs from the XML Showplan for a given query.

2. Registering this procedure on a server running SQL Server. This operation requires two sub-steps:

1) Register the assembly in SQL Server.

2) Create a stored procedure that references an external CLR method.

Shows a schematic for creating user-defined CLR stored procedures.

The following steps step-by-step through the process of the solution.

1. A contains a Visual C#program (ShowplanXPath.cs) that extracts an XML-formatted Showplan from a server running SQL Server and executes an XPath expression on the resulting Showplan to extract the estimated query execution cost. The first step involves compiling the program using the Visual C#compiler and generating a DLL (ShowplanXPath.dll). Compile using the following command line. This command generates a DLL named ShowplanXPath.dll:

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

where should be replaced with the correct path to the Microsoft. NET Framework location, for example

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

Or add it to the system environment variable PATH. Note that you need to modify "v2.0.40607" depending on the version of the. NET Framework installed on your computer. Replace with the correct path to the binary file for the SQL Server 2005 installation, for example

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

If the path contains spaces, enclose the path in quotation marks, as in this example.

2. Next, let SQL Server 2005 know about the assembly (ShowplanXPath.dll) using the following Transact-SQL command issued by a client (for example, SQL Server 2005 Management Studio):

use AdventureWorks go CREATE ASSEMBLY ShowplanXPath FROM '\ShowplanXPath.dll' go

Replace with a path to the location of the DLL compiled in the first step.

3. Create user-defined stored procedures that reference external CLR methods in the registered assembly (ShowplanXPath.dll).

CREATE PROCEDURE dbo.GetXMLShowplanCost ( @tsqlStmt NVARCHAR(MAX), @queryCost NVARCHAR(MAX) OUT ) AS EXTERNAL NAME ShowplanXPath.xmlshowplanaccess.GetXMLShowplan go

Note that the logical format for this external name is: assembly_name.class_name.method_name.@ The tsqlStmt parameter will contain a query, and the query cost will be returned using the OUT parameter @queryCost.

4. The CLR user-defined stored procedure is invoked by the client using the following code:

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.