DaveWentzel.com            All Things Data

Estimated vs Actual Plans

 Everyone should know by now the difference between the "Include Actual Plan" button and "Display Estimated Execution Plan" button.  These equate to "SET STATISTICS PROFILE ON" and "SET SHOWPLAN_ALL ON" respectively, if you want the textual representation of the query plans. 

There is some data available in the textual plans that is not available in the graphical plans, but not much, and not much that you would find valuable daily.  XML Query Plans are the "new" way of viewing textual query plans starting in 2005 and I try to focus on them since they provide even more information that is in fact useful that is not available in the graphical plans. 


In many cases the actual and the estimated execution plans vary wildly.  Why? 

  • temp tables (the estimated plan will actually fail if the query references any temp tables). 

  • bad statistics. 

  • Parallelism.  If the plan is a candidate for parallelism then multiple plans may actually be generated, but obviously only one will be executed.  The estimated plan might show you one plan but the actual could use the other version due to server resource availability when it was actually executed.