sql server运算符_SQL Server执行计划中SELECT运算符的主要概念

sql server运算符

One of the main responsibilities of a database administrator is query tuning and troubleshooting query performance. In this context, SQL Server offers several tools to assist. But among them, query execution plans are essential for query optimization because they include all of the vital information about the query execution process. At the same time as it provides this valuable information “under the hood”, SQL Server creates a graphical description of the execution plan.

数据库管理员的主要职责之一是查询调整和查询性能故障排除。 在这种情况下，SQL Server提供了多种工具来提供帮助。 但是在其中，查询执行计划对于查询优化至关重要，因为它们包括有关查询执行过程的所有重要信息。 SQL Server在“内幕”提供这些有价值的信息的同时，还创建了执行计划的图形描述。

查询执行 (Query execution)

When we look at the query execution; it includes 5 general steps. This diagram illustrates query execution process.

当我们查看查询执行时； 它包括5个常规步骤。 该图说明了查询执行过程。

Query Parsing: The query parsing process checks the syntax of query. Said another way, query parsing process controls the T-SQL query syntax validation which we want to execute.

查询解析：查询解析过程检查查询的语法。 换句话说，查询解析过程控制着我们要执行的T-SQL查询语法验证。

Binding (Algebrizer): We can describe this process, simply, as the step which checks the objects, tables or columns existing in the database. Binding (Algebrizer) creates a query processor tree and this tree will be used as input for the query optimizer.

绑定（Algebrizer）：我们可以简单地将这一过程描述为检查数据库中存在的对象，表或列的步骤。 绑定（Algebrizer）创建一个查询处理器树，该树将用作查询优化器的输入。

Query Optimization: The SQL Server Query Optimizer’s task is to find the optimum execution plan. The Query Optimizer work methodology uses a cost-based optimizer which means that the Query Optimizer analyzes possible execution plans and decides optimum execution plan based on estimated cost.

查询优化： SQL Server查询优化器的任务是找到最佳执行计划。 查询优化器的工作方法使用基于成本的优化器，这意味着查询优化器会分析可能的执行计划，并根据估算的成本来确定最佳执行计划。

Cache Execution Plan: When a query is executed and but the query execution plan was not previously stored in the plan cache, the execution engine creates a new execution plan and stores this execution in the plan cache. When the same query is executed next, SQL Server avoids creating a new execution plan and skips this step. So Query Optimizer avoids consuming redundant time and CPU. In some cases, the execution engine decides on using different execution plans. Some of the reasons leading to this situation are;

缓存执行计划：执行查询但查询执行计划先前未存储在计划缓存中时，执行引擎将创建一个新的执行计划，并将此执行存储在计划缓存中。 接下来执行相同的查询时，SQL Server避免创建新的执行计划，并跳过此步骤。 因此，Query Optimizer避免浪费冗余时间和CPU。 在某些情况下，执行引擎决定使用不同的执行计划。 导致这种情况的一些原因是：

• Removing or adding an index to associated objects which are used by execution plan
  删除执行计划使用的关联对象或向其添加索引
• Database compatibility level changing
  数据库兼容性级别更改
• Cardinality Estimation Model Version
  基数估计模型版本
• RECOMPILE query hint
  RECOMPILE查询提示

Execution: The query is executed by the Execution Engine according to the execution plan which is decided by the query optimizer. Finally, the result of the query is returned.

执行：查询是由执行引擎根据查询优化器确定的执行计划执行的。 最后，返回查询结果。

选择运营商 (Select operator)

In this section, we will discuss the select operator and its main properties. The bellow image illustrates the select operator icon in the execution plan.

在本节中，我们将讨论select运算符及其主要属性。 下面的图像说明了执行计划中的选择操作符图标。

A query or stored procedure which consists of at least one select statement is where we can see select operators. Note that it is considered a best practices of reading execution plan, you should read execution plan right to left.

一个包含至少一个select语句的查询或存储过程是我们可以看到select运算符的地方。 请注意，这是阅读执行计划的最佳实践，您应该从右到左阅读执行计划。

But, in some cases, before starting to analyze the execution plan you can quickly look over select operator properties, because can provide us with basic information of the query.

但是，在某些情况下，在开始分析执行计划之前，您可以快速查看选择的运算符属性，因为它可以为我们提供查询的基本信息。

Now, we will discuss the main properties of the select operator.

现在，我们将讨论select运算符的主要属性。

Cached Plan Size: This value defines how much memory is consumed by the executed query in the plan cache.

缓存的计划大小：此值定义计划缓存中执行的查询消耗了多少内存。

Cardinality Estimation Model Version: Cardinality Estimation predicts how many rows will be returned by the executed query. In this prediction process, Cardinality Estimation uses statistic histograms. Cardinality Estimation prediction accuracy affects the quality of execution plan.

基数估计模型版本：基数估计可预测执行的查询将返回多少行。 在此预测过程中，基数估计使用统计直方图。 基数估计预测精度会影响执行计划的质量。

Microsoft notes that query performance can be affected by the Cardinality Estimation Model. Cardinality Estimation Model settings can be changed under the Database Scoped Configurations. At the same time the FORCE_LEGACY_CARDINALITY_ESTIMATION query hint forces the use of an earlier version of Cardinality Estimation Model Version. When we compare two queries in one of which includes FORCE_LEGACY_CARDINALITY_ESTIMATION query hint, we can see the difference between execution plans Cardinality Estimation Model Version.

Microsoft指出，基数估计模型可能会影响查询性能。 基数估计模型设置可以在“数据库范围配置”下更改。 同时， FORCE_LEGACY_CARDINALITY_ESTIMATION查询提示会强制使用基数估计模型版本的早期版本。 当我们比较其中包含FORCE_LEGACY_CARDINALITY_ESTIMATION查询提示的两个查询时，我们可以看到执行计划之间的差异基数估计模型版本。

Degree of Parallelism: This metric identifies how many CPUs are used to execute this query. This metric is affected by max degree of parallelism and costs threshold for parallelism settings. At the same time, MAXDOP query hint effect can affect Degree of Parallelism value.

并行度：此度量标准标识用于执行此查询的CPU数量。 此指标受最大并行度和并行设置成本阈值的影响。 同时，MAXDOP查询提示效果可能会影响并行度值。

In this section, I want to add a notice about threshold cost for parallelism setting. If your query execution plan cost does not cross the value, the query optimizer does not decide to use parallel execution plans.

在本部分中，我想添加有关并行性设置的阈值成本的通知。 如果您的查询执行计划成本未超过该值，则查询优化器不会决定使用并行执行计划。

The general approach about what to set cost threshold for parallelism value is between 15 and 50. But this value can change according to your system workload. Before the change, this value you have to test your SQL Server environment.

关于为并行度设置成本阈值的方法通常在15到50之间。但是该值会根据您的系统工作负载而变化。 更改之前，此值必须测试SQL Server环境。

Estimated Number of Rows: This value identifies the prediction of how many rows will be returned in the select operator. As mentioned, the Cardinality Estimation Model and statistics play a significant role in this prediction accuracy.

估计的行数：此值标识在select运算符中将返回多少行的预测。 如前所述，基数估计模型和统计量在此预测准确性中起着重要作用。

Estimated Operator Cost: This metric identifies the percentage value of select operator as part of the total execution plan.

估计的运营商成本：此度量标准确定选定的运营商的百分比值作为总执行计划的一部分。

Memory Grant Info: This value identifies a buffer memory which is used to store temporary rows while sorting, grouping and joining rows.

内存授予信息：此值标识用于在对行进行排序，分组和联接时存储临时行的缓冲存储器。

Optimization Level: This metric returns information about what the SQL Optimizer did during the query optimization process. In this metric can return TRIVIAL or FULL.

优化级别：此指标 返回有关SQL优化器在查询优化过程中执行的操作的信息。 在此指标中，可以返回TRIVIAL或FULL。

TRIVIAL is the first step of query optimization. If the executed query is simple, the query optimizer decides to create a TRIVIAL execution plan. The reason for this decision that optimizer doesn’t want to spend much time to choose an effective execution plan.

TRIVIAL是查询优化的第一步。 如果执行的查询很简单，则查询优化器决定创建TRIVIAL执行计划。 之所以做出这样的决定，是因为优化器不想花很多时间来选择有效的执行计划。

FULL means the SQL Server Query Optimizer completes the query optimization cycle. But in this case, we have to observe the Reason for Early Termination of Statement Optimization property. Because this property gives us some idea about the result of the optimization process.

FULL表示SQL Server查询优化器完成了查询优化周期。 但是在这种情况下，我们必须观察“ 语句优化”属性提前终止的原因 。 因为此属性使我们对优化过程的结果有所了解。

In the above image, the reason for Early Termination of Statement Optimization property definition is Good Enough Plan Found. It means that the query optimizer found the optimum execution plan. But in some cases you can find out that the Early Termination of Statement Optimization definition is Time Out. It means that the query optimizer attempts to find the optimum execution plan for the executed query. But this attempt reaches the limit and query optimizer stops before finding the optimum execution plan. The query optimizer decides the most optimum plan from among the various attempts until that time. In some cases, this situation is potentially a negative sign regarding query performance.

在上图中， 提前终止语句优化属性定义的原因是找到了足够好的计划 。 这意味着查询优化器找到了最佳执行计划。 但是在某些情况下，您可以发现“语句优化的提前终止”定义是“超时” 。 这意味着查询优化器尝试为已执行的查询找到最佳执行计划。 但是这种尝试达到了极限，查询优化器在找到最佳执行计划之前就停止了。 查询优化器从那时开始的各种尝试中确定最佳计划。 在某些情况下，这种情况可能会对查询性能产生负面影响。

In this heading, I want to add a notice about the Query Optimizer execution plan decision algorithm. The Query optimizer goal is to find the optimum execution plan, not necessarily to find best execution plan. Some queries have a lot of possibilities for different execution plans and one of these can be best execution plan. But the Query Optimizer execution plan decision algorithm is based on a balance between optimization time and plan quality.

在本标题中，我想添加有关Query Optimizer执行计划决策算法的通知。 查询优化器的目标是找到最佳执行计划，而不必找到最佳执行计划。 一些查询对于不同的执行计划有很多可能性，其中之一可能是最佳执行计划。 但是Query Optimizer执行计划决策算法基于优化时间和计划质量之间的平衡。

Parameter List: If you execute a stored procedure with parameter you will see Parameter List Property. In this property, you can find the information about the stored procedure’s first compiled parameters. In some cases, though, the compiled execution plan does not use the optimum execution plan for different parameters and this can negatively affects stored procedure performance. This performance issue is related to Parameter Sniffing.

参数列表：如果执行带有参数的存储过程，则会看到Parameter List Property 。 在此属性中，您可以找到有关存储过程的第一个编译参数的信息。 但是，在某些情况下，编译后的执行计划不会针对不同的参数使用最佳执行计划，这可能会对存储过程的性能产生负面影响。 此性能问题与参数嗅探有关 。

结论 (Conclusions )

In this article, we discussed the main concepts of select operators and their main properties. At the same time, we highlighted how the query optimizer works. If we want to analyze and tune the performance of a query, for best results we have to acknowledge about the query optimizer working mechanism.

在本文中，我们讨论了选择运算符的主要概念及其主要属性。 同时，我们重点介绍了查询优化器的工作方式。 如果我们要分析和调整查询的性能，为了获得最佳结果，我们必须承认查询优化器的工作机制。

参考资料 (References)

• Understanding SQL Server Cardinality Estimations by Charting Histogram to Actual Values 通过将直方图绘制为实际值来了解SQL Server基数估计
• Configure the cost threshold for parallelism Server Configuration Option 为并行服务器配置选项配置成本阈值
• Identifying query compile/runtime parameter values using XML SHOWPLAN output 使用XML SHOWPLAN输出识别查询编译/运行时参数值

翻译自: https://www.sqlshack.com/main-concepts-of-select-operators-in-sql-server-execution-plans/

sql server运算符