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

The Exploration stage

If the Trivial Plan stage doesn't find a suitable plan, it's time to enter the cost-based optimization stage, known as Exploration, whose goal is to find a good-enough query execution plan based on the minimum estimated cost to access and join data. If this stage is used, the information on the optimization level is still stored in the execution plan under the same Optimization Level property, with a value of FULL.

A good-enough plan refers to the search optimization space and how SQL Server may not actually iterate through all possible plan combinations, but rather may look for a plan that meets its internal thresholds for a good-enough balance between the estimated resource usage and execution times.

The Exploration stage is where the CE comes into play. SQL Server loads statistics and performs some tasks in preparation for cost-based optimization.

These tasks are as follows:

  • Simplification transforms some sub-queries into semi-joins, and even detects whether parts of the query can skip execution, for example avoiding empty tables or searching a table column for a NULL predicate when that table column has a trusted NOT NULL constraint.
  • Normalization uses the query’s filter predicates and some heuristics to reorder join operations, and predicates are pushed-down to the algebrizer tree.

The cost-based optimization process itself is composed of three phases that we discuss in the next sections: Transaction Processing, Quick Plan, and Full Optimization. 

主站蜘蛛池模板: 红桥区| 乐陵市| 广汉市| 怀安县| 通河县| 大城县| 若羌县| 上思县| 金湖县| 东乡族自治县| 任丘市| 辉县市| 丽水市| 庆阳市| 宁南县| 明光市| 龙里县| 绿春县| 徐汇区| 夏邑县| 会泽县| 安多县| 合川市| 乐平市| 孟津县| 深圳市| 阳山县| 娄底市| 会理县| 嘉禾县| 莱阳市| 蒙城县| 舟曲县| 郧西县| 阳高县| 台东市| 安图县| 平远县| 彭泽县| 西昌市| 景德镇市|