如何调优SQL Server性能不好并且设置返回行数的SQL语句?

在设置返回行数或者使用Top关键字后,某些SQL语句会运行的非常慢。设置返回行数和使用Top关键字会告诉SQL Server从SQL语句中选择特定行数而不是提取所有记录。没有多少人知道在设置返回行数或者使用Top关键字后SQL Server会尝试重新优化您的SQL语句,重新优化后的SQL语句产生的执行计划通常可以更快的检索前几条记录,但是也可能适得其反。

设置返回行数重新优化查询的好示例
下面是一个示例,显示了该SQL从数据库中检索217500行记录需要6.78秒。这是一个好的执行计划,因为它对[DEPARTMENT]和[EMPLOYEE]的两个表扫描进行哈希匹配。

下面屏幕显示了设置返回行数为1后新的执行计划把哈希匹配改为嵌套循环。嵌套循环操作通常提供更快的前几条记录检索时间,但在某些情况下可能不利于整体记录的提取。很高兴看到SQL Server提取此SQL的第一行记录仅用了0.013秒。

设置返回行数重新优化查询的坏示例
让我们来看一个坏示例,它展示了在使用设置返回行数为1后,SQL Server如何将一个好的执行计划变成一个糟糕的执行计划。下面一个示例显示,该SQL从数据库中检索1613行记录花费了0.118秒,虽然该执行计划有点复杂,但对于检索完所有行来说是一个很好的执行计划。

下面屏幕显示了设置返回行数为1后生成的新执行计划,该执行计划现在更改为带有两次表扫描的嵌套循环。新的执行计划提取第一条记录需要1.312秒,比从数据库提取所有行所用的0.118秒还要慢。

如何解决这个问题呢?
我们可以使用提示注入或者SQL语法改写来影响SQL Server为设置返回行数或者Top关键字操作改回原始的执行计划或者生成更好的执行计划。下面是提示注入生成的一个很好的执行计划,它比设置返回行数为1的原始SQL快了90倍。

Tosska SQL Tuning Expert (TSES™) for SQL Server® – Tosska Technologies Limited