She can dig it!
D Sharon Pruitt

A couple posts ago in the plan cache series I discussed the children for the Statements element.  This next post will focus on the second of the five possible child elements – the StmtCond element.  You might want to call this the “Statement Conditional” element

The StmtCond element contains logic for a condition followed by a THEN and possibly and ELSE clause.  The condition can be either a query or user-defined function call.  When a plan for a T-SQL batch or query is created, the plan will be built to support all paths within the conditional logic.

A Plan for All Paths

Let’s start by looking at a simple T-SQL batch that includes some conditional logic.

IF 1=2
BEGIN
SELECT TOP 10 * FROM sys.indexes
END
ELSE
SELECT TOP 10 * FROM sys.columns

If you execute the batch and then look at the Actual Execution Plan, you will see the following execution plan:

Didn’t I mention above that the execution plan that is generated should cover all possible execution paths?  I did.

The execution plan above is the plan that was executed – it is not the plan that is in the plan cache.  The execution plans in the plan cache are the compiled plans, not the actual executed plans.  This is an interesting point, because many do believe that t

Instead of jumping ahead to how to get this information out, instead let’s create an estimated plan for the T-SQL batch above.  The plan you get should look like the following:

StmtCond Element

This post is supposed to be about the plan cache and the StmtCond element.  Where are some demos with that information, right?  Well, we’re getting to them.  Next we want to find the plan for our simple T-SQL batch in the plan cache.  If you’re trusting me, then you’re probably guessing some XQuery is coming up now.

And here it is:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT cp.plan_handle
,qp.query_plan
,c.value('@StatementText', 'varchar(255)') AS StatementText
,c.value('@StatementType', 'varchar(255)') AS StatementType
,c.query('Then/.') AS ThenNode
,c.query('Else/.') AS ElseNode
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY qp.query_plan.nodes('//StmtCond') t(c)
WHERE qp.query_plan.exist('//StmtCond') =1

The output for the statement is:

By selecting the link in the query_plan column, you’ll see an execution plan similar to the one from the estimated plan above.  It contains both paths of the T-SQL batch in it.

StmtCond Details

The query above uses the exist() method to identify cached plans with the StmtCond element and then pulls out the StmtCond elements with the nodes() method.

The useful attributes in the StmtUseDb element are:

• StatementText: The T-SQL statement or function that will be evaluated in the condition
• StatementType: The type of T-SQL statement.  This will be COND every time.

In the query, I also included the use of the query() method to extract a couple other elements from the StmtCond element.  These elements are:

• Then element: The T-SQL batch to execute if the condition is True.
• Else element: The T-SQL batch to execute if the condition is False

Each of these elements contains the execution plan for each of the possible paths.  Of course since the SHOWPLAN XML has been shred some, this isn’t readily available to you.

Clever With StmtCond

Though with a little coding you could put together some code similar to the statements below to rebuild each of the elements back into execution plans.  SQL Server won’t be able to do anything with this information, but you will have what you need to be able to click and open the visual representation of the SHOWPLAN XML.

The query for accomplishing this is as follows:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT cp.plan_handle
,qp.query_plan
,c.value('@StatementText', 'varchar(255)') AS StatementText
,c.value('@StatementType', 'varchar(255)') AS StatementType
,CAST('<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="'
+ qp.query_plan.value('ShowPlanXML[1]/@Version','nvarchar(512)')
+ '" Build="'
+ qp.query_plan.value('ShowPlanXML[1]/@Build','nvarchar(512)')
+ '"><BatchSequence><Batch>'
+ REPLACE(REPLACE(CAST(c.query('Then/Statements/.') as varchar(max)),'<p1:','<'),'</p1:','</')
+ '</Batch></BatchSequence></ShowPlanXML>' as XML) AS ThenPlan
,CAST('<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="'
+ qp.query_plan.value('ShowPlanXML[1]/@Version','nvarchar(512)')
+ '" Build="'
+ qp.query_plan.value('ShowPlanXML[1]/@Build','nvarchar(512)')
+ '"><BatchSequence><Batch>'
+ REPLACE(REPLACE(CAST(c.query('Else/Statements/.') as varchar(max)),'<p1:','<'),'</p1:','</')
+ '</Batch></BatchSequence></ShowPlanXML>' as XML) AS ElsePlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY qp.query_plan.nodes('//StmtCond') t(c)
WHERE qp.query_plan.exist('//StmtCond') =1

I’ll add an immediate disclaimer that this might not be the best method for implementing this type of logic with XQuery.  But it does work.  Selecting the links in the either the ThenPlan or the ElsePlan columns will lead to one of the following two execution plans:

Wrapping-Up StmtCond

Similar to StmtUseDb, there isn’t a lot of practical use for this element from a query tuning perspective.  If you’ve heard the myth about conditional logic leading to recompiles, then just go read Gail Shaw’s (Blog | @SQLintheWild) post – Do IF statements cause recompiles?

If you had a need, the query above can be used to find execution plans with multiple paths.  Maybe some of those plans don’t need all of the paths and some can be removed.  Are there large plans in the database with conditional logic.  Maybe it makes sense to break them into multiple stored procedures to reduce the size of the plans.  Or if if you wanted to see the likely plan that will be executed for a given path of execution.  Needless to say – this information is available to you.

So I wouldn’t say that knowledge of this element is worthless.  Because there are things you could suppose and search for with this element.  Just be cautious that it is worth the time.  And if you get anything really good, please leave it in the comments.

Related posts:

1. Can You Dig It? – StmtUseDb Element
2. Can You Dig It? – SHOWPLAN XML Structure
3. Can You Dig It? – The Plan Cache