从wait角度调优

 

 

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

 

you can very easily come up with a way to persist the results every few hours or every day and do some time-series analysis to figure out trends or automatically spot problems as they start to happen. You can also use Performance Dashboard to see these graphically in 2005 and Data Collector in 2008. On SQL Server 2000 you can use DBCC SQLPERF (N’waitstats’).

 

这个sql可以用来产看95%以上的等待。

 

–查看等待类型对应的sql

if exists (select * from sys.objects where object_id = object_id(N'[dbo].[get_statements_from_waiter_list]') and OBJECTPROPERTY(object_id, N'IsProcedure') = 1)
    drop procedure [dbo].[get_statements_from_waiter_list]
go 

create proc get_statements_from_waiter_list (@wait_type nvarchar(60)=NULL)
as
select
        r.wait_type
        ,r.wait_time
        ,SUBSTRING(qt.text,r.statement_start_offset/2,
            (case when r.statement_end_offset = -1
            then len(convert(nvarchar(max), qt.text)) * 2
            else r.statement_end_offset end -r.statement_start_offset)/2)
        as query_text
        ,qt.dbid, dbname=db_name(qt.dbid)
        ,qt.objectid
        ,r.sql_handle
        ,r.plan_handle
FROM sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
where r.session_id > 50
  and r.wait_type = isnull(upper(@wait_type),r.wait_type)
go 

exec get_statements_from_waiter_list

DBCC SQLPERF (N'sys.dm_os_wait_stats' , CLEAR );用来清空等待信息

作者对经常碰到的等待类型做出了解释：

CXPACKET:在并发查询中，某个线程等待其他线程完成时出现。可以使用cost threshold for parallelism，max degree of parallelism2个参数的配置，或者设置资源调控器来减少等待的发送，但往往不是解决问题的根本方法。

这意味着

•发生了并行操作

•发生了并行执行,或是并行执行中的一个worker被阻塞

不要望文生义

•不要将服务器级别的MAXDOP设置为1,也就是禁用并行
当您配置的 MAXDOP 值时，请遵循以下准则。

 

SQL Server 2005 及更高版本

• 对于使用超过八个处理器的服务器，请使用下列配置：

   

  MAXDOP = 8
• 对于使用 8 个或更少的处理器的服务器，请使用下列配置：

   

  MAXDOP = 0 到N
  
  
  
  注意 在此配置中， N表示处理器的数目。
• 对于具有 NUMA 配置的服务器，MAXDOP 不应超过分配给每个 NUMA 节点的 Cpu 的数量。
• 对于已启用超线程的服务器，MAXDOP 值不应超过物理处理器的数量。
• 对于服务器具有 NUMA 配置和启用超线程后，MAXDOP 值不应超过每个 NUMA 节点的物理处理器的数目。

 

更多分析症状和解决方案-PAGEIOLATCH_XX

•是否存在PAGEIOLATCH_SH等待,这意味着大范围SCAN

•同时也观察一下ACCESS_METHODS_DATASET_PARENTLatch和ACCESS_METHODS_SCAN_RANGE_GENERATOR LATCH

•检查导致CXPACKET的请求来查看执行计划是否合理

•其中某个并行线程执行时间过长(也就是其中某个线程不是由于CXPACKET阻塞)

可能的原因

•仅仅是由于发生了并行

•由于缺少聚集索引或是不准确的执行计划导致扫描

•过期的统计信息

•Distinct结果集无法预估执行计划,导致不合适执行计划,从而产生CXPACKET等待,解决办法是临时表(王成辉)

如果的确是问题

•确保统计信息是最新的,并且存在适当的索引

•设置查询的MAXDOP

•考虑MAXDOP=NUMA的物理CPU核数

•在考虑到负载类型(混合)的前提下,再设置实例的MAXDOP

•考虑设置将”cost threshold parallelism”的值设置的更高

 

 

PAGEIOLATCH_XX:从磁盘读入到内存时发送，不一定是io问题，可能是执行计划问题。或者内存压力问题。

这意味着:

•等待页由磁盘被读取到

•最常见的是SH和EX

•SH意味着页被用于读取

•EX意味着页会被修改

避免望文生义

•不要直接判断是IO系统和IO通道的问题

更多分析

•决定哪个表/索引被读取(通过DBCC Page)

•使用sys.dm_io_virtual_file_stats和Avg Disk Secs/Read性能计数器判断IO

•对应的CXPACKET等待,是否存在并行扫描

•通过执行计划查看并行扫描

•通过执行计划查看是否存在隐式转换(可能导致扫描)

•通过Page Life Expectancy查看是否存在缓存区内存压力

创建非聚集索引来减少扫描

更新统计信息

将受影响的数据转移到更快的IO子系统

考虑增加内存

 

 

ASYNC_NETWORK_IO:通常在sql server等待客户端取走数据时发送，客户端生产大量数据，导致取数据很慢，往往是程序设计不合理造成。

这意味着

•SQL Server等待客户端获取数据的ACK反馈

避免望文生义

•不要简单认为是网络延迟

•只有再考虑其他所有因素之后,再考虑是不是网络延迟

更多分析

•分析客户端程序

•分析网络延迟

解决方案

•客户端程序RBAR(Row-By-Agonizing-Row)

•分析网络硬件,TCP配置等

 

 

WRITELOG:日志管理系统等待日志刷新到磁盘时发送。往往说明io子系统的问题，1.把符合分散到多个数据库上或者缩小长事务。可以使用sys.dm_io_virtual_file_stats检查日志的io问题

这意味着

•等待将日志块flush到日志

避免望文生义

•不要一开始就以为是IO问题

•不要直接增加日志文件

更多分析

•查看sys.dm_io_virtual_file_stats

•查看LOGBUFFER等待,看是否存在对日志缓冲区的争抢

•查看日志所在磁盘的磁盘等待队列

•查看事务的平均大小

•查看是否有大量的页分裂(页分裂会导致大量日志)

将日志转移到更快的IO系统(一定要和数据分开)

增加事务的大小来避免大量日志写入(比如说批量写入)

删除没用的非聚集索引,来避免日志开销

修改索引键或使用填充哎减少页分裂

修改程序架构,将负载分布到多个数据库或服务器

 

MSQL_XP: sql server等待扩展存储过程完成时发送，检查扩展存储过程代码

LCK_M_XX:线程等待锁的分配，说明线程堵塞

这意味着：

•由于另一个线程对某个资源加锁，该线程不能对资源加不兼容的锁

避免望文生义

•不要以为锁是Root Cause

更多分析

•通过sys.dm_os_waiting_tasks来找到最开始被阻塞的线程，而阻塞该线程的原因可能是IO、网络、内存等

•使用阻塞进程报表来捕捉等待信息

解决方案基于最开始被阻塞进程的等待类型

一个查范围更新或扫描造成的锁升级

•如果可能，使用分区锁

•尝试创建索引，使得扫描变为非聚集索引查找

•将大批量更新事务分解成多个小事务

•尝试使用不同的隔离等级或是快照隔离

•避免不必要的锁

读写不应该互相阻塞，可以尝试修改隔离等级或使用乐观并发

其它阻止事务释放锁的情况，寻找基本原因

 

IO_COMPLETION:等待io完成时出现，往往说明io问题

SOS_SCHEDULER_YIELD:在等待spinlock时发现可能会浪费很多cpu因此，线程确定自动让出cpu

这意味着

•线程用完4毫秒的时间片，主动放弃CPU

•存在旋锁

避免望文生义

•不一定是CPU问题（CPU问题往往体现在长Runnable队列或大量signal wait）

更多分析

•通过执行计划查看是否存在大量扫描

•查看等待类型

注意：该方式没有Resource_wait等待类型，因此一些查询等待类型的语句可能无法捕获

•无法在sys.dm_os_waiting_tasks中看到

 

PAGELATCH_XX:在访问page时出现（buf闩）的等待。可能是热点页，GAM，SGAM，PFS可能会引起这个问题

这意味着

•等待访问内存中的数据文件页

•常见的是SH和EX

•SH意味着页将被读取

•EX意味着页会被修改

避免望文生义

•不要额PAGEIOLATCH_XX混淆

•不意味着需要增加IO和内存

更多分析

•找出等待页的类型

 

 

SELECT TOP 50 * 

FROM sys.dm_os_waiting_tasks

 

SELECT wt.session_id, wt.wait_type, wt.wait_duration_ms

, s.name AS schema_name

, o.name AS object_name

, i.name AS index_name

FROM sys.dm_os_buffer_descriptors bd

JOIN (

    SELECT  session_id, wait_type,wait_duration_ms,resource_description,

PARSENAME(replace(resource_description,':','.'),1) database_id,

PARSENAME(replace(resource_description,':','.'),2) file_id,PARSENAME(replace(resource_description,':','.'),3)page_id

    FROM sys.dm_os_waiting_tasks

    WHERE wait_type LIKE 'PAGELATCH%'

)wt

ON bd.database_id = wt.database_id

AND bd.file_id = wt.file_id

AND bd.page_id = wt.page_id

JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id

JOIN sys.partitions p ON au.container_id = p.partition_id

JOIN sys.indexes i ON  p.index_id = i.index_id AND p.object_id = i.object_id

JOIN sys.objects o ON i.object_id = o.object_id

JOIN sys.schemas s ON o.schema_id = s.schema_id

 

处理方法：http://sqlcat.com/sqlcat/b/technicalnotes/archive/2009/09/22/resolving-pagelatch-contention-on-highly-concurrent-insert-workloads-part-1.aspx

 

最经典的TempDB争抢

•添加TempDB文件

•4个起,如果还有争抢,再增加4个

•启用跟踪标记1118

•减少TempDB的使用(比如说减少临时表)

•减少临时表的使用,不要显式的drop掉临时表(非BOOT页TempDB争抢)(高继伟)

过多的页分裂

•修改索引键(经典的GUID)

•避免更新太长的记录

•使用填充因子

插入递增表产生插入热点

•使用随机或组合键并结合填充因子来减少页分裂

•修改程序架构,插入分布到多个表、数据库、服务器中

 

 

 

 

BACKUPXX:

可能是

•BACKUPBUFFER

•BACKUPIO

•BACKUPTHREAD

这意味着

•等待数据或是数据的缓存

•读取数据库文件

•第三种通常是由于数据或磁盘的填0初始化

更多分析

•第一种,备份是基于慢速的的IO系统或网络,或是远程服务器的IO系统缓慢

•数据文件所在的IO系统缓慢

•会产生PREEMPTIVE_OS_WRITEFILEGATHER等待

 

OLEDB

这意味着

•使用了OLE DB机制

避免望文生义

•不要直接猜想是因为使用了链接服务器

更多分析

•等待OLE DB的查询是什么

•如果使用了链接服务器，那么什么导致了链接服务器的延时

可能的解决方案

•DBCC CHECKDB这类内部使用了OLEDB的命令

•很多DMV内部使用了OLEDB，因此可能是一些监测工具导致的问题

•低性能的链接服务器

 

 

LATCH_XX:非buf闩的等待（闩分为2种，buf闩和非buf闩，SQL Server 2008内部剖析与故障分析一书的6.6中有详细介绍）

PREEMPTIVE_XX:切换到抢占模式通过windows调度做相关操作时出现的等待

PREEMPTIVE_OS_XX

这意味着

•线程直接调用OS

•线程切换到抢占式调度模式

•线程的状态是RUNNING，而不是SUSPENDED

更多分析

•SQL Server 2012中有194个该类事件

•这类事件文档非常少

•一个小技巧，在MSDN搜索PREEMPTIVE_OS_XX中的XX部分，这部分内容其实就是WINDOWS API

可能的解决方案

•要基于不同种类的等待类型来判断

 

PREEMPTIVE_OS_CREATEFILE

这意味着

•线程会调用Windows来创建文件

•如果使用了FileStream，当FileStream创建新的NTFS文件时，可能会导致该问题

更多分析

•查看不断增长的等待时间

可能的解决方案

•承载FileStream的IO性能不行

•使用FileStream的IO负载过重

•参考WIN32 API:http://msdn.microsoft.com/en-us/library/windows/desktop/aa363858(v=vs.85).aspx

 

PREEMPTIVE_OS_WRITEFILEGATHER

这意味着

•线程会调用Windows来写入文件

避免望文生义

•不要只认为是IO问题

更多分析

•正在进行的数据库操作

•比如说还原数据库，数据库文件的创建、增长和自动增长

可能的解决办法

•在还原数据库或日志增长时对日志填零初始化

•对数据文件填零初始化

•启用快速文件初始化

•在进行数据库还原时，不要删除现有的文件

•参考WIN32 API:http://msdn.microsoft.com/en-us/library/windows/desktop/aa365749(v=vs.85).aspx

 

PREEMPTIVE_OS_WRITEFILEGATHER

这意味着

•一个线程调用Windows来等待同步对象的改变

•通常和NETWORK_IO以及ASYNC_NETWORK_IO一起出现

更多分析

•按照ASYNC_NETWORK_IO处理方式处理

•查看是否存在事务日志复制

可能的解决方案

•ASYNC_NETWORK_IO

•当APP服务器和数据库服务器在同一台时，使用共享内存

•当和NETWORK_IO一起时，很可能是事务日志复制

 

PREEMPTIVE_OS_DBMIRRORXX

示例

•DBMIRROR_EVENT_QUEUE

•DBMIRROR_SEND

•DBMIRRORING_CMD

•DBMIRROR_DBR_MUTEX

这意味着

•等待镜像资源

避免望文生义

•不要仅仅直接移除镜像或选择高性能模式

更多分析

•分析DBMIRROR_DBR_MUTEX的平均等待时间

可能的原因

•如果DBMIRROR_DBR_MUTEX的等待时间过多，则可能是由于镜像的数据库过多，或太多需要镜像的内容

•可能是由于常见的系统瓶颈

 

SQLTRACE_XX

这意味着

•线程等待写入SQLTRACE文件

避免望文生义

•不一定非要停止SQLTRACE

更多分析

•使用sys.traces和sys.fn_trace_geteventinfo是否跟踪了一些非常频繁的事件

•分析跟踪文件所在所在的IO

可能的原因

•跟踪捕获了太多的事件

•行集没有快速消费结果集

•第三方产品在扫描跟踪

 

LATCH_XX

这意味着

•存在非页闩锁

更多分析

•使用sys.dm_os_latch_stats来分析哪一个闩锁等待时间过长

•和其它同时发生的等待类型结合查看

•比如说CXPACKET和LATCH_EX与ACCESS_METHODs_SCAN_RANGE_GENERATOR往往意味着存在大量扫描

可能的解决方案

•这类锁是没有文档支持的，需要自行Google

•接下来探讨几页常见的锁

•微软白皮书：http://sqlcat.com/sqlcat/b/whitepapers/archive/2011/07/05/diagnosing-and-resolving-latch-contention-on-sql-server.aspx

 

 

THREADPOOL:等待可用的workthreads

DBMIRROR_DBM_MUTEX:发送buffer时出现的等待，可能是镜像回话过多

RESOUCE_SEMAPHORE:查询语句等待分配内存时出现，可能是查询语句过大或者需求的内存过大。

MSQL_DG: sql server等待分布式查询完成时出现，说明分布式查询有问题

RESOUCE_SEMAPHORE_QUERY_COMPLIE:过大的并发编译，主要是重编译和无缓冲plan造成

MSSEARCH:全文查询等待

转载于:https://www.cnblogs.com/heqianjin/p/5698661.html