http://www.sqlservercentral.com/blogs/stratesql/2010/12/30/can-you-dig-it_3F00_-_1320_-stmtsimple-element/

Printed 2014/07/28 06:49PM

Can You Dig It? – StmtSimple Element

By StrateSQL, 2010/12/30

3295969599_eb16a58118_m1
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.

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:

image

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):

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:

image

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


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.