在这篇文章中，我们将讨论SQL TOP语句的性能细节，我们还将进行一个性能案例研究。
前提条件
在这篇文章中，我们将使用Adventureworks2019的样本数据库。
什么是SQL TOP语句
SQL TOP语句是用来限制从一个或多个表中获取或更新的行数的。这个行数限制可以确定为使用一个固定值或表的百分比。例如，下面的查询将返回生产表的前10条随机记录。
SELECT TOP 10 Name,ProductNumber,SafetyStockLevel FROM Production.Product
同时，我们可以使用SQL TOP语句中的PERCENT关键字来返回总结果集的百分比。下面的查询将返回生产表的12%。
SELECT TOP (12) PERCENT Name,ProductNumber,SafetyStockLevel FROM Production.Product
这个查询返回了61条记录，因为生产表包含504条记录，这个数字的12%（(504/100)*12=60.48）等于61。
SQL TOP语句对查询计划的影响
在日常工作中，开发人员经常在查询中使用SQL TOP语句来限制查询返回的记录数。然而，SQL TOP语句可能会影响查询优化器生成的执行计划。当我们在查询中使用TOP操作符时，查询优化器可能会对同一个查询决定不同的查询计划，而不使用TOP子句。下面的查询连接了SalesOrderHeaderEnlarged和SalesOrderDetailEnlarged表，当我们执行这个查询时，优化器将决定使用自适应连接操作。这个运算符允许优化器在执行查询时决定选择嵌套循环连接或哈希连接。查询优化器根据行数的阈值来决定连接类型，实际连接类型定义了在执行查询时使用的连接类型。
SELECT SO.AccountNumber FROM
Sales.SalesOrderHeaderEnlarged SO
INNER JOIN Sales.SalesOrderDetailEnlarged SD
ON SD.SalesOrderID = SO.SalesOrderID
正如上面所看到的，查询优化器为这个查询选择了哈希匹配连接 操作。当未排序的大量数据希望被连接时，这种连接类型被优化器所青睐。在散列匹配连接中，SQL Server在内存中建立了一个散列表，然后开始将匹配的行扫描到散列表中。现在，我们将把TOP 10表达式添加到我们的示例查询中，并重新分析其查询计划。
SELECT TOP 10 SO.AccountNumber FROM
Sales.SalesOrderHeaderEnlarged SO
INNER JOIN Sales.SalesOrderDetailEnlarged SD
ON SD.SalesOrderID = SO.SalesOrderID
我们可以在查询的执行计划中看到，优化器已经开始使用嵌套循环 ，而不是哈希连接。嵌套循环连接类型是基于一个非常简单的循环算法。外表的每条记录在内表的记录上搜索满足连接条件的记录。这种连接类型在小行数的情况下显示出良好的性能。执行计划改变背后的想法是，查询优化器知道查询将返回少量的行，因为查询中的TOP子句。因此，优化器试图寻找一个更优化的计划，以更快地获取少量的行。在这种情况下，对于这个查询来说，嵌套循环连接是获取少量行的最便宜和最快的方法，而且嵌套循环需要的资源也比较少。在这里我们需要强调一点，优化器得益于一个被称为行目标的功能，来完成这个因为TOP子句而改变的查询计划。
SQL TOP语句和行目标
SQL Server查询优化器是一个基于成本的优化器，它为一个查询生成各种执行计划备选方案，然后选择一个成本最低的执行计划。另一方面，一些查询关键字和提示限制了从查询中返回的行数，所以优化器会决定一个有效的执行计划，该计划更适合返回较少的行数。正如我们所说的，行目标被优化器应用于整个执行过程或部分执行计划，以更快地获取行。现在我们将执行并深入分析下面的查询执行计划，以更清楚地了解这一特点。
SELECT TOP 10 CarrierTrackingNumber FROM Sales.SalesOrderDetailEnlarged
WHERE ModifiedDate >='20140101'
and CarrierTrackingNumber IS NOT NULL
在这个查询中，估计的行数被设置为10，因为优化器因为SQL的TOP语句而准确地知道这个数字。
然而，这条线索并不足以理解行数目标被优化器应用到查询计划中。EstimateRowsWithoutRowGoal属性表明行目标被应用于查询计划操作者，它定义了如果优化器不使用行目标，将估计多少行。
我们可以使用DISABLE_OPTIMIZER_ROWGOAL查询提示来禁用行目标来应用查询。当我们用这个提示执行示例查询时，估计的行数会发生变化。
SELECT TOP 10 CarrierTrackingNumber FROM Sales.SalesOrderDetailEnlarged
WHERE ModifiedDate >='20140101'
and CarrierTrackingNumber IS NOT NULL
OPTION (USE HINT ('DISABLE_OPTIMIZER_ROWGOAL'));
案例研究：提高SQL TOP语句的性能
在这个案例研究中，我们将尝试改善一个表现不佳的查询实例的性能。首先，我们将启用I/O和时间统计，然后执行查询以分析其执行统计。
SET STATISTICS TIME ON
SET STATISTICS IO ON
GO
SELECT TOP 150 SO.AccountNumber FROM
Sales.SalesOrderHeader SO
INNER JOIN Sales.SalesOrderDetailEnlarged SD
ON SO.ModifiedDate = SD.ModifiedDate
在查询统计的输出中，Worktable已经执行了147.594次逻辑 ，尽管在查询中不存在任何带有WorkTable名称的表。事实上，造成这种混乱的原因是，Worktables表示临时表，在tempdb数据库上执行查询时创建。建立这些表的主要目的是为了临时存储中间结果集。现在让我们把目光转向查询计划，看看是什么导致了这个巨大的IO。在这一点上，我们将看一下查询的实际执行计划。在执行计划中，我们看到一个Table Spool (Lazy Spool)操作符，我们可以注意到它向嵌套循环操作符泵送巨大的数据。Table Spool (Lazy Spool)操作符在tempdb中创建一个临时表，当父级操作符请求一条记录时，将记录存储在这个临时表中。
同时，估计的行数和实际的行数之间存在着巨大的差异。一般来说，为了解决这种类型的问题，我们可以更新统计数据，但是对于这个问题，这种想法无助于提高查询性能。此外，表的线轴位于连接的内侧，它意味着实际的重新绑定和重绕的数量之和等于外侧连接操作（聚类索引扫描）的实际数量。
查询优化器已经将行目标方法应用于聚类索引扫描（SalesOrderDetailEnlarged）和嵌套循环操作，但是这个方法没有应用于聚类索引扫描（SalesOrderHeader）操作。特别是在这个查询中，避免使用表spool操作符可以减少查询的I/O性能，所以它有助于提高查询性能。一般来说，我们可以用嵌套循环连接来观察表 spool操作，但是我们可以强迫优化器用其他替代连接类型来改变这种连接类型。我们可以使用OPTION子句来添加一些提示，这些提示可以迫使优化器改变最优查询计划。为了摆脱表spool操作符，我们可以强迫优化器使用散列连接而不是嵌套循环连接。要做到这一点，我们将在查询的最后添加OPTION（HASH JOIN）语句。
SET STATISTICS TIME ON
SET STATISTICS IO ON
GO
SELECT TOP 150 SO.AccountNumber FROM
Sales.SalesOrderHeader SO
INNER JOIN Sales.SalesOrderDetailEnlarged SD
ON SO.ModifiedDate = SD.ModifiedDate
OPTION( HASH JOIN)
我们可以看到在强制优化器使用哈希连接后，逻辑读数的统计，和执行时间都减少了。作为一个替代方法，我们可以使用跟踪标志8690或NO_PERFORMANCE_SPOOL来禁用嵌套循环内侧的spool。
SELECT TOP 150 SO.AccountNumber FROM
Sales.SalesOrderHeader SO
INNER JOIN Sales.SalesOrderDetailEnlarged SD
ON SO.ModifiedDate = SD.ModifiedDate
OPTION(QUERYTRACEON 8690)
SELECT TOP 150 SO.AccountNumber FROM
Sales.SalesOrderHeader SO
INNER HASH JOIN Sales.SalesOrderDetailEnlarged SD
ON SO.ModifiedDate = SD.ModifiedDate
OPTION(NO_PERFORMANCE_SPOOL)
表Spools操作符是用来提高查询性能的，但有时会导致性能瓶颈。
总结
在这篇文章中，我们已经探讨了SQL TOP语句的性能细节。由于行目标功能，当查询包括TOP语句时，查询优化器可以改变执行计划。
