笔记300 宋沄剑 SQLSERVER等待的分析 2013-6-18
笔记300 宋沄剑 SQLSERVER等待的分析 2013-6-18
1 --宋沄剑 SQLSERVER等待的分析 2013-6-18 2 同一个症状可能是由于多种不同的原因 3 假设观测到IO延迟(Avg. Disk Sec/read>50) 4 5 6 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（如果线程没有资源等待，如果这时候cpu太高不够用，线程每4毫秒由running重新进入runnable，CPU等待类型为SOS_Schedule_yield） 231 存在自旋锁 232 不一定是CPU问题（CPU问题往往体现在长Runnable队列或大量signal wait） 233 通过执行计划查看是否存在大量扫描 234 查看等待类型 235 避免望文生义 236 更多分析 237 注意：该方式没有Resource_wait等待类型，因此一些查询等待类型的语句可能无法捕获 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可用
https://www.cnblogs.com/shanksgao/p/5068891.html
自旋锁(Spin lock)供线程探测数据是否可以访问,
Spin lock实现即一个Bit位(0或1),线程会一直探测内存中的这个Bit位以试图获得自旋锁,如果可以访问则访问,否则自旋,
如果几千次的探测仍无法访问则停下”休息”这个称作一次碰撞.但是在自旋的过程CPU负荷状态,因此也就造成CPU资源白白浪费.
f
