SQL Server估计与实际执行计划

A SQL Server execution plan is the most efficient and least cost road map that is generated by the Query Optimizer’s algorithms calculations to execute the submitted T-SQL query. Execution plans are used by the database administrators to troubleshoot the performance of poorly performing queries to isolate the part of the query that is at the root of the performance issue.

SQL Server执行计划是由查询优化器的算法计算生成的最有效，成本最低的路线图，用于执行提交的T-SQL查询。 数据库管理员使用执行计划来对性能不佳的查询的性能进行故障排除，以隔离查询中属于性能问题根源的部分。

When a T-SQL query is submitted to the SQL Server database engine, a specific number of processes will be assigned to work on that query, concurrently, in order to execute it in the minimum time with the least resources.

当将T-SQL查询提交到SQL Server数据库引擎时，将同时分配特定数量的进程以对该查询进行处理，以便在最少的时间内以最少的资源执行该查询。

In the first step, the SQL Server Relational Engine will check that the T-SQL query is written correctly in a process called query Parsing. A parsing tree will be generated from the parser that contains the steps that are required to execute the submitted query. For DML queries, the parsing tree will be processed by the Algebrizer that is responsible for resolving the name of the database objects that are used in the query. The query processor tree that is generated by the algebrizer will be passed to the SQL Server Query Optimizer.

第一步，SQL Server Relational Engine将检查T-SQL查询是否在称为query Parsing的过程中正确编写。 解析器将生成一个解析树，其中包含执行提交的查询所需的步骤。 对于DML查询，解析树将由Algebrizer处理，该Algebrizer负责解析查询中使用的数据库对象的名称。 由代数生成器生成的查询处理器树将传递到SQL Server 查询优化器 。

What the Query Optimizer does is specify the best way to execute the submitted query, by choosing the most efficient plan with the lowest CPU, memory, and I/O cost between different execution plans. The query Optimizer uses the query processor tree and SQL Server Statistics, which are the metadata that describe the data distribution and uniqueness within the database tables and indexes, to create the best plan, taking into consideration that the Optimization Level that is used is the Full optimization level. The Query Optimizer keeps testing and trying different indexes and orders for the query execution steps, calculating the estimated cost for each step and accumulating the overall plan cost until it reaches the fastest execution plan. When the plan is generated by the Query Optimizer, it will be stored in a special memory storage called the Plan Cache for reuse in future. Finally, the plan will be used by the SQL Server Storage Engine to execute the query and insert, retrieve or update the requested data.

查询优化器的作用是通过在不同执行计划之间选择CPU，内存和I / O成本最低的最有效计划，来指定执行提交查询的最佳方法。 查询优化器使用查询处理器树和SQL Server统计信息 （它们是描述数据库表和索引内数据分布和唯一性的元数据）来创建最佳计划，同时考虑到所使用的优化级别是“ 完全”。优化级别。 查询优化器会继续测试并尝试查询执行步骤的不同索引和顺序，计算每个步骤的估计成本，并累积总体计划成本，直到达到最快的执行计划。 当查询优化器生成计划时，它将存储在称为计划缓存的特殊内存中，以备将来使用。 最后，SQL Server存储引擎将使用该计划执行查询以及插入，检索或更新请求的数据。

The least execution time with the minimum resources consumption describes the optimal and most efficient execution plan expected from the Query Optimizer. But it is an expensive process that the query optimizer skips it if it can reuse the plans saved in the plan cache. Another situation when the submitted query is a simple SELECT query w