Hands-On-Lab: SQL Server Plan Cache Recompilation and Reuse

Hello Friends,

Here is another popular lab from Hands-On-Lab library: SQL Server Plan Cache Recompilation and Reuse.

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

SQLMaestros Hands-On-Labs enables the practical way of learning. Each lab document consists of multiple exercises and each exercise consists of multiple tasks. Apart from step-by-step instructions, the HOL document contains images, explanations, observations, notes, tips & tricks to ensure a great learning experience. With each lab, you can practice a specific concept step-by-step. With our HOL documents, you will get hands-on with hundreds of topics in SQL Server & Microsoft Data Platform. Learn More.