SQL Server SOS_SCHEDULER_YIELD等待事件说明及案例分析

文章目录

概要
理解 SOS_SCHEDULER_YIELD 等待类型
- SQL Server 的调度器架构：
- SQL OS scheduler组件
- SQL OS Scheduler状态
- 调度器工作原理
高 SOS_SCHEDULER_YIELD 的原因
案例
- 压测报告
- 查看调度器运行状况
- 查看调度器关联的cpu状况
- 查看cpu分配情况
- 根因
- 结论

概要

SQL Server 的 SOS_SCHEDULER_YIELD 等待类型是一种比较常见的等待类型，通常表明以下两种情况之一：

SQL Server 的 CPU 调度器被充分利用并高效运行
表示当前系统中的 CPU 资源分配合理，SQL Server 调度器按照预期的方式处理任务，没有出现资源瓶颈或调度问题。
CPU 存在压力
表示当前系统中 CPU 的负载接近或超过其处理能力，SQL Server 的任务调度受到限制，可能需要优化查询、调整服务器资源，或分析是否存在不必要的资源消耗。
虽然被称为wait type(等待类型），但 SOS_SCHEDULER_YIELD 并不完全像其他等待类型那样简单。这种等待类型经常因为其普遍性而被误解。为便于理解，以下是 Microsoft 官方文档对它的定义：

“当任务主动让出调度器以便其他任务执行时发生。在此等待期间，任务正在等待其时间片被重新分配。”

理解 SOS_SCHEDULER_YIELD 等待类型

为了明白它的意义、何时出现以及为何出现，我们需要更好地了解 SQL 调度器的设计及其功能。

SQL Server 的调度器架构：

SQL Server 的 SQL OS（通过存储引擎）独立负责线程调度，而非依赖 Windows 操作系统。
SQL OS 为每个 CPU 核心分配一个调度器，用于管理线程的执行。
用户的所有请求都会通过这些调度器安排到对应的 CPU 核心上执行。
例如：
如果 SQL Server 部署在一台带有 2 个八核处理器的服务器上，SQL OS 会为该实例创建 16 个调度器（对应每个 CPU 核心）。
SOS_SCHEDULER_YIELD 的出现机制：
SQL Server 任务主动让出 CPU，表示任务已用完其分配的 CPU 时间片（通常是 4ms 左右）。
让出的任务会被暂时挂起，直到其分配的时间片被重新激活。
在这里插入图片描述

SQL OS scheduler组件

SQL OS scheduler或 SOS schduler）由三个组件组成：

Processor：指物理或逻辑的 CPU/CPU 核心，负责以一次一个线程的速率处理线程。
Waiter List：
解释：Waiter List 存储处于挂起状态的线程，这些线程需要等待资源可用。
特性：Waiter List 对线程停留时间没有限制，也无法通过设置参数来限制线程在其中的等待时间。然而，如果查询执行会话中指定了timeout(超时时间)，则优先遵循超时时间。若达到超时，线程可能会被取消执行。
Runnable Queue：
解释：Runnable Queue 严格遵循**先进先出（FIFO）**顺序。线程从 Waiter List 或 Processor 转移到 Runnable Queue 时，会被放在队列的末尾。
特殊情况：如果启用了 Resource Governor（资源管理器），FIFO 顺序可以被覆盖。启用后，可为工作负载组分配不同的资源池优先级（高、中、低）。拥有更高优先级的线程可以覆盖较低优先级的线程在 Runnable Queue 中的位置。
备注：Resource Governor 是特殊场景，实际应用中并不常见。
SQL Server 中线程管理和调度的机制，Processor 负责线程的执行，而 Waiter List 和 Runnable Queue 则分别管理线程的等待和运行队列。

SQL OS Scheduler状态

线程在调度器中可以处于以下三种状态之一：

RUNNING（运行中）
定义：线程正在处理器核心上运行。
限制：每个核心同时只能有一个线程处于活动状态并运行。
SUSPENDED（挂起）
定义：当线程请求不可用的资源时，会被移动到 Waiter List（等待列表）。
状态保持：线程将在 Waiter List 中停留，直到所需资源变为可用。
RUNNABLE（可运行）
定义：线程已获得所需资源，但被移动到 Runnable Queue（运行队列）中，等待 CPU 核心变得可用后执行。

调度器工作原理

接下来，我们将介绍两种场景来说明调度器的工作原理，逐一进行解释。
这种状态模型体现了 SQL Server 调度器如何在有限资源下管理并发线程。

线程运行三种状态直到完成用户请求
运行中的线程尝试访问所需资源时，如果该资源无法立即获取，线程将变为 SUSPENDED（挂起状态），并被移动到 Waiter List（等待列表）。线程会一直处于挂起状态，直到所请求的资源可用。请注意，线程在等待列表中没有时间限制，也没有线程数量限制。

当资源变得可用时，线程将从等待列表中移至 Runnable Queue（可运行队列），在那里等待处理器变得可用。一旦处理器可用，线程将完成请求，然后进入 SLEEP（休眠状态）。如果线程再次需要等待资源，则会重复这一序列。
在这里插入图片描述
上图中：

SPID 53 需要一个当前不可立即获取的资源，因此它将被移入 Waiter List（等待列表），并标记为相应的等待类型。
SPID 72 的资源已经可用，因此它将从 Waiter List 移动到 Runnable Queue（可运行队列）的最后一个位置。
SPID 51 进入 RUNNING（运行）状态，因为现在轮到它，且 CPU 资源已可用。
线程运行两种状态直到完成用户请求
每个线程都有一个由 SQL OS 分配的固定时间片（即线程在无中断的情况下使用 CPU 的时间），时间为 4 毫秒，且无法更改。每个线程需通过 SQL OS 提供的辅助例程来评估其分配的时间片是否已用完。如果已用完，线程会自愿让出 CPU 时间，允许下一个线程使用 CPU。在这种情况下，让出的线程不会被移入 Waiter List（等待列表），因为它所需的资源实际是可用的，且无需等待其他资源。因此，它会被直接移到其调度程序的 Runnable Queue（可运行队列）底部。

在这种情况下，SQL OS 会将线程从 Running State（运行状态）移至 Runnable Queue（可运行状态），并记录为 SOS_SCHEDULER_YIELD 等待类型。由于无需等待任何资源（线程不等待任何资源，只是自愿进入可运行队列），SOS_SCHEDULER_YIELD 的资源等待时间为 0 毫秒。此过程会产生信号等待时间（Signal Wait Time），显示线程Runnable Queue中等待的时间，即线程重新进入运行状态所需的时间。
在这里插入图片描述在上述示例中：
SPID 53 已耗尽其分配的时间片（4 毫秒），并正在过渡到 Runnable Queue（可运行队列）。
SPID 51 正在进入 Running State（运行状态），因为 SPID 53 自愿移至 Runnable Queue 后，处理器资源已变得可用。

高 SOS_SCHEDULER_YIELD 的原因

正如前面的示例所示，SOS_SCHEDULER_YIELD 是一种常见的等待类型，它实际上表明线程已经耗尽了其分配的时间片。如果 SOS_SCHEDULER_YIELD 是主要的等待类型，这可能表明 CPU 存在压力问题，但这并不一定意味着 CPU 无法处理用户请求。

经验较少的管理员常常尝试通过将 MAXDOP 设置为 1 来解决过多的 CXPACKET 等待，或者将 CTFP 数值设置得过高，从而导致高 SOS_SCHEDULER_YIELD 等待类型值。这种不恰当的排障方式直接导致了问题。一个常见但错误的看法是，高 SOS_SCHEDULER_YIELD 值表明 CPU 是瓶颈，需要更多的 CPU 资源。然而，高 SOS_SCHEDULER_YIELD 等待类型值实际上表明导致此问题的query语句本身需要更多的 CPU 资源，因为这些额外CPU资源可以使query更快完成。
在这里插入图片描述
上图示例，说明单线程查询如何总是使用一个 CPU 核心。虽然该核心被 100% 占用，导致查询执行中出现瓶颈，并产生较高的 SOS_SCHEDULER_YIELD 等待类型值，但其余的七个核心几乎处于空闲状态。这个例子清楚地表明，CPU 并不是瓶颈，也不是高 SOS_SCHEDULER_YIELD 等待的根本原因，因为显然有大量的 CPU 资源可用。实际上，这是由于查询需要更多的 CPU 计算能力，但它只能使用单个核心进行执行的限制。如果查询可以在 6 个核心上并行运行，那么执行速度可能会提高 6 倍，因此这是 SQL Server 在优化方面表现不佳的案例。

Spinlock 通常被认为是导致 SOS_SCHEDULER_YIELD 等待值过高的原因之一，但这种说法实际上是错误的。如果希望深入了解为什么 Spinlock 与大量 SOS_SCHEDULER_YIELD 等待无关，请参阅此文章：SOS_SCHEDULER_YIELD waits and the LOCK_HASH spinlock。

因此，当高 SOS_SCHEDULER_YIELD 等待类型值出现时，应该根据如下三种不同的场景去排查，主要取决于 SOS_SCHEDULER_YIELD 是否频繁出现，以及它是否伴随较高的信号等待时间。

SOS_SCHEDULER_YIELD 频繁出现但信号等待时间(signal wait time)较低
这是一个典型场景，其中 SOS_SCHEDULER_YIELD 等待并非问题的指标。这仅表明有许多线程正在调度器上运行，并且所有线程的优先级相同，没有任何线程占据 CPU 的主导地位。在这种情况下，性能问题的根源应从其他地方排查。

SOS_SCHEDULER_YIELD 频繁出现但信号等待时间较低
这是一个典型场景，其中 SOS_SCHEDULER_YIELD 等待并非问题的指标。这仅表明有许多线程正在调度器上运行，并且所有线程的优先级相同，没有任何线程占据 CPU 的主导地位。在这种情况下，性能问题的根源应从其他地方排查。

SOS_SCHEDULER_YIELD 频繁出现且信号等待时间较高
在这种情况下，CPU 资源紧张的可能性较高。这可能表明大量 CPU 密集型查询正在争夺 CPU 资源，因此频繁出现 SOS_SCHEDULER_YIELD 等待。较高的等待时间说明被移到可运行队列中的线程需要等待较长时间，才能被调度器恢复到运行状态。
通常，这种情况需要对query进行优化，因为问题的根源在于query本身。
检查执行计划中是否存在以下可能导致问题的未规划 CPU 密集型操作，例如：
1.复杂的数据转换
2.大范围的索引或表扫描
3.用户定义函数
4.被删除的非聚集索引
SOS_SCHEDULER_YIELD 不频繁出现但信号等待时间较高
这种情况表明等待进入运行状态的线程数量并不多，因此是外部系统因素（非 SQL Server 线程）导致了 CPU 资源被垄断。例如：
某些 CPU 密集型的 Windows 应用程序运行优先级较高，从而未及时将 CPU 控制权交还给处于可运行队列中的 SQL 线程。
此外，一个常被忽略但需要调查的原因是服务器的电源管理功能。启用该功能时，会根据对 CPU 功率需求的估计动态调整 CPU 频率。然而，这种频率变化通常无法及时跟上 SQL Server 的需求，从而导致 SOS_SCHEDULER_YIELD 等待的产生。在这种情况下，关闭电源管理功能并允许 CPU 以最高速度运行通常是最简单的解决方案。

案例

如下记录自真实案例，当用户反映系统运行缓慢时，做的压测报告

压测报告

use master
go![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/b464e794e6254570bc229c93c67513d2.png)exec dbo.sp_PressureDetector @minimum_disk_latency_ms=7

在这里插入图片描述
上图中，top 3的指标均与CPU有关,其中尤其值得注意的是SOS_SCHEDULER_YIELD指标,主机 uptime共3264小时，其中此指标的总等待时间达到1004.62小时，而且信号等待时间（percent_signal_waits)占总等待时间的99.99%

查看调度器运行状况

SELECT scheduler_id, cpu_id, is_online, is_idle, current_tasks_count, runnable_tasks_count
FROM sys.dm_os_schedulers;

在这里插入图片描述
上图显示cpu 0~3异常繁忙，current_tasks_count都是两位数，runnable_tasks_count也有多个任务排队中，而形成鲜明对比的是，cpu 4 ~ 7几乎没有任务执行

查看调度器关联的cpu状况

SELECT scheduler_id, cpu_id, status, is_online, is_idle
FROM sys.dm_os_schedulers
WHERE status != 'HIDDEN ONLINE';

在这里插入图片描述
图中输出显示，有4个cpu处于"VISIBLE OFFLINE" 状态，说明没有参与调度。
至于为何没有参与调度，我们接着调查虚拟机层面的cpu core分配

查看cpu分配情况

插卡vmware cpu分派情况，确实分配了8个cpu cores,而且是8socket*1core的组合
在这里插入图片描述

根因

此台sqlserver 版本为SQL Server 2016 Standard,事后调查得知，这个版本得sql server最高只能支持到4 socket，后续通过调整虚拟机core分配方案为4socket*2(每个socket2core)后，系统运行回复正常
在这里插入图片描述

结论

说明由于cpu存在压力，SOS_SCHEDULER_YIELD指标的信号等待达到总等待的99.99%原因，意味着有于sql os给每个线程4ms的运行时间片，导致线程频繁的自愿让出cpu资源，然后进入runnable队列，如此往复，造成恶性循环