sql server 常见等待状态分析

   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 在考虑到负载类型(混合)的前提下,再设置实例的MAXDOP100 考虑设置将”cost threshold parallelism”的值设置的更高101 如果的确是问题102 •103 •104 •105 •106 •107 --------------------------------------------------------------108 症状和解决方案-PAGEIOLATCH_XX109 这意味着:110 111 等待页由磁盘被读取到112 最常见的是SH和EX113 114 SH意味着页被用于读取115 EX意味着页会被修改116 避免望文生义117 118 不要直接判断是IO系统和IO通道的问题119 决定哪个表/索引被读取(通过DBCC Page)120 使用sys.dm_io_virtual_file_stats和Avg Disk Secs/Read性能计数器判断IO121 对应的CXPACKET等待,是否存在并行扫描122 通过执行计划查看并行扫描123 通过执行计划查看是否存在隐式转换(可能导致扫描)124 通过Page Life Expectancy查看是否存在缓存区内存压力125 更多分析126 127 症状和解决方案-PAGEIOLATCH_XX128 创建非聚集索引来减少扫描129 更新统计信息130 将受影响的数据转移到更快的IO子系统131 考虑增加内存132 --------------------------------------------------------------------133 症状和解决方案-ASYNC_NETWORK_IO134 这意味着135 136 SQL Server等待客户端获取数据的ACK反馈137 不要简单认为是网络延迟138 只有再考虑其他所有因素之后,再考虑是不是网络延迟139 分析客户端程序140 分析网络延迟141 客户端程序RBAR(Row-By-Agonizing-Row)142 分析网络硬件,TCP配置等143 144 -------------------------------------------------------------------145 症状和解决方案-WRITELOG146 这意味着147 148 等待将日志块flush到日志149 不要一开始就以为是IO问题150 不要直接增加日志文件151 查看sys.dm_io_virtual_file_stats152 查看LOGBUFFER等待,看是否存在对日志缓冲区的争抢153 查看日志所在磁盘的磁盘等待队列154 查看事务的平均大小155 查看是否有大量的页分裂(页分裂会导致大量日志)156 避免望文生义157 更多分析158 159 症状和解决方案-WRITELOG160 将日志转移到更快的IO系统(一定要和数据分开)161 增加事务的大小来避免大量日志写入(比如说批量写入)162 删除没用的非聚集索引,来避免日志开销163 修改索引键或使用填充减少页分裂164 修改程序架构,将负载分布到多个数据库或服务器165 166 -----------------------------------------------------------------167 症状和解决方案-PAGELATCH_XX168 这意味着169 170 等待访问内存中的数据文件页171 常见的是SH和EX172 173 SH意味着页将被读取174 EX意味着页会被修改175 避免望文生义176 177 不要同PAGEIOLATCH_XX混淆178 不意味着需要增加IO和内存179 找出等待页的类型180 更多分析181 182 症状和解决方案-PAGELATCH_XX183 最经典的TempDB争抢184 185 添加TempDB文件186 4个起,如果还有争抢,再增加4个187 启用跟踪标记1118188 减少TempDB的使用(比如说减少临时表)189 减少临时表的使用,不要显式的drop掉临时表(非BOOT页TempDB争抢)(高继伟)190 修改索引键(经典的GUID)191 避免更新太长的记录192 使用填充因子193 过多的页分裂194 插入递增表产生插入热点195 196 使用随机或组合键并结合填充因子来减少页分裂197 修改程序架构,插入分布到多个表、数据库、服务器中198 199 200 ----------------------------------------------------------201 202 症状和解决方案-LCK_M_XX203 这意味着：204 205 由于另一个线程对某个资源加锁，该线程不能对资源加不兼容的锁206 不要以为锁是Root Cause207 通过sys.dm_os_waiting_tasks来找到最开始被阻塞的线程，而阻塞该线程的原208 因可能是IO、网络、内存等209 使用阻塞进程报表来捕捉等待信息210 避免望文生义211 更多分析212 213 症状和解决方案-LCK_M_XX214 解决方案基于最开始被阻塞进程的等待类型215 一个查范围更新或扫描造成的锁升级216 217 如果可能，使用分区锁218 尝试创建索引，使得扫描变为非聚集索引查找219 将大批量更新事务分解成多个小事务220 尝试使用不同的隔离等级或是快照隔离221 避免不必要的锁222 读写不应该互相阻塞，可以尝试修改隔离等级或使用乐观并发223 其它阻止事务释放锁的情况，寻找基本原因224 225 -------------------------------------------------------------------------226 症状和解决方案-227 SOS_SCHEDULER_YIELD228 这意味着229 230 线程用完4毫秒的时间片，主动放弃CPU231 存在自旋锁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 症状和解决方案-BACKUPXX245 可能是246 247 BACKUPBUFFER248 BACKUPIO249 BACKUPTHREAD250 等待数据或是数据的缓存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 症状和解决方案-OLEDB265 这意味着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_XX281 这意味着282 283 线程直接调用OS284 线程切换到抢占式调度模式285 线程的状态是RUNNING，而不是SUSPENDED286 SQL Server 2012中有194个该类事件287 这类事件文档非常少288 一个小技巧，在MSDN搜索PREEMPTIVE_OS_XX中的XX部分，这部分内容其289 实就是WINDOWS API290 要基于不同种类的等待类型来判断291 292 -----------------------------------------------------------------------293 症状和解决方案-294 PREEMPTIVE_OS_CREATEFILE295 这意味着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).aspx308 309 ------------------------------------------------------------------------------310 症状和解决方案-311 PREEMPTIVE_OS_WRITEFILEGATHER312 这意味着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).aspx327 328 ---------------------------------------------------------------------------------329 症状和解决方案-330 PREEMPTIVE_OS_WRITEFILEGATHER331 这意味着332 333 一个线程调用Windows来等待同步对象的改变334 通常和NETWORK_IO以及ASYNC_NETWORK_IO一起出现335 按照ASYNC_NETWORK_IO处理方式处理336 查看是否存在事务日志复制337 更多分析338 可能的解决方案339 340 ASYNC_NETWORK_IO341 当APP服务器和数据库服务器在同一台时，使用共享内存342 当和NETWORK_IO一起时，很可能是事务日志复制343 344 --------------------------------------------------------------------------------345 症状和解决方案-346 PREEMPTIVE_OS_DBMIRRORXX347 示例348 349 DBMIRROR_EVENT_QUEUE350 DBMIRROR_SEND351 DBMIRRORING_CMD352 DBMIRROR_DBR_MUTEX353 等待镜像资源354 这意味着355 避免望文生义356 357 不要仅仅直接移除镜像或选择高性能模式358 分析DBMIRROR_DBR_MUTEX的平均等待时间359 如果DBMIRROR_DBR_MUTEX的等待时间过多，则可能是由于镜像的数据库过多，360 或太多需要镜像的内容361 可能是由于常见的系统瓶颈362 363 -------------------------------------------------------------------------------364 症状和解决方案-SQLTRACE_XX365 这意味着366 367 线程等待写入SQLTRACE文件368 不一定非要停止SQLTRACE369 使用sys.traces和sys.fn_trace_geteventinfo是否跟踪了一些非常频繁的事件370 分析跟踪文件所在的IO371 跟踪捕获了太多的事件372 行集没有快速消费结果集373 第三方产品在扫描跟踪374 375 --------------------------------------------------------------------------------376 症状和解决方案-LATCH_XX377 这意味着378 379 存在非页闩锁380 使用sys.dm_os_latch_stats来分析哪一个闩锁等待时间过长381 和其它同时发生的等待类型结合查看382 383 比如说CXPACKET和LATCH_EX与ACCESS_METHODs_SCAN_RANGE_GENERATOR往384 往意味着存在大量扫描385 更多分析386 可能的解决方案387 388 这类锁是没有文档支持的，需要自行Google389 接下来探讨几页常见的锁390 微软白皮书：391 http://sqlcat.com/sqlcat/b/whitepapers/archive/2011/07/05/diagnosing-and-resolving-latch-contention-on-sql-server.aspx392 393 394 ----------------------------------------------------------------------------------395 症状和解决方案-ACCESS_METHOD_XX396 可能是397 398 ACCESS_METHOD_DATASET_PARENT399 ACCESS_METHOD_SCAN_RANGE_GENERATOR400 ACCESS_METHOD_HOBT_VIRTUAL_ROOT401 前两个用于并行扫描，非常常见402 第三种用于页根节点分裂403 这意味着404 更多分析405 406 分析执行计划缓存，看是否存在大量扫描和HASH操作407 查看存在大量页分裂的索引408 参考处理并行执行计划409 想办法减少页分裂410 -------------------------------------------------------------------------------411 症状和解决方案-FGCB_ADD_REMOVE412 闩锁413 这意味着414 415 在添加、删除、增长、收缩文件组中的文件时，对文件组控制块(FGCB)所加416 的闩锁417 分析文件组的自动增长设置418 文件的自动增长过低，导致频繁的文件增长419 数据文件过小，导致需要增长420 421 ---------------------------------------------------------------------------422 症状和解决方案-DBCC_XX 闩锁423 可能是424 425 DBCC_MULTIOBJECTSCANNER（最常见）426 DBCC_FILE_CHECK_OBJECT427 DBCC_FPS_STATUS428 DBCC_CHECK_AGGREGATE429 DBCC_OBJECT_METADATA430 不要因为这类就停止一致性检查431 不需要，这些都是DBCC CHECKDB的内部事件432 433 -------------------------------------------------------------------------434 症状和解决方案-Miscellaneous435 EXECSYNC436 437 并行线程在运行时交换信息438 等待非数据文件IO完成，填零日志文件或写入备份集合439 等待非数据文件IO完成，比如说在回滚和事务日志复制中读取日志440 等待非buffer IO完成，比如说创建PFS页441 等待Worker线程可用，大量的并行计划可能用完Worker442 443 症状和解决方案-Miscellaneous444 RESOURCE_SEMAPHORE445 446 查询等待被授予内存，比如说SORT需要内存447 通常意味着并发且大量消耗内存的查询448 等待扩展存储过程完成449 当flush日志时，等待log buffer可用