She can dig it!
D Sharon Pruitt

We’re up to the seventh post on the in the plan cache series.  This time we’ll be talking about the last of the possible children for the Statements elements – the StmtSimple element.  StmtSimple, commonly referred to as statement simple, can contain the plan information for a query plan, a store procedure, or a user defined function.

The StmtSimple element is the place in which most of the interesting and useful queries against the plan cache will originate.  The missing index and parallelism queries are both based on children of the StmtSimple element.  And after his post, the shift of the Can You Dig It? posts will shift from academic to practical.  I can hear the crowds roar now.

The StmtSimple Element

The StmtSimple element defines the execution plan for a single query, store procedure, or user defined function.  Note the user defined function bit – you won’t find their plans embedded in the query or store procedure plans.  They have their own separate plans.

Much of the information gathered for the execution plan will be contained at this level including statistics on rows and cost, along with the SET OPTIONS for the statement.

StmtSimple Attributes

The statistics mentioned for the statement are stored as attributes for the StmtSimple element.  Rather than go on and on about how awesome they are, here’s a list of some of the more useful elements.

• StatementEstRows: The number of rows that have been estimated to be returned by the plan.
  StatementOptmLevel: This states whether the plan that is trivial or full.  A trivial plan occurs when the optimizer determines only one plan will ever be optimal.
• StatementOptmEarlyAbortReason: This attribute indicates if the plan compilation aborted before before the compile threshold was reached.  Aborting early isn’t necessarily bad since less time compiling plans leaves more resources for other activities.  There are three values possible for this attribute, besides no value.  These values are GoodEnoughPlanFound, TimeOut, and MemoryLimitExceeded.  The only one to be concerned with is the MemoryLimitExceeded as it can indicate resource issues.  For more details on these reasons see the note in the XML Showplans MSDN article.
• StatementSubTreeCost: The total calculated cost for all of the operations in the statement.
• StatementText: The T-SQL statement or function that will be evaluated in the condition
• StatementType: The type of T-SQL statement.  This will vary based on the statement.  Some possible values are ASSIGN, SELECT, DELETE, GOTO, etc.
• QueryHash: Binary hash value that can be used to help identify similar query statements.
• QueryPlanHash: Binary hash value that can be used to help identify similar query execution plans.

This isn’t an exhaustive list of the attributes for StmtSimple, but it does highlight some of the more useful.  With some of these you can immediately start investigating your plan cache.  A few of the upcoming posts will examine some of these use case.

For today, the following query can be used to return information on the StmtSimple attributes:

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('@StatementEstRows', 'float') AS StatementEstRows
,c.value('@StatementOptmLevel', 'varchar(255)') AS StatementOptmLevel
,c.value('@StatementOptmEarlyAbortReason', 'varchar(255)') AS StatementOptmEarlyAbortReason
,c.value('@StatementSubTreeCost', 'float') AS StatementSubTreeCost
,c.value('@StatementText', 'varchar(max)') AS StatementText
,c.value('@StatementType', 'varchar(255)') AS StatementType
,c.value('@QueryHash', 'varchar(255)') AS QueryHash
,c.value('@QueryPlanHash', 'varchar(255)') AS QueryPlanHash
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('//StmtSimple') t(c)
WHERE qp.query_plan.exist('//StmtSimple') = 1

The output for this query will look like the following:

StatementSetOptions Element

A possible child of the the StmtSimple element is the StatementSetOptions element.  This element is applicable whether or not the the statement is a query, stored procedure, or a user-defined function.  This element contains all of the SET options that can have a bearing on your query cost and execution.

These SET options are (for brevity I’ll link to information on each of these):

• QUOTED_IDENTIFIER
• ARITHABORT
• CONCAT_NULL_YIELDS_NULL
• ANSI_NULLS
• ANSI_PADDING
• ANSI_WARNINGS
• NUMERIC_ROUNDABORT

Each of these can have an expected or unexpected impact on how queries run in your environment.  The store procedure out in your environment that is rounding the investments made to your retirement account because of NUMERIC_ROUNDABORT setting might be important to discover.  Or how about finding out your annual bonus was miscalculated in a suboptimal way due to ARITHABORT?

To find out what statements are in your plan cache with any of these settings can be discovered using the following query:

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(max)') AS StatementText
,c.value('@StatementText', 'varchar(max)') AS sql_statement
,c.value(N'(StatementSetOptions)[1]/@QUOTED_IDENTIFIER', N'bit') AS [quoted_identifier]
,c.value(N'(StatementSetOptions)[1]/@ARITHABORT', N'bit') AS [arithabort]
,c.value(N'(StatementSetOptions)[1]/@CONCAT_NULL_YIELDS_NULL', N'bit') AS [concat_null_yields_null]
,c.value(N'(StatementSetOptions)[1]/@ANSI_NULLS', N'bit') AS [ansi_nulls]
,c.value(N'(StatementSetOptions)[1]/@ANSI_PADDING', N'bit') AS [ansi_padding]
,c.value(N'(StatementSetOptions)[1]/@ANSI_WARNINGS', N'bit') AS [ansi_warnings]
,c.value(N'(StatementSetOptions)[1]/@NUMERIC_ROUNDABORT', N'bit') AS [numeric_roundabort]
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('//StmtSimple') t(c)
WHERE c.exist('StatementSetOptions') = 1

The exist() method is used to remove any StmtSimple elements that are returned that don’t contain the StatementSetOptions element.  Outputting this query looks like the following:

Where To Now?

This post might feel like the end, but it is just the beginning.  As I mentioned in the beginning of this post, the remaining posts will be shifting to more practical posts that can be applied to your environment.

Through the last few posts the groundwork of the information within the plan cache has been laid out.  From here we’ll start to look at specific needs and how to query your plan cache to discover the occurrences of this information.  This will provide you with methods to evaluate your SQL Server as a whole as address the areas with the greatest need.

If you have a need to retrieve some information from your plan cache and can’t figure out how to get it out, feel free to leave a comment below and I’ll see what I can do to get it out for you.  It’ll likely become a future blog post and useful resource for others.

Related posts:

1. Can You Dig It? – StmtUseDb Element
2. Can You Dig It? – StmtCond Element
3. Can You Dig It? – StmtCursor and StmtReceive Elements