waitstats,latch,spinlock相关文章-阿里云开发者社区

开发者社区> 开发与运维> 正文
登录阅读全文

waitstats,latch,spinlock相关文章

简介: Wait statistics, or please tell me where it hurts 本文大意:      waits stats(waits and queues) 是sql server调优的一个重要环节。

Wait statistics, or please tell me where it hurts

本文大意:
     waits stats(waits and queues) 是sql server调优的一个重要环节。waits是sql server 跟踪的值,queue是线程等待的资源。当线程使用cpu是(running状态),当等待一个资源时移动到等待队列(suspended状态),并且从一个先进先出的队列中取出一个线程控制cpu执行。当线程获取到资源就开始等待cpu执行(runnable状态)并等待获取cpu。sql server 会跟踪从running状态到下一个running状态的时间,叫做等待时间(wait time),从runnable到running的时间叫做信号等待时间(signal wait time)
WITH [Waits] AS
    (SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitS],
        ([wait_time_ms] - [signal_wait_time_ms] ) / 1000.0 AS [ResourceS],
        [signal_wait_time_ms] / 1000.0 AS [SignalS],
        [waiting_tasks_count] AS [WaitCount],
        100.0 * [wait_time_ms] / SUM ( [wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC ) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [wait_type] NOT IN (
        N'CLR_SEMAPHORE',    N'LAZYWRITER_SLEEP',
        N'RESOURCE_QUEUE',   N'SQLTRACE_BUFFER_FLUSH',
        N'SLEEP_TASK',       N'SLEEP_SYSTEMTASK',
        N'WAITFOR',          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
        N'XE_TIMER_EVENT',   N'XE_DISPATCHER_JOIN',
        N'LOGMGR_QUEUE',     N'FT_IFTS_SCHEDULER_IDLE_WAIT',
        N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
        N'CLR_AUTO_EVENT',   N'DISPATCHER_QUEUE_SEMAPHORE',
        N'TRACEWRITE',       N'XE_DISPATCHER_WAIT',
        N'BROKER_TO_FLUSH',  N'BROKER_EVENTHANDLER',
        N'FT_IFTSHC_MUTEX',  N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'DIRTY_PAGE_POLL',   N'SP_SERVER_DIAGNOSTICS_SLEEP')
    )
SELECT
    [W1]. [wait_type] AS [WaitType],
    CAST ([W1]. [WaitS] AS DECIMAL( 14, 2 )) AS [Wait_S],
    CAST ([W1]. [ResourceS] AS DECIMAL( 14, 2 )) AS [Resource_S],
    CAST ([W1]. [SignalS] AS DECIMAL( 14, 2 )) AS [Signal_S],
    [W1]. [WaitCount] AS [WaitCount],
    CAST ([W1]. [Percentage] AS DECIMAL( 4, 2 )) AS [Percentage],
    CAST (([W1]. [WaitS] / [W1]. [WaitCount]) AS DECIMAL (14, 4)) AS [AvgWait_S],
    CAST (([W1]. [ResourceS] / [W1]. [WaitCount]) AS DECIMAL (14, 4)) AS [AvgRes_S],
    CAST (([W1]. [SignalS] / [W1]. [WaitCount]) AS DECIMAL (14, 4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
    ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1]. [RowNum], [W1].[wait_type] , [W1] .[WaitS],
    [W1]. [ResourceS], [W1].[SignalS] , [W1] .[WaitCount], [W1].[Percentage]
HAVING SUM ([W2] .[Percentage]) - [W1].[Percentage] < 95 ; -- percentage threshold
GO
这个sql可以用来产看95%以上的等待。
DBCC SQLPERF (N'sys.dm_os_wait_stats' , CLEAR );用来清空等待信息
作者对经常碰到的等待类型做出了解释:
CXPACKET:在并发查询中,某个线程等待其他线程完成时出现。可以使用cost threshold for parallelism,max degree of parallelism2个参数的配置,或者设置资源调控器来减少等待的发送,但往往不是解决问题的根本方法。
PAGEIOLATCH_XX:从磁盘读入到内存时发送,不一定是io问题,可能是执行计划问题。或者内存压力问题。
ASYNC_NETWORK_IO:通常在sql server等待客户端取走数据时发送,客户端生产大量数据,导致取数据很慢,往往是程序设计不合理造成。
WRITELOG:日志管理系统等待日志刷新到磁盘时发送。往往说明io子系统的问题,1.把符合分散到多个数据库上或者缩小长事务。可以使用sys.dm_io_virtual_file_stats检查日志的io问题
MSQL_XP: sql server等待扩展存储过程完成时发送,检查扩展存储过程代码
LCK_M_XX:线程等待锁的分配,说明线程堵塞
IO_COMPLETION:等待io完成时出现,往往说明io问题
SOS_SCHEDULER_YIELD:在等待spinlock时发现可能会浪费很多cpu因此,线程确定自动让出cpu
PAGELATCH_XX:在访问page时出现(buf闩)的等待。可能是热点页,GAM,SGAM,PFS可能会引起这个问题
LATCH_XX:非buf闩的等待(闩分为2种,buf闩和非buf闩,SQL Server 2008内部剖析与故障分析一书的6.6中有详细介绍)
PREEMPTIVE_XX:切换到抢占模式通过windows调度做相关操作时出现的等待
THREADPOOL:等待可用的workthreads
DBMIRROR_DBM_MUTEX:发送buffer时出现的等待,可能是镜像回话过多
RESOUCE_SEMAPHORE:查询语句等待分配内存时出现,可能是查询语句过大或者需求的内存过大。
MSQL_DG: sql server等待分布式查询完成时出现,说明分布式查询有问题
RESOUCE_SEMAPHORE_QUERY_COMPLIE:过大的并发编译,主要是重编译和无缓冲plan造成
MSSEARCH:全文查询等待
本文大意:
     本文主要介绍了4个wait stats相关的DMV,和使用的相关sql
     sys.dm_os_wait_stats,sys.dm_os_waiting_tasks, sys.dm_os_latch_stats, sys.dm_os_spinlock_stats
     sys.dm_os_wait_stats:sql server 会把跟踪到的等待会记录,通过这个dmv反映,缺点是所有的等待都集中在一起无法等位具体sql,只能用于发现问题,使用的sql 可以查看上一篇文字
     sys.dm_os_wait_tasks:通过这个dmv可以查看当前系统正在等待的等待类型
     SELECT
       [owt].[session_id] ,
       [owt].[exec_context_id] ,
       [owt].[wait_duration_ms] ,
       [owt].[wait_type] ,
       [owt].[blocking_session_id] ,
       [owt].[resource_description] ,
       [es].[program_name] ,
       [est].1,
       [est].[dbid] ,
       [eqp].[query_plan] ,
       [es].[cpu_time] ,
       [es].[memory_usage]
FROM sys .dm_os_waiting_tasks [owt]
INNER JOIN sys. dm_exec_sessions [es] ON
       [owt].[session_id] = [es].[session_id]
INNER JOIN sys. dm_exec_requests [er] ON
       [es].[session_id] = [er].[session_id]
OUTER APPLY sys. dm_exec_sql_text ( [er].[sql_handle] ) [est]
OUTER APPLY sys. dm_exec_query_plan ( [er].[plan_handle] ) [eqp]
WHERE [es] .[is_user_process] = 1
ORDER BY [owt]. [session_id], [owt].[exec_context_id] ;
GO
     sys.dm_os_latch_stats:latch分为2类buf latch 和非buf latch,闩是一个轻量级别的锁用来保护内存的访问和修改,若要获取一个闩,那么需要从running到suspended状态。其中非buf latch 和 wait_stats中的LATCH_XX相关
WITH [Latches] AS
       (SELECT
             [latch_class],
             [wait_time_ms] / 1000.0 AS [WaitS],
             [waiting_requests_count] AS [WaitCount],
            100.0 * [wait_time_ms] / SUM ( [wait_time_ms]) OVER() AS [Percentage],
             ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC ) AS [RowNum]
       FROM sys .dm_os_latch_stats
       WHERE [latch_class] NOT IN (
             N'BUFFER')
       --AND [wait_time_ms] > 0
       )
SELECT
       [W1].[latch_class] AS [LatchClass],
       CAST ( [W1].[WaitS] AS DECIMAL(14 , 2)) AS [Wait_S],
       [W1].[WaitCount] AS [WaitCount],
       CAST ( [W1].[Percentage] AS DECIMAL(14 , 2)) AS [Percentage],
       CAST (( [W1].[WaitS] / [W1].[WaitCount] ) AS DECIMAL ( 14, 4 )) AS [AvgWait_S]
FROM [Latches] AS [W1]
INNER JOIN [Latches] AS [W2]
       ON [W2] .[RowNum] <= [W1] .[RowNum]
GROUP BY [W1]. [RowNum], [W1].[latch_class] , [W1] .[WaitS], [W1].[WaitCount] , [W1] .[Percentage]
HAVING SUM ([W2] .[Percentage]) - [W1].[Percentage] < 95 ; -- percentage threshold
GO
     sys.dm_os_spinlock_stats:是一个轻量级的同步机制在访问特定的数据结构是会实用,并且使用时间很短,自旋锁不会产生重新调度的状况。自旋锁缺点或照成cpu的浪费
IF EXISTS (SELECT * FROM [tempdb] .[sys]. [objects]
       WHERE [name] = N'##TempSpinlockStats1')
       DROP TABLE [##TempSpinlockStats1];
 
IF EXISTS (SELECT * FROM [tempdb] .[sys]. [objects]
       WHERE [name] = N'##TempSpinlockStats2')
       DROP TABLE [##TempSpinlockStats2];
GO
 
-- Baseline
SELECT * INTO [##TempSpinlockStats1]
FROM sys .dm_os_spinlock_stats
WHERE [collisions] > 0
ORDER BY [name];
GO
 
-- Now do something
DBCC CHECKDB (N'SalesDB' ) WITH NO_INFOMSGS;
GO
 
-- Capture updated stats
SELECT * INTO [##TempSpinlockStats2]
FROM sys .dm_os_spinlock_stats
WHERE [collisions] > 0
ORDER BY [name];
GO
 
-- Diff them
SELECT
    '***' AS [New] ,
    [ts2]. [name] AS [Spinlock],
    [ts2]. [collisions] AS [DiffCollisions],
    [ts2]. [spins] AS [DiffSpins],
    [ts2]. [spins_per_collision] AS [SpinsPerCollision],
    [ts2]. [sleep_time] AS [DiffSleepTime],
    [ts2]. [backoffs] AS [DiffBackoffs]
FROM [##TempSpinlockStats2] [ts2]
LEFT OUTER JOIN [##TempSpinlockStats1] [ts1]
    ON [ts2].[name] = [ts1].[name]
WHERE [ts1] .[name] IS NULL
UNION
SELECT
    '' AS [New] ,
    [ts2]. [name] AS [Spinlock],
    [ts2]. [collisions] - [ts1]. [collisions] AS [DiffCollisions],
    [ts2]. [spins] - [ts1]. [spins] AS [DiffSpins],
    CASE ( [ts2].[spins] - [ts1].[spins] ) WHEN 0 THEN 0
        ELSE ([ts2] .[spins] - [ts1] .[spins]) /
            ([ts2]. [collisions] - [ts1]. [collisions]) END
            AS [SpinsPerCollision] ,
    [ts2]. [sleep_time] - [ts1]. [sleep_time] AS [DiffSleepTime],
    [ts2]. [backoffs] - [ts1]. [backoffs] AS [DiffBackoffs]
FROM [##TempSpinlockStats2] [ts2]
LEFT OUTER JOIN [##TempSpinlockStats1] [ts1]
    ON [ts2].[name] = [ts1].[name]
WHERE [ts1] .[name] IS NOT NULL
    AND [ts2].[collisions] - [ts1].[collisions] > 0
ORDER BY [New] DESC, [Spinlock] ASC ;
GO

SOS_SCHEDULER_YIELD waits and the LOCK_HASH spinlock

本文大意:
     SOS_SCHEDULER_YIELD自动让步让其他线程运行,往往出现在cpu使用比较高的代码中。当放弃查看并等待自旋锁的访问时不是显式的退让,而是直接sleep,资源退让后,直接到了runnable队列中。当出现大量这里等待时就需要关心性能问题。作者用一个例子演示,里面出现了大量的SOS_SCHEDULER_YIELD等待。使用sys.dm_os_waiting_stats没有发现什么问题,当使用sys.dm_os_spinlock_stats是出现大量的LOCK_HASH,SQL Server并没有继续自选,而是使用自动退让的方法。
为了解决这一类问题有以下2个方法:
    把冲突分散到多个数据库中
     使用中间件或者客户端缓存,并使用数据修改的通知机制
本文大意:
     MAXDOP是根据环境的不同,设置也是不同的。
     1.在oltp下MAXDOP设置为1,并使用查询提示的MAXDOP去覆盖,往往是不错的选择
     2.在混合负载下使用MAXDOP,总会让另外一种负载性能有问题最好的办法是设置为1,并且使用查询提示或者资源调控器来处理
     3.当出现CXPACKETd等待时,应该先考虑清楚是什么问题造成的,可能是统计信息过期或者统计信息不正确
     4.考虑使用并发阀值
本文大意:
     作者使用例子说明语句级别的wait stats的收集和分析。
本文大意:
     为了保证非buf数据结构的线程级安全,就必须要使用同步机制,要不就是闩要不就是自选。当使用频繁,使用闩开销太大,使用时间很短的情况下就会使用自选锁。若在统计信息里面发现是LATCH_XX等待有问题,那么为了进一步缩小范围应该查询sys.dm_os_latch_stats,也可以再sys.dm_os_waiting_tasks中的resource_description中看到闩锁类型。一下是10个比较常见的闩锁类型:
     ACCESS_METHODS_DATASET_PARENT,ACCESS_METHODS_SCAN_RANGE_GENERATOR:当并发扫描给定一个pageid 范围让各个线程扫描是会出现,往往伴随出现LATCH_XX,CXPACKET,PAGEIOLATCH_XX。
     ACCESS_METHODS_HOBT_COUNT:hobt页和行计数器的访问时出现,可能是单表中出现大量dml引起。
     TRACE_CONTROLLER:这个闩缓存跟踪的很多事情,出现冲突说明有多个trace的问题
     DBCC_MULTIOBJECT_SCANNER:只有在DBCC CHECK允许并行时出现
     ACCESS_METHODS_HOBT_VIRTUAL_ROOT:这个闩用于访问索引元数据和根页,出现冲突主要是根页的分页,可能是小索引上的大量并发移动
     FGCB_ADD_REMOVE:出现在文件组中文件的添加删除,文件增长,填充率重新计算(每8192分配就会重新计算),文件组中文件循环分配信息(多文件的数据库可能会出现)。
     DATABASE_MIRRORING_CONNECTION:控制镜像消息流,出现冲突可能是有太多数据库回话。
     NESTING_TRANSACTION_FULL:用于控制访问并发嵌套事务的事务访问结构。调用并发操作的查询必须为每个并发事务启动子事务,这些事务是并发嵌套查询的子事务。出现这个问题往往是没必要的并发导致但是不能定论是否如此

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
开发与运维
使用钉钉扫一扫加入圈子
+ 订阅

集结各类场景实战经验,助你开发运维畅行无忧

其他文章