View Plan, Estimated or Actual?

[vcmcstech]

When I view a plan in this software, is it the actual plan or the estimated plan?

[DavidWiseman]

Hi, it's an estimated plan. Note: Actual plans are just estimated plans with runtime stats included. The runtime stats are very useful though.

So why not collect the actual plan? The plans are collected for running queries - the query is still executing so the actual plan wouldn't be available until the query has completed its execution. Even then - you can't get the plan through the DMVs. It would have to be captured via extended events and this could add some overhead. Potentially the live query plan might be available to capture via the DMVs while the query is running. sp_BlitzWho can do this if you have it enabled in advance.

If you want the actual plans (estimated query plans with runtime stats) - you need to collect the plan each time the query is executed. The same goes with Live query plans which are estimated plans with partial runtime stats. DBA Dash takes a smart approach with query plan collection where it keeps track of the plans collected and only collects new query plans. This reduces the cost of collecting plans and also the cost of storing query plans in the repository database. I discuss this here.

The estimated plan is still very useful even if it doesn't highlight problems in the same way as looking at the actual execution plan. From Running Queries you can click the link in the Session ID column. If you have slow query capture enabled and the query has finished executing (and collected by the DBA Dash agent) - you will have the query that was run with all the parameters. You could run this in SSMS to get the actual plan. Sometimes you might get a different plan - maybe due to parameter sniffing and possibly different SET options in SSMS. You could take a look at the parameters used to compile the estimated plan captured from DBA Dash. If you compile the query with those parameters you might be able to reproduce the issue. Or you could use the USE PLAN query hint to run the query with the plan captured from DBA Dash - allowing you to get the actual plan for the query.

Hope this helps,

David