笔记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
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可用

 

posted @ 2013-08-04 21:16 桦仔 阅读(...) 评论(...)  编辑 收藏