October 10, 2011 at 7:10 am
Hi, Folks!
I fired the following Code from SSMS (SQL2K5 SP2):
USE AdventureWorksDW
SET STATISTICS XML ON
SELECT
OrderNumber
,LineNumber
,Model
FROM
[dbo].[vDMPrep]
WHERE
FiscalYear = '2004'
SET STATISTICS XML OFF
The XML Showplan result shows me the execution plan data in XML as expected:
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.0" Build="9.00.3042.00">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementText="SELECT [OrderNumber],[LineNumber],[Model] FROM [dbo].[vDMPrep] WHERE [FiscalYear]=@1" StatementId="1" StatementCompId="3" StatementType="SELECT" StatementSubTreeCost="2.68985" StatementEstRows="19442.6" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="TimeOut">
<StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false" />
<QueryPlan DegreeOfParallelism="0" MemoryGrant="1092" CachedPlanSize="219" CompileTime="104" CompileCPU="104" CompileMemory="2312">
<RelOp NodeId="0" PhysicalOp="Hash Match" LogicalOp="Inner Join" EstimateRows="19442.6" EstimateIO="0" EstimateCPU="0.230856" AvgRowSize="84" EstimatedTotalSubtreeCost="2.68985" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[AdventureWorksDW]" Schema="[dbo]" Table="[FactInternetSales]" Alias="[f]" Column="SalesOrderNumber" />
<ColumnReference Database="[AdventureWorksDW]" Schema="[dbo]" Table="[FactInternetSales]" Alias="[f]" Column="SalesOrderLineNumber" />
<ColumnReference Column="Expr1017" />
</OutputList>
...
<ParameterList>
<ColumnReference Column="@1" ParameterCompiledValue="'2004'" ParameterRuntimeValue="'2004'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
If I use the sys.dm_exec_cached_plans DMV like this...
SELECT
--*,
[cp].[refcounts]
, [cp].[usecounts]
, [cp].[objtype]
, [st].[dbid]
, [st].[objectid]
, [st].[text]
, [qp].[query_plan]
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
...I got a very simple XML without all the useful query plan informations like row counts etc. 🙁
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.0" Build="9.00.3042.00">
<BatchSequence>
<Batch>
<Statements>
<StmtUseDb StatementText="USE AdventureWorksDW " StatementId="1" StatementCompId="1" StatementType="USE DATABASE" Database="[AdventureWorksDW]" />
<StmtSimple StatementText=" SET STATISTICS XML ON " StatementId="2" StatementCompId="2" StatementType="SET STATS" />
<StmtSimple StatementText=" SELECT OrderNumber ,LineNumber ,Model FROM [dbo].[vDMPrep] WHERE FiscalYear = '2004' " StatementId="3" StatementCompId="3" StatementType="SELECT" />
<StmtSimple StatementText=" SET STATISTICS XML OFF " StatementId="4" StatementCompId="4" StatementType="SET STATS" />
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
What's wrong - or what I'm missing?
TIA from Berlin/Germany
SeBaFlu
October 10, 2011 at 10:19 am
Nothing is wrong. The plans in cache have no run-time information (they get reused, which execution's stats will they use?'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 10, 2011 at 10:38 am
Hi, Gail!
Thanx for the fast reply - but I'm a little bit confused:
As far as I understood is there a difference between "estimated plan" (without runtime information) and "actual plan" (with runtime information) as SSMS query toolbar offers.
My expectation was to find something like an estimated execution plan within the plan cache. If my expectation is wrong, what is the idea of plan cache (were the "cached plans" are stored I thought) and how and what can I deal with plans within the plan cache - any hints at whitepapers, blog entries etc. are very welcome!
TIA & regards from Berlin/Germany
SeBaFlu
October 10, 2011 at 2:16 pm
Not sure I follow....
What you get when you pull a plan from cache is exactly the same as what you get if you ask for an estimated execution plan from management studio - a plan with only compile time information.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply