Analyze an Estimated Execution Plan at RunTime
2011-04-08 2 Comments
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:
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:
- 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.
- 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.
- 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.
- 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.
Jeff Mlakar's Blog 
Jeff,
Great article. I knew you would eventually solve this issue.
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….. 😀