waitstats,latch,spinlock相关文章

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:全文查询等


Advanced SQL Server performance tuning

本文大意:

     本文主要介绍了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 configuration survey results

本文大意:

     MAXDOP是冲环境之不比,设置也是殊的。

   
 1.每当oltp下MAXDOP设置为1,并动用查询提示的MAXDOP去覆盖,往往是毋庸置疑的挑三拣四

   
 2.每当混负载下下MAXDOP,总会吃另外一种植负载性能有问题太好的法是安也1,并且采取查询提示或资源调控器来拍卖

   
 3.当出现CXPACKETd等待时,应该先行考虑清楚是呀问题造成的,可能是统计信息过期或统计信息不正确

     4.考虑采取并发阀值


Capturing wait stats for a single operation

正文大意:

     作者使用例子说明语句级别之wait stats的采和分析。


Most common latch classes and what they mean

正文大意:

   
 为了确保非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:用于控制访问并发嵌套事务的事情访问结构。调用并发操作的查询必须也每个并发事务启动子事务,这些工作是连发嵌套查询的子事务。出现这个题目反复是未曾必要之面世导致但是非可知定论是否这样

相关文章