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

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. 

主站蜘蛛池模板: 台中市| 廊坊市| 遂平县| 湘潭市| 宁明县| 龙岩市| 新平| 呼图壁县| 孟津县| 淮北市| 乌鲁木齐县| 镇远县| 高平市| 临高县| 巨野县| 张掖市| 长岭县| 京山县| 东丽区| 景德镇市| 普洱| 新丰县| 夹江县| 大新县| 屏山县| 礼泉县| 白朗县| 石柱| 宁城县| 长沙市| 道孚县| 凌云县| 楚雄市| 定西市| 壶关县| 屯门区| 荆门市| 阿尔山市| 陕西省| 鄂州市| 井冈山市|