彩世界平台-彩世界时时app-彩世界开奖app苹果下载

热门关键词: 彩世界平台,彩世界时时app,彩世界开奖app苹果下载

您的位置:彩世界平台 > 工作委员会 > sql server 性能调优 资源等待之SOS_SCHEDULER_YIELD

sql server 性能调优 资源等待之SOS_SCHEDULER_YIELD

发布时间:2019-08-30 10:09编辑:工作委员会浏览(137)

     一.  概述

      这次介绍实例级别资源等待LCK类型锁的等待时间,关于LCK锁的介绍可参考 “sql server 锁与事务拨云见日”。下面还是使用sys.dm_os_wait_stats 来查看,并找出耗时最高的LOK锁。

    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 'LCK%' 
    order by  wait_time_ms desc
    

     查出如下图所示:

    图片 1

       1.  分析介绍

       重点介绍几个耗时最高的锁含义:

        LCK_M_IX: 正在等待获取意向排它锁。在增删改查中都会有涉及到意向排它锁。
      LCK_M_U: 正在等待获取更新锁。 在修改删除都会有涉及到更新锁。
      LCK_M_S:正在等待获取共享锁。 主要是查询,修改删除也都会有涉及到共享锁。
      LCK_M_X:正在等待获取排它锁。在增删改中都会有涉及到排它锁。
      LCK_M_SCH_S:正在等待获取架构共享锁。防止其它用户修改如表结构。
      LCK_M_SCH_M:正在等待获取架构修改锁 如添加列或删除列 这个时候使用的架构修改锁。

          下面表格是统计分析

    锁类型 锁等待次数 锁等待总时间(秒) 平均每次等待时间(毫秒) 最大等待时间
    LCK_M_IX 26456 5846.871 221 47623
    LCK_M_U 34725 425.081 12 6311
    LCK_M_S 613 239.899 391 4938
    LCK_M_X 4832 77.878 16 4684
    LCK_M_SCH_S 397 77.832 196 6074
    LCK_M_SCH_M 113 35.783 316 2268

      注意: wait_time_ms 时间里,该时间表包括了signal_wait_time_ms信号等待时间,也就是说wait_time_ms不仅包括了申请锁需要的等待时间,还包括了线程Runnable 的信号等待。通过这个结论也能得出max_wait_time_ms 最大等待时间不仅仅只是锁申请需要的等待时间。

     

    2. 重现锁等待时间

    --  重置
    DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);  
    

     图片 2

    --  会话1 更新SID=92525000, 未提交
    begin tran 
    update [dbo].[PUB_StockTestbak] set model='mmtest' where sid=92525000
    
    -- 会话2 查询该ID, 由于会话1更新未提交 占用x锁,这里查询将阻塞
    select * from [PUB_StockTestbak] where sid=92525000
    

       手动取消会话2的查询,占用时间是61秒,如下图:

    图片 3

      再来统计资源等待LCK,如下图 :

    图片 4

      总结:可以看出资源等待LCK的统计信息还是非常正确的。所以找出性能消耗最高的锁类型,去优化是很有必要。比较有针对性的解决阻塞问题。

    3. 造成等待的现象和原因

    现象:

      (1)  用户并发越问越多,性能越来越差。应用程序运行很慢。

      (2)  客户端经常收到错误 error 1222 已超过了锁请求超时时段。

      (3)  客户端经常收到错误 error 1205 死锁。

      (4)  某些特定的sql 不能及时返回应用端。

    原因:

      (1) 用户并发访问越多,阻塞就会越来越多。

      (2) 没有合理使用索引,锁申请的数量多。

      (3) 共享锁没有使用nolock, 查询带来阻塞。 好处是必免脏读。

      (4) 处理的数据过大。比如:一次更新上千条,且并发多。

      (5) 没有选择合适的事务隔离级别,复杂的事务处理等。

    4.  优化锁的等待时间

       在优化锁等待优化方面,有很多切入点 像前几篇中有介绍 CPU和I/O的耗时排查和处理方案。 我们也可以自己写sql来监听锁等待的sql 语句。能够知道哪个库,哪个表,哪条语句发生了阻塞等待,是谁阻塞了它,阻塞的时间。

      从上面的平均每次等待时间(毫秒),最大等待时间 作为参考可以设置一个阀值。 通过sys.sysprocesses 提供的信息来统计, 关于sys.sysprocesses使用可参考"sql server 性能调优 从用户会话状态分析"。 通过该视图 监听一段时间内的阻塞信息。可以设置每10秒跑一次监听语句,把阻塞与被阻塞存储下来。

       思想如下:

    -- 例如 找出被阻塞会话ID 如时间上是2秒 以及谁阻塞了它的会话ID
    SELECT spid,blocked #monitorlock FROM sys.sysprocesses 
    where blocked>0 and    waittime>2000 
    
    -- 通过while或游标来一行行获取临时表的 会话ID,阻塞ID,通过exec动态执行来获取sql语句文本 进行存储
    exec('DBCC INPUTBUFFER('+@spid+')') 
    
    exec('DBCC INPUTBUFFER('+@blocked+')') 
    

     

     一.概念

       SOS_SCHEDULER_YIELD等待类型是一个任务自愿放弃当前的资源占用,让给其他任务使用。   这个等待类型与CPU有直接关系,与内存与也有间接关系,与CPU有关系是因为在sql server里是通过任务调度SCHEDULER来关联CPU。 通过SCHEDULER下的Worker线程来处理SQL任务。为什么跟内存有关系呢,是因为获取的资源需要内存来承载。 
      Yelding的发生:是指SCHEDULER上运行的Worker都是非抢占式的, 在 SCHEDULER上Worker由于资源等待,让出当前Worker给其它Worker就叫Yielding。 关于SCHEDULER_YIELD产生的原理查看  sqlserver 任务调度与CPU。SOS_SCHEDULER_YIELD 等待的情况可以了解到:

      (1)CPU有压力

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

    1.1 从实例级别来查看等待数

    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
    

      查询如下图所示: 

    图片 5

      这个等待类型排名第二,从请求的次数来说有69367060次,也就是说该线程用完了4ms的时间片,主动放弃cpu。如果没有大量的runnable队列或者大量的signal wait,证明不一定是cpu问题。因为这两个指标是cpu压力的一个体现。需要检查执行计划中是否存在大量扫描操作。

    1.2 通过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
    

      如下图所示:

    图片 6

      如果你注意到runnable_tasks_count计数有两位数,持续很长时间(一段时间内),你就会知道CPU压力。两位数字通常被认为是一件坏事 无法应对当前负荷。另外可以通过性能监视器%Processor Time 来查看CPU的状况。

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

    SELECT * FROM sys.dm_exec_requests  WHERE wait_type LIKE 'SOS_SCHEDULER_YIELD%'
    

      -- 或查找资源等待的
      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。

    图片 7

    图片 8

    图片 9

    1.4 减少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

     

    本文由彩世界平台发布于工作委员会,转载请注明出处:sql server 性能调优 资源等待之SOS_SCHEDULER_YIELD

    关键词:

上一篇:SQLServer之DEFAULT约束

下一篇:没有了