合 SQL Server中的所有等待事件类型说明

简介

SQL Server数据库接收到查询请求，从生成计划到执行计划的过程，等待次数和等待时间在一定程度上揭示了系统性能的压力，如果资源严重不足，就会成为性能的瓶颈。因此，对等待的监控非常有助于对系统性能进行诊断，对查询语句进行性能调优。偶尔一次的异常等待，不足以表明系统存在瓶颈，但是，SQL Server实例经常出现特定的等待类型，并且等待时间趋于增加，这就说明，系统存在压力，或内存，或IO等，根据WaitType对系统进行监控和诊断，还能对查询进行性能调优，例如，Lock等待表明执行查询存在数据竞争，PageIOLatch等待表明IO响应缓慢，PageLatch等待表明文件的布局需要改进等。

当辅助角色请求访问因资源正被其他某个辅助角色使用或尚不可用而不可用的资源时，将发生资源等待。 资源等待的示例包括锁、闩锁、网络和磁盘 I/O 等待。 锁等待和闩锁等待是指等待同步对象。

当辅助角色处于空闲状态并等待分配工作时，将发生队列等待。 队列等待通常发生在系统后台任务（如监视死锁以及清除已删除的记录等任务）中。 这些任务将等待工作请求被放入工作队列。 即使没有新数据包放入队列，队列等待也可能定期处于活动状态。

当SQL Server辅助角色正在等待外部事件（如扩展存储过程调用或链接服务器查询）完成时，会发生外部等待。 诊断阻塞问题时，请记住，外部等待并不总是意味着辅助角色处于空闲状态，因为辅助角色可能正在主动运行某些外部代码。

此动态管理视图显示已完成的等待的时间。 此动态管理视图不显示当前等待。

如果存在以下任一条件，则不会将SQL Server工作线程视为正在等待：

• 资源变得可用。
• 查询非空。
• 外部进程完成。

尽管线程不再等待，但线程不必立即开始运行。 这是因为此类线程首先放入可运行工作线程的队列中，并且必须等待量程在计划程序中运行。

查看等待信息

本文分享常用的等待类型及其产生的原因，通常使用DMV来查看等待：

• sys.dm_exec_requests 来查看系统当前正在处理的请求，
• sys.dm_os_wait_stats 统计当前系统发生过的等待的信息
• sys.dm_os_waiting_tasks 查看当前正处于等待状态的task

1，等待信息统计

SQL Server 保存了从上一次服务器启动或者手动清空之后累计的等待信息，总的来说，等待类型分为三类：资源等待，队列等待和外部等待，在日常使用中，通常会过滤掉系统相关的等待类型，因为这些等待对诊断性能瓶颈没有多大用处，同时还过滤掉等待时间为0的类型，脚本见文末的附言。

2，清空等待信息统计

对于生成环境，如果需要收集等待信息，那么最好把等待信息清0，然后重新开始计数，通常使用DBCC SQPERF()命令来实现：

所有类型

• 并行CXPACKET ：发生在多任务系统中，用于表示并发执行的子线程在等待其他子线程的完成。如果针对该等待类型的争用成为问题时，可以考虑降低并行度。若系统主机的CPU持续在99%以上，则说明系统并发度过高，或系统的CPU有瓶颈了。

• I/O：ASYNC_IO_COMPITION或IO_COMPITION ，IO瓶颈的潜在指标

• IO_COMPLETION ：　在等待 I/O 操作完成时出现。通常，该等待类型表示非数据页 I/O。例如，会话被kill掉时，可能会发生该等待。IO_COMPLETION 这种等待类型表示数据文件中的各种同步读和写操作，这些操作与表无关，并且从事务日志中读取。在等待I/O操作完成时发生。这种等待类型通常表示非数据页I/O。IO_COMPLETION与PAGEIOLATCH_XXX的最大的区别是就在于IO_COMPLETION是非数据页的等待，而PAGEIOLATCH_XXX是数据页的IO等待

• Buffer：PAGEIOLATCHXXX ，用于描述数据页的IO争用，说明系统正在从硬盘加载数据到内存的Buffer Pool中。当你的系统出现大量的 PAGEIOLATCH 类等待，说明你磁盘可能存在压力（磁盘速度不能满足当前业务需求）或你的内存不够用，不能缓存业务常用数据而经常要与磁盘交互！

  若大量SQL等待同一个表，且等待PAGEIOLATCH_SH事件，则表示该表索引碎片可能很严重 ，需要重建索引。

• PAGELATCH_XXX：

• 非Buffer：LATCH_XXXX

• 锁阻塞：LCK_*

• CPU：SOS_SCHEDULER_YIELD，当前任务正在执行，但是其主动退让调度程序上的时间片（time slicer），供其他进程使用，自己在后台执行任务。

• THREADPOOL：查询正在等待可用的Worker线程，可能出现了大量的并行计划，以至于用完了系统可用的Worker。

• DISPATCHER_QUEUE_SEMAPHORE：发生当一个进程（Thread）等待处理更多的Work时，也就是说，一个Thread处于空闲状态，等待调度去工作。如果等待时间增加，说明调度器（Dispatcher）非常空闲；该WaitType不会成为竞争资源，而将其他事务阻塞，在做Wait统计分析，可以过滤掉。

• 日志：WRITELOG 或 LOGBUFFER ， 说明 数据库的写入量比较大。WRITELOG 和磁盘有关的另一个等待状态，正在等待写日志记录，意味着写入速度也明显跟不上。而速度跟不上一般有两种情况：磁盘压力大响应时间长或真的速度不能满足读写需要。

• WRITELOG :等待日志刷新完成时出现。导致日志刷新的常见操作是检查点和事务提交。若是2014版本，则可以考虑配置延迟事务持久性提交特性。

• 内存不足：RESOURCE_SEMAPHORE

• 外部：PREEMPTIVE_XXX

• 备份：DISKIO_SUSPENDE

• 等待事件： HADR_SYNC_COMMIT

• LOG_MANAGER：日志太大导致。根据DMV查看资源等待情况，发现大量LATCH_EX，等待资源则是LOG_MANAGER。检查日志发现已经是超过900G，系统初始化时已经使用了256个日志文件，总共64G日志，感觉日志部分可能不受控制，所以把数据库日志还原模型改成SIMPLE，立刻问题解决了。分析原因应该是日志写满后，SQL Server向操作系统申请更多磁盘空间造成的磁盘IO，期间造成了写日志等待。

• ASYNC_NETWORK_IO： 当任务被阻止在网络之后时出现在网络写入中。验证客户端是否正在处理来自服务器的数据。

说明： 实时备库可能有很耗CPU的sql

等待事件： LATCH_EX [ACCESS_METHODS_DATASET_PARENT]

说明：查询没有索引，需要回表查数据

LATCH_EX：Exclusive Latch，表明数据正在被改变或添加。两个EX Latch不能同时保留在用一个页上

查看sys.dm_os_waitstats的内容，你会看到LATCH, PAGELATCH_ 和PAGEIOLATCH_打头的Latch，

​ 其中LATCH_打头的等待都是用于Non-BUF类型，

​ PAGELATCH_打头的Latch应用于各种页，

​ PAGEIOLATCH_打头的Latch用于数据从磁盘移到RAM。

下表列出各任务所遇到的等待类型。

以下 XEvents 与分区 SWITCH 和联机索引重新生成相关。 有关语法的信息，请参阅 ALTER TABLE (Transact-SQL) 和 ALTER INDEX (Transact-SQL) 。

• lock_request_priority_state
• process_killed_by_abort_blockers
• ddl_with_wait_at_low_priority

有关锁兼容性矩阵，请参阅 sys.dm_tran_locks (Transact-SQL) 。

等待事件SOS_SCHEDULER_YIELD

和CPU有关，参考：https://www.dbaup.com/sql-serverzhongdedengdaishijiansos_scheduler_yieldshuoming.html

等待事件MISCELLANEOUS

在 SQL Server 中，等待事件“MISCELLANEOUS”表示当前连接正在等待某个未知的或未被识别的事件。这通常是一种不常见的等待事件，通常出现在以下情况下：

• 空闲等待：在等待需要输入命令，例如事务未提交
• 系统资源不足：如果系统资源（如CPU、内存、磁盘IO等）不足，可能会导致连接出现MISCELLANEOUS等待事件，因为连接需要等待更多资源才能继续执行。
• 等待操作系统资源：如果连接需要等待操作系统资源（如文件系统、网络等）时，可能会导致出现MISCELLANEOUS等待事件。
• 第三方组件问题：某些第三方组件或扩展可能会导致MISCELLANEOUS等待事件的出现，因为它们可能会引入未知或未被识别的等待事件。

如果在SQL Server中发现了MISCELLANEOUS等待事件，建议先检查系统资源是否足够，并尝试识别是否有其他等待事件或性能瓶颈问题。如果问题持续存在，可能需要考虑使用SQL Server Profiler或Extended Events等工具进行详细的诊断和调试。

"MISCELLANEOUS" 是 SQL Server 中的一种等待事件，通常表示未分类的等待事件。这个等待事件通常是一个泛指，用于捕获不属于其他明确等待事件类型的等待情况。在性能监视或诊断中，"MISCELLANEOUS" 等待事件可能会出现，但它并不提供有关性能问题的具体信息。

要更详细地了解 "MISCELLANEOUS" 等待事件的性质以及可能的解决方法，您需要进一步分析等待事件的上下文和其他相关性能计数器。以下是一些可能的步骤：

1. 查看等待事件的上下文：可以通过查询 sys.dm_os_waiting_tasks 等系统动态管理视图来查看正在等待 "MISCELLANEOUS" 事件的会话和任务的详细信息。这可能会提供更多关于等待事件的上下文信息。

   select * from master.dbo.sysprocesses a where a.lastwaittype='MISCELLANEOUS'; SELECT * FROM sys.dm_os_waiting_tasks WHERE wait_type = 'MISCELLANEOUS';

   1 2 3

   select * from master.dbo.sysprocesses a where a.lastwaittype='MISCELLANEOUS';   SELECT * FROM sys.dm_os_waiting_tasks WHERE wait_type = 'MISCELLANEOUS';

2. 查看其他性能计数器：查看其他性能计数器，例如 CPU 使用率、内存使用率、磁盘 I/O 等，以查看是否存在其他性能问题或瓶颈。"MISCELLANEOUS" 事件可能是其他问题的副产品。

3. 审查最近的操作：检查会话的最近操作，以确定是否有长时间运行的查询或操作，可能会导致等待事件。使用 SQL Server 的查询执行计划来分析查询性能。

4. 检查活动进程：查看当前正在运行的会话和查询，以确定是否有会话占用了太多资源或执行时间过长。

5. 审查错误日志：检查 SQL Server 错误日志，以查看是否有与 "MISCELLANEOUS" 事件相关的错误消息或异常。

6. 考虑升级或修补程序：有时，"MISCELLANEOUS" 事件可能会由 SQL Server 版本或修补程序的问题引起。确保您的 SQL Server 实例是最新的，并应用了相关的修补程序。

7. 使用性能监视工具：使用性能监视工具如 SQL Server Profiler 或 Extended Events 来跟踪和分析系统活动，以识别问题的根本原因。

最终，要解决 "MISCELLANEOUS" 等待事件，您需要深入分析具体的等待情况和性能指标，以确定导致等待的根本原因，并采取适当的措施来解决性能问题。

等待事件CXPACKET

CXPACKET 这个等待可以简单理解成CPU相关的等待，主要发生在并行计划中。由于并行计划需要协同多个task同时工作，那么“协同”分配等等操作的时候出现的就是这个等待。如果 CXPACKET 在你系统中是最为严重的等待，这时候一般的表现是你的CPU很高。

在 SQL Server 中，CXPACKET 表示并行查询的一种类型，它是一种等待状态，表示某个查询的线程正在等待其他并行线程完成工作。通常，CXPACKET 等待的时间很短，但是如果等待时间过长，则可能会导致性能问题。

在一个并行查询中，SQL Server 会将查询分成多个任务，并将这些任务分配给不同的处理器或核心执行。每个任务将在一个独立的线程中执行，并在完成后将结果合并。CXPACKET 等待状态表示某个线程已经完成了其任务，并正在等待其他线程完成其任务以进行结果合并。

如果出现 CXPACKET 等待状态时间过长的情况，可能是由于以下原因导致的：

1. 并行度设置不当：如果将并行度设置得太高或太低，都可能导致 CXPACKET 等待状态的出现。如果并行度设置得太高，可能会导致 CPU 过度使用，而如果并行度设置得太低，则可能会导致查询速度变慢。
2. 不均衡的并行查询：如果某个并行任务的工作量过大，可能会导致其他任务无法及时完成其工作，从而导致 CXPACKET 等待状态的出现。
3. 锁竞争：如果多个并行线程访问同一数据页或索引，可能会导致锁竞争，从而导致 CXPACKET 等待状态的出现。

为了解决 CXPACKET 等待状态问题，可以采取以下措施：

1. 调整并行度设置：根据系统资源和查询性质，适当调整并行度设置，避免过度或不足的使用 CPU。
2. 优化查询计划：通过优化查询计划，避免不均衡的并行查询或锁竞争问题。
3. 加速 IO 操作：通过增加磁盘读取速度或使用更快的存储介质，减少 IO 操作等待时间，从而降低 CXPACKET 等待状态的出现。

总结
1、Server 在执行查询时如果使用并行(parallel)，这通常会比在序列化进程(serialized process)中执行的查询要快。使用并行计划时，查询在多个线程中执行，只有在所有并行线程都完成后才能继续查询。这意味着整个查询的快慢取决于最慢的那个线程。并行时SQL Server将一个线程指定为调度线程，这个调度的线程会将并行工作负载的一部分分配给工作线程，并行性就像生产者(工作线程CXPACKET)将数据推送给消费者(调度线程CXCONSUMER)，消费者可能不得不等待生产者的数据。因此，需要注意生产者等待，而消费者等待是不可避免的，因为生产者运行时间较长，则消费者等待是被动的结果。当出现CXPACKET等待，表示工作线程CXPACKET出现性能问题，调度线程CXCONSUMER等待工作线程CXPACKET提供数据；当经常出现CXCONSUMER等待时，就表示调度线程CXCONSUMER出现性能问题，调度线程CXCONSUMER无法及时把数据处理完成。

2、SQL Server为了加快执行速度，当语句消耗超过Cost Threshold for Parallelism设置时就会采用多线程并行的方式进行，每个线程处理一部分数据，最后再进行结果合并，在等待一个或多个线程返回结果时就会产生CXPACKET、CXCONSUMER等待。对于复杂的SQL语句，并行的方式可以大幅的降低执行时间。如果简单的的SQL语句也出现CXPACKET、CXCONSUMER等待。那么在高并发的情况下，这样的简单语句也会产生严重的性能问题。

3、Cost Threshold for Parallelism 值以秒为单位，这意味着对于 SQL Server 估计运行时间将超过 5 秒的每个查询，将创建一个并行计划。

4、通过查询sys.dm_exec_session_wait_stats可以看到某个会话中哪个等待消耗多少时间

5、如果select和DML语句使用了并行并导致严重等待，那么思路就是去检查执行计划，如果DDL语句使用了并行并导致了严重等待，那么可能要考虑增加内存和cpu资源。

另请参阅

• 与 SQL Server 操作系统相关的动态管理视图 (Transact-SQL)
• sys.dm_exec_session_wait_stats (Transact-SQL)
• sys.dm_db_wait_stats（Azure SQL 数据库）
• sys.dm_os_sys_info (Transact-SQL)

WRITELOG（写事务日志到硬盘）

和Disk的写速度有关，说明任务当前正在等待将日志记录写入日志文件，出现这个等待状态，意味着Disk的写入速度是性能瓶颈。

若是2014版本，则可以考虑配置延迟事务持久性提交特性。

等待事件RESOURCE_SEMAPHORE和RESOURCE_SEMAHPORE_QUERY_COMPILE说明

参考：https://www.dbaup.com/mssqlzhongdedengdaishijianresource_semaphoreheresource_semahpore_query_compileshuoming.html

视图sys.dm_os_wait_stats

返回执行的线程所遇到的所有等待的相关信息。 可以使用此聚合视图来诊断SQL Server以及特定查询和批处理的性能问题。 sys.dm_exec_session_wait_stats (Transact-SQL) 按会话提供类似的信息。

备注

若要从 Azure Synapse Analytics 或 Analytics Platform System (PDW) 调用此功能，请使用名称 sys.dm_pdw_nodes_os_wait_stats。 Azure Synapse Analytics 中的无服务器 SQL 池不支持此语法。

在SQL Server等待时间计数器是 bigint 值，因此不像早期版本的 SQL Server 中的等效计数器那样容易发生计数器滚动更新。

执行查询期间的特定等待时间类型可以说明查询中存在瓶颈或失效点。 同样，如果服务器级的等待时间较长或等待计数较多，说明服务器实例内交互查询交互中存在瓶颈或热点。 例如，锁等待指示查询争用数据；页 IO 闩锁等待指示 IO 响应时间较慢；页闩锁更新指示表示文件布局不正确。

清空当前等待事件统计

可以重置此动态管理视图的内容。 此 T-SQL 命令将所有计数器重置为 0：

备注：

这些统计信息在数据库引擎重启后不会保留，并且自上次重置统计信息或数据库引擎启动以来，所有数据都是累积的。 使用 sys.dm_os_sys_info 中的 sqlserver_start_time 列查找上次数据库引擎启动时间。

权限

对于 SQL Server 和 SQL 托管实例，需要 VIEW SERVER STATE 权限。

在 SQL 数据库“基本”、“S0”和“S1”服务目标中，对于“弹性池”中的数据库，服务器管理员帐户、Azure Active Directory 管理员帐户或##MS_ServerStateReader##服务器角色中的成员身份是必需的。 对于所有其他 SQL 数据库服务目标，需要数据库的 VIEW DATABASE STATE 权限或 ##MS_ServerStateReader## 服务器角色中的成员身份。

IO相关的等待

IO相关的等待与IO资源有关，比如，备份和还原等待的等待类型是：BACKUPIO/BACKUPBUFFER，这类等待发生在备份任务正在等待所需的数据或者数据缓存时。

　　IO_COMPLETION 和 PAGEIOLATCH_和 WRITELOG 这三个等待是最为常见的和磁盘相关的等待。

　　他们的不同点是 　

　　　　IO_COMPLETION 主要针对非数据页 I/O ，如备份操作所需的磁盘交互。

　　　　PAGEIOLATCH_XXX 是数据页相关的磁盘等待。

　　　　WRITELOG 是日志相关。

　　如果系统中这三个等待是主要等待，说明系统磁盘存在压力或已经成为瓶颈。

PAGEIOLATCH_XXX

PAGEIOLATCH_的 官方解释：在任务等待 I/O 请求中缓冲区的闩锁时发生。闩锁请求处于“XX”模式。长时间的等待可能指示磁盘子系统出现问题。

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

验证码：

获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。