官术网_书友最值得收藏!

  • Learn T-SQL Querying
  • Pedro Lopes Pam Lahoud
  • 425字
  • 2021-06-24 14:38:14

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.

We will further discuss the role of statistics in Chapter 3, Mechanics of the Query Optimizer.

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.

The same process is followed for new incoming queries where no query plan is yet cached.

主站蜘蛛池模板: 汾西县| 五河县| 凤台县| 仁化县| 浮梁县| 镇赉县| 东光县| 黄龙县| 平果县| 珲春市| 福海县| 资溪县| 河东区| 西贡区| 临清市| 绩溪县| 抚远县| 本溪市| 定日县| 常熟市| 吴旗县| 额敏县| 新源县| 镇远县| 太谷县| 上虞市| 永兴县| 满城县| 阿鲁科尔沁旗| 大兴区| 新竹县| 准格尔旗| 乌拉特中旗| 崇州市| 社旗县| 化州市| 南召县| 淮安市| 靖宇县| 张家港市| 大洼县|