sql limit 子句

TOP操作员基础 ( Basics of TOP Operator )

The TOP keyword in SQL Server is a non-ANSI standard expression to limit query results to some set of pre-specified rows. As an argument, it takes 0 to positive Bigint (9223372036854775807) and anything beyond or less gives an error message. TOP without an order by clause, in production, seems buggy because it can produce results in any order depending on the current schema and execution plan. You can also specify a percent with TOP expression which returns only the expression percent of rows from the result set. Float expressions used in TOP percent are rounded up to the next integer value.

SQL Server中的TOP关键字是一个非ANSI标准表达式，用于将查询结果限制为一组预先指定的行。 作为参数，Bigint（9223372036854775807）的取值为0，并且任何超出或小于此值的值都会给出错误消息。 在生产中不带order by子句的TOP似乎有问题，因为它可以根据当前模式和执行计划以任何顺序产生结果。 您还可以使用TOP表达式指定百分比，该百分比仅返回结果集中行的表达式百分比。 TOP百分比中使用的浮点表达式将四舍五入到下一个整数值。

确定性TOP的排序依据（TOP WITH TIES） ( Deterministic TOP with order by (TOP With TIES) )

仅当指定order by时，TOP才是确定性的。 只有这样，您才能保证结果将按特定的顺序排列。 但是，即使按顺序排序的TOP在按顺序排序的列包含非唯一值时也可能是不确定的。

Consider a scenario a user come with a requirement of TOP 3 product performed best in sale for the given saledata:

考虑以下场景：对于给定的销售数据，用户对TOP 3产品的需求在销售中表现最佳：

If you look at the data above, ProductID 3 and ProductID 4 aggregated sale Quantities are equal to 11.

如果您查看上面的数据，则ProductID 3和ProductID 4的合计销售数量等于11。

So actually both qualify for the 3rd place, but With regular TOP with ascending order by only 1 will be chosen randomly, of course, there are many ways to fulfill the requirement, but SQL Server has TOP with Ties which is useful when you want to return two or more rows that ties for last place in the limited results set.

因此，实际上两者都有资格获得第三名，但是对于常规的TOP，只有升序为1的情况会被随机选择，当然，有很多方法可以满足要求，但是SQL Server具有TOP并带有领带，当您想要使用TOP时很有用。返回两个或更多与有限结果集中最后一位相关的行。

Below is the result of above data of query TOP (3) with ties, as we can see that 4 rows are in the below result set as ProductID 3 and 4 qualify for the 3rd place.

下面是带有关系的查询TOP（3）的上述数据的结果，因为我们可以看到下面的结果集中有4行，因为ProductID 3和4符合第三名。

样本数据 (Sample Data)

We will use two tables, table1 and table2 for the example below in this article. Table2 is just a replica of table1, so both tables have the same data and data type. Both tables have 300000 rows. The primarykey column is marked as Primary Key.

本文下面的示例将使用两个表table1和table2。 Table2只是table1的副本，因此两个表具有相同的数据和数据类型。 两个表都有300000行。 主键列被标记为主键。

The primarykey and Keycol column consists of a numeric sequence from 1 to 300000 and the searchcol column has some random data. Below are the images of sample tables and the data.

主键和Keycol列由1到300000的数字序列组成，而searchcol列具有一些随机数据。 下面是示例表和数据的图像。

You can find the script to populate the data from below of the article.

您可以从本文下面找到用于填充数据的脚本。

Testing Environment

测试环境

Microsoft SQL SERVER 2014 – 12.0.4100.1 (X64) Developer Edition (64-bit)

Microsoft SQL SERVER 2016 (SP1) – 13.0.4001.0 (X64) Developer Edition (64-bit)

Microsoft SQL SERVER 2014 – 12.0.4100.1（X64）开发人员版（64位）

Microsoft SQL SERVER 2016（SP1）– 13.0.4001.0（X64）开发人员版（64位）

使用TOP 1进行表/索引扫描 (Table/Index scan with TOP 1)

The Table/Index scan is usually misunderstood by SQL Server users as it touches all the data pages of Table/Index. Sometimes it’s not at all necessary to touch all the rows from the table/index scan, for example, a semi-join or when you set the row goal. In both cases, it’s not necessary to touch all the data pages. Execution can terminate early when it founds the requested row.

表/索引扫描通常被SQL Server用户误解，因为它涉及到表/索引的所有数据页。 有时根本不需要触摸表/索引扫描中的所有行，例如，半联接或设置行目标时。 在这两种情况下，都无需触摸所有数据页。 执行可以在找到所请求的行时提早终止。

As stated above, with non-deterministic TOP expression queries, it sets up the Row Goal to return the N number of rows and it doesn’t matter in which order, so it’s completely unnecessary to get all the data pages. Just get an N number of rows and return it to the SSMS result tab.

如上所述，对于非确定性的TOP表达式查询，它将行目标设置为返回N个行，并且顺序无关紧要，因此完全不需要获取所有数据页。 只需获得N行，然后将其返回到SSMS结果标签即可。

I will run dbcc dropcleanbuffers to flush all the data pages. Please note that the dbcc dropcleanbuffers command is not recommended on a production server .

我将运行DBCC DROPCLEANBUFFERS 刷新 所有 数据页。 请注意， 不建议在生产服务器 上使用dbcc dropcleanbuffers命令。

Look at the query below with STATISTCS IO ON you will get the idea.

在STATISTCS IO ON的情况下查看下面的查询，您将获得想法。

 dbcc dropcleanbuffers; SEt statistics io on;Select  top 1 * from table1 

Table ‘table1’. Scan count 1, logical reads 3, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

表 “ table1” 。 扫描计数1， 逻辑读取3 ，物理读取3，预读读取0，lob逻辑读取0，lob物理读取0，lob提前读取0。

The Execution plan shows, on the right side, its clustered index scan strategy used to fetch the data from the table. Now look at the output of STATISTICS IO and you can see that there are just 3 logical reads. This verifies, for the above execution, that on the query index scan hasn’t read all the data pages from the index. It looked for only 1 row and when it finds it, it stops looking further.

执行计划在右侧显示了其聚簇索引扫描策略，该策略用于从表中获取数据。 现在查看STATISTICS IO的输出，您可以看到只有3个逻辑读取。 对于以上执行，这验证了查询索引扫描没有从索引读取所有数据页。 它只寻找1行，找到后就停止寻找。

执行计划的读取方式（行模式） (How an Execution plan reads (Row mode))

In SQL Server, an execution plan’s operators are called iterators for the reason that they work repeatedly like an iterations and process row by row based on demand-driven pipeline model.

在SQL Server中，执行计划的运算符称为迭代器，原因是它们像迭代一样反复工作，并基于需求驱动的管道模型逐行处理。

Generally, there are three common functions called by iterates:

通常，由迭代器调用的函数共有三个：

1 OPEN: to initialize the iterator.

1 OPEN：初始化迭代器。

2 GETNEXT: get the row and process according to the iterator property.

2 GETNEXT：根据迭代器属性获取行和进程。

3 CLOSE: Shut down the iterator.

3 CLOSE：关闭迭代器。

Execution starts from the leftmost iterator and control passes to next iterator, data flow to right to left.

执行从最左边的迭代器开始，控制权传递给下一个迭代器，数据流从右到左。

The TOP iterator helps it to understand how the execution plan runs left to right not right to left. To understand it better we will execute a query with TOP expression and examine an execution plan. Warm the cache by running the below query ones at least.

TOP迭代器帮助它了解执行计划如何从左到右而不是从右到左运行。 为了更好地理解它，我们将使用TOP表达式执行查询并检查执行计划。 至少运行下面的查询者预热高速缓存。

 SELECT TOP (2)  OT.Primarykey,  OT.SearchCol,  IT.SearchColFROM Table1 OTJOIN table2 IT  ON ot.Primarykey = IT.PrimarykeyWHERE ot.SearchCol < 1000; 

Execution starts from the leftmost select iterator, then control passes to TOP iterator.

从最左边的选择迭代器开始执行，然后控制权传递给TOP迭代器。

In the above execution plan TOP expression 2 is used that set up the row goal for 2 rows, then control passes to nested loop join iterator, then table1(clustered index scan) initiated and starts requesting the data for the filter ([TEST].[dbo].[table1].[SearchCol] as [ot].[SearchCol]<(1000)) on the Table1.

在上面的执行计划中，使用TOP表达式2将行目标设置为2行，然后控制传递给嵌套循环连接迭代器，然后启动table1（集群索引扫描），并开始为过滤器请求数据（ [TEST]。 [dbo]。[table1]。[SearchCol]的形式为[ot]。[SearchCol] <（1000） ）。

The first row that satisfies the filter criteria is 616 (the result would vary on your system). It is associated with the primarykey column value 72, so the nested loop join initiated the search for the value 72 in the table2, as table2 is indexed on the primarykey column so seek happened and passed all the request column to the nested loop join iterator. The nested loop join gets the data and sends it to TOP iterator and TOP iterator passes it to select an operator, then again filtered applied on the Table1(Cluster Index Scan) and return the data to the nested loop join then it matched to the Table2 then return the matches to the TOP iterator. Because only 2 was specified in the TOP expression, it signals to shut down to nested loop join and that signal is passed to both the outer and inner tables.

满足过滤条件的第一行是616（结果在您的系统上会有所不同）。 它与主键列值72相关联，因此嵌套表连接在table2中启动了对值72的搜索，因为table2在主键列上建立了索引，所以查找发生了，并将所有请求列传递给了嵌套循环连接迭代器。 嵌套循环联接获取数据并将其发送到TOP迭代器，TOP迭代器将其传递给选择运算符，然后再次对表1进行过滤（集群索引扫描），然后将数据返回到嵌套循环联接，然后将其与Table2匹配然后将匹配项返回到TOP迭代器。 因为在TOP表达式中仅指定了2，所以它发出信号以关闭嵌套循环连接，并且该信号同时传递给外部表和内部表。

However, on my system for the predicate [ot].[SearchCol]<(1000) there are 3077 actual numbers or rows present in the table, but because execution follows demand-driven pipeline model, the nested loop join iterator produced only 2 rows to the TOP iterator and shut down scanning when parent iterators signal it to shut down.

但是，在我的谓词[ot]。[SearchCol] <（1000）的系统上，表中存在3077个实际数字或行，但是由于执行遵循需求驱动的管道模型，因此嵌套循环联接迭代器仅产生2行到TOP迭代器，并在父迭代器发出关闭信号时关闭扫描。

Please note result may vary to your system as the above query results are non deterministic. If you want to explore more about it you can see this link .

请注意，由于上述查询结果不确定，因此结果可能因您的系统而异。 如果您想进一步了解它，可以看到此链接 。

聚合和并行嵌套循环联接的前1个问题 ( TOP 1 problem with aggregation and parallel nested loop join )

In this part of the article, we will see a specific situation with TOP 1 where Query Optimizer final selected plan runs extremely slow. We will try to examine the Query Optimizer final cheapest selected plan for the below expressed query and is it really scale and perform well if not, then we will try to find out the reason behind it and available alternative if this pattern bothers you.

在本文的这一部分中，我们将看到TOP 1的特定情况，其中Query Optimizer最终选择的计划运行得非常慢。 我们将尝试为以下表示的查询检查Query Optimizer最终最便宜的计划，它是否真的可以扩展并表现良好（如果不是），那么我们将尝试找出背后的原因以及如果该模式困扰您的可用替代方法。

Consider a scenario where you need to count number of occurrences of maximum numeric value in the searchcol column of table1 after joining the table2. What if you have this task in hand and you have to do it manually how would do it? Most probably the answer would be:

考虑一个场景，在加入table2之后，您需要在table1的searchcol列中计算最大数值的出现次数。 如果您手头有此任务并且必须手动执行该怎么办？ 答案很可能是：

Get both inputs

获取两个输入

Step 1: join two inputs

步骤1：加入两个输入

Step 2: Group the output result on search column

步骤2：在搜索列上将输出结果分组

Step 3: order by column descending

步骤3：按列降序排列

Step 4: get the TOP 1

步骤4：获得TOP 1

There are many alternative options are available to express the above requirement in the query, but most natural seems to me has written below. Now lets see how it implements by the Query Optimizer with an estimated execution plan query and execution plan below:

有很多可供选择的选项可用于表达查询中的上述要求，但在我看来，最自然的写法是在下面。 现在，通过下面的估算的执行计划查询和执行计划，看看查询优化器如何实现它：

 Select  TOP 1 T1.SearchCol , COUNT_BIG(T1.SearchCol) from Table1 T1join Table2 T2 on T1.Keycol = T2.Keycol  Group by T1.SearchColorder by T1.SearchCol desc 

Step 1: Get the Table1

步骤1：获取表1

Step 2: Sort table 1

步骤2：对表格1进行排序

Step 3: join table 2 (stream based non blocking) with nested loop join

步骤3：使用嵌套循环联接联接表2（基于流的非阻塞）

Step 4: group the output result

步骤4：将输出结果分组

Step 5: get the TOP 1

步骤5：获得TOP 1

以下是更详细的描述：（我们将以此为数据流透视图进行调试） (More detail description below: (we will debug this as data flow perspective) )

The Query Optimizer chooses a parallel execution plan. The chosen technique is actually a very smart approach for the two large tables (the Query Optimizer only join 2 tables at a time, if in the query there are more than 2 tables, then the join tree of tables is purely cost based).

查询优化器选择一个并行执行计划。 对于两个大表，选择的技术实际上是非常聪明的方法（Query Optimizer一次仅连接2个表，如果查询中有2个以上的表，则表的连接树纯粹是基于成本的）。

As for the above expressed query there is no need of joining all the rows, so it’s wise to just sort one input (here the Query Optimizer is using a full outer table sort after all the outer side rows are sorted then passes to the nested loop join row by row on demand) as specified in the order by clause then display the output.

对于上面表示的查询，不需要连接所有行，因此明智的做法是仅对一个输入进行排序（此处，在对所有外侧行进行排序然后传递到嵌套循环之后，Query Optimizer使用完整的外部表排序按 order by子句中的指定按行连接） ，然后显示输出。

This kind of implementation, with a non-blocking loop join, suits best as it would preserver the outer order (in some cases NL doesn’t preserver the outer order, but here it will preserver the outer order) and then group with global stream aggregated (it requires the same set of values at same the worker thread, somehow its child iterator responsibility to do that, it’s not blocking but required sorted input) and pass the sorted first group to the TOP iterator.

这种具有非阻塞循环连接的实现最适合，因为它将保留外部顺序（在某些情况下NL不会保留外部顺序，但在这里它将保留外部顺序），然​​后与全局流分组聚合（它在相同的工作线程中需要相同的一组值，以某种方式由其子迭代器负责，这不是阻塞而是需要排序的输入），然后将排序的第一组传递给TOP迭代器。

Now let’s run the query with Maxdop 2 to limit the query for and turn on the actual execution plan:

现在，让我们使用Maxdop 2运行查询以限制查询并打开实际的执行计划：

 Select  TOP 1 T1.SearchCol , COUNT_BIG(T1.SearchCol) from Table1 T1join Table2 T2 on T1.Keycol = T2.Keycol  Group by T1.SearchColorder by T1.SearchCol descoption (maxdop 2) 

Something went really wrong as it took 1:13 minutes to complete the execution on my system, so what was wrong in the above execution. Maybe we are running with Maxdop 2. Let use maximum capacity of system CPU processing and check how much query performance improved.

发生了真正的错误，因为花了1:13分钟才能完成我的系统上的执行，所以上述执行中出了什么问题。 也许我们正在使用Maxdop 2运行。让我们最大程度地利用系统CPU处理的能力，并检查查询性能得到了多少改进。

 Select  TOP 1 T1.SearchCol , COUNT_BIG(T1.SearchCol) from Table1 T1join Table2 T2 on T1.Keycol = T2.Keycol  Group by T1.SearchColorder by T1.SearchCol descoption (maxdop 4) 

Here it took 3:56 minutes to complete the execution. If your machine has more than 4 processors, then try to run it with the maximum logical processor available. If your query is having the same pattern, then I am sure it would be worse by increasing of DOP. We are using a machine with four logical processors available to SQL Server.

在这里花了3:56分钟才能完成执行。 如果您的计算机具有四个以上的处理器，请尝试在可用的最大逻辑处理器下运行它。 如果您的查询具有相同的模式，那么我相信增加DOP将使情况变得更糟。 我们正在使用一台具有四个可用于SQL Server的逻辑处理器的计算机。

Why would this query execution time become worse with increased DOP? Does that mean parallelism is bad?

为什么随着DOP的增加，此查询的执行时间会变得更糟？ 这是否意味着并行性不好？

Not at all. Parallelism is a boon in SQL Server and itself implemented very well. SQL Server uses horizontal parallelism for every iterator. Each worker is assigned a separate part of the work and the partial results are combined for the final result set. For queries involving large datasets SQL Server generally scales linearly or nearly linearly.

一点也不。 并行性在SQL Server中是一个福音，它本身实现得很好。 SQL Server为每个迭代器使用水平并行性。 每个工人都被分配了工作的一个单独部分，部分结果被合并为最终结果集。 对于涉及大型数据集的查询，SQL Server通常线性或几乎线性缩放。

So let’s go back and try to understand why query took that much time execution plan below:

因此，让我们回过头来尝试理解为什么查询花了这么多时间执行下面的计划：

You can see that the number of executions at the inner side for the table2 of nested loop joins its 8290 and the estimated number of execution is 2.10229. That means that the Query Optimizer comes with this final execution plan with nested loop join because it estimated that nested loop join will only be executed to near about 2.10229 times but it actually executed 8290 times on my system. Keep in mind that both tables have perfect statistics in place. Still, we are seeing a huge difference in an actual VS estimated number of executions.

您可以看到嵌套循环的table2内侧的执行次数联接到其8290，估计的执行次数为2.10229。 这意味着Query Optimizer带有此带有嵌套循环连接的最终执行计划，因为它估计嵌套循环连接将仅执行约2.10229次，但实际上在我的系统上执行了8290次。 请记住，两个表都具有完善的统计信息。 不过，我们发现实际VS估计的执行数量存在巨大差异。

So finally, it turns out that the Nested Loop Join is the main culprit for the slow performance of the query.

因此，最后，事实证明嵌套循环联接是导致查询性能降低的主要原因。

As Stated above TOP, SET ROWCOUNT, Fast N and IF EXISTS enable the row goal and this change the optimization model to generate the query plan for specified rows only, in some specific cases, After enabling the row goal the query may take a long time to run. Microsoft is aware of this problem so there is a to disable the row goal. Let’s run the above query with this trace flag and see the actual execution plan.

如上面的TOP所述，SET ROWCOUNT，Fast N和IF EXISTS启用行目标，这会更改优化模型以仅针对指定的行生成查询计划，在某些特定情况下，启用行目标后，查询可能需要很长时间跑步。 Microsoft已意识到此问题，因此有一个可以禁用行目标。 让我们使用该跟踪标志运行上述查询，并查看实际的执行计划。

 Select  TOP 1 T1.SearchCol , COUNT_BIG(T1.SearchCol) from Table1 T1join Table2 T2 on T1.Keycol = T2.Keycol  Group by T1.SearchColorder by T1.SearchCol descoption (QueryTraceON 4138) 

It uses the same steps as we expected in the very first guess:

它使用与我们最初猜测中预期的步骤相同的步骤：

Get both inputs

获取两个输入

Step 1: join two inputs

步骤1：加入两个输入

Step 2: Group the output result on search column

步骤2：在搜索列上将输出结果分组

Step 3: order by all descending

步骤3：按降序排列

Step 4: get the TOP 1

步骤4：获得TOP 1

更多细节： (More details:)

The Query Optimizer again chooses a parallel execution plan, as no predicate was specified in the query.

查询优化器再次选择并行执行计划，因为在查询中未指定谓词。

Here is the sequence of operations

这是操作顺序

• The entire table2 is build input for the hash match,

• Rows are then distributed to all the available threads on demand based schema by the Parallel Page Supplier, • Then exchange iterator (hash iterator, we will give details about hash exchange iterator later), • Then hash Match (Hash joins parallelize and scale better than any other physical join type and are great at maximizing throughput in data warehouses) is applied, as a physical join type as the hash join is a blocking iterator for the build input and it required additional memory to build the hash table in memory.

•整个表2是哈希匹配的构建输入，

•然后，并行页面供应商将行分配给基于需求的所有可用线程架构， •然后进行交换迭代器（哈希迭代器，稍后我们将提供有关哈希交换迭代器的详细信息）， •然后应用哈希匹配（哈希连接比任何其他物理连接类型都更好地并行化和扩展，并且在最大化数据仓库中的吞吐量方面表现出色），因为哈希连接是构建输入的阻塞迭代器，因此它是一种物理连接类型，并且需要额外的内存以在内存中构建哈希表。

Note that this may spill into tempdb if not enough memory available for hash join at a run time. One tip we should remember while dealing with parallel hash join is that memory is divided amongst the threads equally, so a hash join can spill even if enough memory calculated for the hash join but rows skewed across the threads for any reason.

请注意，如果在运行时没有足够的内存可用于哈希联接，则这可能会溢出到tempdb中。 我们在处理并行散列连接时应记住的一个技巧是，内存在线程之间平均分配，因此即使为散列连接计算了足够的内存，但由于任何原因导致行在行之间偏移，散列连接也会溢出。

However, this is not the case in the above example, as rows are evenly distributed amongst the threads and hash join ran entirely in memory, as shown in the execution plan global stream iterator used for aggregating the rows (because before joining the two table hash partition applied which is deterministic function which guarantees that values don’t overlap between the threads) and the estimated cost calculated for this version of execution plan is 22.5983 units, the memory grant to this query on my system is 83.328 MB and it ran for 583 ms only. As such this was quite an improvement without any index.

但是，在上面的示例中情况并非如此，因为行平均分布在线程之间，并且哈希联接完全在内存中运行，如用于聚合行的执行计划全局流迭代器所示（因为在联接两个表哈希之前应用的分区是确定性函数，可确保值在线程之间不重叠），为此版本的执行计划计算的估计成本为22.5983单位，此查询在我的系统上的内存授权为83.328 MB，运行583仅毫秒。 因此，这是一个没有任何索引的改进。

Let’s go back to the first query which chooses the nested loop join, and try to deep dive and understand the execution plan.

让我们回到选择嵌套循环联接的第一个查询，然后尝试深入研究并理解执行计划。

Both tables used in the query are identical and represent the same dataset, so why did it take that much time and after increasing the maxdop setting and why is it worse?

查询中使用的两个表是相同的，并且代表相同的数据集，那么为什么增加maxdop设置后花费那么多时间，又为什么会变得更糟？

This time we will use the DMV for more detail information. We will run our queue and on the other session we will run the dmv and try to interpret its result.

这次，我们将使用DMV 获取更多详细信息。 我们将运行队列，在另一个会话中，我们将运行dmv并尝试解释其结果。

 Select  TOP 1 T1.SearchCol , COUNT_BIG(T1.SearchCol) from Table1 T1join Table2 T2 on T1.Keycol = T2.Keycol  Group by T1.SearchColorder by T1.SearchCol desc 

The DMV shows Execution Context ID 5 to 8 blocked on a wait by Execution Context ID 1 and Execution Context ID 0 blocked on a CXPACKET wait by execution context 8. Execution context zero is a coordinating thread that runs the serial part of the execution plan to the leftmost Gather Streams operator. CXPACKET (Class exchange packet) means that the thread is involved in a parallelism-related wait. You can find more details in the resource_description column for Execution Context ID 5 to 8 it says Node ID 3 is waiting to get rows, looking into the execution plan confirm node id 3 is exchange iterator (hash partition).

DMV显示执行上下文ID 1阻塞了等待的执行上下文ID 5至8，执行上下文8阻塞了等待的执行上下文ID0。执行上下文零是运行执行计划的串行部分的协调线程。到最左侧的Gather Streams运算符。 CXPACKET（类交换包）意味着线程参与与并行性相关的等待。 您可以在执行上下文ID 5至8的resource_description列中找到更多详细信息，其中说节点ID 3正在等待获取行，查看执行计划，确认节点ID 3是交换迭代器（哈希分区）。

So now let’s deep inside exchange iterator:

现在，让我们深入了解交换迭代器：

The is unique in many ways as it’s quite different compared to other iterators, It is actually two iterators: a producer and a consumer. The producer reads input rows from its branch and pushes data to the consumer side of exchange iterator, whereas most iterators follow the Demand-driven model. Data flow between exchange’s producer to consumer iterator follows the push-based method for efficiency reasons. It fills the packet with rows (that’s why parallelism iterators always required additional memory) and passes it to the consumer side. The consumer side receives packets, removes the rows from these packets, and returns rows to its parent iterator based on demand-driven pipeline model. One important detail that needs to be understood here is that the packet of rows at the producer is only transferred when a packet is full or a child iterator runs out of data.

在很多方面都是独一无二的，因为与其他迭代器相比，它有很大不同。它实际上是两个迭代器：生产者和使用者。 生产者从其分支读取输入行，并将数据推送到交换迭代器的使用者端，而大多数迭代器遵循需求驱动模型。 出于效率考虑，交易所生产者到消费者迭代器之间的数据流遵循基于推的方法。 它用行填充数据包（这就是并行迭代器始终需要额外内存的原因），并将其传递给用户方。 消费者方接收数据包，从这些数据包中删除行，然后根据需求驱动的管道模型将行返回给其父迭代器。 这里需要理解的一个重要细节是，仅当数据包已满或子迭代器的数据用完时才在生产方传输行数据包。

Now look at the execution plan again and now more specifically at node 3 as reported above by the sys.dm_os_waiting_tasks dmv. Node ID 3 in the above execution plan is the parallelism (exchange) iterator. Parallelism (exchange) iterator with hash partition type routes incoming rows to the all available threads using a hash function (deterministic) on the partition column. So rows won’t get mixed up with other threads, in our query partition column is searchcol column.

现在再次查看执行计划，现在更具体地看一下上面由sys.dm_os_waiting_tasks dmv报告的节点3。 上述执行计划中的节点ID 3是并行性（交换）迭代器。 具有散列分区类型的并行（交换）迭代器使用分区列上的散列函数（确定性）将进入的行路由到所有可用线程。 因此行不会与其他线程混淆，在我们的查询分区列中是searchcol列。

From the child iterator (nested loop join) parallelism iterator gets 8484 rows, but passed only 45 number of rows to the parent (stream aggregated) iterator, for the reason as stated above. The exchange iterator’s producer side sends rows to the consumer in a packet only, when either packet is full or the child iterator runs out of data, so producer side of parallelism iterator waited for nested loop join to fill the data packet. When the data packet filled with rows then it send to consumer side of parallelism iterator, the consumer side of the exchange receives data packet(s) and removes rows from the packet(s) and then send it to its parent iterator (stream aggregated) based on demand.

从子迭代器（嵌套循环联接）中，并行性迭代器获得8484行，但由于上述原因，仅将45行的数量传递给父（流聚合）迭代器。 交换迭代器的生产者端仅在一个数据包已满或子迭代器用完数据时才向数据包中的行发送给消费者，因此并行性迭代器的生产者端等待嵌套循环联接来填充数据包。 当数据包中充满行然后将其发送到并行迭代器的消费者方时，交换的消费者方将接收数据包并从数据包中删除行，然后将其发送给其父迭代器（流聚合）根据需求。

So if somehow we would able to reduce the packet size (not possible) at the producer end of exchange iterator or increase the searchcol column data size, then the producer side would send the packet to the consumer side early. Here we will do a small test to fill the producer of the exchange early by altering the data type at the run time to the maximum capacity of numeric data type per row, so the exchange packet could fill up early and send the packet early.

因此，如果我们能够以某种方式减小交换迭代器的生产者端的数据包大小（不可能）或增加searchcol列数据大小，那么生产者端将提早将数据包发送给消费者端。 在这里，我们将通过在运行时将数据类型更改为每行数字数据类型的最大容量来进行一次小型测试，以尽早填充交换的生产者，以便交换数据包可以尽早填充并尽早发送数据包。

 Select  TOP 1 SearchCol = convert (decimal(38,0) ,T1.SearchCol)     , Count=  COUNT_BIG(T1.SearchCol) from Table1 T1join Table2 T2 on T1.Keycol = T2.Keycol  Group by convert (decimal(38,0) ,T1.SearchCol)order by convert (decimal(38,0) ,T1.SearchCol) descoption ( loop join ) 

The execution plan shape is identical to our first parallel nested loop join query. There is just one additional compute scalar iterator after the sort iterator, a compute scalar has a task to convert the integer searchcol column to decimal, and the new value is labeled as [Expr1002].

执行计划的形状与我们的第一个并行嵌套循环联接查询相同。 排序迭代器之后只有一个附加的计算标量迭代器，计算标量具有将整数searchcol列转换为十进制的任务，新值标记为[Expr1002]。

Now note that the Exchange (parallelism) iterator partition type is also Hash type and partition column is [Expr1002]. The actual number of rows feeding to exchange iterator is 3614 whereas in the last query actual number of rows were 8484. It happened because now the producer side of the exchange is able to push the data packet to the consumer side of exchange early because the row size is now increased. Hence the data packet filled up with a few rows and pushed an early compares to the last execution plan. This query executed in 1:40 minutes on my system.

现在注意，Exchange（并行）迭代器的分区类型也是哈希类型，分区列是[Expr1002]。 馈送给交换迭代器的实际行数是3614，而在上一个查询中，实际行数是8484。之所以发生，是因为现在交换的生产者端能够将数据包尽早推送到交换的消费者端，因为该行大小现在增加了。 因此，数据包填充了几行，并推动了与最后执行计划的早期比较。 此查询在我的系统上以1:40分钟执行。

As stated, for the problem, specific to the above, with this data set nested loop join would be the best strategy. We need to eliminate exchange (parallelism) iterator from the query plan, so we will run again the query with maxdop 1 to force SQL Server to choose the serial version over its default parallel version of execution.

如上所述，对于特定于上述问题的问题，使用此数据集嵌套循环连接将是最佳策略。 我们需要从查询计划中消除交换（并行）迭代器，因此我们将再次使用maxdop 1运行查询，以强制SQL Server选择其默认并行执行版本的串行版本。

 Select  top 1 ot.SearchCol , COUNT_BIG(ot.SearchCol) from  Table1 ot join Table2 in1 on ot.Keycol =in1.Keycol Group by ot.SearchColorder by ot.SearchCol descoption(maxdop 1) 

If you are running this query on SQL Server 2012/2016, you might see the as reported on my system. It states “Operator used tempdb to spill data during execution with spill level 1 and 1 spilled thread(s), Sort wrote 766 pages to and read 766 pages from tempdb with granted memory 22552KB and used memory 22552KB”. This is strange as the estimated number of rows and average row size are accurate, and the system has sufficient memory still it spills to the tempdb.

如果您在SQL Server 2012/2016上运行此查询，则可能会看到系统上报告的 。 它指出：“操作员在溢出级别为1和1的溢出线程执行期间使用了tempdb来溢出数据，Sort向tempdb写入766页和从tempdb中读取766页，并授予了内存22552KB和已使用的内存22552KB”。 这很奇怪，因为估计的行数和平均行大小是准确的，并且系统仍然有足够的内存溢出到tempdb。

Actually, it is a bug in SQL Server which appears in certain conditions and it is fixed in subsequent updates. To enable this fix you need to use the trace flag 7470, however, this trace flag should not be enabled at the server level as it increases memory requirements for sort queries and may impact memory availability for concurrent queries.

实际上，这是SQL Server中的错误，在某些情况下会出现，并且在后续更新中已修复。 要启用此修复程序，您需要使用跟踪标志7470，但是，不应在服务器级别启用此跟踪标志，因为它会增加排序查询的内存要求，并可能影响并发查询的内存可用性。

Apart from that, as expected, the plan is identical to parallel nested loop join. This is a perfect looking plan shape as the inner side on nested loop join executes twice and passed 2 rows to the stream aggregated and more importantly the query executed in 273 milliseconds.

除此之外，正如预期的那样，该计划与并行嵌套循环连接相同。 这是一个看起来完美的计划形状，因为嵌套循环连接的内侧执行两次，并将2行传递给聚合的流，更重要的是，查询在273毫秒内执行。

I have seen these parallel nested loop join with row goal running for a long time and consuming all the CPU resources. it doesn’t run itself slowly but affects other concurrent queries as well, competing for resources. I hope in this article we all get the idea how row goal can affect your query performance and how to deal with it.

我已经看到这些并行嵌套循环与行目标运行了很长时间并消耗了所有CPU资源。 它不会运行缓慢，但也会影响其他并发查询，从而争夺资源。 我希望在本文中我们所有人都能理解行目标如何影响查询性能以及如何处理它。

Here you can .

在这里，您可以 。

翻译自:

sql limit 子句