Interview Question of the Week #051 – Actual Execution Plan vs. Estimated Execution Plan

Question: Do you use an actual execution plan or an estimated execution plan? I prefer an actual plan when I can safely run the query and want to understand what happened. An estimated plan is useful when executing the query is unnecessary, expensive or unsafe.

In a recent interview, a candidate turned the usual questions around and asked which plan I use. My original answer was emphatic: the actual plan. The useful reason is runtime evidence, rather than the claim that every estimate is wrong.

The actual plan adds what happened during execution

An actual plan includes the compiled plan plus runtime information from the completed execution. You can inspect actual rows, executions, warnings and available runtime metrics alongside the optimizer’s estimates.

The estimates do not turn into perfect measurements just because the plan is called actual. Estimated costs and row counts remain estimates; the runtime rows provide something to compare them with. One actual execution also represents its parameters, data and conditions, not every future execution.

An estimated plan does not run the query

It shows the optimizer’s compiled plan without measured execution rows or runtime warnings. That is valuable before a large modification, during a review, or when the question is whether a predicate or access path can be compiled as expected. Do not run a destructive statement merely to obtain an actual plan.

In SSMS, press Ctrl+L to display the estimated plan. Press Ctrl+M to include the actual plan, then execute the query. Ctrl+M alone does not run it.

This original SSMS menu image remains useful: both shortcuts are clearly visible. Its interface is historical; the shortcuts are still documented. For comparison between saved plans, see the original SQL Server 2016 plan comparison article.

My practical answer is: start with the question you are investigating. Use estimates to inspect a proposed plan, and use a safely obtained actual plan to test its assumptions against runtime evidence.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.