What type of execution plan is stored in the plan cache?

The type of execution plan that is stored in the plan cache is the estimated execution plan. This plan is generated by the SQL Server Query Optimizer when a query is compiled and is based on the statistics available at the time of query compilation. The estimated execution plan outlines how SQL Server will execute the query, including which indexes to use and the order of operations.

One of the primary benefits of having an estimated execution plan stored in the plan cache is that it allows the database engine to reuse execution plans for queries that have similar structures, thereby improving performance by reducing the overhead of recompiling the same queries repeatedly.

In contrast, the actual execution plan, while also valuable, is not stored in the plan cache. It contains information on the actual runtime behavior of a query, including the number of rows processed and the actual resources used. The logical and physical execution plans refer to different aspects of query execution. The logical execution plan describes the logical operations that need to be performed, whereas the physical execution plan details how those logical operations will be executed on the physical resources of the server, such as which join algorithms will be used. However, neither of these is stored in the plan cache in the same way the estimated execution plan is.