SQL Server Plan Cache Recompilation and Reuse

Description

Before a query batch begins execution on SQL Server, the batch is compiled into a plan. The plan is then executed for its effects or to produce results. The compilation of execution plans is a relatively expensive operation so an attempt is made to avoid these costs by caching the compiled plans in a SQL Server memory region called the Plan Cache. When another query batch needs to be executed, SQL Server searches the Plan Cache for possible plan reuse opportunities. If plan reuse is achieved, the compilation costs are avoided. The compilation is the process of creating a compiled plan from a query batch. When the database engine begins execution of a compiled plan, it first checks that the plan is still valid and optimal. If either of these checks fails, the statement corresponding to the query plan or the entire batch is compiled again. Such compilations are known as recompilations. This lab is divided into three exercises. This lab is divided into three exercises explaining different types of plan caching mechanism, plan recompilation and plan reuse.

Exercise 1: Plan Caching Mechanism
Exercise 2: Plan Recompilation
Exercise 3: Plan Reuse