If the SP1 introduced two new Dynamic Management Objects sys.dm_exec_query_resource_semaphores and sys.dm_exec_query_memory_grants, SP2 comes with just one - the sys.dm_exec_text_query_plan dynamic management function.
As the name says it returns the Showplan for a batch or a specific stament within the batch in text format.
The syntax is very simple the function having as arguments a plan_handle (identifies a cached plan) and optionally the start and the end position of the statement you are interested of:
sys.dm_exec_text_query_plan
(
plan_handle
, { statement_start_offset | 0 | DEFAULT }
, { statement_end_offset | -1 | DEFAULT }
)Let's see it at work by using a query that displays its own plan:
SELECT
qp.*
FROM sys.dm_exec_requests AS r
CROSS APPLY
sys.dm_exec_text_query_plan(r.plan_handle, DEFAULT, DEFAULT) qp
WHERE r.session_id = @@SPID;
GO
The result:
dbid | objectid | number | encrypted | query_plan |
NULL | NULL | NULL | 0 | <ShowPlanXML xmlns="http://s... |
Or another query:
SELECT
CAST(qp.query_plan AS XML) query_plan
FROM sys.dm_exec_requests AS r
CROSS APPLY
sys.dm_exec_text_query_plan(r.plan_handle, DEFAULT, DEFAULT) qp
WHERE r.session_id = @@SPID;
GO
and the result:
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.0" Build="9.00.3027.00">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementText="
SELECT 
CAST(qp.query_plan AS XML) query_plan
FROM sys.dm_exec_requests AS r 
CROSS APPLY 
sys.dm_exec_text_query_plan(r.plan_handle, DEFAULT, DEFAULT) qp
WHERE r.session_id = @@SPID;
" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="7.05265e-005" StatementEstRows="11.1803" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound">
<QueryPlan CachedPlanSize="15" CompileTime="5" CompileCPU="5" CompileMemory="168">
<RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="11.1803" EstimateIO="0" EstimateCPU="4.67338e-005" AvgRowSize="1055" EstimatedTotalSubtreeCost="7.05265e-005" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Column="Expr1000" />
</OutputList>
<NestedLoops Optimized="0">
<OuterReferences>
<ColumnReference Table="[SYSREQUESTS]" Column="plan_handle" />
</OuterReferences>
<RelOp NodeId="1" PhysicalOp="Table-valued function" LogicalOp="Table-valued function" EstimateRows="11.1803" EstimateIO="0" EstimateCPU="1.13373e-005" AvgRowSize="43" EstimatedTotalSubtreeCost="1.13373e-005" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Table="[SYSREQUESTS]" Column="plan_handle" />
</OutputList>
<TableValuedFunction>
<DefinedValues>
<DefinedValue>
<ColumnReference Table="[SYSREQUESTS]" Column="plan_handle" />
</DefinedValue>
</DefinedValues>
<Object Table="[SYSREQUESTS]" />
<ParameterList>
<ScalarOperator ScalarString="(1)">
<Const ConstValue="(1)" />
</ScalarOperator>
<ScalarOperator ScalarString="@@spid">
<Identifier>
<ColumnReference Column="ConstExpr1001">
<ScalarOperator>
<Intrinsic FunctionName="@@spid" />
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
</ParameterList>
</TableValuedFunction>
</RelOp>
<RelOp NodeId="4" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="1055" EstimatedTotalSubtreeCost="1.24554e-005" Parallel="0" EstimateRebinds="10.0623" EstimateRewinds="0.118034">
<OutputList>
<ColumnReference Column="Expr1000" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1000" />
<ScalarOperator ScalarString="CONVERT(xml,DM_EXEC_TEXT_QUERY_PLAN.[query_plan],0)">
<Convert DataType="xml" Style='0' Implicit='0'>
<ScalarOperator>
<Identifier>
<ColumnReference Table="[DM_EXEC_TEXT_QUERY_PLAN]" Column="query_plan" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp NodeId="5" PhysicalOp="Table-valued function" LogicalOp="Table-valued function" EstimateRows="1" EstimateIO="0" EstimateCPU="1.157e-006" AvgRowSize="1055" EstimatedTotalSubtreeCost="1.13373e-005" Parallel="0" EstimateRebinds="10.0623" EstimateRewinds="0.118034">
<OutputList>
<ColumnReference Table="[DM_EXEC_TEXT_QUERY_PLAN]" Column="query_plan" />
</OutputList>
<TableValuedFunction>
<DefinedValues>
<DefinedValue>
<ColumnReference Table="[DM_EXEC_TEXT_QUERY_PLAN]" Column="query_plan" />
</DefinedValue>
</DefinedValues>
<Object Table="[DM_EXEC_TEXT_QUERY_PLAN]" />
<ParameterList>
<ScalarOperator ScalarString="SYSREQUESTS.[plan_handle]">
<Identifier>
<ColumnReference Table="[SYSREQUESTS]" Column="plan_handle" />
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
<ScalarOperator ScalarString="(-1)">
<Const ConstValue="(-1)" />
</ScalarOperator>
</ParameterList>
</TableValuedFunction>
</RelOp>
</ComputeScalar>
</RelOp>
</NestedLoops>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>