5.0 Retrieving Query Plans from Plan Cache DMV’s

Article
01/12/2007

The compiled plan is generated for the entire batch (batch level) while individual statements in the batch have query plans (statement level) associated with them. We demonstrated earlier how to retrieve the compiled plan, and in this section we will examine to retrieve the query plans from the plan cache DMV’s sys.dm_exec_query_plan and sys.dm_exec_text_query_plan.

5.1 Query Plans for Safe Auto-Parameterized Queries

Sys.dm_exec_query_plan takes the plan_handle of the cached or currently executing batch and returns among other columns the query plan in XML format. There are some important things to note regarding the query_plan returned from this DMF: First, if the plan_handle is for a query that has been parameterized by the server using either simple or forced parameterization, then the query_plan will just contain the statement text of the un-parameterized (shell) query. In order to retrieve the actual query_plan associated with the parameterized query, the plan handle of the parameterized query should be passed to sys.dm_exec_query_plan. To illustrate this with an example consider the query below that gets auto parameterized:

select col2 from t1 where col1 = 5

Now look at the query plans in sys.dm_exec_query_plan using the query below:

select st.text, qp.query_plan, cp.cacheobjtype, cp.objtype, cp.plan_handle

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

The showplan XML for the shell query with objtype ‘adhoc’ just contains the statement text, while the showplan XML for the parameterized query with objtype ‘prepared’ contains the actual query plan.

Text

Query_plan

Cache

obj

type

obj

type

Plan_handle

select col2 from t1 where col1 = 5

<ShowPlanXML xmlns=

"https://schemas.

microsoft.com/

sqlserver/2004/07/

showplan" Version="1.0" Build="9.00.0000.00">

<StmtSimple

StatementText="


select col2 from

t1 where col1 = 5
"

StatementId="1"

StatementCompId="1"

StatementType="SELECT" />

</Statements></Batch>

</BatchSequence>

</ShowPlanXML>

Compi

led

Plan

Adhoc

0x06000100

7E400B2FB8

4136040000

0000000000

(@1 tinyint)SELECT [col2] FROM [t1] WHERE [col1]=@1

<ShowPlanXML xmlns=

"https://schemas.

microsoft.com/

sqlserver/2004/07/

showplan" Version="1.0" Build="9.00.0000.00">

<StmtSimple

StatementText=

"(@1 tinyint)

SELECT [col2] FROM [t1]

WHERE [col1]=@1"

StatementId="1"

StatementCompId="1"

StatementType="SELECT" StatementSubTreeCost

="0.0032831"

StatementEstRows="1"

StatementOptmLevel=

"TRIVIAL">

<QueryPlan

CachedPlanSize="9"

CompileTime="11" CompileCPU="11"

CompileMemory="64">

<RelOp NodeId="0"

PhysicalOp=

"Clustered Index Seek"

</QueryPlan>

</StmtSimple>

</Statements>

</Batch>

</BatchSequence>

</ShowPlanXML>

Compi

led

Plan

Prep

ared

0x06000100

9F36A508B8

0119040000

0000000000

However if we had executed the query below, then we would have just gotten back one row corresponding to the parameterized query since sys.dm_exec_query_stats only gives query statistics for the parameterized query:

select st.text, qp.query_plan, qs.plan_handle

from sys.dm_exec_query_stats qs

cross apply sys.dm_exec_sql_text(qs.sql_handle) st

cross apply sys.dm_exec_query_plan(qs.plan_handle) qp

5.2 Query Plans for Unsafe Auto-Parameterized Queries

Consider the example below when the query gets unsafe auto-parameterized:

select value_in_use from sys.configurations where configuration_id = 16384

An increment in the SQL Server:SQL Statistics\Unsafe Auto-Params/sec performance counter indicates that the query was unsafe auto-parameterized. The query is parameterized and the shell query is cached. The parameterized query is not cached and is not visible through any of the plan cache DMV’s. The shell query however points to the parameterized query and this is the only way to get to the parameterized query. Now let us look at the query plan in the DMVs:

select st.text, qp.query_plan, qs.plan_handle

from sys.dm_exec_query_stats qs

cross apply sys.dm_exec_sql_text(qs.sql_handle) st

cross apply sys.dm_exec_query_plan(qs.plan_handle) qp

Text

Query_plan

Plan_handle

select value_in_use

from sys.configurations where configuration_id = 16384

<ShowPlanXML xmlns="https://schemas.microsoft.com

/sqlserver/2004/07/showplan" Version="1.0" Build="9.00.0000.00">

<Batch>

</Statements>

</Batch>

</BatchSequence>

</ShowPlanXML>

0x0600010

0CC96AD05

B861D0030

000000000

00000

The query plan returned is corresponding to that of the shell query. Since the query parameterization was unsafe, the parameterized query plan is not cached (shell query just points to it) and is not viewable through the DMVs.

5.3 Query Plans for Multi-Statement Batch

Consider the example below where a batch has more than one query, where some are parameterized:

select * from t1

select col2 from t1 where col1 = 5

Now query the DMVs using the query below:

select st.text, qp.query_plan, cp.cacheobjtype, cp.objtype, cp.plan_handle

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

Text

Query_plan

Cache

objtype

obj

type

Plan_handle

select * from t1 select col2 from t1 where col1 = 5

<ShowPlanXML xmlns="https://

schemas.microsoft.com/

sqlserver/2004/07/showplan"

Version="1.0"

Build="9.00.0000.00">

<Statements><StmtSimple StatementText=

"select * from t1" StatementId="1"

StatementCompId="1"

<QueryPlan

CachedPlanSize="8"

CompileTime="0"

CompileCPU="0"

CompileMemory="56">

<RelOp NodeId="0"

PhysicalOp=

"Clustered Index Scan"

<StmtSimple

StatementText=

"
select col2

from t1 where

col1 = 5
"

StatementId="2"

StatementCompId="2" StatementType

="SELECT" />

</Statements>

</Batch>

</BatchSequence>

</ShowPlanXML>

Compi

led

Plan

Adhoc

0x06000100

9632E81EB8

E134040000

0000000000

(@1 tinyint)SELECT [col2] FROM [t1] WHERE [col1]=@1

<ShowPlanXML xmlns="https://

schemas.microsoft.com/

sqlserver/2004/07/showplan"

Version="1.0"

Build="9.00.0000.00">

<Statements><StmtSimple StatementText="

(@1 tinyint)SELECT [col2]

FROM [t1] WHERE [col1]=@1" StatementId="1"

StatementCompId="2"

</Statements>

</Batch>

</BatchSequence>

</ShowPlanXML>

Compi

led

Plan

Prep

ared

0x0600010

09F36A508

B8E10F040

000000000

00000

There are 2 query plans, one corresponding to the batch and one for the parameterized query. The query plan for the batch has only the shell query statement text for the 2^nd statement and not the parameterized query.

However the same batch executed with showplan_xml on returns a different query plan. Query plan returned has both the parameterized query as well as the shell query statement text.

set showplan_xml on

select * from t1

select col2 from t1 where col1 = 5

Microsoft SQL Server 2005 XML Showplan

<ShowPlanXML xmlns="https://schemas.microsoft.com/

sqlserver/2004/07/showplan"

Version="1.0"

Build="9.00.0000.00">

<Batch>

<StmtSimple StatementText=

"select * from t1"

StatementId="1" StatementCompId="1".

<StmtSimple

StatementText="
select col2

from t1 where col1 = 5
"

StatementId="2"

ScalarOperator

ScalarString="CONVERT_IMPLICIT(int,[@1],0)">

</ShowPlanXML>

There are two things to be called out regarding sys.dm_exec_query_plan:

1. It is actually possible to get back NULL for the query_plan from sys.dm_exec_query_plan. Once scenario under which this can occur is which the plan handle is captured first, and then later passed as a parameter to sys.dm_exec_query_plan. In the time window between when the plan handle is captured and passed to the DMF, the plan may be booted out of the cache due to memory pressure. In such a case, we would get NULL for the query_plan.

2. For queries that are not cached it is not possible to retrieve their query plan using sys.dm_exec_query_plan. The only possibility of capturing the query plans for such queries would be if we queried sys.dm_exec_query_plan while the non-cacheable query was still executing on the server.

5.4 When and How to Use Sys.dm_exec_text_query_plan

The query_plan column returned by sys.dm_exec_query_plan has type XML and suffers from the inherent limitation of the data type that it cannot have nest levels greater than or equal to 128. In SQL Server 2005 RTM and SP1, if the query_plan had a depth of greater than or equal to 128 levels this would prevent the query from returning. The second limitation is that it is not easy to get the query plan of a particular statement in the batch directly from the DMVs without parsing the query plan XML returned from sys.dm_exec_query_plan as demonstrated here.

To address both these limitations, in SQL Server 2005 SP2, we introduced sys.dm_exec_text_query_plan. Sys.dm_exec_text_query_plan is a TVF that takes 3 parameters: plan_handle, statement_start_offset and statement_end_offset. It returns the showplan in text format for the batch or for a specific statement within the batch. Like sys.dm_exec_query_plan, the plan_handle specified can be that of a currently executing or a cached plan.

To summarize, the key differences between sys.dm_exec_query_plan and sys.dm_exec_text_query_plan are as follows: query plan is returned in text format instead of XML in sys.dm_exec_text_query_plan, the output of the query plan is not limited in size, and individual statements in a batch can be specified. The fact that we can extract the query plan for an individual statement in a batch makes this DMV extremely powerful when investigating performance problems on the server.

Consider the example below:

create procedure p1 as

begin

select * from t1

select col2 from t1 where col1 = 5

end

exec p1

When the procedure is executed, a compiled plan for procedure is cached. However it is possible to retrieve the query plans for each statement inside the stored procedure using the query below. This query especially useful when there are long running stored procedures with multiple queries, and we want to identify the queries with poor plans:

select substring(st.text, (qs.statement_start_offset/2) + 1,

((case statement_end_offset

when -1

then datalength(st.text)

else

qs.statement_end_offset

end

- qs.statement_start_offset)/2) + 1) as statement_text

, plan_handle, query_plan

from sys.dm_exec_query_stats qs

cross apply sys.dm_exec_sql_text(qs.sql_handle) st

cross apply sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset)

Statement_

Text

Plan_handle

Query_plan

select col1 from t1

0x05000100

39D82449B8

610F040000

0000000000

<ShowPlanXML xmlns="https://

schemas.microsoft.com/sqlserver

/2004/07/showplan" Version="1.0"

Build="9.00.0000.00">

<StmtSimple

StatementText="


create proc p1 as


begin
	

select col1 from t1
"

StatementId="1"

</BatchSequence></ShowPlanXML>

select col2 from t1 where col1 = 50000

0x05000100

39D82449B8

610F040000

0000000000

<ShowPlanXML xmlns="https://

schemas.microsoft.com/

sqlserver/2004/07/showplan"

Version="1.0"

Build="9.00.0000.00">

<StmtSimple

StatementText="


create proc p1 as 


begin
	select col1

from t1
	select

col2 from t1 where col1 =

50000" StatementId="1"

</BatchSequence></ShowPlanXML>

The important thing to note here is that using the plan_handle we can retrieve the compiled plan for the entire batch and the sql text of the entire batch. However since query plans are at the statement level, using the statement offsets exposed through sys.dm_exec_query_stats we can retrieve the statement text and its corresponding query plan via sys.dm_exec_text_query_plan. This is especially useful when trying to identify queries in a long batch that may have poor plans, and hence poor performance. Use the query below to identify the top five queries by average CPU time and their query plans:

select top 5 total_worker_time/execution_count as avg_cpu_time,

substring(st.text, (qs.statement_start_offset/2) + 1,

((case statement_end_offset

when -1

then datalength(st.text)

else

qs.statement_end_offset

end

- qs.statement_start_offset)/2) + 1) as statement_text

, plan_handle, query_plan

from sys.dm_exec_query_stats qs

cross apply sys.dm_exec_sql_text(qs.sql_handle) st

cross apply sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset)

order by total_worker_time/execution_count desc;

5.0 Retrieving Query Plans from Plan Cache DMV’s

Additional resources