Which execution plan is stored in the plan cache?

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?

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.

StackExchange Database Administrators Q#312552, answer score: 14