适用于: SQL Server
概要
当针对 SQL Server 实例或特定数据库应用程序的所有查询速度缓慢时,原因通常不在任何单个查询本身。 本文帮助数据库管理员和应用程序开发人员按顺序逐层分析这九个层面,从而排查 SQL Server 性能缓慢问题:应用程序、操作系统(OS)、网络、CPU、I/O、内存、阻塞、计划程序以及跟踪开销。 每个步骤都会说明要查找的症状,并提供故障排除的详细信息。 按照以下步骤可帮助你找到导致速度放缓的层,并避免在问题位于其他位置时优化单个查询。
步骤 1:排查使 SQL Server 看起来很慢的应用程序问题
检查应用程序层。 从应用程序获取查询,在 SQL Server 实例上手动运行它,并查看其运行方式。 以这种方式测试多个查询。 如果 SQL Server 实例上的查询速度更快,问题可能位于应用程序或应用程序服务器的层上。
注意
请注意数据库应用程序和 SSMS 之间的查询性能差异。
如果应用程序在不同的服务器上运行,请检查应用程序服务器的性能(有关故障排除,请参阅 步骤 2:排查导致 SQL Server 变慢的操作系统问题)。 可能需要让应用程序开发团队参与,以检查应用程序是否存在任何问题。
步骤 2:排查导致 SQL Server 变慢的操作系统问题
检查运行 SQL Server 的操作系统是否正在缓慢响应。 例如,鼠标移动缓慢,窗口长时间不响应,对服务器的远程桌面访问速度缓慢,或者连接到服务器上的共享速度较慢。
另一个服务或应用程序可能会导致此问题。 使用 性能监视器 (Perfmon) 进行故障排除。 有关详细信息,请参阅使用 性能监视器排查问题。
有关其他 OS 性能问题,请参阅 Windows Server 性能故障排除文档。
常见问题包括:
系统上运行的其他应用程序、OS 或驱动程序可能会导致此问题。
若要排查此问题,请使用任务管理器、性能监视器或资源监视器来识别问题。 有关详细信息,请参阅 高 CPU 使用率故障排除指南。
步骤 3:排查应用程序和SQL Server之间的网络问题
问题可能位于网络层中,导致应用程序与 SQL Server 之间的通信速度缓慢。 使用以下方法排查此问题:
此问题的一个症状可能是
ASYNC_NETWORK_IOSQL Server端等待。 有关详细信息,请参阅 排查由 ASYNC_NETWORK_IO 等待类型导致的慢查询。请与网络管理员协作,检查网络问题(防火墙、路由等)。
收集网络跟踪并检查网络重置和重新传输事件。 有关跟踪工具的信息,请参阅持续出现的 SQL Server 网络连接问题。 有关故障排除想法,请参阅 间歇性或定期网络问题。
启用 Perfmon 计数器以检查网络接口级别(NIC)的网络性能。 应存在零个丢弃的数据包和错误数据包。 检查网络接口带宽:
- 网络接口\已接收并丢弃的数据包
- 网络接口\接收数据包错误数
- 网络接口\出站数据包已丢弃
- 网络接口\数据包出站错误
- 网络接口\字节总数/秒
- 网络接口\当前带宽
步骤 4:排查 SQL Server 中的 CPU 使用率过高问题
如果系统上运行 CPU 密集型查询,则可能会导致其他查询因 CPU 容量不足而无法获得足够的 CPU 资源。 但是,更频繁地,来自查询的高 CPU 使用率可能表明需要优化查询。 若要排查 CPU 使用率高的问题,请按照SQL Server 中高 CPU 使用率问题的故障排除中的步骤进行操作。
步骤 5:排查 SQL Server 中导致速度缓慢的过多 I/O 问题
SQL Server 工作负荷的总体速度缓慢的另一个常见原因是 I/O 问题。 I/O 速度缓慢可能会影响系统上的大多数或所有查询。 使用以下方法排查问题:
-
- SAN 配置错误(交换机、电缆、HBA、存储)。
- 超出 I/O 容量(整个 SAN 网络不均衡,而不仅仅是后端存储,检查共享 SAN 的所有服务器的 I/O 吞吐量)。
- 驱动程序或固件问题或更新。
检查会导致大量 I/O 并使磁盘卷因 I/O 请求而饱和的次优 SQL Server 查询。
- 查找导致大量逻辑读取(或写入)的查询,并优化这些查询以最大程度地减少磁盘 I/O。 使用 适当的索引 是第一步。
- 更新统计信息,因为它们为查询优化器提供了足够的信息来选择最佳计划。
- 重新设计查询,有时还需要重新设计表,这可能有助于改进 I/O。
筛选器驱动程序:如果文件系统筛选器驱动程序处理大量 I/O 流量,SQL Server I/O 响应可能会受到严重影响。
- 从防病毒扫描中排除数据文件夹,并让软件供应商更正筛选器驱动程序问题,以防止对 I/O 性能造成影响。
其他应用程序:同一台安装了 SQL Server 的计算机上的另一个应用程序可能会因过多的读取或写入请求而使 I/O 路径饱和。 这种情况可能会使 I/O 子系统超出容量限制,并导致 SQL Server I/O 速度缓慢。 确定应用程序并对其进行优化或将其移动到其他位置以消除其在 I/O 堆栈上的影响。
此问题也可能是由在其他计算机上运行、但与此 SQL Server 计算机共享同一 SAN 的应用程序引起的。 与 SAN 管理员合作,平衡 I/O 流量(请参阅 检查硬件问题)。
有关 SQL Server 与 I/O 相关问题的详细故障排除,请参阅 排查 I/O 问题导致的 SQL Server 性能缓慢问题。
步骤 6:排查导致SQL Server速度缓慢的内存问题
当查询正在等待内存授予(RESOURCE_SEMAPHORE)或编译内存(RESOURCE_SEMAPHORE_QUERY_COMPILE)时,系统整体或 SQL Server 内部的内存不足可能会导致变慢。 有关内存授予等待的详细信息,请参阅排查SQL Server中内存授予导致的性能缓慢或内存不足问题。
使用以下方法排查问题:
使用 Perfmon 计数器检查 OS 级别的提交内存:
- Memory\Available MBytes
- Process(*)\Working Set (所有实例)
- Process(*)\\Private Bytes (所有实例)
对于内部内存压力,请使用 SQL Server 查询来查询 sys.dm_os_memory_clerks 或使用 DBCC MEMORYSTATUS。
检查 SQL Server 错误日志中是否存在 701 错误。
有关详细故障排除步骤,请参阅 排查 SQL Server 中的内存不足或内存不足问题。
步骤 7:排查导致所有查询等待的阻止问题
获取锁可保护数据库系统中的资源。 如果会话长时间保留锁,而其他会话等待这些锁,则就会出现阻塞情况。
在 SQL Server 等数据库系统中,经常会发生短暂阻塞。 但是,长时间阻塞,尤其是大多数或所有查询等待锁时,可能会导致整个服务器被视为无响应。
使用以下步骤来排查该问题:
通过查看 sys.dm_exec_requests DMV 输出中的
blocking_session_id列或sp_who2存储过程输出中的BlkBy列来确定头阻止会话。查找头部阻塞链执行的查询(长时间持有锁)。
如果未在头阻塞会话上主动运行任何查询,则由于应用程序问题,可能存在孤立事务。
重新设计或优化头阻塞查询以更快地运行,或减少事务中的查询数。
检查查询中使用的事务隔离并对其进行调整。
有关阻塞场景的详细故障排除,请参阅 了解和解决 SQL Server 阻塞问题。
步骤 8:排查计划程序问题(无响应、死锁计划程序、无响应的 IOCP 侦听器、资源监视器)
SQL Server 使用协作式调度机制(调度程序)向 OS 公开其线程,以便由操作系统在 CPU 上调度。 如果存在与 SQL 调度程序相关的问题,SQL Server 线程可能会停止处理查询、登录、注销等。 因此,SQL Server 似乎无响应(部分或完全),具体取决于受影响的调度程序数。
计划程序问题可能由各种问题引起,包括产品 bug、外部驱动程序和筛选器驱动程序以及硬件问题。 按照以下步骤排查这些问题。
识别SQL Server错误日志中的计划程序错误
在报告的 SQL Server 无响应期间,请检查 SQL Server 错误日志中是否存在如下错误:
-
*********************************************** * * BEGIN STACK DUMP: * 03/10/22 21:16:35 spid 22548 * * Non-yielding Scheduler * *********************************************** -
********************************************** * * BEGIN STACK DUMP: * 03/25/22 08:50:29 spid 355 * * Deadlocked Schedulers * * ******************************************** -
* ******************************************************************************* * * BEGIN STACK DUMP: * 09/07/22 23:01:04 spid 0 * * Non-yielding IOCP Listener * * ******************************************************************************* -
* ******************************************** * * BEGIN STACK DUMP: * 07/25/22 11:44:21 spid 2013 * * Non-yielding Resource Monitor * * ********************************************
解决计划程序错误
如果找到上一部分所述的错误之一,请执行以下步骤:
确定正在使用的SQL Server的累积更新 (CU) 版本。 检查当前 CU 之后的 CU 中是否存在任何已修复的问题。 有关 SQL Server 修补程序,请参阅 当前支持的 SQL Server 版本的可用最新更新。 有关详细的修补程序列表,可以下载此 Excel 文件。
有关更多想法,请参阅SQL Server 调度和让出疑难解答。
检查可能导致发生死锁的调度程序的大量阻塞情况或大规模并行查询。 有关详细信息,请参阅 死锁调度程序之道。
对于非让出 IOCP 侦听器,请检查系统是否内存不足,并且 SQL Server 被换出到分页文件中。另一个原因是防病毒或入侵防护软件截获 I/O API 调用并降低线程活动速度。 有关详细信息,请参阅 IOCP 侦听器是否确实正在侦听? 以及 某些模块或筛选器驱动程序加载时的性能和一致性问题。
对于资源监视器相关问题,在某些情况下,您可能不必担心这个问题。 有关详细信息,请参阅 运行 SQL Server 的服务器上的资源监视器进入非让出状态。
如果这些资源没有帮助,请向 Microsoft 提交支持工单,并上传在 \LOG 子目录中创建的内存转储以供分析。
步骤 9:查找资源密集型 Profiler 或扩展事件跟踪
查找活动扩展事件或 SQL Server Profiler 跟踪,尤其是那些筛选文本列(数据库名称、登录名、查询文本等)的跟踪。 如果可能,请禁用跟踪,并查看查询性能是否提高。 根据所选事件,每个线程可能会消耗额外的 CPU,从而导致整体速度缓慢。
若要识别扩展事件的活动跟踪会话,请参阅 sys.dm_xe_sessions。 有关 Profiler 跟踪,请参阅 sys.traces。
SELECT * FROM sys.dm_xe_sessions
GO
SELECT * FROM sys.traces
注意
SQL Server Profiler 和 SQL 跟踪已弃用。 请改用 扩展事件 。