5.0 Retrieving Query Plans from Plan Cache DMV’s
Published Mar 23 2019 04:47 AM 756 Views
Microsoft
First published on MSDN on Jan 12, 2007

P.MsoNormal {<br/> FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"; mso-style-parent: ""<br/>}<br/>TABLE.MsoNormalTable {<br/> FONT-SIZE: 10pt; FONT-FAMILY: "Times New Roman"; mso-style-parent: ""<br/>}<br/>


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


go



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


go



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=


"http://schemas.


microsoft.com/


sqlserver/2004/07/


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


<BatchSequence>


<Batch><Statements>


<StmtSimple


StatementText="&#xD;&#xA;


select col2 from


t1 where col1 = 5&#xD;&#xA;"


StatementId="1"


StatementCompId="1"


StatementType="SELECT" />


</Statements></Batch>


</BatchSequence>


</ShowPlanXML>


Compi


led


Plan


Adhoc


0x06000100


7E400B2FB8


4136040000


0000000000


0000000000


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


<ShowPlanXML xmlns=


"http://schemas.


microsoft.com/


sqlserver/2004/07/


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


<BatchSequence>


<Batch><Statements>


<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


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


go



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


go



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


go




Text


Query_plan


Plan_handle


select value_in_use


from sys.configurations where configuration_id = 16384


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


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


<BatchSequence>


<Batch>


<Statements>


<StmtSimple StatementText="select value_in_use from sys.configurations where configuration_id = 16384&#xD;&#xA;" StatementId="1" StatementCompId="1" StatementType="SELECT" />


</Statements>


</Batch>


</BatchSequence>


</ShowPlanXML>


0x0600010


0CC96AD05


B861D0030


000000000


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


go



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


go




Text


Query_plan


Cache


objtype


obj


type


Plan_handle


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


<ShowPlanXML xmlns="http://


schemas.microsoft.com/


sqlserver/2004/07/showplan"


Version="1.0"


Build="9.00.0000.00">


<BatchSequence><Batch>


<Statements><StmtSimple StatementText=


"select * from t1&#xD;" StatementId="1"


StatementCompId="1"


.


.


<QueryPlan


CachedPlanSize="8"


CompileTime="0"


CompileCPU="0"


CompileMemory="56">


<RelOp NodeId="0"


PhysicalOp=


"Clustered Index Scan"


.


.


<StmtSimple


StatementText=


"&#xA;select col2


from t1 where


col1 = 5&#xD;&#xA;"


StatementId="2"


StatementCompId="2" StatementType


="SELECT" />


</Statements>


</Batch>


</BatchSequence>


</ShowPlanXML>


Compi


led


Plan


Adhoc


0x06000100


9632E81EB8


E134040000


0000000000


0000000000


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


<ShowPlanXML xmlns="http://


schemas.microsoft.com/


sqlserver/2004/07/showplan"


Version="1.0"


Build="9.00.0000.00">


<BatchSequence><Batch>


<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


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


go



select * from t1


select col2 from t1 where col1 = 5


go




Microsoft SQL Server 2005 XML Showplan


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


sqlserver/2004/07/showplan"


Version="1.0"


Build="9.00.0000.00">


<BatchSequence>


<Batch>


<Statements>


<StmtSimple StatementText=


"select * from t1&#xD;"


StatementId="1" StatementCompId="1".


.


<StmtSimple


StatementText="&#xA;select col2


from t1 where col1 = 5&#xD;&#xA;"


StatementId="2"


.


.


ScalarOperator


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


<Convert DataType="int" Style="0" Implicit="1">


.


<ColumnReference Column="@1" />


.


.


</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


go



exec p1


go



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)


go




Statement_


Text


Plan_handle


Query_plan


select col1 from t1


0x05000100


39D82449B8


610F040000


0000000000


0000000000


<ShowPlanXML xmlns="http://


schemas.microsoft.com/sqlserver


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


Build="9.00.0000.00">


<BatchSequence>


<Batch><Statements>


<StmtSimple


StatementText="&#xD;&#xA;


create proc p1 as


&#xD;&#xA;begin&#xD;&#xA;&#x9;


select col1 from t1&#xD;&#xA;"


StatementId="1"


.


.


</BatchSequence></ShowPlanXML>


select col2 from t1 where col1 = 50000


0x05000100


39D82449B8


610F040000


0000000000


0000000000


<ShowPlanXML xmlns="http://


schemas.microsoft.com/


sqlserver/2004/07/showplan"


Version="1.0"


Build="9.00.0000.00">


<BatchSequence>


<Batch><Statements>


<StmtSimple


StatementText="&#xD;&#xA;


create proc p1 as &#xD;&#xA;


begin&#xD;&#xA;&#x9;select col1


from t1&#xD;&#xA;&#x9;select


col2 from t1 where col1 =


50000&#xD;" 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;

go
Version history
Last update:
‎Mar 23 2019 04:47 AM
Updated by: