www.9159.com1.进程等待,那么该Worker就会让出Sched

作者: www.9159.com  发布:2019-12-10

一、问题概述

问题大概是这样的,有一个功能页面经常查询超时,有时候就算能查询出来也要很长的时间,但是有时又会很快。遇到的这种问题在排除掉网络原因之后基本上可以从查询语句上去找原因。

编译查询SQL语句和查询进程等待结果如下:

1.进程等待

www.9159.com 1

2.编译查询

www.9159.com 2

问题分析:

1.等待类型为SOS_SCHEDULER_YIELD并且CPU时长很长

2.而且查询时长基本上都是消耗在编译这块,实际的查询时长不到一秒。

SOS_SCHEDULER_YIELD等待是SQL Server OS调度这块的线程之间的切换的等待。

 

这里出于不便没有把SQL语句贴上,实际的SQL语句单单子查询就达60多个,查询语句达到200行,导致生成执行计划的时间过长。单个子查询很简单。

本文出处: 

SQL Server 内置系统调度算法采用:非抢占式争用CPU资源和主动退让策略,主动退让(voluntarily yield)是指在调度器(Scheduler)上运行的Worker都是以非抢占模式来争用CPU资源的。Worker 会一直在一个Scheduler上运行,直到运行结束,或者主动将Scheduler 让出给其他Worker为止。SQL Server定义了很多Yield的规则,约束一个Task占用Scheduler运行的时间,保证Task在合适的时间点主动Yield,不至于占用Scheduler 太多时间,使其他Task等待太久。当Task 主动Yield时,Request处于SOS_SCHEDULER_YIELD等待类型。Worker 也会因为阻塞而主动退让,如果一个Worker在运行过程中被锁或是其他资源阻塞,那么该Worker就会让出Scheduler,来让其它Worker运行,当前Worker处于阻塞状态。

二、解决方案

出现上面的这个原因是由于查询语句子查询太多导致编译时间过长,解决办法可以将每一个子查询单独查询赋值到变量,或者把子查询冗余处理。

 

 

 

 

 

 

备注:

    作者:pursuer.chen

    博客:http://www.cnblogs.com/chenmh

本站点所有随笔都是原创,欢迎大家转载;但转载时必须注明文章来源,且在文章开头明显处给明链接,否则保留追究责任的权利。

《欢迎交流讨论》

 

 

一,消耗CPU资源的操作

 

在SQL Server中,有三类操作非常消耗CPU资源:

进程的状态转换

1,编译执行计划,生成执行计划是非常消耗CPU资源的操作,当一个语句生成执行计划之后,SQL Server将其存储在Plan Cache中,以便重用执行计划。

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

2,执行排序(Sort),聚合计算(Aggregation),哈希连接操作都需要消耗CPU来完成计算,同时Disk IO也需要消耗一定的CPU资源;

 

3,以并发方式执行查询请求,并发控制受到配置选项 Maximum Degree of Parallelism 和 Cost Threshold of Parallelism的影响。

  如下图,分别是:运行态,阻塞态,就绪态。各个状态之间的转换关系及粗略原因如下:
  运行态-->阻塞态,原因:等待某种资源的完成,比如IO等。
  阻塞态-->就绪态,原因:锁请求的资源已完成,加入获取CPU队列中(going directly to the bottom of the Runnable Queue for the scheduler)。
  运行态-->就绪态,原因:用完了一个CPU的时间片周期(当前线程仍未完成,需要CPU资源),那么就等待进入下一个时间片周期。
              (因为一个线程不会永久性占用CPU,CPU资源而是各个线程轮询占用的一个过程)

在发现一个Task比较复杂时,SQL Server会生成多个Child Task,使用多个Thread并发执行该Task,从而提高整体的响应时间,Maximum DOP(Degree of Parallelism)选项控制查询的并发度,决定一个Task在一个时间点最多可以拥有线程的数量。

www.9159.com 3

Cost Threshold of Parallelism 选项决定一个Task是否并发执行。当SQL Server 编译查询语句时,总是先做单线程的执行计划,同时计算执行计划的Cost。如果发现这个执行计划的Cost 值大于Cost Threshold of Parallelism 选项设置的值,那么SQL Server 就会改用并行执行计划。如果提高Cost Threshold of Parallelism 选项的值,SQL Server 会提高做并行执行计划的标准,从而有更少的Task会并发执行,减少CPU的利用,以便留出空闲的CPU执行其他用户的Task。

www.9159.com, 

由于CPU资源的有限,如果为一个Task使用全部的CPU,那么在Task并发执行期间,其他用户的Task得不到及时响应。Task的并发度和用户的并发度时互斥的,为了平衡这种并发度,一般设置Maximum Degree of Parallelism 为CPU总数的1/4,既能够以并发执行计划快速提高单个Task的执行性能,也能兼顾用户的并发度,及时响应其他用户的Request。

SOS_SCHEDULER_YIELD类型等待

二,调度产生的等待类型

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

1,SOS_SCHEDULER_YIELD

关于SOS_SCHEDULER_YIELD的问题之一是SOS_SCHEDULER_YIELD这种等待类型并非一种真正的等待,
当此种等待类型发生的时候,原因是线程耗尽了它的4ms的(时间片)轮询周期,并且自动地让出(voluntary yields)CPU资源,
直接被调度器排列在可执行新线程队列的末尾,绕过等待信息列表,
此时的线程由执行态转换为就绪态,虽然此等待记录为SOS_SCHEDULER_YIELD等待,并不是因为真正的资源等待所导致的的。

当Task主动Yield时,查询请求处于SOS_SCHEDULER_YIELD等待,等待被重新调用。多数情况下,出现 SOS_SCHEDULER_YIELD 等待是由于查询语句正在进行表或索引扫描,原因可能是缺失相应的索引,统计信息过期,优化器选择差的查询计划,产生参数嗅探,或者查询脚本强制不适用索引等

而是CPU轮询周期完成之后,当前线程任务仍没有完成,需要等待轮询下一个周期来获取CPU资源执行导致的。
也就是说当然线程的执行一直在就绪和执行态之间转换,从中可以推断出当前线程是一个较为耗费CPU的操作。

2,CXPACKET

www.9159.com 4

在查询请求以并行方式运行时,某些 task 在等待其他兄弟Task的处理完成,此时,查询请求处于CXPACKET等待。如果系统中该等待出现次数过多,时间过长,通过增加索引等不能减少该等待,考虑调整增加并发阈值(cost threshold for parallelism)和降低并发度(degree of parallelism)

不少参考资料上都是是扫描( 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等待因此产生。

CXPACKET 等待类型是SQL Server 并发执行一个查询时产生的。在执行查询请求时,SQL Server充分利用系统的所有资源(CPU,Memory,Disk IO),争取在最短时间内返回结果。在拥有多个处理器的系统中,如果将一个查询请求的工作负载(Wordload)分布在多个处理器上,那么每个处理器只处理一部分数据,这将成倍的减少执行查询请求的时间消耗,相应地,成倍的提高查询性能。

 

在执行Query时,SQL Server 估计其时间消耗,如果大于并发的阈值,那么SQL Server使用多个Thread(每个CPU上在同一时间只能运行一个Thread),以并发方式执行查询请求;如果低于并发阈值,那么SQL Server 使用单个Thread执行查询请iu。这个并发阈值是由选项 cost threshold for parallelism 决定的。

总结

不是所有的query都适合并发执行,并发执行有一定的管理开销(Overhead),如果一个查询耗时十分小,SQL Server 创建并发执行结构体的时间消耗都会比执行整个查询的时间消耗高,那就得不偿失。并发阈值选项(Cost threshold for parallelism)的默认值是5,一般不需要修改默认值。

  SOS_SCHEDULER_YIELD类型等待并非真正意义上的等待资源,
  而是CPU的一种调度机制导致较为耗费CPU资源的线程在执行过程中轮询获取CPU资源的一种现象。
  有可能是线程运行的SQL语句本身性能较低,比如全面扫描之类的,或者是SQL语句本身逻辑运算,产生了Hash Join,排序之类的
  如果发现有非常严重的SOS_SCHEDULER_YIELD类型等待,也应该慎重对待,比如从执行计划等方面去分析是否是正常的
  是否确实是数据高CPU使用类型的查询,否则就要从SQL语句入手分析优化了。

如果SQL Server 以并发方式执行单个查询请求,那么SQL Server分配的进程数量是由并发度选项(max degree of parallelism )决定的。

 

在并发执行的Thread中,包括Master Thread,假如MDP=6,那么同时有7个Thread来执行单个查询请求,其中一个Thread是Master Thread,其他被称作Child Thread,Master Thread负责分配任务和将结果结合(combine)在一起,Child Thread负责执行特定的任务。由于Child Thread 被分配的Workload不平均,执行的速度不相同,因此,当一些Child Thread完成任务之后,需要等待未完成的Child Thread。这种在并发执行内部,一些Child Thread需要等待其他Child Thread的Wait type就是:CXPACKET。

参考

使用以下脚本修改默认的并发阈值和并发程度:

  
  

sp_configure 'show advanced options', 1;
GO
reconfigure;
GO
sp_configure 'cost threshold for parallelism', 6;
GO
reconfigure;
GO

sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 8;
GO
RECONFIGURE WITH OVERRIDE;
GO

 

 

 

参考文档:

 

cost threshold for parallelism Option.aspx)

max degree of parallelism Option.aspx)

Wait statistics, or please tell me where it hurts

What is the CXPACKET Wait Type, and How Do You Reduce It?

Should you worry about SOS_SCHEDULER_YIELD?

Knee-Jerk Wait Statistics : SOS_SCHEDULER_YIELD

New whitepapers on latches and spinlocks published

本文由9159.com发布于www.9159.com,转载请注明出处:www.9159.com1.进程等待,那么该Worker就会让出Sched

关键词:

上一篇:创建数据库指定字符集
下一篇:没有了