ACCESSwaitstats,latch,spinlock相关小说

Wait statistics, or please tell me where it hurts

本文疏忽:

     waits stats(waits and queues) 是sql
server调优的多个首要环节。waits是sql server
跟踪的值,queue是线程等待的财富。当线程使用cpu是(running状态),当等待2个能源时移动到等候队列(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’ , CLEAKoleos );用来清空等待音信

小编对平日遇上的等待类型做出精晓释:

CXPACKET:在产出查询中,有个别线程等待其余线程达成时出现。能够应用cost
threshold for parallelism,max degree of
parallelism3个参数的铺排,或许安装财富调节和控制器来收缩等候的殡葬,但频繁不是缓解难点的常有措施。

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 二零一零里头分析与故障分析一书的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

本文马虎:

     本文首要介绍了6个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,闩是3个轻量级其余锁用来维护内部存款和储蓄器的拜访和修改,若要获取3个闩,那么要求从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并从未继续自行选购,而是采用机关妥洽的法子。

为了消除这一类标题有以下1个办法:

    把争辩分散到四个数据库中

     使用中间件或然客户端缓存,并应用数据修改的打招呼机制


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中见到闩锁类型。一下是十二个相比较普遍的闩锁类型:

 
   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:出现在文书组中文件的充足删除,文件增进,填充率重新计算(每81九十多分红就会重复计算),文件组汉语件循环分配音讯(多文书的数据库恐怕会油可是生)。

   
 DATABASE_MIRRORING_CONNECTION:控制镜像新闻流,出现抵触可能是有太多数据库回话。

   
 NESTING_TRANSACTION_FULL:用于控制访问并发嵌套事务的工作访问结构。调用并发操作的询问必须为各样并发事务运维子事务,那几个事情是并发嵌套查询的子事务。出现那么些题材屡屡是没须求的产出导致然而无法定论是还是不是如此

相关文章