sql server 常见等待状态分析

ゞ浴缸里的玫瑰 2022-08-08 06:24 12阅读 0赞

1、同一个症状可能是由于多种不同的原因
      3 假设观测到IO延迟(Avg. Disk Sec/read>50)
    
      7 可能是由于IO子系统无法满足负载
      8 可能是由于同步IO子系统镜像
      9 可能是由于网络延迟
     10 可能是IO子系统配置不当
     11 可能是内存压力
     12 过多的计划缓存
     13 SQL Server之外程序引起的压力
     14 大量即席查询
     15 不准确的执行计划
     16 可能是其他原因
     17 -----------------------------------------------
     18 用户调度模式（UMS）
     19 
     20 
     21 合作式调度（除非线程放弃CPU，否则永远不会剥夺线程的CPU）
     22 •
     23 减少Context Switch
     24 允许在沙盒外的非合作式调度
     25 
     26 -----------------------连接池--------------------------
     27 客户端程序通过TDS连接到SQL Server，TDS基于TCP协议
     28 TCP协议需要经历三次握手，如果每次都要三次握手的话，开销巨大
     29 所以当程序中Close连接时，只是逻辑上断开，并不会物理上断开，直到
     30 超时
     31 连接池是客户端概念，连接字符串是连接池的标识符，同样的连接，如
     32 果连接字符串甚至是多一个空格，也会被认为是不同连接池
     33 
     34 
     35 --------------------------------------------------------
     36 Scheduler
     37 对应逻辑CPU
     38 Task的容器
     39 任务执行的基本单元
     40 • Worker
     41 • Task
     42 对应的DMV
     43 
     44 sys.dm_os_schedulers
     45 sys.dm_os_tasks
     46 sys.dm_os_workers
     47 
     48 -------------------------------------------------------------
     49 资源等待类型
     50 
     51 并行：CXPACKET
     52 Buffer：PAGEIOLATCH_X
     53 非Buffer：LATCH_X
     54 I/O：ASYNC_IO_COMPITION；IO_COMPITION
     55 CPU：SOS_SCHEDULER_YIELD
     56 日志：WRITELOG&LOGBUFFER
     57 锁阻塞：LCK_*
     58 内存：RESOURCE_SEMAPHORE
     59 外部：PREEMPTIVE_XXX
     60 备份：DISKIO_SUSPENDED
     61 SQL OS：THREADPOOL
     62 
     63 -----------------------------------------------------------
     64 症状和解决方案-CXPACKET
     65 这意味着
     66 •
     67 •
     68 •
     69 •
     70 发生了并行操作
     71 发生了并行执行,或是并行执行中的一个worker被阻塞
     72 不要将服务器级别的MAXDOP设置为1,也就是禁用并行
     73 是否存在PAGEIOLATCH_SH等待,这意味着大范围SCAN
     74 •
     75 同时也观察一下ACCESS_METHODS_DATASET_PARENT Latch和
     76 ACCESS_METHODS_SCAN_RANGE_GENERATOR LATCH
     77 不要望文生义
     78 更多分析症状和解决方案-PAGEIOLATCH_XX
     79 •
     80 •
     81 检查导致CXPACKET的请求来查看执行计划是否合理
     82 其中某个并行线程执行时间过长(也就是其中某个线程不是由于CXPACKET
     83 阻塞)
     84 
     85 症状和解决方案-CXPACKET
     86 可能的原因
     87 •
     88 •
     89 •
     90 •
     91 仅仅是由于发生了并行
     92 由于缺少聚集索引或是不准确的执行计划导致扫描
     93 过期的统计信息
     94 Distinct结果集无法预估执行计划,导致不合适执行计划,从而产生CXPACKET
     95 等待,解决办法是临时表(王成辉)
     96 确保统计信息是最新的,并且存在适当的索引
     97 设置查询的MAXDOP
     98 考虑MAXDOP=NUMA的物理CPU核数
     99 在考虑到负载类型(混合)的前提下,再设置实例的MAXDOP
    100 考虑设置将”cost threshold parallelism”的值设置的更高
    101 如果的确是问题
    102 •
    103 •
    104 •
    105 •
    106 •
    107 --------------------------------------------------------------
    108 症状和解决方案-PAGEIOLATCH_XX
    109 这意味着:
    110 
    111 等待页由磁盘被读取到
    112 最常见的是SH和EX
    113 
    114 SH意味着页被用于读取
    115 EX意味着页会被修改
    116 避免望文生义
    117 
    118 不要直接判断是IO系统和IO通道的问题
    119 决定哪个表/索引被读取(通过DBCC Page)
    120 使用sys.dm_io_virtual_file_stats和Avg Disk Secs/Read性能计数器判断IO
    121 对应的CXPACKET等待,是否存在并行扫描
    122 通过执行计划查看并行扫描
    123 通过执行计划查看是否存在隐式转换(可能导致扫描)
    124 通过Page Life Expectancy查看是否存在缓存区内存压力
    125 更多分析
    126 
    127 症状和解决方案-PAGEIOLATCH_XX
    128 创建非聚集索引来减少扫描
    129 更新统计信息
    130 将受影响的数据转移到更快的IO子系统
    131 考虑增加内存
    132 --------------------------------------------------------------------
    133 症状和解决方案-ASYNC_NETWORK_IO
    134 这意味着
    135 
    136 SQL Server等待客户端获取数据的ACK反馈
    137 不要简单认为是网络延迟
    138 只有再考虑其他所有因素之后,再考虑是不是网络延迟
    139 分析客户端程序
    140 分析网络延迟
    141 客户端程序RBAR(Row-By-Agonizing-Row)
    142 分析网络硬件,TCP配置等
    143 
    144 -------------------------------------------------------------------
    145 症状和解决方案-WRITELOG
    146 这意味着
    147 
    148 等待将日志块flush到日志
    149 不要一开始就以为是IO问题
    150 不要直接增加日志文件
    151 查看sys.dm_io_virtual_file_stats
    152 查看LOGBUFFER等待,看是否存在对日志缓冲区的争抢
    153 查看日志所在磁盘的磁盘等待队列
    154 查看事务的平均大小
    155 查看是否有大量的页分裂(页分裂会导致大量日志)
    156 避免望文生义
    157 更多分析
    158 
    159 症状和解决方案-WRITELOG
    160 将日志转移到更快的IO系统(一定要和数据分开)
    161 增加事务的大小来避免大量日志写入(比如说批量写入)
    162 删除没用的非聚集索引,来避免日志开销
    163 修改索引键或使用填充减少页分裂
    164 修改程序架构,将负载分布到多个数据库或服务器
    165 
    166 -----------------------------------------------------------------
    167 症状和解决方案-PAGELATCH_XX
    168 这意味着
    169 
    170 等待访问内存中的数据文件页
    171 常见的是SH和EX
    172 
    173 SH意味着页将被读取
    174 EX意味着页会被修改
    175 避免望文生义
    176 
    177 不要同PAGEIOLATCH_XX混淆
    178 不意味着需要增加IO和内存
    179 找出等待页的类型
    180 更多分析
    181 
    182 症状和解决方案-PAGELATCH_XX
    183 最经典的TempDB争抢
    184 
    185 添加TempDB文件
    186 4个起,如果还有争抢,再增加4个
    187 启用跟踪标记1118
    188 减少TempDB的使用(比如说减少临时表)
    189 减少临时表的使用,不要显式的drop掉临时表(非BOOT页TempDB争抢)(高继伟)
    190 修改索引键(经典的GUID)
    191 避免更新太长的记录
    192 使用填充因子
    193 过多的页分裂
    194 插入递增表产生插入热点
    195 
    196 使用随机或组合键并结合填充因子来减少页分裂
    197 修改程序架构,插入分布到多个表、数据库、服务器中
    198 
    199 
    200 ----------------------------------------------------------
    201 
    202 症状和解决方案-LCK_M_XX
    203 这意味着：
    204 
    205 由于另一个线程对某个资源加锁，该线程不能对资源加不兼容的锁
    206 不要以为锁是Root Cause
    207 通过sys.dm_os_waiting_tasks来找到最开始被阻塞的线程，而阻塞该线程的原
    208 因可能是IO、网络、内存等
    209 使用阻塞进程报表来捕捉等待信息
    210 避免望文生义
    211 更多分析
    212 
    213 症状和解决方案-LCK_M_XX
    214 解决方案基于最开始被阻塞进程的等待类型
    215 一个查范围更新或扫描造成的锁升级
    216 
    217 如果可能，使用分区锁
    218 尝试创建索引，使得扫描变为非聚集索引查找
    219 将大批量更新事务分解成多个小事务
    220 尝试使用不同的隔离等级或是快照隔离
    221 避免不必要的锁
    222 读写不应该互相阻塞，可以尝试修改隔离等级或使用乐观并发
    223 其它阻止事务释放锁的情况，寻找基本原因
    224 
    225 -------------------------------------------------------------------------
    226 症状和解决方案-
    227 SOS_SCHEDULER_YIELD
    228 这意味着
    229 
    230 线程用完4毫秒的时间片，主动放弃CPU
    231 存在自旋锁
    232 不一定是CPU问题（CPU问题往往体现在长Runnable队列或大量signal wait）
    233 通过执行计划查看是否存在大量扫描
    234 查看等待类型
    235 避免望文生义
    236 更多分析
    237 注意：该方式没有Resource_wait等待类型，因此一些查
    238 询等待类型的语句可能无法捕获
    239 
    240 无法在sys.dm_os_waiting_tasks中看到
    241 
    242 -------------------------------------------------------------------------
    243 
    244 症状和解决方案-BACKUPXX
    245 可能是
    246 
    247 BACKUPBUFFER
    248 BACKUPIO
    249 BACKUPTHREAD
    250 等待数据或是数据的缓存
    251 读取数据库文件
    252 第三种通常是由于数据或磁盘的填0初始化
    253 第一种,备份是基于慢速的的IO系统或网络,或是远程服务器的IO系统缓慢
    254 数据文件所在的IO系统缓慢
    255 会产生PREEMPTIVE_OS_WRITEFILEGATHER等待
    256 这意味着
    257 更多分析
    258 第一种,备份是基于慢速的的IO系统或网络,或是远程服务器的IO系统缓慢
    259 数据文件所在的IO系统缓慢
    260 会产生PREEMPTIVE_OS_WRITEFILEGATHER等待
    261 
    262 
    263 -------------------------------------------------------------------------
    264 症状和解决方案-OLEDB
    265 这意味着
    266 
    267 使用了OLE DB机制
    268 不要直接猜想是因为使用了链接服务器
    269 等待OLE DB的查询是什么
    270 如果使用了链接服务器，那么什么导致了链接服务器的延时
    271 避免望文生义
    272 更多分析
    273 可能的解决方案
    274 
    275 DBCC CHECKDB这类内部使用了OLEDB的命令
    276 很多DMV内部使用了OLEDB，因此可能是一些监测工具导致的问题
    277 低性能的链接服务器
    278 
    279 -----------------------------------------------------------------------
    280 症状和解决方案-PREEMPTIVE_OS_XX
    281 这意味着
    282 
    283 线程直接调用OS
    284 线程切换到抢占式调度模式
    285 线程的状态是RUNNING，而不是SUSPENDED
    286 SQL Server 2012中有194个该类事件
    287 这类事件文档非常少
    288 一个小技巧，在MSDN搜索PREEMPTIVE_OS_XX中的XX部分，这部分内容其
    289 实就是WINDOWS API
    290 要基于不同种类的等待类型来判断
    291 
    292 -----------------------------------------------------------------------
    293 症状和解决方案-
    294 PREEMPTIVE_OS_CREATEFILE
    295 这意味着
    296 
    297 线程会调用Windows来创建文件
    298 如果使用了FileStream，当FileStream创建新的NTFS文件时，可能会导致该问
    299 题
    300 更多分析
    301 
    302 查看不断增长的等待时间
    303 可能的解决方案
    304 
    305 承载FileStream的IO性能不行
    306 使用FileStream的IO负载过重
    307 参考WIN32 API:http://msdn.microsoft.com/en-us/library/windows/desktop/aa363858(v=vs.85).aspx
    308 
    309 ------------------------------------------------------------------------------
    310 症状和解决方案-
    311 PREEMPTIVE_OS_WRITEFILEGATHER
    312 这意味着
    313 
    314 线程会调用Windows来写入文件
    315 不要只认为是IO问题
    316 正在进行的数据库操作
    317 比如说还原数据库，数据库文件的创建、增长和自动增长
    318 在还原数据库或日志增长时对日志填零初始化
    319 对数据文件填零初始化
    320 启用快速文件初始化
    321 在进行数据库还原时，不要删除现有的文件
    322 避免望文生义
    323 更多分析
    324 可能的解决办法
    325 •
    326 参考WIN32 API:http://msdn.microsoft.com/en-us/library/windows/desktop/aa365749(v=vs.85).aspx
    327 
    328 ---------------------------------------------------------------------------------
    329 症状和解决方案-
    330 PREEMPTIVE_OS_WRITEFILEGATHER
    331 这意味着
    332 
    333 一个线程调用Windows来等待同步对象的改变
    334 通常和NETWORK_IO以及ASYNC_NETWORK_IO一起出现
    335 按照ASYNC_NETWORK_IO处理方式处理
    336 查看是否存在事务日志复制
    337 更多分析
    338 可能的解决方案
    339 
    340 ASYNC_NETWORK_IO
    341 当APP服务器和数据库服务器在同一台时，使用共享内存
    342 当和NETWORK_IO一起时，很可能是事务日志复制
    343 
    344 --------------------------------------------------------------------------------
    345 症状和解决方案-
    346 PREEMPTIVE_OS_DBMIRRORXX
    347 示例
    348 
    349 DBMIRROR_EVENT_QUEUE
    350 DBMIRROR_SEND
    351 DBMIRRORING_CMD
    352 DBMIRROR_DBR_MUTEX
    353 等待镜像资源
    354 这意味着
    355 避免望文生义
    356 
    357 不要仅仅直接移除镜像或选择高性能模式
    358 分析DBMIRROR_DBR_MUTEX的平均等待时间
    359 如果DBMIRROR_DBR_MUTEX的等待时间过多，则可能是由于镜像的数据库过多，
    360 或太多需要镜像的内容
    361 可能是由于常见的系统瓶颈
    362 
    363 -------------------------------------------------------------------------------
    364 症状和解决方案-SQLTRACE_XX
    365 这意味着
    366 
    367 线程等待写入SQLTRACE文件
    368 不一定非要停止SQLTRACE
    369 使用sys.traces和sys.fn_trace_geteventinfo是否跟踪了一些非常频繁的事件
    370 分析跟踪文件所在的IO
    371 跟踪捕获了太多的事件
    372 行集没有快速消费结果集
    373 第三方产品在扫描跟踪
    374 
    375 --------------------------------------------------------------------------------
    376 症状和解决方案-LATCH_XX
    377 这意味着
    378 
    379 存在非页闩锁
    380 使用sys.dm_os_latch_stats来分析哪一个闩锁等待时间过长
    381 和其它同时发生的等待类型结合查看
    382 
    383 比如说CXPACKET和LATCH_EX与ACCESS_METHODs_SCAN_RANGE_GENERATOR往
    384 往意味着存在大量扫描
    385 更多分析
    386 可能的解决方案
    387 
    388 这类锁是没有文档支持的，需要自行Google
    389 接下来探讨几页常见的锁
    390 微软白皮书：
    391 http://sqlcat.com/sqlcat/b/whitepapers/archive/2011/07/05/diagnosing-and-resolving-latch-contention-on-sql-server.aspx
    392 
    393 
    394 ----------------------------------------------------------------------------------
    395 症状和解决方案-ACCESS_METHOD_XX
    396 可能是
    397 
    398 ACCESS_METHOD_DATASET_PARENT
    399 ACCESS_METHOD_SCAN_RANGE_GENERATOR
    400 ACCESS_METHOD_HOBT_VIRTUAL_ROOT
    401 前两个用于并行扫描，非常常见
    402 第三种用于页根节点分裂
    403 这意味着
    404 更多分析
    405 
    406 分析执行计划缓存，看是否存在大量扫描和HASH操作
    407 查看存在大量页分裂的索引
    408 参考处理并行执行计划
    409 想办法减少页分裂
    410 -------------------------------------------------------------------------------
    411 症状和解决方案-FGCB_ADD_REMOVE
    412 闩锁
    413 这意味着
    414 
    415 在添加、删除、增长、收缩文件组中的文件时，对文件组控制块(FGCB)所加
    416 的闩锁
    417 分析文件组的自动增长设置
    418 文件的自动增长过低，导致频繁的文件增长
    419 数据文件过小，导致需要增长
    420 
    421 ---------------------------------------------------------------------------
    422 症状和解决方案-DBCC_XX 闩锁
    423 可能是
    424 
    425 DBCC_MULTIOBJECTSCANNER（最常见）
    426 DBCC_FILE_CHECK_OBJECT
    427 DBCC_FPS_STATUS
    428 DBCC_CHECK_AGGREGATE
    429 DBCC_OBJECT_METADATA
    430 不要因为这类就停止一致性检查
    431 不需要，这些都是DBCC CHECKDB的内部事件
    432 
    433 -------------------------------------------------------------------------
    434 症状和解决方案-Miscellaneous
    435 EXECSYNC
    436 
    437 并行线程在运行时交换信息
    438 等待非数据文件IO完成，填零日志文件或写入备份集合
    439 等待非数据文件IO完成，比如说在回滚和事务日志复制中读取日志
    440 等待非buffer IO完成，比如说创建PFS页
    441 等待Worker线程可用，大量的并行计划可能用完Worker
    442 
    443 症状和解决方案-Miscellaneous
    444 RESOURCE_SEMAPHORE
    445 
    446 查询等待被授予内存，比如说SORT需要内存
    447 通常意味着并发且大量消耗内存的查询
    448 等待扩展存储过程完成
    449 当flush日志时，等待log buffer可用