笔记64-徐 其他资源等待

笔记64-徐  其他资源等待

  1 --其他资源等待
  2 
  3 --除了上面说的这些最常见的资源等待问题,一个用户任务在某些情况下,还可能
  4 --等待下面这些资源
  5 
  6 --1、LATCH_X
  7 --在SQL里,除了同步数据页面访问的latch(buffer latch),还有很多其他内部
  8 --资源要同步。在这些资源上也会出现latch。如果SQL处理得正常,这些latch的申请
  9 --和释放是非常快的,用户应该不会看到这种等待。但是在一些不正常的情况下,会
 10 --看到任务在等待某个latch资源。常见的原因有:
 11 
 12 --(1)某个先前的任务出现了访问越界(access violation)异常。SQL强行终止了
 13 --这个任务,但是没能完全将他所申请的所有资源都释放干净,使得某个或者某些latch
 14 --成为无主孤儿。后面任务要申请同样的latch,都会被阻塞住
 15 
 16 --这类问题判断起来比较容易,只要打开SQL日志文件(errorlog),看看出问题之前
 17 --是否有过access violation问题发生。但是从用户层面上,是没有办法命令SQL申请
 18 --或释放某个latch资源的。一般只有通过重启SQL服务才能解决问题
 19 
 20 
 21 --(2)SQL同时发生了其他资源瓶颈,例如:内存,线程调度,磁盘等,而latch等待
 22 --只不过是一个衍生的等待
 23 
 24 --(3)当某个数据库文件空间用尽,做自动增长的时候,同一个时间点只能有一个用户
 25 --任务可以做文件自动增长动作,其他任务必须等待。这时候也会出现latch资源的等待
 26 
 27 
 28 --(4)在一些特殊情况下,有可能是SQL自己没有处理好并发同步,没有使用比较优化
 29 --的算法,使得用户任务比较容易遇到等待。SQL的一些补丁,就曾经修复过这类问题
 30 
 31 
 32 --所以当看到SQL里的任务出现大量latch等待时,往往是由其他问题衍生而来。
 33 --首先需要检查SQL是否在健康运行,是否先前已经有过任何异常发生,是否有其他
 34 --资源瓶颈。只有保证SQL在健康运行,才能谈论这种latch等待。将SQL升级到
 35 --最新版本,也是一个推荐的做法
 36 
 37 
 38 
 39 
 40 --2、ASYNC_NETWORK_IO(NETWORK_IO)
 41 --当SQL要返回数据结果集给客户端的时候,会先将结果集填充到输出缓存中(output cache)
 42 --与此同时,网络层会开始将输出缓存里的数据打包,由客户端接收。如果SQL返回结果集
 43 --的速度比客户端接收结果集的速度要快,会出现SQL已经把后面的结果集准备好,但是
 44 --输出缓存里前面的数据还没有传完,SQL没有地方放新数据结果的情况。这时候,任务
 45 --就会进入ASYNC_NETWORK_IO这个等待状态(SQL2000的时候叫NETWORK_IO)
 46 
 47 --这个等待状态是比较容易出现的。要强调以下几点:
 48 --(1)这个等待状态并不意味着SQL自身性能有问题,而是说明,客户端没有及时把数据
 49 --取走。调整SQL这边的配置是不会有什么大的帮助的
 50 
 51 
 52 --(2)网络层的瓶颈当然是问题的一个可能原因
 53 --如果网络层的确出现瓶颈,要首先考虑的问题应该是:为什麽SQL需要向客户端传输这麽大量
 54 --的结果集?对大部分用户来讲,一个成千上万行的结果集对它们没有任何意义,没有人
 55 --会把这么大的结果集从头到尾看一遍,最多也就只看前面的几十行。所以程序设计是否
 56 --合理?有没有设计合理的逻辑,将客户真正需要的数据集返回?
 57 
 58 
 59 --很多程序员没有意识到的是:返回大结果集对应用程序的影响往往会大于对SQL的影响。
 60 --SQL是一个为处理大数据操作专门做过优化的服务,返回几万行,甚至几十万行的结果集
 61 --对SQL来讲,不是一件很吃力的事情。但是对应用程序来讲就不是这麽回事。应用程序
 62 --一般不会在这方面做特殊优化。想象一个WEB应用程序,如果同时有十几个,或者几十个
 63 --用户都在从SQL上要求返回几十万行的结果集,而在应用层需要将这些结果缓存在内存里
 64 --处理,最后展示给用户,那应用服务自己的性能和健康就很难保证了
 65 
 66 --(3)应用程序端的性能问题,也会导致SQL里的ASYNC_NETWORK_IO等待
 67 
 68 --SQL网络层将结果集打包发给客户端以后,要等到客户端确认收到,才会接着发下一个包
 69 --如果客户端确认得很慢,SQL也不得不慢慢地发。这其实是ASYNC_NETWORK_IO等待最常见
 70 --的原因
 71 
 72 --那么客户端为什麽会确认得很慢呢?常见原因有:客户端应用有意只取开头的一段数据
 73 --而不把数据全部取完,或者自己100%CPU了,或者自己hang住了,或者自己遇到了内存
 74 --或者磁盘瓶颈,运行得很慢
 75 
 76 --总之,遇到ASYNC_NETWORK_IO等待,要检查应用程序的健康状况,也要检查应用是否
 77 --有必要向SQL申请这么大的结果集返回
 78 
 79 
 80 --3、和内存相关的等待状态
 81 --前面讲到,当用户任务申请内存暂时申请不到时,会出现一些特殊的等待状态,常见的有
 82 --CMEMTHREAD
 83 --SOS_RESERVEDMEMBLOCKLIST
 84 --RESOURCE_SEMAPHORE_QUERY_COMPILE
 85 
 86 --如果在DMV里看到这些等待状态,就要开始确认SQL是否存在内存瓶颈,详见第六章
 87 
 88 
 89 --4、SQLTRACE_X
 90 --笔者已经反复提到过,对一个繁忙的SQL,开启SQL TRACE,尤其是直接开SQL SREVRE PROFILER
 91 --很可能对性能产生负面影响。所有的操作都要向跟踪报告,导致跟踪自己成为了一个瓶颈
 92 
 93 --在SQL2000之前,如果出现了跟踪瓶颈,会看到很多latch等待。SQL2005以后,特别加入了
 94 --以SQLTRACE_X开头的一组等待状态。当看到SQL经常有这样的等待时,除非迫不得已,
 95 --要立刻停止收集SQL TRACE,尤其是用SQL SERVER PROFILER收集跟踪这样的行为。
 96 --关于如何收集SQLTRACE,详见第14章
 97 
 98 
 99 
100 -----------------------------最后一道瓶颈:许多任务处于runnable状态-----------------------
101 
102 --如果您的SQL没有遇到以上的任何一个等待状态,那么恭喜您,你的SQL既没有遇到阻塞,也没有
103 --遇到内存,磁盘瓶颈,数据结果集也能够及时被客户端取走,各个用户任务跑得很欢。您的SQL
104 --系统要不就是负载比较轻,比较空闲,要不就是优化得比较好。对于一台比较繁忙的SQL,
105 --完全没有等待状态是很少见的
106 
107 --但是你不能高兴的太早,这样的SQL还可能会遇到最后一道瓶颈:有很多任务处于runnable状态
108 --可以运行,但是没有在运行。这样的状态,也会严重影响SQL的性能
109 
110 
111 SELECT [status] FROM sys.[dm_exec_requests]
112 SELECT [status] FROM sys.[sysprocesses]
113 --的status这一列,反映了当前所有任务的状态。如果你看到好几个任务的状态都是runnable,那
114 --就要严肃对待了。正常的SQL,哪怕非常繁忙,也不应该经常看见runnable的任务,连
115 --running状态的任务,都不应该很多
116 
117 
118 --如果SQL没有遇到线程调度问题,没有报出:17883 / 17884之类的警告,出现非常多的runnable
119 --任务通常有两种可能原因:
120 
121 --(1)SQL CPU使用率已经接近100%,真的是没有足够CPU资源来及时处理用户的并发任务
122 --如果通过性能监视器可以确认,SQL的CPU使用率非常高,超过80%,甚至90%,甚至
123 --接近100%,那么这些runnable的任务是因为用户发过来的请求太多,CPU来不及做完
124 --在SQL Schedule上积压了下来,这时候只能说,SQL已经超负荷运行了。解决的方法
125 --是找出最耗CPU资源语句或者应用,将其优化,或者减少负载。当然,在服务器上多加
126 --几个CPU也是快速解决问题的好选择。
127 
128 
129 
130 --(2)SQL CPU使用率并不是很高,小于50%
131 --这是一种令人很头痛的现象,SQL的CPU使用率不高,常常是在30%~50%之间。SQL
132 --也在运行,每秒钟甚至能够处理几百个批处理请求,但是用户反映,SQL没有正常
133 --的时候那么快。这时候检查
134 SELECT task_state FROM [sys].[dm_os_tasks]
135 SELECT * FROM [sys].[dm_os_tasks]
136 --state列,常常会发现很多任务的状态都是runnable
137 
138 --为什麽还有CPU资源,但是任务就是不能运行呢?这是因为SQL除了lock和latch以外
139 --还有一种更轻量级的同步资源:spin lock(自旋锁) 在SQL里,有些很快就能得到
140 --也很快会被释放,一般来讲不大会发生长时间等待的同步资源,SQL选择让线程在CPU
141 --上稍微等一会(所谓自旋),而不是将CPU资源让出来。由于资源很快能够得到,这样
142 --处理能够减少CPU上线程的切换,更有效率
143 
144 
145 --像latch一样,spin是一种很轻量级的资源,正常情况下不应该成为SQL的瓶颈。
146 --不过在SQL内部,自旋锁的种类还是挺多的。你可以运行下面语句,得到SQL
147 --在所有自旋锁上等待的次数
148 DBCC SQLPERF(spinlockstats)
149 
150 --在SQL2005上有一个比较著名的问题,就发生在自旋锁上,而且经常发生在64位
151 --的SQL上。当SQL的内存比较宽裕时,他会缓存很多执行计划,同时也缓存了很多
152 --的执行计划安全上下文。memory clerk里,用TokenAndPermUserStore表示。
153 --当这段内存比较大时,并发用户会容易遇到一种叫MUTEX互斥锁的自旋锁上的瓶颈
154 --下面的知识库文章对这个问题有所描述
155 --http://support.microsoft.com/kb/927396
156 
157 --这个问题只在安全上下文缓存得太多的时候才容易发生,所以定期运行下面语句
158 --可以有效地防止SQL遇到这个问题,而且对系统的整体性能,也没有什么坏影响
159 
160 DBCC freesystemcache(tokenandpermuserstore)
161 
162 --另外一种选择是以/T 4618 和/T 4610 这两个跟踪标志启动SQL,让SQL使用另外
163 --一种缓存管理机制,也能够有效避免问题发生
164 
165 
166 --据说,SQL2008对安全上下文的管理有了改进,这种自旋锁将不容易遇到
167 --其他的自旋锁到现在为止,还没有报告过显著问题
168 
169 
170 ----------------------------小结---------------------------------------
171 --讲完了所有可能发生的等待状态,下面来总结一下一个用户请求在其生命周期
172 --中,大致会经过哪些阶段,以及在各个阶段可能需要等待的资源
173 
174 --1、客户端向SQL发出请求指令,指令经过网络层,SQL接收到
175 --在这一步,如果指令比较长,或者比较多,客户端发指令的快慢会影响到SQL接收的速度
176 --网络传输速度也有影响。这也是为什麽把几百个小指令合并成几个大的批处理可能会对
177 --整体性能有帮助的原因,也能解释为什麽把应用程序和SQL安装在同一台机器上,有些
178 --时候(注意:不是总是)会对性能有所帮助。如果应用程序自己出现性能问题,在这一步
179 --也会对性能产生影响
180 
181 
182 
183 --2、SQL对收到的指令进行语法、语义检查、编译、生成新执行计划,或者找到缓存的执行计划
184 --重用
185 
186 --这一步需要用到的资源种类比较多
187 --CPU资源:做指令语法,语义检查,编译,生成新执行计划,都是要做计算的。所以这一步是
188 --消耗CPU资源比较多的地方,尤其是当指令本身比较复杂,牵涉的表数量很多,表结构复杂
189 --上面有很多索引时,SQL可能需要计算很多候选方案,才能决定一个比较合适的执行计划
190 
191 
192 --如果这一步遇到瓶颈,会出现CPU使用率高,SQLOS SCHEDULER繁忙的现象
193 
194 --内存:一般短小的语句,这一步需要使用的内存不会很多。但是如果语句很长,例如
195 --里面有个非常非常长的in子句,或者一个批处理是由几万个,甚至几十万个语句组成。
196 --那么这一步要花的内存有可能也会很大。而这部分内存,主要是stolen内存,在32位
197 --的SQL里是一块比较小的区域
198 
199 --如果这一步内存紧张,有可能会出现下面这些等待状态
200 --cmemthread
201 --sos_reservedmemblocklist
202 --resource_semaphore_query_compile
203 
204 --当然也可能直接报告 701 错误
205 
206 
207 --表格上的架构锁(schema lock)。当一条指令在编译的时候,他要防止别人对他要
208 --访问的对象(表,视图,存储过程等)进行架构上的修改。所以在这一步会有一些架构锁
209 --产生。如果同时有大量的并发用户在做一样的编译动作,或者有一个人在对象上申请了
210 --级别很高的锁,可能在指令编译的时候就遇到阻塞
211 
212 
213 --下面是一个压力测试下遇到的问题。并发用户在同时运行一个存储过程,而这个存储过程
214 --由于设计上的缺陷,每次运行都要进行编译,不能重用执行计划。编译的时候在存储过程
215 --上会申请架构锁,从而造成了阻塞
216 
217 
218 --在SQL确认是否有现成的执行计划可用时,要在内存中进行搜索。这时候可能会有一些
219 --自旋锁
220 
221 
222 --3、运行指令
223 --在得到执行计划以后,就进入运行阶段。在这个阶段,用到的资源是最多的。一般来讲
224 --在这一步花的时间也是最长的。运行一条指令,SQL要做很多事情:
225 
226 --(1)SQL首先为指令的运行申请内存
227 --需要内存大小和指令的长度,复杂度有关。小的指令可能只要几KB,几十KB或者几百KB
228 --超大的指令可能需要几MB甚至十几MB。如果同时有很多复杂的指令在执行,可能新的
229 --指令在申请内存上会遇到困难。这时候会看到一些以resource_semaphore_开头的等待状态
230 
231 
232 
233 --(2)如果发现要访问的数据不在内存里,要将数据从磁盘读到内存中,如果发现内存里
234 --没有足够的空闲页面存放所有数据,还要做一些内存整理和paging动作(换页动作)
235 --腾出足够的空间放数据
236 --这一步是挺花时间的。如果要访问的数据事先都缓存在内存里,这一步就不需要做了
237 --可以省下大把时间,这就是为什麽有些语句第一次运行会比第二次运行慢很多的原因
238 --也能解释为什麽一个内存资源不足的SQL性能有明显下降
239 
240 --在这一步的等待状态,通常是PAGEIOLATCH_X 。这时候也会观察到其他表明内存和磁盘
241 --繁忙的现象
242 
243 --(3)按照执行计划,扫描或者seek内存中的数据页面,将指令需要处理的记录找出来
244 --在这一步,SQL要申请各式各样的锁,以实现事务隔离。所以这个动作很容易遇到阻塞
245 --问题。等待状态一般是以LCK_X开头的那些
246 
247 
248 --(4)指令可能还要做一些联接或者计算的工作(sum ,max, sort等)
249 --这些动作要使用的资源主要是CPU。如果这方面比较复杂,或者选取的执行计划不够
250 --优化,导致计算量庞大,而指令没有遇到其他资源等待,那么会看到SQL CPU使用率
251 --比较高的现象
252 
253 --(5)根据指令内存,执行计划和数据量,SQL可能还要在tempdb里创建一些对象,存放
254 --临时表,表变量,帮助做join,sort等
255 
256 --这时候,tempdb可能会成为瓶颈,包括他的空间,系统表,系统页面等。我们会看到
257 --tempdb上的资源的等待
258 
259 
260 --(6)如果指令要修改数据记录,SQL会修改内存缓冲区里页面的内容
261 --由于修改的对象是内存里的页面,在这一步不会直接触发磁盘写入,所以应该
262 --不会直接导致磁盘瓶颈。但是如果有并发用户在修改同一个页面的时候,会出现
263 --PAGELATCH_X的等待状态
264 
265 
266 --(7)如果指令发生数据修改,在提交事务之前,SQL必须将相应的日志记录按照顺序
267 --写入事务日志文件
268 --这一步会发生对事务日志文件所在磁盘的物理写入。如果磁盘写入速度不够快,或者
269 --瞬间需要写入的日志量太大,会出现WRITELOG的等待状态
270 
271 --(8)将结果集返回给客户端
272 --不是SQL将指令的结果集做好,指令就能够结束的。SQL会把结果集放入输出缓存,等
273 --客户端来把结果集完全取走,SQL这里指令才能够结束。如果结果集太大,或者一次
274 --批处理是由许多小的命令组成,导致网络交互太多,或者是应用程序自己有性能问题
275 --取数据取得很慢,都会导致指令整体运行时间延长。而这种等待也不是SQL能够控制解决的
276 
277 
278 --如果这里有等待,等待状态会是:ASYNC_NETWORK_IO(在SQL2000里是NETWORK_IO)
279 
280 --当然,以上这些动作都要在SQLOS里首先拿到一个Worker(也就是thread),然后这个
281 --Worker还要能排上Scheduler,在CPU上运行。如果:
282 
283 --SQL所有的WORKER都在忙自己的事情,没有空闲的worker,而且总的worker数目也达到了
284 --最大值,那么任务就要等待一个空闲的worker出现。这时候可以看到任务的等待状态
285 --是Ox46(UMSTHREAD)。而
286 SELECT [work_queue_count] FROM sys.[dm_os_schedulers] --可用工作队列数
287 --的值会不等于0
288 
289 --任务成功拿到一个worker,但是scheduler现在正在运行其他worker,任务进入等待队列
290 --这时候任务的状态是runnable
291 SELECT [runnable_tasks_count] FROM sys.[dm_os_schedulers]
292 --的值应该大于1
293 
294 --任务拿到了scheduler,进入运行状态(running)。如果任务非常消耗CPU资源,可能
295 --会在CPU上跑一会。这时候会看到某颗CPU使用率高的现象
296 
297 
298 --总之,从任务当前的等待状态,可以大概知道他当前运行到哪一步,也可以分析SQL可能
299 --存在的资源瓶颈,从而提高SQL的并发性。在做性能问题定位的时候,先看一下出问题时候
300 --下面一类DMV里各个连接的状态,对找到问题正确方向会很有帮助
301 SELECT * FROM sys.[dm_exec_requests]

 

posted @ 2013-07-29 09:05 桦仔 阅读(...) 评论(...)  编辑 收藏