【等待优化】SOS_SCHEDULER_YIELD等待，cpu等待引起

本文出处：http://www.cnblogs.com/wy123/p/6856802.html 

【2】出处：https://www.10qianwan.com/articledetail/191771.html

【1】前置情况介绍

（1.1）进程的状态转换

　　在说明SOS_SCHEDULER_YIELD等待之前，先简要介绍一下进程的状态（迷迷糊糊记得操作系统原理课上讲过，三态五态转换的，比下面这个图要复杂，大部分都还给老师了）。

　　如下图，分别是：运行态，阻塞态，就绪态。各个状态之间的转换关系及粗略原因如下：

　　运行态-->阻塞态，原因：等待某种资源的完成，比如IO等。

　　阻塞态-->就绪态，原因：锁请求的资源已完成，加入获取CPU队列中（going directly to the bottom of the Runnable Queue for the scheduler）。

　　运行态-->就绪态，原因：用完了一个CPU的时间片周期（当前线程仍未完成，需要CPU资源），那么就等待进入下一个时间片周期。

　　　　　　　　　　　　　　（因为一个线程不会永久性占用CPU，CPU资源而是各个线程轮询占用的一个过程）

　　

（1.2）类型等待

SQL Server中的调度机制也类似于进程的三态模型，运行，阻塞，就绪。

关于SOS_SCHEDULER_YIELD的问题之一是SOS_SCHEDULER_YIELD这种等待类型并非一种真正的等待，

当此种等待类型发生的时候，原因是线程耗尽了它的4ms的（时间片）轮询周期，并且自动地让出（voluntary yields）CPU资源,

直接被调度器排列在可执行新线程队列的末尾，绕过等待信息列表，

此时的线程由执行态转换为就绪态，虽然此等待记录为SOS_SCHEDULER_YIELD等待，并不是因为真正的资源等待所导致的的。

而是CPU轮询周期完成之后，当前线程任务仍没有完成，需要等待轮询下一个周期来获取CPU资源执行导致的。

也就是说当然线程的执行一直在就绪和执行态之间转换，从中可以推断出当前线程是一个较为耗费CPU的操作。

　　

不少参考资料上都是是扫描（ large scans happening ），如下

You want to investigate these waits if they’re a prevalent wait on your server,

as they could be an indicator of large scans happening (of data that’s already in memory)

where you’d really rather have small index seeks.

不过本人觉得这是全表或者索引扫描是仅仅存在的原因之一

　　其他有可能的原因，比如Hash Join，大结果集的Sort，Distinct等等，都是比较耗费CPU的。

另外就是，如果CPU确实比较忙，比如操作系统上的进程很多，CPU使用率很高

　　一个线程从就绪态到运行态需要等待的时间也会变长，因为等待占用CPU时间片的线程比较多。

总之，要明白的是，产生SOS_SCHEDULER_YIELD等待是因为当前线程需要耗费大量的CPU资源，

　　而CPU的调用机制是分时间片轮询供各个线程使用的，当前线程在CPU周期结束的时候，仍旧没有完成运算任务

　　那么就要等到下一个CPU周期去获取CPU资源，SOS_SCHEDULER_YIELD等待因此产生。

总结

　　SOS_SCHEDULER_YIELD类型等待并非真正意义上的等待资源，

　　而是CPU的一种调度机制导致较为耗费CPU资源的线程在执行过程中轮询获取CPU资源的一种现象。

　　有可能是线程运行的SQL语句本身性能较低，比如全面扫描之类的，或者是SQL语句本身逻辑运算，产生了Hash Join，排序之类的

　　如果发现有非常严重的SOS_SCHEDULER_YIELD类型等待，也应该慎重对待，比如从执行计划等方面去分析是否是正常的

　　是否确实是数据高CPU使用类型的查询，否则就要从SQL语句入手分析优化了。

【2】SOS_SCHEDULER_YIELD 概念

（2.1）基本概念

SOS_SCHEDULER_YIELD等待类型是一个任务自愿放弃当前的资源占用，让给其他任务使用。   

　　这个等待类型与CPU有直接关系，与内存与也有间接关系，与CPU有关系是因为在sql server里是通过任务调度SCHEDULER来关联CPU。

　　通过SCHEDULER下的Worker线程来处理SQL任务。为什么跟内存有关系呢，是因为获取的资源需要内存来承载。 

　　Yelding 的发生：是指SCHEDULER上运行的Worker都是非抢占式的, 在 SCHEDULER上Worker由于资源等待，让出当前Worker给其它Worker就叫Yielding。

　　　　 关于SCHEDULER_YIELD产生的原理查看  。SOS_SCHEDULER_YIELD 等待的情况可以了解到:

　　　　(1)CPU有压力

　　　　(2) SQL Server CPU scheduler 使用得当处理就会效率高。

（2.2）从实例级别来查看等待数

select wait_type,
waiting_tasks_count,
wait_time_ms ,
max_wait_time_ms,
signal_wait_time_ms
from sys.dm_os_wait_stats
where wait_type like 'SOS_SCHEDULER_YIELD%'
order by wait_type

　　查询如下图所示: 

　　

　　这个等待类型排名第二，从请求的次数来说有69367060次，也就是说该线程用完了4ms的时间片，主动放弃cpu。

　　如果没有大量的runnable队列或者大量的signal wait，证明不一定是cpu问题。因为这两个指标是cpu压力的一个体现。需要检查执行计划中是否存在大量扫描操作。

（2.3）通过dmv scheaduler 的描述查看cpu压力

SELECT scheduler_id, current_tasks_count, runnable_tasks_count, work_queue_count, pending_disk_io_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255

　　如下图所示：

　　如果你注意到runnable_tasks_count计数有两位数，持续很长时间（一段时间内），你就会知道CPU压力。

　　两位数字通常被认为是一件坏事 无法应对当前负荷。另外可以通过性能监视器%Processor Time 来查看CPU的状况。

（2.4） 通过案例实时查看sql语句级的资源等待

SELECT * FROM sys.dm_exec_requests  WHERE wait_type LIKE 

'SOS_SCHEDULER_YIELD%'<br>
　　-- 或查找资源等待的
　　SELECT session_id ,status ,blocking_session_id
　　,wait_type ,wait_time ,wait_resource
　　,transaction_id
　　FROM sys.dm_exec_requests
　　WHERE status = N'suspended';

　　如下图所示 运行sys.dm_exec_requests 表，由于字段多截取了三断。

　　会话202的sql 语句上一次 等待类型是SOS_SCHEDULER_YIELD。

　　之所以会出现YIELD，是因为SCHEDULER下的Worker已经发起了task 命令，但由于资源等待 如锁或者磁盘输入/输出等，Worker又是非抢占式，所以让出了当前的Worker。

　　

　　

　　

（2.5）减少sos_scheduler_yield 等待

　　正如上面所讨论的，这种等待类型与CPU压力有关。增加更多CPU是简单的解决方案，然而实现这个解决方案并不容易。当这个等待类型很高时，你可以考虑其他的事情。这里通过从缓存中找到与CPU相关的最昂贵的SQL语句。

　　

--查询编译以来 cpu耗时总量最多的前50条(Total_woker_time) 第一种查询
select
'total_worker_time(ms)'=(total_worker_time/1000),
q.[text], --DB_NAME(dbid),OBJECT_NAME(objectid),
execution_count,
'max_worker_time(ms)'=(max_worker_time/1000),
'last_worker_time(ms)'=(last_worker_time/1000),
'min_worker_time(ms)'=(min_worker_time/1000),
'max_elapsed_time(ms)'=(max_elapsed_time/1000),
'min_elapsed_time(ms)'=(min_elapsed_time/1000),
'last_elapsed_time(ms)'=(last_elapsed_time/1000),
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_reads,
last_logical_reads,
max_logical_reads,
creation_time,
last_execution_time
from
(select top 50 qs.* from sys.dm_exec_query_stats qs order by qs.total_worker_time desc)
as highest_cpu_queries cross apply sys.dm_exec_sql_text(highest_cpu_queries.plan_handle) as q
order by highest_cpu_queries.total_worker_time DESC