In this series of articles, we will navigate the SQL Server Execution Plan ocean, starting from defining the concept of the Execution Plans, walking through the types, components and operators of Execution Plans analyze execution plans and we’ll finish with how to save and administrate the Execution Plans.
在本系列文章中，我们将在定义执行计划的概念，浏览执行计划的类型，组件和运算符，分析执行计划的过程中导航SQL Server执行计划的海洋，然后完成如何保存和管理执行计划。
When you submit a T-SQL query, you tell the SQL Server Engine what you want, but without specifying how to do it for you. Between submitting the T-SQL query to the SQL Server Database Engine and returning the query result to the end user, the SQL Server Engine will perform four internal query processing operations, to convert the query into a format that can be used by the SQL Server Storage Engine easily use to retrieve the requested data, using the processes assigned to the SQL Engine from the Operating System to work on the submitted query.
提交T-SQL查询时，您告诉SQL Server Engine想要什么，但没有指定如何为您执行此操作。 在将T-SQL查询提交给SQL Server数据库引擎并将查询结果返回给最终用户之间，SQL Server Engine将执行四个内部查询处理操作，以将查询转换为SQL Server可以使用的格式。通过使用从操作系统分配给SQL引擎的进程来处理提交的查询，存储引擎可以轻松地用于检索请求的数据。
The first three processes; Parsing, Algebrizing and Optimizing will be performed by the SQL Server Relational Engine. On the other hand, the Execution step is performed by the SQL Server Storage Engine. If the submitted query is not a Data Manipulation Language (DML) statement, such as CREATE TABLE or ALTER TABLE, there will be no need to optimize that query, as there is only one straight way for the SQL Server Engine to perform that action. The four processing steps that are performed by both the SQL Server Relational Engine and SQL Server Storage Engine are summarized as below:
前三个过程； 解析，代数和优化将由SQL Server关系引擎执行。 另一方面，执行步骤由SQL Server存储引擎执行。 如果提交的查询不是诸如CREATE TABLE或ALTER TABLE之类的数据操作语言（DML）语句，则无需优化该查询，因为SQL Server Engine只有一种直接方法可以执行该操作。 SQL Server关系引擎和SQL Server存储引擎执行的四个处理步骤总结如下：
Once the SQL query is submitted, it will be broken into individual parts and checked by the SQL Server Relational Engine to make sure that it is written in a correct way, with no syntax errors for example, under a process that is called Query Parsing. The result of the query parsing process is the parsing tree. The parsing tree is an internal representation of the query that includes all the steps, also known as preemptive operations, that are followed to execute the provided query. After creating the parsing tree for the DML queries, the Algebrizer will take the generated tree and resolve the names of the different database objects referenced by the submitted query against the system catalog, to make sure that these objects exist in the database and that the user has permission to execute the query. The algebrizer will generate a query tree that will be used in the next step, as shown below:
提交SQL查询后，它将分为几部分，并由SQL Server Relational Engine检查以确保在称为Query Parsing的过程中以正确的方式编写了该文件，例如没有语法错误。 查询解析过程的结果是解析树。 解析树是查询的内部表示，包括所有步骤（也称为抢先操作），可以执行所有步骤以执行提供的查询。 在为DML查询创建了解析树之后， Algebrizer将采用生成的树并针对系统目录解析提交的查询所引用的不同数据库对象的名称，以确保这些对象存在于数据库中，并且用户有权执行查询。 代数器将生成一个查询树，将在下一步中使用它，如下所示：
The query processor tree result, from the algebrizer processing, will be input for the Query Optimizerfor SQL Server Execution Plan. The Query Optimizer is functionality that is responsible for modeling the way the SQL Server Relational Engine works, by creating the most efficient plan to execute the provided query, with the lowest SQL Server resources consumption; this is called the Cost-based Execution Plan. The query optimizer will try different execution methods, reading all table rows, using different indexes, joins and orders and compromising between different resources consumption scenarios, until it ends up with the most optimal execution plan with the least possible cost. The total cost of each candidate execution plan is calculated by assigning a weight for each operation, then summing the cost of all these operations together.
来自代数处理器处理的查询处理器树结果将输入到SQL Server执行计划查询优化器 。 查询优化器是一种功能，它通过创建最有效的计划来执行提供的查询，同时消耗最少SQL Server资源，从而对SQL Server Relational Engine的工作方式进行建模。 这称为基于成本的执行计划。 查询优化器将尝试不同的执行方法，读取所有表行，使用不同的索引，联接和顺序，并在不同的资源消耗情况之间进行折衷，直到最终以最小的成本获得最佳的执行计划。 通过为每个操作分配权重，然后将所有这些操作的成本加在一起，可以计算出每个候选执行计划的总成本。
SQL Server Execution Plan is a binary representation of the steps that will be followed by the SQL Server Engine to execute the query. In other words, it is the most efficient and least cost roadmap, generated by the SQL Server Query Optimizer, by following different algorithms to execute the submitted query. The plan will be created based on the query processor tree, resulting from the binding step, and the database tables and indexes statistics, that describes the distribution and uniqueness of the data within the database objects, making sure that the optimization level setting is configured as Full. These statistics help the SQL Server Query Optimizer to compare the number of records returned from scanning all the table rows and the records returned from using different indexes, with the cost of each operation. It is very important to keep the statistics of the database tables and indexes up to date, in order to be able to create the most optimal execution plan.
SQL Server 执行计划是SQL Server引擎执行查询所遵循的步骤的二进制表示。 换句话说，它是SQL Server查询优化器通过遵循不同的算法来执行提交的查询而生成的最有效，成本最低的路线图。 该计划将基于绑定步骤产生的查询处理器树以及数据库表和索引统计信息创建，该统计信息描述数据库对象中数据的分布和唯一性，并确保将优化级别设置配置为充分。 这些统计信息可帮助SQL Server查询优化器将扫描所有表行返回的记录数与使用不同索引返回的记录数进行比较，并将其与每个操作的成本进行比较。 保持数据库表和索引的统计信息最新是非常重要的，以便能够创建最佳的执行计划。
After choosing the most efficient SQL Serve Execution Plan for the newly submitted query, it will be stored in the Plan Cache memory storage, which is a part of SQL Server buffer where query plans are stored, for future reuse, which is more efficient than generating a new plan on each execution. This is due to the fact that, generating the most optimal execution plan is an expensive process. When a new query is submitted, the SQL Server Query Optimizer will search in the plan cache storage for an existing SQL Server Execution Plan for that query to be reused. If it finds no plan that can be reused, the Query Optimizer will go through the previously described process to create a new plan, taking more time to execute that query. Reusing execution plans is very beneficial in the case of stored procedures that are executed frequently with different parameters but are still using the same cached execution plan. Having a large number of ad hoc queries running on the database will prevent the execution plans from being reused and require continuous plans generations, that should be considered when configuring the workload typesetting, as we will discuss in detail in this series.
为新提交的查询选择最有效SQL服务执行计划后，它将存储在计划缓存内存存储中，该存储是存储查询计划SQL Server缓冲区的一部分，以备将来重用，比生成效率更高每个执行都有一个新计划。 这是由于以下事实：生成最佳执行计划是一个昂贵的过程。 提交新查询时，SQL Server查询优化器将在计划缓存存储中搜索要重复使用的该查询的现有SQL Server执行计划。 如果找不到可重用的计划，则查询优化器将通过前面描述的过程来创建新计划，这将花费更多时间来执行该查询。 对于经常使用不同参数执行但仍使用相同的缓存执行计划的存储过程，重用执行计划非常有益。 在数据库上运行大量临时查询将阻止执行计划被重用，并需要连续生成计划，这在配置工作负载类型设置时应予以考虑，这将在本系列中进行详细讨论。
In specific situations, the SQL Server Query Optimizer prefers to create a basic plan, also known as a Trivial plan, to execute the queries that have no aggregations or complex calculations, rather than spending time and consuming resources in generating the most efficient plan for that query. After that, the execution plan will be scheduled and used by the SQL Server Storage Engine in the Execution stage of the query according to the selected plan, and perform the requested data retrieval, insertion or modification process, as shown below:
在特定情况下，SQL Server查询优化器更喜欢创建一个基本计划（也称为琐碎计划）来执行没有聚合或复杂计算的查询，而不是花费时间和资源来生成最有效的计划查询。 之后，执行计划将由SQL Server存储引擎在查询的执行阶段根据所选计划进行计划和使用，并执行请求的数据检索，插入或修改过程，如下所示：
The SQL Server Database Administrators depend frequently on SQL Server Execution Plans in troubleshooting the performance of the T-SQL queries and locating the worst performing parts. The Execution Plan also provides DBAs with answers for different questions, such as why the query is slow and taking a long time, consuming large amount of CPU, Memory or I/O resources, or why this index is not used in the query. In addition, the Execution Plan helps the DBAs to write queries in a more efficient way and choose the most suitable and highest recommended index to speeds up data retrieval. To be able to use the execution plan in tuning the performance of the queries, a DBA should have the required skills to create the execution plans, understand its different types and analyze its components and operators.
在对T-SQL查询的性能进行故障排除并找出性能最差的部分时，SQL Server数据库管理员经常依赖于SQL Server执行计划。 执行计划还为DBA提供了不同问题的答案，例如为什么查询速度慢，耗时长，消耗大量CPU，内存或I / O资源，或者为什么查询中未使用此索引。 此外，执行计划还可以帮助DBA以更有效的方式编写查询，并选择最合适，最推荐的索引来加速数据检索。 为了能够使用执行计划来调整查询的性能，DBA应该具有创建执行计划，了解其不同类型以及分析其组件和运算符所需的技能。
Now we have a clear image of what is a SQL Server execution plan and how it is generated internally by the SQL Server Query Optimizer. We are ready to jump to the next article of this series to discuss the different types of the execution plans, Actual, Estimated or Live plans and Graphical, Text or XML formats. Prepare for a deep dive into Execution plans!
现在，我们清楚地了解了什么是SQL Server执行计划，以及如何由SQL Server查询优化器在内部生成该计划。 我们准备跳到本系列的下一篇文章，讨论执行计划的不同类型，即实际计划，估计计划或实时计划以及图形，文本或XML格式。 准备深入了解执行计划！
目录 (Table of contents)
参考资料 (References)
- Display an Actual Execution Plan 显示实际执行计划
- SQL Server Estimated Vs Actual Execution Plans SQL Server估计与实际执行计划
- SQL Server query execution plans – Basics SQL Server查询执行计划–基础
- SQL Server Query Execution Plans for beginners – Types and Options 面向初学者SQL Server查询执行计划–类型和选项
翻译自: https://www.sqlshack.com/sql-server-execution-plans-overview/
3540
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
