Which execution plan is stored in the plan cache?

Question

When query is executed, SQL server will produce a list of query plans and heuristically choose the plan with lower cost.

The chosen plan will be stored in the plan cache for subsequent use when it sees the same query.

When certain attributes of table change or when an index is rebuilt, then it will again produce a list of query plans again and heuristically choose one with lower cost, and store it in the plan cache.

However, the MSDN seems to indicate that estimated plan is stored in the plan cache, see below screenshot. Is that correct?

Answer 1

Yes, it is correct, though the terminology used isn't perfect.

The terms 'estimated' and 'actual' are a convenient way to distinguish between a plan without runtime statistics ('estimated') and one also including statistics from a particular execution ('actual').

A cached plan doesn't include runtime statistics from any particular execution, so it is 'estimated'.

Query Store also holds 'estimated' plans, but also records some runtime information separately.

Answer 2

Just for reference a would like to add this to the great Paul's answer.

This is the microsoft doc about execution plans:

https://learn.microsoft.com/en-us/sql/relational-databases/performance/execution-plans?view=sql-server-ver16

as you can see, it say this:

• The Estimated Execution Plan is the compiled plan, as produced by the Query Optimizer based on estimations. This is the query plan that is stored in the plan cache.
• The Actual Execution Plan is the compiled plan plus its execution context. It becomes available after the query execution has completed. This includes actual runtime information such as execution warnings, or in newer versions of the Database Engine, the elapsed and CPU time used during execution.
• The Live Query Statistics is the compiled plan plus its execution context. It is available for in-flight query executions, and updated every second. This includes runtime information such as the actual number of rows flowing through the operators, elapsed time, and the estimated query progress.

In the Query Store you can also find estimated execution plans and query statistcs.