Processing XML Showplans Using SQLCLR in SQL Server 2005

Microsoft SQL Server 9.0 Technical Articles

Arun Marathe
Boris Baryshnikov

Microsoft Corporation

April 2005

Applies to:
Microsoft SQL Server 2005
Microsoft Visual C# .NET

Summary: Build an application to extract a query's estimated execution cost from its XML showplan. Users can submit only those queries costing less than a predetermined threshold to a server running SQL Server 2005, thereby ensuring it is not overloaded with costly, long-running queries. (12 printed pages)

Introduction
Goals and Audience
Problem Statement
Solution 1: Extract Query Cost Using CLR Stored Procedure and In-Process Data Access
Solution 2: Extract Query Cost Using CLR Stored Procedure and XQuery Expression
Conclusion
Appendix A: Code Listing of "ShowplanXPath.cs" (Solution 1)
Appendix B: Code Listing of "ReturnShowplanXML.cs" (Solution 2)

Introduction

Microsoft SQL Server 2005 makes its query execution plans (also known as showplans) available in XML format. XML showplans can be processed using any XML technology—for example, XPath, XQuery, or XSLT. This paper describes an application that extracts the estimated execution cost of a query from its XML showplan. The extracted cost is available for use in a Transact-SQL window. Using this technique, a user can submit to a server running SQL Server only those queries that cost less than a predetermined threshold. This ensures that the server does not get overloaded with costly, long-running queries.

Goals and Audience

This paper targets both SQL Server developers and database administrators (DBAs). It provides a brief introduction to the SQLCLR (common language runtime) for database administrators. The application for extracting showplans makes use of two small Microsoft Visual C# .NET programs, and this paper explains in detail how SQL Server invokes the DLLs generated by compiling those programs. The application also makes use of XPath and XQuery technologies that can be used to query and extract information out of XML data. SQL Server 2005 provides built-in support for these two query languages. This paper demonstrates how these languages and Transact-SQL can interoperate seamlessly.

Problem Statement

SQL Server DBAs sometimes encounter the situation where a user submits a long-running query to the server during peak business hours, thereby slowing down the server's responsiveness. Such a situation can be prevented in two ways:

The DBA can set the query governor cost limit option to a particular threshold using sp_configure. (This is an advanced option.) The threshold is effective server-wide.
To affect the threshold for a connection, the DBA can use a SET QUERY_GOVERNOR_COST_LIMIT statement.

One can imagine scenarios in which more fine-grained control is needed. For example, a user might have three equivalent but syntactically different queries, and would like to automatically submit the query in the form that is most likely to execute the fastest. In addition, the user may want to prevent the execution of any query that has an estimated execution cost over a certain threshold. Programmatic access to query cost would allow the user to build server-friendly applications by controlling the query submission process based on estimated execution cost.

Techniques described in this paper enable programmatic access to a query's estimated execution cost using SQLCLR user-defined procedures, XPath, XQuery, and Visual C# technologies. The basic technique of accessing SQL Server 2005 using SQLCLR through user-defined procedures, as explained here, can be used in other applications.

In SQL Server 2005, one can define user-defined types, functions, procedures, and aggregates using any programming language available in the .NET Framework, such as Microsoft Visual Basic .NET or Visual C#. Conceptually, after you have defined a user-defined entity, you can use that entity in SQL Server just like entities that are provided by SQL Server itself. For example, after defining a user-defined type T, a relational table with a column of type T can be defined. After defining a user-defined procedure P, it can be called using EXEC P just like a Transact-SQL procedure.

Solution 1: Extract Query Cost Using CLR Stored Procedure and In-Process Data Access

To implement this solution

Define the stored procedure in a .NET Framework language (Visual C# is used in this paper) that would obtain the query cost from the XML showplan from a given query.
Register the procedure with the server that is running SQL Server. This requires two sub-steps:
1. Register the assembly in SQL Server.
2. Create a stored procedure referencing the external CLR method.
  
  Figure 1. Steps for implementing and registering a user-defined stored procedure in SQLCLR

A schematic diagram for creating a user-defined CLR stored procedure is shown in Figure 1. The step-by-step process of the solution is explained in the following steps.

Appendix A contains a Visual C# program (ShowplanXPath.cs) that extracts a showplan in XML format from a server running SQL Server, and then executes an XPath expression on the obtained showplan to extract the estimated query execution cost. The first step consists of compiling the program and generating a DLL (ShowplanXPath.dll) using the Visual C# compiler. The following command line can be used for compilation. The command generates a DLL called ShowplanXPath.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
```
where <path-to-.NET-framework> should be replaced with the correct path to your Microsoft .NET Framework location, such as
C:\WINNT\Microsoft.NET\Framework\v2.0.40607
or added to the system environment PATH variable. Note that "v2.0.40607" will need to be modified depending on the version of .NET Framework that is installed on your computer. Replace <path-to-SQL-Server-installation> with the correct path to the location of the binaries of the SQL Server 2005 installation, such as
"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\"
Enclose the path in quotation marks if it contains spaces as it does in this example.
Next, the assembly (ShowplanXPath.dll) is made known to SQL Server 2005 using the following Transact-SQL command issued from a client such as SQL Server 2005 Management Studio:
```
use AdventureWorks
go
CREATE ASSEMBLY ShowplanXPath 
FROM '<path-to-compiled-DLL>\ShowplanXPath.dll'
go
```
Replace <path-to-compiled-DLL> with the path to the place where you compiled the DLL in the first step.
Create a user-defined stored procedure that references the external CLR method 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 external name is logically formed as: 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.

The client calls the CLR user-defined stored procedure 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
EXEC dbo.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

Note that you can submit a set of queries (a batch) through the @query variable, and then the total cost of the batch will be returned. If the query or the batch contains errors, "-1" is returned as its cost. You can modify the exception handling code in Appendix A to better suit your needs in the case of an error.

The query's estimated execution cost is returned to the client via the output parameter @cost. This is shown in the code example in step 4.
Based on the value of @cost, the client may choose to submit the query to SQL Server for execution as shown in the code in step 4.

Figure 2.Schematic processing steps on execution of the CLR stored procedure

The major steps that occur during the execution of the stored procedure are illustrated in Figure 2 and described in detail as follows:

Once the procedure is called, it receives a query whose cost is to be estimated.
The CLR stored procedure sets SHOWPLAN_XML mode to ON. None of the statements submitted on this connection will be executed; showplans will be produced for them instead. The query itself is sent to SQL Server.
The server returns the showplan in XML format piece-by-piece, and the Visual C# program puts it together.
The procedure sets SHOWPLAN_XML mode to OFF.
The CLR stored procedure prepares and executes an XPath expression on the showplan in XML format to extract the query cost. The cost of every query plan is extracted and summed for every statement in the batch.
The estimated query execution cost is returned to the caller. In the case of errors in the SQL code, "-1" is returned as the cost.
Note The DLL's communication with SQL Server is known as in-process data access because the DLL has been linked to the SQL Server process. The exchanged data does not cross the SQL Server process boundary because the DLL was dynamically linked with the SQL Server process. When doing in-process data access, only XPath queries can be sent to SQL Server; XQuery queries cannot use in-process data access.

Solution 2: Extract Query Cost Using CLR Stored Procedure and XQuery Expression

The step-by-step process for this solution is similar to the previous solution (Solution 1), with some important differences. In Solution 2, the CLR stored procedure returns the showplan in XML format for a given query without doing any further processing. The client uses an XQuery expression to extract the estimated query cost from the returned XML showplan.

To implement this solution

Appendix B contains a Visual C# program that extracts a showplan in XML format from SQL Server, and returns it to the client. Similar to the first step of Solution 1, the following command line can be used to compile this program into a DLL. The command generates a DLL called ReturnShowplanXML.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
```
Similar to the first step of the previous solution, <path-to-.NET-framework> and <path-to-SQL-Server-installation> should be replaced with the correct paths to your Microsoft .NET Framework location and the binaries of the SQL Server 2005 installation location, respectively.
Next, the assembly (ReturnShowplanXML.dll) is made known to SQL Server 2005 using the following Transact-SQL command, which is issued from a client such as SQL Server 2005 Management Studio.
```
use AdventureWorks
go

CREATE ASSEMBLY ReturnShowplanXML 
FROM '<path-to-compiled-DLL>\ReturnShowplanXML.dll'
go
```
Replace <path-to-compiled-DLL> with the path to the place where you compiled the DLL in step 1 of this procedure.
Create a user-defined stored procedure that references the external CLR method in the registered assembly (ReturnShowplanXML.dll).
```
CREATE PROCEDURE dbo.ReturnXMLShowplan
(
   @tsqlStmt   NVARCHAR(MAX), 
   @retPlanXML   NVARCHAR(MAX) OUT
)
AS EXTERNAL NAME ReturnShowplanXML.xmlshowplanaccess.GetXMLShowplan
go
```
The @tsqlStmt parameter will contain a query, and the showplan in XML format will be returned using the OUT parameter @retPlanXML.

The client calls the CLR user-defined procedure using code similar to the following:

-- @shplan will contain the showplan in XML format
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'
EXEC dbo.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

If the query contains an error, XML chunk <error>text of the exception</error> will be returned instead of the showplan. You may want to modify the exception handling part of the code in Appendix B to better suit your needs.

The showplan in XML format is returned to the client via the OUTPUT parameter @shplan. The client then sends to SQL Server the showplan and an XQuery expression that extracts the estimated execution cost from the showplan.
The server responds by returning the query cost in the variable @querycost.
If the cost is below a threshold, the client sends the query to the server for execution.

Figure 3. Schematic processing steps for the second solution (compare with Figure 2, note that the step numbers do not necessarily match)
Figure 3 outlines the processing steps for this solution. Two important points should be emphasized in this approach:
- The extraction of XML showplan is in-process, just as in Solution 1.
  Note The query cost extraction using an XQuery expression does not use in-process data access because the showplan was sent to the client process, and the client re-sent the showplan along with an XQuery expression that extracts the query cost out of the showplan.
- For out-of-process data access, SQL Server supports XQuery queries, not just XPath queries. Therefore, more expressive queries can be used for processing XML showplans. This method is not as efficient as that used in Solution 1 because the showplan gets sent over the connection twice.

Conclusion

Using the SQL Server 2005 SQLCLR feature, showplans in XML format can be processed using the XPath or XQuery languages. Because XPath and XQuery engines are built into SQL Server 2005, seamless integration among them and Transact-SQL is possible. The Visual C# code that implements the CLR user-defined procedures to act as links between Transact-SQL, on the one hand, and XPath or XQuery on the other, is relatively simple. SQLCLR greatly extends the capabilities of Transact-SQL, and CPU-intensive computations can be efficiently implemented using such procedural languages as Visual C# and Visual Basic .NET.

Appendix A: Code Listing of "ShowplanXPath.cs" (Solution 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 XML format
            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 XML format is contained in thePlan.
            // We shall now evaluate an XPath expression 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 XPath that 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

Appendix B: Code Listing of "ReturnShowplanXML.cs" (Solution 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 XML format

        // 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 XML format
            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 XML format
        } 
        catch (SqlException e) // return well formed xml document with the text of exception
        {
            tsqlStmtShowplan = "<error>" + e.ToString() + "</error>";
        }
    }  // GetXMLShowplan ends
}  // xmlshowplanaccess ends

Copyright

This is a preliminary document and may be changed substantially prior to final commercial release of the software described herein.

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT.

Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.

Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, e-mail address, logo, person, place, or event is intended or should be inferred.

Microsoft, Visual Basic .NET, and Visual C# are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

The names of actual companies and products mentioned herein may be the trademarks of their respective owners.

Contents

Introduction

Goals and Audience

Problem Statement

Solution 1: Extract Query Cost Using CLR Stored Procedure and In-Process Data Access

To implement this solution

Solution 2: Extract Query Cost Using CLR Stored Procedure and XQuery Expression

To implement this solution

Conclusion

Appendix A: Code Listing of "ShowplanXPath.cs" (Solution 1)

Appendix B: Code Listing of "ReturnShowplanXML.cs" (Solution 2)

Copyright