对 SQL Server 上运行缓慢的查询进行故障排除

简介

本文介绍如何处理应用程序可能会与 SQL Server 一起遇到的性能问题:特定查询或查询组的性能较低。 如果您正在对性能问题进行故障排除,但尚未将问题隔离到特定查询或执行速度比预期慢的小组查询,请参阅 监视器和优调以提高性能 ,然后再继续。

本文假定您已使用文章298475缩小了问题的范围,并已捕获了包含文章224587中详细说明的特定事件和数据列的 SQL 事件探查器跟踪。

优化数据库查询可能是一项多方面的努力。 以下各节讨论在调查查询性能时要检查的常见项目。

原始产品版本:   SQL Server
原始 KB 数:   243589

验证是否存在正确的索引

当您遇到查询执行速度缓慢时,首先要执行的一次检查是索引分析。 如果要调查单个查询,可以使用 SQL 查询分析器中的 "在数据库引擎优化顾问中分析查询" 选项。如果您有一个大型工作负荷的 SQL 事件探查器跟踪,则可以使用数据库引擎优化顾问。 这两种方法都使用 SQL Server 查询优化器来确定哪些索引对指定的查询有用。 这是一种有效的方法,用于确定数据库中是否存在正确的索引。

有关如何使用数据库引擎优化顾问的信息,请参阅 SQL Server 联机丛书中的 "开始和使用数据库引擎优化顾问" 主题。

如果已从早期版本的 SQL Server 升级了应用程序,由于优化程序和存储引擎发生变化,在新的 SQL Server 内部版本中可能会提高不同的索引效率。 数据库引擎优化顾问可帮助您确定索引策略中的更改是否会提高性能。

删除所有查询、表和联接提示

提示将替代查询优化,并可阻止查询优化器选择最快的执行计划。 由于优化器发生更改,在早期版本的 SQL Server 中改进性能的提示可能不会对后续 SQL Server 生成中的性能产生任何影响,也可能对性能产生不利影响。 此外,联接提示可能会因以下原因而导致性能下降:

  • 联接提示可防止临时查询符合自动参数化和查询计划缓存的条件。

  • 如果使用联接提示,则意味着要强制执行查询中所有表的联接顺序,即使这些联接未明确使用提示也是如此。

如果您正在分析的查询包括任何提示,请将其删除,然后再重新评估性能。

检查执行计划

确认存在正确的索引,并且没有提示来限制优化器生成有效计划的能力后,可以检查查询执行计划。 您可以使用下列任一方法来查看查询的执行计划:

  • SQL 事件探查器

    如果您在 SQL 事件探查器中捕获了 MISC:执行计划事件,则它将在 StmtCompleted 事件用于查询系统进程 ID (SPID) 之前发生。

  • SQL 查询分析器:图形显示计划

    在查询窗口中选择查询后,单击 "查询" 菜单,然后单击 "显示估计的执行计划"。

    备注

    如果存储过程或批处理创建并引用临时表,则必须使用 SET STATISTICS PROFILE ON 语句或在显示执行计划之前显式创建临时表。

  • SHOWPLAN_ALLSHOWPLAN_TEXT

    若要接收估计的执行计划的文本版本,可以使用 SET SHOWPLAN_ALL 和 SET SHOWPLAN_TEXT options。 有关详细信息,请参阅 SET SHOWPLAN_ALL (t-sql) 和 SHOWPLAN_TEXT Sql Server 联机丛书中的 (T-sql) 主题中的。

    备注

    如果存储过程或批处理创建并引用临时表,则必须使用 SET STATISTICS PROFILE ON 选项,或在显示执行计划之前显式创建临时表。

  • 统计信息配置文件

    以图形方式或通过使用显示计划显示估计的执行计划时,不会执行查询。 因此,如果在批处理或存储过程中创建临时表,则无法显示估计的执行计划,因为临时表将不存在。 统计信息配置文件首先执行查询,然后显示实际的执行计划。 有关详细信息,请参阅 SQL Server 联机丛书中的 SET STATISTICS PROFILE (t-sql) 主题。 在 SQL 查询分析器中运行时,它将以图形格式显示在 "结果" 窗格中的 "执行计划" 选项卡上。

有关如何显示估计的执行计划的详细信息,请参阅在 SQL Server 联机丛书中 显示估计的执行计划 主题。

检查显示计划输出

显示计划输出提供了有关 SQL Server 对特定查询使用的执行计划的大量信息。 下面是执行计划的一些基本方面,您可以查看它以确定是否正在使用最佳计划:

  • 正确的索引使用

    显示计划输出显示查询所涉及的每个表以及用于从该查询获取数据的访问路径。 使用图形化显示时,将指针移到表上以查看每个表的详细信息。 如果索引正在使用中,您将看到索引 Seek;[!注意] 如果未使用索引,则会为具有成簇索引的表看到堆或聚集索引扫描的表扫描。 聚集索引扫描指示表正在通过聚集索引进行扫描,而不是使用聚集索引直接访问单个行。

    如果您确定有用的索引存在且不用于查询,则可以尝试使用索引提示强制编制索引。 有关索引提示的详细信息,请参阅 SQL Server 联机丛书中的 FROM (T-sql) 主题。

  • 正确的联接顺序

    显示计划输出指示联接查询所涉及的表的顺序。 对于嵌套循环联接,列出的上部表是外部表,它应为两个表中的较小者。 对于哈希联接,上层表将成为生成输入,并且也应为两个表中的较小者。 但是,请注意,由于查询处理器发现优化器做出了错误的决策,因此如果查询处理器可以在运行时反向生成和探测输入,这种顺序不太关键。 您可以通过检查显示计划输出中的行数估计值来确定哪个表返回较少的行。

    如果您确定查询可能会从不同的联接顺序中受益,则可以尝试使用联接提示强制联接顺序。 有关联接提示的更多详细信息,请参阅 SQL Server 联机丛书中的 FROM (T-sql) 主题。

    备注

    在大型查询中使用联接提示会隐式强制查询中其他表的联接顺序,就像已设置的情况一样 FORCEPLAN

  • 正确的联接类型

    SQL Server 使用嵌套的循环、哈希和合并联接。 如果执行速度较慢的查询使用一种联接技术,则可以尝试强制使用不同的联接类型。 例如,如果查询使用的是哈希联接,则可以使用 LOOP 联接提示强制执行嵌套循环联接。 有关联接提示的更多详细信息,请参阅 SQL Server 联机丛书中的 "FROM (T-sql) " 主题。

    在大型查询中使用联接提示会隐式强制查询中其他表的联接类型,就像已设置的情况一样 FORCEPLAN

  • 并行执行

    如果使用的是多处理器计算机,您还可以调查并行计划是否正在使用。 如果正在使用并行机制,您会看到 (收集流) 事件的并行机制。 如果某个特定查询在使用并行计划时速度较慢,则可以使用 (MAXDOP 1) 提示的选项尝试强制实施非并行计划。 有关更多详细信息,请参阅 SQL Server 联机丛书中的 "选择 (T-sql) " 主题。

有关如何使用显示计划执行计划输出的详细信息,请参阅 SQL Server 联机丛书中的以下主题:

  • 以 XML 格式保存执行计划

  • 比较和分析执行计划

  • 显示计划逻辑和物理运算符引用

注意

由于查询优化器通常会为查询选择最佳执行计划,因此 Microsoft 建议您仅将联接提示、查询提示和表提示用作最后的手段,并且只有当您是有经验的数据库管理员时才这样做。