SQL Server中TOP子句可能导致的问题以及解决办法

TABLE TestTop

(id INT,sortkey INT,SOMEvalue CHAR(1000))

  DECLARE @i INT =1

  WHILE @i<300000

  BEGIN

  INSERT INTO TestTop VALUES(@i,@i,‘a‘)

  SET @i=@i+1

  END

  CREATE CLUSTERED INDEX PK_id ON TestTop(id)

  --test 1

  SELECT TOP(100) * FROM TestTop

  ORDER BY sortkey

  --test 2

  SELECT TOP(101) * FROM TestTop

  ORDER BY sortkey

代码清单1.测试数据与测试代码

 

    第一个测试为TOP 100，正好使用TOP N Sort的算法，第二个测试为TOP 101，只能使用普通Sort的算法，如图2所示。

图2.TOP 101的SORT需要更多内存，从而导致内存授予不足spill to tempdb

 

    我们再来看执行时间，由于spill to tempdb的存在，那么执行时间如图3所示。

图3.相差非常大的执行时间

    从图3可以看出，执行时间相差非常大。

   因此对于TOP的使用来说，尽量使用TOP 100以内的数值。

 

多表情况

    由于TOP语句带有对优化器基数估计的提示功能，因此多表查询时在极端情况下可能导致行数低估从而影响性能。

    比如下面如图4的示例查询

图4.使用TOP 1的表接连查询

 

    在这种情况下，由于TOP1的存在使得查询优化器使用1作为估计行数，与实际的行数差异巨大，因此对于这种情况，使用TOP反而可能导致成本更高（虽然我们看到图4中估计的是0%对比100%，但实际差异巨大），成本如图5所示。

图5.使用TOP反而导致性能下降

 

    对于上面这种情况来说，我们通常可以有下面集中解决办法：

1.使用提示，由于我们知道这是由于实际行数远大于估计行数导致，因此我们可以尝试使用hash join,forcescan等提示。

2.增加where条件，使得返回行数具有更高的选择性。

3.不使用TOP1，而使用TOP 10以上的数字，让估计行数变大，比如图5中的查询我们由TOP1 变为TOP10，那么执行计划则变为如图6所示。

图6.TOP 10的执行计划

 

    这是由于当行数少时，LOOP JOIN可以更快返回有限的行数，相当于对表加了FAST N提示，但行数增多时，优化器更倾向使用MERGE或者HASH完成操作，在上面返回行极多（选择性低）的极端情况下，会拥有更好的性能，结果如图7所示。

图7.特殊情况下TOP10相比TOP1有更好性能。

 

    因此结合单表的例子，推荐使用TOP关键字时，数字在10到100之间。

 

小结

    本文介绍了TOP关键字在单表和多表条件下可能对执行计划产生的影响，进而影响了查询计划。TOP影响执行计划主要是下面两个方面：

• 内存授予
• 估计行数

    因此在特殊情况下调优TOP语句时，可以根据实际情况考虑本文的建议。

SQL Server中TOP子句可能导致的问题以及解决办法

标签：