笔记32-徐 内存压力分析

笔记32-徐 内存压力分析

  1 --内存压力分析
  2 
  3 --表现特征
  4 --SQL经常触发lazy writer
  5 --SQL需要经常从硬盘里读数据,会有很多硬盘读
  6 --执行计划经常被清除,所以buffer pool里的stolen内存部分应该不会很多
  7 --由于数据页经常被清除,所以page life expectancy不会很高,而且会经常下降
  8 
  9 --page life expectancy:缓存页面生存时间
 10 
 11 --如果数据页面 buffer pool内存有压力,SQL会优先清除内存里的执行计划
 12 
 13 --解决办法:
 14 --来自外部压力:Windows内存不够,SQL会压缩自己的内存,这时database page首当其冲被压缩
 15 --建议SQL安装在专用服务器上
 16 
 17 
 18 -------------------------------------------------------------------
 19 --database page自身使用需求的压力
 20 --1、32位服务器开启AWE
 21 --2、增加物理内存
 22 --3、如果scale up向上扩展不行,考虑scale out向外扩展,把SQL中的多个数据库移到其他服务器上,只留一个应用数据库或者分库处理
 23 --4、使用表索引,减少不必要的读和表扫描
 24 
 25 --1、2、3都需要新的硬件投资,而且到底加多少内存能够解决问题不好说,最好是最后一种方法
 26 
 27 -----------------------------------------------------------------------
 28 --来自buffer pool里的stolen压力
 29 --如果声明了很多游标,用完了不关,或者prepare很多执行计划,不un-prepare,不登出SQL就会占用
 30 --database page的空间
 31 --使用sys.dm_os_memory_clerks的single_pages_kb字段查看那个clerk用掉了较多的stolen内存
 32 
 33 
 34 -----------------------------------------------------------------------------------
 35 
 36 
 37 --来自multi-page memtoleave的压力
 38 --由于multi-page的量不大,所以一般这种问题较少
 39 --1、32位系统没有开AWE,使用 /g 参数增大了multi-page的上限值
 40 --2、64位系统multi-page没有上限,如果SQL调用了一些内存泄漏很厉害的第三方代码,
 41 --64位系统就算内存再充裕也有漏完的可能性
 42 
 43 ----使用sys.dm_os_memory_clerks的single_pages_kb字段查看哪个clerk用掉比较多的内存
 44 
 45 
 46 ---------------------------------------------------------------------------------
 47 --使用内存比较多的语句
 48 --有一些语句在不停地调用数据,把读数据页面最多的语句找出来,基本就能够找到SQL内存压力
 49 --的地方。分析一下这些语句是不是天生就要返回很多数据,为什麽读那么多数据页面
 50 
 51 
 52 --例如:一个表有100W行记录,查询要返回70W行记录。那么这个语句在SQL使用很多内存是正常的
 53 --在SQL上调整的余地不大,选择的方案有限:
 54 --1、增加物理内存
 55 --2、归档历史数据,把表里的数据降下来
 56 --3、调整应用程序设计,避免不必要的大数据量查询
 57 
 58 --因为没有索引,所以SQL不得不把表里的数据页面读到内存里,这样内存消耗是可以优化的
 59 --最好的办法添加索引
 60 
 61 --分析方法:
 62 --使用DMV提取历史信息
 63 --使用SQL Trace
 64 
 65 
 66 --SQL2005以后有一个动态管理视图sys.dm_exec_query_stats,返回缓存查询计划的性能统计信息
 67 --SQL会统计从上次SQL启动以来,一共做了多少次logical读写,多少次physical读,还记录执行所用的
 68 --CPU时间总量。
 69 
 70 --按照物理读的页面数排序 前50名
 71 SELECT TOP 50
 72 qs.total_physical_reads,qs.execution_count,
 73 qs.total_physical_reads/qs.execution_count AS [avg I/O],
 74 SUBSTRING(qt.text,qs.statement_start_offset/2,
 75 (CASE WHEN qs.statement_end_offset=-1
 76 THEN LEN(CONVERT(NVARCHAR(max),qt.text))*2
 77 ELSE qs.statement_end_offset END -qs.statement_start_offset)/2) AS query_text,
 78 qt.dbid,dbname=DB_NAME(qt.dbid),
 79 qt.objectid,
 80 qs.sql_handle,
 81 qs.plan_handle
 82 from sys.dm_exec_query_stats qs
 83 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
 84 ORDER BY qs.total_physical_reads DESC
 85 
 86 --按照逻辑读的页面数排序 前50名
 87 SELECT TOP 50
 88 qs.total_logical_reads,
 89 qs.execution_count,
 90 qs.total_logical_reads/qs.execution_count AS [AVG IO],
 91 SUBSTRING(qt.text,qs.statement_start_offset/2,(CASE WHEN qs.statement_end_offset=-1 THEN LEN(CONVERT(NVARCHAR(max),qt.text))*2 ELSE qs.statement_end_offset END -qs.statement_start_offset)/2) AS query_text,
 92 qt.dbid,
 93 dbname=DB_NAME(qt.dbid),
 94 qt.objectid,
 95 qs.sql_handle,
 96 qs.plan_handle
 97 from sys.dm_exec_query_stats qs
 98 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
 99 ORDER BY qs.total_logical_reads DESC
100 
101 --上面语句的缺点是没有时效性,记录的生存期与执行计划本身相关联
102 
103 
104 
105 
106 
107 --使用SQL Trace
108 
109 --SQL Trace里面有一个reads字段,记录了某条语句完成过程中一共做了多少次读的动作,找到read最多的语句
110 --每个SQL Trace里有成千成万的语句,可以使用fn_trace_gettable 像一张表一样把trace文件里的记录查询出来
111 --可以用他将记录转入到SQLSERVER里,然后用查询语句进行统计分析。
112 
113 --例如,在c:\sample目录下有一个问题时段的trace文件,叫a.trc.
114 --导入到SQLSERVER
115 
116 SELECT * INTO #SAMPLE
117 FROM sys.fn_trace_gettable('C:\Users\Administrator\Desktop\1.trc',DEFAULT)
118 WHERE EventClass IN(10,12)
119 
120 SELECT TOP 100 * FROM #SAMPLE
121 SELECT TOP 1000 TextData,DatabaseID,HostName,ApplicationName,LoginName,SPID,
122 StartTime,EndTime,Duration,reads,writes,CPU
123 FROM #SAMPLE
124 
125 
126 
127 --(1)找到是哪台客户端上的哪个应用发过来的语句,从整体上讲在数据库上引起的读最多
128 --得到这个结果,就能大概知道哪些客户端要访问大量的数据页面,可能造成内存压力
129 SELECT * INTO #SAMPLE
130 FROM sys.fn_trace_gettable('C:\Users\Administrator\Desktop\1.trc',DEFAULT)
131 WHERE EventClass IN(10,12)
132 
133 SELECT DatabaseID,HostName,ApplicationName,SUM(reads) AS reads
134 FROM #SAMPLE
135 GROUP BY DatabaseID,HostName,ApplicationName
136 ORDER BY SUM(reads) DESC
137 
138 
139 
140 --(2)按照reads从大到小排序,最大的1000个语句 用这个方法可以找出最昂贵的单笔语句
141 SELECT TOP 1000 TextData,DatabaseID,HostName,ApplicationName,LoginName,
142 SPID,StartTime,EndTime,Duration,reads,writes,CPU
143 FROM #SAMPLE
144 ORDER BY Reads DESC
145 
146 
147 
148 
149 --利用readtrace这个工具自动分析trace文件,找出使用大量系统资源的语句
150 --如果只是要找到一段时间内系统资源使用比较多的语句,不用做详细分析,readtrace这个工具
151 --能够自动完成
152 
153 
154 --通过以上三种方法,DBA比较快地找到某个SQL上造成很多读操作的语句。这里需要说明的是
155 --SQL Trace里reads字段是逻辑读,而不一定是物理读,只有语句访问的数据不在内存里时,
156 --才有物理读!!!!!!!!
157 
158 
159 
160 
161 --stolen内存压力分析
162 --除了database pages,其他内存分配基本都是直接从地址空间申请,不是先reserve,后commit的
163 --stolen内存主要以8KB为单位分配,分布在buffer pool里
164 
165 --stolen内存不缓存数据页面,任何一条语句的执行都需要stolen内存来做语句分析,优化,执行计划的缓存
166 --可能也需要内存来做排序,计算。任何一个连接的建立,需要分配stolen内存给他建立数据结构
167 --和输入输出缓存区。如果stolen内存申请不到,SQL任何操作都会遇到问题
168 
169 
170 --stolen内存有缓存:执行计划,用户安全上下文(每次执行sql语句之前都不需要重新建立安全上下文)
171 --这些缓存越多越好
172 
173 
174 --stolen内存没有缓存:使用完毕立刻释放,供其他用户使用,例如:语义分析,优化,做排序,做Hash等
175 --所以在32位SQL,虽然stolen内存只有不到2GB,但是很少有stolen内存不够用的情况
176 
177 
178 
179 --在SQL2005 SP2以后,SQL产品组调小了执行计划的最高上限。这是因为如果一个SQL能够缓存这么多不同的执行计划
180 --说明他内部运行的大多数都是动态TSQL,很少能够重用。如果经常有执行计划重用的现象,SQL也就不需要每次都
181 --生成新的执行计划,从而缓存这麽多份了。在这样的SQL里,就算缓存再多的执行计划,重用的机会都很小。
182 --所以这么多的缓存对SQL的性能帮助不是很大,反而增加SQL的维护成本。从经验上看,SQL缓存1GB~2GB的执行计划
183 --基本足够了,更多的缓存基本上对性能帮助不大。在有些情况下,定期清空执行计划缓存,反而对SQL的健康起到
184 --帮助作用
185 CHECKPOINT
186 DBCC DROPCLEANBUFFERS
187 
188 
189 
190 --stolen缓存区与数据缓存区相互关系
191 --stolen内存很多是用完就释放的,不会累积下来。
192 --对于有缓存机制的stolen内存,主要就是执行计划,也有清理机制,当buffer pool有内存压力的时候,SQL
193 --会同时清楚执行计划和database pages ,SQL都会把最久没有使用的对象清除
194 
195 --就算一个查询一次访问上百MB的数据,但是他的执行计划却是非常小的。但是当lazy writer没有能清除
196 --正在被使用的stolen内存的时候,会发生stolen内存越积越多,最后侵占database pages空间的现象
197 
198 
199 
200 
201 
202 --外部压力与内部压力
203 --外部压力:Windows通知SQL要压缩内存的时候,整个buffer pool里的所有内存都面临着清理
204 
205 --内部压力:
206 --database pages挤压,当一个查询需要大量的data page的时候
207 --stolen内存内部一些始终未清理的对象,例如,打开了游标不关,或者prepare了一些语句没有unprepare,
208 --那么只要不logout,SQL就无法清理和释放这些对象
209 
210 
211 
212 
213 
214 --解决办法:
215 --由于stolen内存是SQL自己申请使用的,可以用clerk看到
216 --特征:
217 --(1)返回错误信息701,在SQL 的errorlog里能看到错误信息
218 --(2)在sys.sysprocesses里的waittype字段不等于0X0000,而和stolen内存相关的等待状态:
219 SELECT spid,
220 blocked,
221 waittype,
222 DB_NAME(dbid)AS dbname,
223 open_tran,hostname,
224 program_name,
225 hostprocess,
226 cmd,
227 loginame
228 FROM sys.sysprocesses
229 ORDER BY spid ,dbid ASC
230 SELECT @@SPID
231 --waittype代码:
232 
233 --cmemthread(0x00B9)
234 --当多个用户同时往同一块缓存区里申请或释放内存时,在一个时间点,只有一个连接可以做申请或
235 --释放内存动作,其他连接必须等待。这些连接的等待状态,就是cmemthread。这种等待状态发生得
236 --比较少,通常只会发生在并发度非常高的SQL里。而这些并发连接,在大量地使用需要每次做编译的
237 --动态TSQL语句发生得比较多。
238 --这个等待一般不是因为内存数量少,而是同时申请的人太多。所以解决的方法不是增加内存,而是修改
239 --客户连接的行为,尽可能更多地使用存储过程,或者是参数化的TSQL语句调用,减少语句的编译量,
240 --增加执行计划的重用,避免大量连接同时申请内存做语句编译
241 SELECT spid,
242 blocked,
243 waittype,
244 DB_NAME(dbid)AS dbname,
245 open_tran,hostname,
246 program_name,
247 hostprocess,
248 cmd,
249 loginame
250 FROM sys.sysprocesses
251 WHERE waittype=0x00B9
252 ORDER BY spid ,dbid ASC
253 
254 --select * from sys.sysprocesses 的waittype字段显示0x40或0x0040(resource_semaphore) 等待资源
255 
256 --SOS_RESERVEDMEMBLOCKLIST(0x007B)
257 --当用户发过来的语句内含有大量的参数,或者有一个很长的"in"的子句,他的执行计划在8KB的single pages
258 --可能会放不下,需要用multi-page来存储。所以SQL需要在MEMTOLEAVE申请空间。造成的后果是随着缓存
259 --的执行计划越来越多,不但buffer pool里的stolen内存不断增长,memtoleave里用来存储执行计划的stolen
260 --内存也在不断增长。由于在32位buffer pool可以比memtoleave大很多,所以buffer pool还没有压力,
261 --lazy writer不会被触发。但是memtoleave里的内存已经比较紧张了。当用户要申请这块内存而暂时不能得到
262 --满足时,他的等待状态就是SOS_RESERVEDMEMBLOCKLIST
263 --解决这种等待方法有三种:
264 --(1)避免使用这种带有大量参数,或者“in”子句的语句。这种语句的运行需要消耗比正常语句多得多的内存
265 --以及CPU资源,不是一种合理的设计。可以先把参数值存储到临时表里,用join临时表代替直接引用这些值。
266 --这是一种从根本上解决这种问题的方法
267 
268 --(2)扩展memtoleave的大小,推迟SQL遇到瓶颈的时间
269 
270 
271 --(3)定期运行DBCC freeproccache语句,手工清除缓存的执行计划,缓解内存瓶颈。虽然没有第一种理想,
272 --但是在现实使用中,效果还不错,对SQL整体性能的影响不大
273 
274 --type:USERSTORE_SXC:暂时存放正在执行中的语句的参数。例如,客户调用存储过程,或者用sp_executesql
275 --调用动态TSQL语句时,需要带入过程参数。如果参数过长,这部分内存的使用量就会比较大
276 
277 
278 
279 
280 --RESOURCE_SEMAPHORE_QUERY_COMPILE(0x011A)
281 --当一个batch或存储过程非常长和复杂的时候,SQL编译他所需要的内存可能超过你的想象。
282 --为了防止太多内存被用来做编译,SQL为编译内存设了一个上限。当有太多复杂的语句同时
283 --在做编译时,可能编译内存使用会达到这个上限。后面的语句将不得不进入等待状态,等前面
284 --的语句编译完成,把内存释放出来以后,后面的语句才能继续编译。这个等待状态被称为
285 --RESOURCE_SEMAPHORE_QUERY_COMPILE    SEMAPHORE(信号量)
286 --解决办法:
287 --是前两种的综合:
288 --(1)修改客户连接的行为,尽可能更多地使用存储过程,或者是使用参数化的TSQL语句调用,
289 --减少语句编译量,增加执行计划的重用,避免大量连接同时申请内存做语句编译的现象
290 
291 
292 --(2)简化每次需要编译的语句的复杂度,降低编译需要的内存量
293 
294 --(3)当stolen内存使用总量比较大的时候,也可以考虑定期运行DBCC freeproccache语句,
295 --手工清除缓存的执行计划,保证stolen内存一直处在一个比较富裕的状态
296 
297 
298 --* DBCC FREEPROCCACHE
299 --从过程缓冲区删除所有元素
300 
301 
302 --虽然stolen内存没有database pages那么多,但也是SQL正常运行不可缺少的重要部分
303 
304 --在64位系统,max server memory的设置仅对buffer pool起作用
305 --memtoleave默认是384,如果/g512 启动参数,那么memtoleave就是512+0.5*最大线程=640
306 
307 --一般SQL根据负载量开启线程,大部分SQL的线程数目比256要少,如果地址空间被大量占用,-
308 --SQL可能无法再创建新线程,其中一个明显特征就是,新用户很难登入SQL
309 
310 --使用大量multi-page的原因,multi-page只有384MB
311 --(1)SQL内部
312 --1大量参数或者长“in”
313 
314 --2network packet size设成8KB或更高,而这种连接成百上千
315 --SQL默认的network packet size是4KB,这样每个连接的输入输出缓存都可以放在buffer pool里。
316 --在SSMS登录窗口,选项》 -》网络-》网络数据包大小:4096字节
317 --如果调高到8KB,或更高,每个网络包加上包头、包尾就要超过8KB。SQL只能把他们缓存在multi-pages里面
318 --如果连接非常多,会造成multi-pages大量使用
319 
320 --有些应用例如:SAP  .NET应用等,需要提高网络交互效率
321 
322 
323 --SQL的确提供了比较丰富的XML处理功能,但是类似的功能在应用程序也可以通过直接调用一些XML的API
324 --解决。那么是放在SQL里,还是放在应用程序里做呢,需要设计者平衡一下。放在SQL里做可能比较方便
325 --但是如果处理的XML串比较长,很复杂,那么处理所要花费的资源是比较昂贵的,内存只是其中一部分,
326 --这个SQL还有其他用户要服务,那么难免会影响到用户的响应速度。
327 
328 --(2)非SQL自己的代码
329 --SQL CLR
330 --LINKED SERVER
331 --EXEC sys.sp_OACreate 调用COM对象
332 --扩展存储过程EXEC sys.xp_cmdshell
333 
334 --对multi-page来讲,只要用户调用,SQL就只好申请,所以从SQL的角度没有什么积极的办法。
335 --使用/g 参数或者 升级到64位系统
336 
337 
338 --常见内存错误和解决办法:
339 --主要有三类,这些内存错误跟内存瓶颈不同,有内存瓶颈SQL会发起很多paging 和lazy writer,但是
340 --内存错误轻则某些操作不能完成,重则整个SQL没有响应,不仅仅是性能问题
341 
342 --1、OOM
343 --701错误:基本在32位机器,跟stolen内存有关,发现地址空间可供申请
344 --1、memtoleave地址段没有连续空间可供使用
345 --2、第三方代码申请太多内存没有释放掉
346 --SQL CLR
347 --LINKED SERVER
348 --EXEC sys.sp_OACreate 调用COM对象
349 --扩展存储过程EXEC sys.xp_cmdshell
350 --由于这些代码不是SQL自带的,所以在SQL这里做调整是很难解决问题的
351 
352 --3、buffer pool里的stolen内存申请不到
353 --常见的会使用很多stolen内存的memory clerk有如下几个:
354 
355 
356 
357 
358 --17803错误
359 --解决方法:
360 --使用/g 参数扩大memtoleave的大小,延缓问题发生频率
361 --定期重启SQL,延缓问题发生频率
362 --升级SQL到64位
363 --前两种不是长久之计
364 
365 --memoryclerk_sqloptimizer和cachestore_phdr:语句指令做编译时使用的内存,如果用户一次发过来的批指令太长,这段内存使用会较多
366 
367 --memoryclerk_sqloreservation:语句运行的时候在内存里存储临时数据的地方
368 --cachestore_sqlcp:缓存动态TSQL语句执行计划的地方。记得释放游标很unprepare语句
369 
370 --user_tokenperm:缓存用户的安全上下文。SQL在buffer pool没有压力的时候通常是64位机器可以涨到上百MB。在SQL2005中
371 --维护这么大的安全上下文缓存会影响SQL的整体性能
372 
373 --objectstore_lock_manager:SQL里的锁结构使用的内存。SQL里有严重的阻塞问题,一些用户申请了大量的锁却不释放
374 --而另一些用户想要申请大量的锁却拿不到,造成锁的数目上百万,这时这段内存的使用量会很多
375 
376 
377 
378 
379 
380 --2、语句运行时没能及时申请到内存,最常见的错误是8645
381 --这个错误通常发生在一个需要申请内存做排序或者hash等操作的查询里,在规定时间里没有能得到足够内存
382 --申请的内存基本都是buffer pool里的内存
383 
384 --SQL内存本身有压力,现在再发过来哪怕很小的内存申请数也不能满足
385 --用户突然发来一个或几个需要大量内存非常复杂的语句,一下子把SQL内存资源搞得非常紧张
386 
387 
388 --现象:
389 --login failed
390 --select * from sys.sysprocesses 的waittype字段显示0x40或0x0040(resource_semaphore) 等待资源
391 --性能计数器:sql mamager:memory grants pending对象值不为0
392 
393 
394 --解决办法:
395 --避免其他程序把SQL内存侵占
396 --收集buffer manager 和memory manager性能计数器
397 --检查一下SQL内存参数
398 --max server memory
399 --min server memory
400 --awe enabled
401 --min memory per query
402 --lock page in memory
403 
404 --检查各个memory clerk的内存申请
405 --检查工作负荷,例如:并发会话数,当前执行的查询,有可能的话开启sqltrace
406 
407 --为SQL提供更多内存:
408 --移开占用资源的应用程序到别的服务器
409 --设置max server memory
410 --运行下面DBCC命令释放SQL内存缓存
411 DBCC freesessioncache
412 DBCC freeproccache
413 
414 
415 
416 --找出使用内存比较多的语句,简化他们,调整应用程序行为,减少工作负荷
417 --检查动态管理视图,了解每个查询资源信号量的状态信息。(SQL里默认有两个查询资源信号量,分别处理复杂度不一样
418 --的查询,这样的设计有助于防止几个超大的查询把整个SQL资源用尽,连一些很简单的查询都不能响应的现象发生)
419 
420 --检查语句是:
421 SELECT CONVERT(VARCHAR(30),GETDATE(),121) AS runtime,
422 resource_semaphore_id,
423 target_memory_kb,
424 total_memory_kb,
425 available_memory_kb,
426 granted_memory_kb,
427 used_memory_kb,
428 grantee_count,
429 waiter_count,
430 timeout_error_count
431 from sys.dm_exec_query_resource_semaphores
432 
433 --resource_semaphore_id:资源信号量的非唯一ID,0表示常规资源信号量,1表示小型查询资源信号量
434 --target_memory_kb:该资源信号量能够授予使用的内存目标,也就是当前的使用上限
435 --total_memory_kb:资源信号量现在所持有的总内存,是可用内存和被授予内存的和。如果系统内存不足或频繁强制缩小内存,该值可以
436 --大于target_memory_kb值,但意味着这个资源信号量有内存压力
437 --available_memory_kb:可用于新授予的内存
438 --granted_memory_kb:授予的总内存
439 --used_memory_kb:授予内存中实际使用的部分
440 --grantee_count:内存授予得到满足的活动查询数
441 --waiter_count:等待内存授予得到满足的查询数,如果不为0,意味着内存压力存在
442 --timeout_error_count:自服务器启动以来的超时错误总数,对于小型查询资源信号量,该值为null
443 
444 
445 
446 --检查sys.dm_exec_query_memory_grants,返回已经获得内存授予的查询的有关信息,或依然在等待内存授予的查询的
447 --有关信息。无须等待就获得内存授予的查询将不会出现在此视图中。所以对一个没有内存压力的SQL,这个视图应该
448 --是空的
449 
450 SELECT GETDATE() AS runtime,
451 session_id,
452 scheduler_id,
453 dop,
454 request_time,
455 grant_time,
456 requested_memory_kb,
457 granted_memory_kb,
458 used_memory_kb,
459 timeout_sec,
460 query_cost,
461 resource_semaphore_id,
462 wait_order,
463 is_next_candidate,
464 wait_time_ms,
465 REPLACE(REPLACE(CAST(s2.text AS VARCHAR(4000)),CHAR(10),''),CHAR(13),'') AS sql_statement
466 FROM sys.dm_exec_query_memory_grants
467 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
468 
469 --session_id:正在运行查询的会话ID(spid)
470 --scheduler_id:正在计划查询的SQL Processor调度的ID
471 --dop:查询的并行度
472 --request_time:查询请求内存授予的日期和时间
473 --grant_time:向查询授予内存的日期和时间。如果尚未授予内存,则此值为null
474 --requested_memory_kb:请求的内存总量
475 --granted_memory_kb:实际授予的内存总量。如果尚未授予内存,该值为null。在典型情况下,该值应该与requested_memory_kb相同
476 --创建索引时,除了初始授予的内存外,服务器还允许增加按需分配的内存
477 --used_memory_kb:此刻使用的物理内存
478 --query_cost:估计查询开销
479 --timeout_sec:查询放弃内存授予请求前的超时时间
480 --resource_semaphore_id:查询正在等待的资源信号量的非唯一ID
481 --wait_order:等待查询在指定的queue_id中的顺序,如果其他查询获得内存授予或超时,则给定查询的该值可以更改。如果已授予内存,则为null
482 --is_next_candidate:下一个内存授予的候选对象:1:是  0:否 null:已授予内存
483 --wait_time_ms:等待时间。如果已经授予内存,则为null
484 --plan_handle:查询计划的标志符。使用sys.dm_exec_query_plan可提取实际的xml计划
485 --sql_handle:查询的TSQL文本标志符。查询中使用他链接sys.dm_exec_sql_text获取实际的TSQL文本
486 
487 
488 
489 --3、SQL无法创建新线程供新连接使用
490 --当SQLSERVER上所有的进程都被用户请求占据,而又有新的客户端发出连接请求时,SQL需要创建一个新的线程
491 --来响应这个请求。如果连接创建不出来,会出现SQL断断续续地连接不上的现象,已经在SQL里的连接还能继续工作
492 --只要有线程创建不出来的问题发生,SQL errorlog里就会有记录
493 
494 --17802错误
495 --17189错误
496 
497 --解决办法:
498 --(1):检查SQL使用了多少线程,是不是的确到了上限
499 --运行下面的查询,检查有多少个KPID<>0的SPID。当一个SPID的KPID不为0的时候,就说明他正在使用线程
500 --运行中。再加上SQL自己运行所需要的线程(一般10到20个),就差不多是SQL使用的线程数目
501 SELECT COUNT(*) FROM sys.sysprocesses WHERE kpid<>0
502 
503 --(2):如果线程的数目远小于设置的最大数,那就要考虑是不是memtoleave有压力了
504 --由于线程使用的是memtoleave的内存,确认SQL还有足够的memtoleave
505 SELECT  type ,
506         SUM(virtual_memory_reserved_kb) AS [vm reserved] ,
507         SUM(virtual_memory_committed_kb) AS [vm commited] ,
508         SUM(awe_allocated_kb) AS [awe allocated] ,
509         SUM(shared_memory_reserved_kb) AS [sm reserved] ,
510         SUM(shared_memory_committed_kb) AS [sm committed] ,
511         SUM(single_pages_kb) AS [singlepage allocator],
512         SUM(multi_pages_kb) AS [multi page allocated]
513 FROM    sys.dm_os_memory_clerks
514 GROUP BY type
515 ORDER BY type

 

posted @ 2013-07-27 15:58 桦仔 阅读(...) 评论(...)  编辑 收藏