How query processing impacts plan reuse
It’s important to contextualize what happens in terms of query processing that can result in plan caching and reuse. In this section, we will focus on the highlighted section of the following diagram that determines whether a query plan can be reused from the cache or needs to be recompiled:
Figure 1.9: States of query processing related to query compilation/recompilation
As mentioned before, when an incoming T-SQL statement is parsed, a query hash value representing that statement is generated, and if that query hash is already mapped to a cached query plan, then it can just attempt to reuse that plan – unless special circumstances exist that don’t even allow plan caching, such as when the RECOMPILE hint is present in the T-SQL statement.
Assuming no such pre-existing conditions exist, after matching the query hash with a plan hash, the currently cached plan is tested for correctness, meaning that the SQL Database Engine will check whether anything has changed in the underlying referenced objects that would require the plan to be recompiled. For example, if a new index was created or an existing index referenced in the plan was dropped, the plan must be recompiled.
If the cached plan is found to be correct, then the SQL Database Engine also checks whether enough data has changed to warrant a new plan. This refers to the statistics objects associated with tables and indexes used in the T-SQL statement, and if any are deemed outdated – meaning its modification counter is high enough as it relates to the overall cardinality of the table to consider it stale.
Note
In SQL Server 2022 and Azure SQL Database, if the new Parameter Sensitive Plan (PSP) Optimization feature is used, one query hash can map to multiple query plan hashes. Each different plan hash is a standalone query plan called a variant, and maps to a single query hash that was deemed eligible for PSP Optimization. Each plan variant can be recompiled independently. PSP Optimization will be discussed later in the The importance of parameters section.
We will further discuss the role of statistics in the chapter Mechanics of the Query Optimizer, and query hashes and query plan hashes in the chapter Exploring Query Execution Plans, in the Operator-level properties section.
If nothing has significantly changed, then the query plan can be executed, as we discussed in this chapter in the Query execution essentials section.
The following picture depicts the high-level process for an already cached plan that can be executed as-is:
Figure 1.10: Process for executing a cached plan as-is
However, if any of the preceding checks fail, then the SQL Database Engine invalidates the cached plan and a new query plan needs to be compiled, as the available optimization space may be different from the last time the plan was compiled and cached. In this case, the T-SQL statement needs to undergo recompilation and go through the optimization process driven by the Query Optimizer so that a new query execution plan is generated (we will describe this process in greater detail in the chapter Mechanics of the Query Optimizer). If eligible, this newly generated query plan is cached.
Note
The same process is followed for new incoming queries where no query plan is yet cached.
Now that we understand how the SQL Database Engine caches and reuses query plans, let’s explore one of the most important factors that determines whether a plan may be reused – parameters.