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 sections of the following diagram, which determine whether a query plan can be reused from the cache or needs to be recompiled:

As mentioned, when an incoming T-SQL statement is parsed, a query hash value representing that statement as it was written is generated, and if that query hash is already mapped to a cached query plan, 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 SQL Server 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 if an existing index referenced in the plan was dropped, the plan must be recompiled.

If the cached plan is found to be correct, SQL Server 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.

If nothing has significantly changed, the query plan can be executed, as we discussed in the Query execution essentials section.

The following diagram depicts the high-level process for an already-cached plan that can be executed as is:

However, if any of the preceding checks fail, SQL Server 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 Chapter 3, Mechanics of the Query Optimizer.) If eligible, this newly-generated query plan is cached.