Analyze an Estimated Execution Plan at RunTime

There are many reasons why one might want to analyze an Estimated Execution Plan for a query. Of course you can analyze it in the graphical Execution Plan display tab in SSMS, but I wanted to analyze the more-complete XML plan.
And, (here’s the kicker) I wanted to do it at RUNTIME.
This post outlines my journey in finding a way to do this. The eventual solution was implemented as a CLR procedure with a wrapper SQL procedure to do the analysis.

The Problem

I was working on an application whose frontend allowed an advanced user to write queries against underlying views.  Even thought the user was restricted to just SELECT access against certain views to prevent him/her from wreaking havoc, there were still actions we wanted to take based on how they were writing the query.  For example, we might want to prevent the query from executing if (a) the query were written in a way that would return too much data, or (b) if the query would have run slowly, or (c) if the user were SQL-experienced and was trying to execute something like sp_help, which in some cases we weren’t able to deny execute to for the user (for example: in SQLAzure, you’re currently not able to deny execute to this).

The (Possible) Solution

I thought that getting the Execution Plan in XML and analyzing it would be a good way to get the information we needed.  Indeed, we could query the XML for all we wanted.

For pedagogical purposes, let’s just use a select from sys.objects for an example of a statement to analyze:

SELECT * FROM sys.objects

If we just wanted to use the IDE, we could get the estimated plan by just clicking the “Display Estimated Execution Plan” button in the toolbar.

From there, you can get view the Plan.

Now Graphical Plans are nice to look through, but they mask detailed data for the plan. The XML plan is far more complete. You can right click and select “Show Execution Plan XML”. And that can give you an XML file you can view and save:

All this is well and good, but what if you wanted to do it at RUNTIME, like I want to?  This brings up a series of issues.

Options and Respective Issues

Obviously, the IDE isn’t going to work for runtime.  So the question becomes: how can we store this XML in a variable at runtime on which we can do an XQuery?  Can we do this programmatically?

Option:  Tracing

You could use a SQL trace to capture the showplan XML, as outlined in this post:

http://blogs.msdn.com/b/sqlqueryprocessing/archive/2007/06/01/store-statistics-xml-in-database-tables-using-sql-traces-for-further-analysis.aspx

The issue: the query would still have to be executed.  I want to get the estimated execution plan, and then decide whether or not to run the query.

Option:  Query DMVs

You could query dynamic management views and dynamic management functions, as in the snippet below.  (The example XQuery to analyze the XML from the previous link)

DECLARE @PlanXML xml;
DECLARE @SQL nvarchar(max)
SET @SQL = 'SELECT * FROM sys.objects';

SELECT @PlanXML = [QP].[query_plan]
FROM sys.dm_exec_cached_plans CP
CROSS APPLY sys.dm_exec_sql_text(CP.plan_handle) ST
CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) QP
WHERE [ST].[text] = @SQL;

SELECT @PlanXML AS PlanXML;

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sql)
SELECT
	 ro.relop.value('@NodeId', 'int')					AS NodeId
	,ro.relop.value('@PhysicalOp', 'nvarchar(200)')		AS PhysicalOp
	,ro.relop.value('@LogicalOp', 'nvarchar(200)')		AS LogicalOp
	,(ro.relop.value('@EstimateRows', 'float')
		* (ro.relop.value('@EstimateRewinds', 'float')
		+  ro.relop.value('@EstimateRebinds', 'float')
		+ 1.0))											AS EstimateRows
	,CAST(ro.relop.exist('*/sql:RelOp') AS bit)			AS IsNotLeaf
FROM @PlanXML.nodes('//sql:RelOp') AS ro(relop)

The issue: again, the query would have had to have been kicked off and cached first. If the plan were cached, we’d get this nice output:

However, we can’t count on the text of that query having been cached.

Option:  SET SHOWPLAN_XML

You’ll find that behind the scenes when you click “Display Estimated Execution Plan”, what Management Studio is actually doing is this:

SET SHOWPLAN_XML ON
GO

SELECT * FROM sys.objects
GO

SET SHOWPLAN_XML OFF
GO

Provided the user has proper rights,:

GRANT SHOWPLAN TO [username]

you’ll get what you want.

The issue:  good luck trying to save this off as an XML variable.  Because after you have SHOWPLAN_XML on, anything executed after that will not actually be executed.  The XML for the Plan of anything you try to do (including saving to a variable) will just be returned in the results tab.  We’re still getting nowhere.

Option: Dynamic SQL

Not a bad idea.  We could execute everything in dynamic SQL and insert it into a table.   Let’s try it:

IF object_id('tempdb..#EstimatedPlans') IS NOT NULL
BEGIN
   DROP TABLE #EstimatedPlans
END

CREATE TABLE #EstimatedPlans
(
   PlanXML xml
)

DECLARE @SQL nvarchar(max)
SET @SQL =
'
	SET SHOWPLAN_XML ON
	GO

	SELECT * FROM sys.objects
	GO

	SET SHOWPLAN_XML OFF
	GO
'

INSERT INTO #EstimatedPlans
EXEC sp_executesql @SQL

You may have already noticed the problem with this. “GO” is for separating batches in Management Studio. You can’t use it in a batch of dynamic SQL. You’ll get:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘GO’.

Ok, let’s take out the “GO”s then:

IF object_id('tempdb..#EstimatedPlans') IS NOT NULL
BEGIN
   DROP TABLE #EstimatedPlans
END

CREATE TABLE #EstimatedPlans
(
   PlanXML xml
)

DECLARE @SQL nvarchar(max)
SET @SQL =
'
	SET SHOWPLAN_XML ON

	SELECT * FROM sys.objects

	SET SHOWPLAN_XML OFF
'

INSERT INTO #EstimatedPlans
EXEC sp_executesql @SQL

The issue:

Msg 1067, Level 15, State 1, Line 0
The SET SHOWPLAN statements must be the only statements in the batch.

Arrrg! We have to execute multiple batches on the same connection to turn SHOWPLAN_XML on and pass in our query. But we can’t execute multiple batches on the same connection in Dynamic SQL!

But wait! What about this:

DECLARE @SQL nvarchar(max)

SET @SQL = 'SET SHOWPLAN_XML ON'
EXEC sp_executesql @SQL

SET @SQL = 'SELECT * FROM sys.objects'
EXEC sp_executesql @SQL

SET @SQL = 'SET SHOWPLAN_XML OFF'
EXEC sp_executesql @SQL

Sorry, all you get now is the output of SELECT * FROM sys.objects, since each dynamic SQL execution is in its own scope that returns back to the calling scope. Results:

So how do we execute multiple batches on the same connection?

Option:  CLR to the rescue!

Perhaps if we created a CLR procedure and pass in the SQL, it could execute “SET SHOWPLAN_XML ON” first, and then the input SQL on the same open connection, and return the XML results.  Huzzah!

I’ll spare you the pain of the code samples from my CLR failures, and just summarize the issues I had to address along the way:

  1. You can usually return data in a dataset from a CLR procedure using
    SqlContext.Pipe.Send(sqlDataRecord);
    

    The issue with this is that this requires a SQLConnection with the connection string of “context connection=true” to run on the same database.  Unfortunately, there are some limitations to this connection that will prevent us from doing what we want to do:

    http://msdn.microsoft.com/en-us/library/ms131101.aspx

    Ultimately, we need to pass in the server and database name and open up a new connection.

  2. I tried to build a connection string and open up that connection, get the XML, and then open up a context connection to pass the data out.  This will work in some cases, but will error sometimes if you try to do an EXEC INTO (output the data of the CLR proc into a temp table)

    Long story short: I needed use an output parameter.

  3. Tried the output type of SQLString from the CLR procedure (since working with strings seemed easier than XML), but SQLString has a max length of 4000 and thus wouldn’t work.

    I had to have a SqlXml output parameter.

  4. Packaging up a SqlXml datatype took me a bit of searching to figure out.  Here’s how to do it:
//Package XML as output
System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();
xmlDoc.InnerXml = //The XML in a string here//
System.Xml.XmlNodeReader xnr = new System.Xml.XmlNodeReader(xmlDoc);
PlanXML = new SqlXml(xnr);

The Final Solution

Here’s the final CLR procedure I created to solve my problem. Note I had to prepare a connection string from passed-in parameters. Next, I opened up a single connection and executed two batches: the first to set SHOWPLAN_XML ON, the second to run the input SQL. I then pulled out the result into a dataset, took the first column of the first row of the first table and packaged it up as a SqlXml type.

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetShowPlanXML
    (
          SqlString SQL
        , out SqlXml PlanXML
        , SqlString server
        , SqlString database
        , SqlBoolean isIntegratedSecurity
        , SqlString loginIfNotIntegrated
        , SqlString passwordIfNotIntegrated
    )
    {
        //Prep connection
        string strConnectionString;

        if ((bool)isIntegratedSecurity)
        {
            strConnectionString = @"Data Source="
                + server.ToString()
                + @";Initial Catalog="
                + database.ToString()
                + @";Integrated Security=True";
        }
        else
        {
            strConnectionString = @"data source="
                + server.ToString()
                + @";initial catalog="
                + database.ToString()
                + @";Persist Security Info=True;User ID="
                + loginIfNotIntegrated.ToString()
                + @";Password="
                + passwordIfNotIntegrated.ToString();
        }

        SqlConnection cn = new SqlConnection(strConnectionString);

        //Set command texts
        SqlCommand cmd_SetShowPlanXml = new SqlCommand("SET SHOWPLAN_XML ON", cn);
        SqlCommand cmd_input = new SqlCommand(SQL.ToString(), cn);

        if (cn.State != ConnectionState.Open)
        {
            cn.Open();
        }

        //Run SET SHOWPLAN_XML ON
        cmd_SetShowPlanXml.ExecuteNonQuery();

        //Run input SQL
        SqlDataAdapter da = new SqlDataAdapter();
        DataSet ds = new DataSet();

        da.SelectCommand = cmd_input;
        ds.Tables.Add(new DataTable("Results"));

        ds.Tables[0].BeginLoadData();
        da.Fill(ds, "Results");
        ds.Tables[0].EndLoadData();

        if (cn.State != ConnectionState.Closed)
        {
            cn.Close();
        }

        //Package XML as output
        System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();
        //XML is in 1st Col of 1st Row of 1st Table
        xmlDoc.InnerXml = ds.Tables[0].Rows[0][0].ToString();
        System.Xml.XmlNodeReader xnr = new System.Xml.XmlNodeReader(xmlDoc);
        PlanXML = new SqlXml(xnr);
    }

Below is the example wrapper TSQL procedure I created around the CLR procedure. The purpose of this wrapper is twofold. One, I wanted it to assign default connections for server and database. Two, I’ve put in an example XML analysis that I might do.

CREATE PROCEDURE [dbo].[GetEstimatedExecutionPlanInfo]
(
	 @SQL nvarchar(max)
	,@server nvarchar(4000)						= NULL
	,@database nvarchar(4000)					= NULL
	,@isIntegratedSecurity bit					= 1
	,@loginIfNotIntegrated nvarchar(4000)		= NULL
	,@passwordIfNotIntegrated nvarchar(4000)	= NULL
)
AS
BEGIN

	--Use current connection as default
	IF @server IS NULL
		SET @server = (SELECT @@SERVERNAME)

	IF @database IS NULL
		SET @database = (SELECT DB_NAME())

	--Get XML from CLR Proc
	DECLARE @PlanXML xml

	EXEC [dbo].[GetShowPlanXML]
		 @SQL
		,@PlanXML OUTPUT
		,@server
		,@database
		,@isIntegratedSecurity
		,@loginIfNotIntegrated
		,@passwordIfNotIntegrated

	--Return Plan
	SELECT @PlanXML AS PlanXML

	--Return info about Plan
	;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sql)
	SELECT
		 ro.relop.value('@NodeId', 'int')					AS NodeId
		,ro.relop.value('@PhysicalOp', 'nvarchar(200)')		AS PhysicalOp
		,ro.relop.value('@LogicalOp', 'nvarchar(200)')		AS LogicalOp
		,(ro.relop.value('@EstimateRows', 'float')
			* (ro.relop.value('@EstimateRewinds', 'float')
			+  ro.relop.value('@EstimateRebinds', 'float')
			+ 1.0))											AS EstimateRows
		,CAST(ro.relop.exist('*/sql:RelOp') AS bit)			AS IsNotLeaf
	FROM @PlanXML.nodes('//sql:RelOp') AS ro(relop)

END
GO

Here’s a test execution:

DECLARE @SQL nvarchar(max);
SET @SQL = 'SELECT * FROM sys.objects'

EXEC [dbo].[GetEstimatedExecutionPlanInfo] @SQL

Yay! The output we wanted, it’s during runtime, and we didn’t have to execute the query! Again, this is an example XML analysis that I might do; this could be used later on in code to decide whether or not to execute the user’s query.

Now CLR isn’t the only solution. I imagine the front end application could do the work that the CLR procedure is doing to get the plan. Indeed, this might have to be the option if we’re in SQLAzure, where CLR isn’t currently supported. And yes, there’d be a lot of analysis of the XML we’ll have to decide on and develop. But we’re on our way.

Advertisements

2 Responses to Analyze an Estimated Execution Plan at RunTime

  1. iLya Lozovyy says:

    Jeff,

    Great article. I knew you would eventually solve this issue.

  2. techsreenath says:

    Wow… i was also looking for some thing similar to this one. Though my ultimate aim is to build a tool for SQL simlar to FxCop….. 😀

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: