June 16, 2011 at 1:06 pm
i wanted to see what is the estimated execution plan and from management studio i clicked on "display estimated execution plan " and it gave an error " drop table #table1 not found". The sproc i am testing does drop temp tables, so does it mean it wont create an estimated execution plan when it is actually executing? So query optimizer doesnt really have an estimated execution plan to compare with actual?
June 16, 2011 at 1:09 pm
Think about it. The estimate plan runs with FMTONLY = ON.
If you don't execute the code, the table is not created and it can't be found while processing the script so it throws an error.
I'm afraid you're stuck with running that query to get the actual plan (table variables can very likely give you a different plan than temp table).
June 16, 2011 at 1:17 pm
sqldba_icon (6/16/2011)
The sproc i am testing does drop temp tables, so does it mean it wont create an estimated execution plan when it is actually executing? So query optimizer doesnt really have an estimated execution plan to compare with actual?
I'm going to quote a reply I made to you earlier today:
GilaMonster (6/16/2011)
sqldba_icon (6/15/2011)
I think i answered my own question after reading Gail's blog. With the query in question what would have probably happened is it generated an estimate plan and then estimate plan was good and used as actual execution plan and so i see the estimated plan( in this case which is also actual plan) in sys.dm_exec_query_planAaah...
The ONLY differences between an estimated plan and an actual plan is that the actual plan contains run time information. It is not that an 'estimated plan' is less work, rough quick guess or anything like that. It's not.
When you ask SSMS for an estimated plan it submits the query to SQL, SQL parses, binds and optimises and hands back the plan. No run time info because the query wasn't run.
When you ask SSMS for an actual plan it submits the query to SQL, SQL parses, binds and optimises, executes and hands back the plan with run-time information. No difference in the optimise portion.
What gets put into cache is the plan that the query uses, just with the run-time information stripped out (it's cached and reused, which Execution's run-time info should be used?)
http://sqlinthewild.co.za/index.php/2009/02/19/estimated-and-actual-execution-plan-revisited/
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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply