笔记34-徐 任务调度和CPU问题

笔记34-徐 任务调度和CPU问题

  1 --任务调度和CPU问题
  2 --SQL作为一个企业级数据库平台,一个基本的要求就是要有能力顺畅地同时处理
  3 --成百上千的用户请求,SQL能使线程调度得更加适应高并发的数据库应用
  4 
  5 --SQL还开发出一套任务调度的监视机制,定期检查各个任务调度的运行状态。
  6 --如果发现某个任务在CPU上占据了较长时间,就会报警。所以如果SQL
  7 --服务发生不响应的问题server hang,一般在日志文件error log 里都会看到报警信息
  8 
  9 --SQLSERVER独特的任务调度算法(SQLOS)
 10 --SQL在Windows的基础上又开发出了一套自己的任务调度机制。所以SQL作为一个应用程序,
 11 --有抽象出一般由操作系统代为管理的功能
 12 --任务调度管理子系统
 13 --内存管理
 14 --错误,异常处理机制
 15 --死锁侦测和解决机制
 16 --运行第三方代码(dll,extended SP等)机制
 17 
 18 
 19 --SQL的管理功能组件又叫SQLOS SQL OPERATING SYSTEM ,而内存管理和任务调度管理是SQLOS的两大核心内容
 20 
 21 --对于SQLSERVER来讲,除了从DAC dedicated administrator connection过来的连接,其他
 22 --用户连接对SQL来讲都是同等重要的,而这样的连接在同一个时间点,可能会有成百上千。
 23 --如果SQL完全依赖Windows任务调度就不行了
 24 
 25 --在早期的SQLSERVER曾经依赖Windows的线程调度。随着硬件水平和并发用户的增多,这种方法缺点越发明显
 26 --SQL开发出自己的一套任务调度机制,特点如下:
 27 --1、只有需要运行任务的连接才会被分配线程。出于空闲状态的连接,在SQL里会以一组数据结构表示,
 28 --不会占用线程资源。大大降低SQL进程需要的线程数目
 29 
 30 --2、对于每一个CPU,SQL内部会有一个调度(scheduler),由这个scheduler决定在某个时间点,到底是哪个
 31 --SQL线程去运行。所以在Windows层面,每个CPU最多只会对应一个处于运行状态的线程。大大降低Windows
 32 --层面的上下文切换context switch
 33 
 34 
 35 --实践证明:很多有着1000~2000个并发用户的SQL,线程数也只需要一两百个。SQL完成的批处理量每秒钟可以
 36 --达到3000~4000个。
 37 
 38 --这个角度看,SQL的确是一个在Windows上高并发应用的典范
 39 
 40 
 41 --scheduler
 42 --对于每个逻辑CPU,SQL会有一个scheduler与之对应,在SQL层面上代表CPU对象
 43 --只有拿到scheduler所有权的任务worker才能在这个逻辑CPU上运行
 44 
 45 --所谓逻辑CPU,就是SQL从Windows层面上看到的CPU数目,如果是一个双核的CPU,
 46 --那么一个物理CPU在SQL看来就是两个逻辑CPU。如果系统还使用了超线程hyper-threaded
 47 --那对SQL来讲就是4个逻辑CPU
 48 
 49 --SQL Server 上,每一个CPU通常会对应一个Scheduler, 有几个额外的系统的Scheduler,只是用来执行一些系统任务。
 50 --对用户来讲,我们只需要关心User Scheduler就可以了。如果有4个CPU的话,那么通常就会有4个User Scheduler。
 51 
 52 --规则:
 53 --每个scheduler上的最大worker数目等于SQL的最大线程数除以scheduler的数目
 54 --在同一个时间点,只能有一个拥有scheduler的worker处于运行状态,其他worker
 55 --都必须处于等待状态。这样能降低每个逻辑CPU上的处于正在运行状态的线程数目,降低
 56 --context switch,提供可扩展性
 57 
 58 --scheduler是SQL的一个逻辑概念,他不与物理CPU相绑定。也就是说,一个scheduler可以
 59 --被Windows安排一会儿在这个CPU上,一会儿在那个CPU上。但是,如果在sp_configure里设置
 60 --了CPU affinity mask,那么scheduler就会固定在某个特定的CPU上
 61 
 62 --worker
 63 --每个worker跟一个线程(或纤程fiber)相对应,是SQL任务的执行单位。SQL不直接调度线程/纤程,
 64 --而是调度worker,使得SQL能够控制任务调度
 65 
 66 --规则:
 67 --每个worker会固定代表一个线程(或纤程),并且和一个scheduler相绑定。如果scheduler是
 68 --固定在某个CPU上的(通过设置CPU affinity mask),那么worker也会固定在某个CPU上
 69 
 70 --每个scheduler有worker的上限值,并且可以根据SQL工作负荷创建或释放worker
 71 --每次worker都会去运行一个完整的任务(task)。在任务做完之前不会退出,除非这个任务主动
 72 --进入了等待状态
 73 
 74 --scheduler只在有新任务要运行,而当前没有空闲的worker的情况下,才会创建新的worker。
 75 
 76 --某个worker空闲超过15分钟,scheduler可能会删除这个worker,以及其对应的线程。当SQL
 77 --遇到内存压力的时,也会大量删除处于空闲状态的worker,以节省multi-page内存开销
 78 
 79 --各种CPU和SQLSERVER版本组合自动配置的最大工作线程数
 80 --CPU数                 32位计算机                        64位计算机
 81 --<=4                    256                               512
 82 --8                       288                              576
 83 --16                      352                              704
 84 --32                      480                              960
 85 
 86 
 87 --task
 88 --在worker上运行的最小任务单元。最简单的task就是一个简单batch。例如,客户发过来下面
 89 --的请求:
 90 SELECT @@SERVERNAME
 91 GO
 92 SELECT GETDATE()
 93 GO
 94 --那么这两个batch就分别是两个task。SQL会先分配给第一个batch(select @@servername)一个
 95 --worker,将结果返回给客户端,再分配第二个batch(select getdate())一个worker。这两个
 96 --worker可能是不同的worker,甚至在不同的scheduler上
 97 
 98 --只要一个task开始运行,他就不会从这个worker上被移出。例如,如果一个select语句被
 99 --其他连接阻塞住,worker就不能继续运行,只能进入等待状态。但是这个select task 不会
100 --将这个worker释放,让他做其他任务。所以结果是,这个worker所对应的线程会进入等待状态
101 
102 
103 --yieding
104 --SQLOS的任务调度算法的核心,就是所有在逻辑scheduler上运行的worker都是非抢占式的
105 --(non-preemptive)。worker始终在scheduler上运行,直到他运行结束,或者主动将
106 --scheduler让出给其他worker为止。这个“让出”scheduler的动作,我们叫yieding
107 
108 --每个scheduler都会有一个runnable列表,所有等待CPU运行的worker都会在这个列表里排队,
109 --以先进先出的算法,等待SQL分配给他scheduler运行
110 
111 --SQL定义了很多yieding的规则,约束一个task在scheduler运行的时间。如果task比较复杂,
112 --不能很快完成,会保证task在合适的时间点做yieding,不至于占用scheduler太多时间。
113 
114 --常见时间点:
115 --当worker每次要去读数据页的时候,SQL会检查这个worker已经在scheduler上运行了多久,
116 --如果已经超过4ms,就做yieding
117 
118 --每做64KB的结果集排序,就会做一次yieding
119 
120 --在做语句编译compile的过程中(这个过程比较占CPU资源),经常会有yieding
121 
122 --如果客户端不能及时把结果集取走,worker就会做yieding
123 
124 --一个batch里的每一句话做完,都会做一次yieding
125 
126 
127 --正常来讲,哪怕一个task要做很久,他使用的worker是会经常做yieding的,不会长时间
128 --占用CPU不放。如果在一个scheduler上同时有很多worker要运行,SQL通过worker自动
129 --yieding的方式调度并发运行。这个比Windows用上下文切换context switch这麽粗鲁
130 --不分青红皂白地打断更有效
131 
132 
133 
134 --对于每个CPU,SQL都会有一个scheduler与之对应。在每个scheduler里,会有若干个worker,对应
135 --于每个线程。在客户端发过来请求之后,SQL会将其分解成一个或多个task。根据每个scheduler的繁忙程度,
136 --task会被分配到某个scheduler上。如果scheduler里有空闲的worker,task就会被分配到某个worker上。
137 --如果没有,scheduler会创建新的worker,供task使用。如果scheduler里的worker已经到了他的上限值,
138 --而他们都有task要运行,那么新的task只好进入等待worker的状态
139 
140 
141 --动态管理视图查看每个scheduler的状态
142 SELECT
143 scheduler_id AS schedulerid,
144 cpu_id AS cpuid,
145 parent_node_id AS parentnodeid,
146 current_tasks_count AS currenttaskcount,
147 runnable_tasks_count AS runnabletaskcount,
148 current_workers_count AS currentworkercount,
149 active_workers_count AS activeworkercount,
150 work_queue_count AS workqueuecount
151 from sys.dm_os_schedulers
152 
153 --字段:
154 --current_tasks_count:与此scheduler关联的当前任务数,包括等待工作线程worker资源的任务数task
155 --和已经拿到worker,当前正在等待或运行的任务处于suspended或runnable状态
156 
157 --runnable_tasks_count:已分配任务task并且正在可运行队列runnable list中等待被调度的工作线程数。
158 --只要不为0,就说明这个scheduler对应的CPU当时正在做事情,如果SQL CPU使用率不高,这个值在大部分
159 --情况下会是0
160 
161 --current_workers_count:与此计划程序scheduler关联的工作线程数,包括有task要运行的worker和正处于
162 --空闲状态的worker
163 
164 --current_workers_count:处于活动状态的工作线程数,他们必须有关联的任务,并且必须处于正在
165 --运行running,可运行runnable或挂起suspend状态中
166 
167 --work_queue_count :队列中等待空闲worker来执行的任务数。通常这个值应该为0。如果不为0,意味着
168 --SQL存在线程用尽的压力
169 
170 
171 
172 
173 
174 --SPID:60,51,64,87,52,93,73,59,56,55
175 
176 --其中SPID60占据了一个scheduler,正在CPU上面运行,所以他的状态是running
177 --SPID55正在等待系统资源,73被阻塞,59等待客户取走结果集,56等待同步,
178 --现在他们都是waiter list里,等待获得所需要的资源
179 
180 --waiter list(resource wait)队列:一般阻塞,等待资源就会放在这个队列里
181 --runnable queue队列:一切准备就绪,等待在scheduler运行的task做yieding
182 --running:正在scheduler里运行,一个scheduler同时只能有一个task
183 
184 
185 
186 --调度:放在waiter list里的task准备好运行之后就放在runnable queue,当在runnable queue的时候,
187 --前面没有人排队,他就可以进去scheduler里运行了,当在scheduler里运行的task突然要等待
188 --资源的时候,他会做yieding,然后又重新回到waiter list
189 
190 --这种做法可以最大程度消除Windows做context switch的需求
191 
192 --这个算法的缺点是当SQLSERVER代码质量有问题或者系统资源出问题,那么某个task意外运行
193 --很长时间都没有做yieding,那么他会长时间占用CPU,问题轻的话,会使SQL产生不良影响
194 --重的话,整个SQL都可能没有响应
195 
196 
197 
198 --为了及时发现问题,SQLSERVER在SQL2000 SP3以后开发出一套scheduler的健康监测机制。
199 --当SQL发现某个或某些scheduler有问题时,会及时在错误日志里记录下相关信息,并且生成
200 --一个mini——dump文件,把SQL这个进程当时在内存里的重要信息保存在文件里。
201 --通过错误日志和mini-dump,微软技术支持工程师可以分析当时每个scheduler的状态,
202 --以及SQL为什么认为scheduler有问题。
203 
204 --分析mini-dump里每个线程的call stack,了解当时出问题的task究竟在运行什么函数,以及
205 --各个函数的参数。总之通过debug手段,mini-dump会为分析提供很多关键信息
206 
207 --dump文件的debug对专业知识要求比较高,有时候需要对SQL的底层设计有所了解。大部分情况下
208 --只是对专业SQLSERVER支持工程师的要求。
209 
210 --错误报警的具体含义:
211 --17883 -某个scheduler疑似有问题
212 --SQL有一些优先级更高的线程会定期检查每个scheduler上运行的task。如果发现某个task运行了
213 --超过60秒钟都没有做过yieding,SQL就会打印出一个17883错误。从SQL启动以来第一次遇到17883
214 --错误的话,还会生成mini-dump文件
215 
216 --SQL会每隔5秒钟检查一下所有的scheduler。如果发现哪个task没有yieding,SQL就开始统计
217 --这个线程的各项指标。60秒过后还没有yieding就正式报告17883错误。
218 
219 --如果usermode的时间很长:很有可能当前线程所运行的代码进入了一个循环,很长时间都出不来。
220 --这种问题在SQL代码里不应该出现。如果出现,常常是怀疑是SQL代码哪里写得不够优化。建议升级
221 --SQL到最新的服务包版本,以避免一切已知的问题
222 
223 
224 --如果kernelmode的时间很长:说明当前线程主要都是在运行操作系统管理的核心态功能。如果
225 --要找到问题的根本原因,可能要作kernel mode的debug。怀疑的方向是某个驱动程序,或者是
226 --操作系统本身。建议升级操作系统的补丁包和有关驱动的版本
227 
228 
229 --如果usermode和kernelmode的时间都不高:线程一般是正在等某个API返回,例如:waitforsingleobject
230 --,sleep,writefile,readfile。这些函数按道理应该很快返回,所以SQL就没有设计在这里做yieding。
231 --但是当时却因为某种未知原因而长时间地没有返回。其中I/O问题导致的writefile和readfile长时间不
232 --返回,是17883的一个最常见原因。这时候伴随系统I/O问题,DBA要检查一下各个磁盘的吞吐量是否正常
233 
234 
235 --如果系统空闲率system idle%和进程使用率process utilization%都很低:很可能是因为由于SQL进程
236 --以外的其他应用或操作系统本身产生了CPU100%的现象,使得SQL拿不到CPU资源去运行线程,进而导致
237 --task没有及时做完。这个要观察性能监视器和CPU有关的计数器的值就能确认
238 
239 
240 
241 
242 --17884和17888 -所有scheduler都疑似有问题
243 --在SQL调度算法里,scheduler本身就是一个资源,是资源就难免产生死锁的现象
244 --以下情形:
245 --一个SPID开启了一个事务,在某张表上申请了一个X锁。指令运行完毕以后,这个事务没有提交
246 --,锁还被这个连接持有。但是连接进入空闲状态,线程因此被释放,连接没有放回连接池,只是线程释放
247 
248 --其他用户发来请求,需要读取这张表。由于申请不到锁,task被阻塞住。这时,连接将持有线程,处于等待
249 --状态,直到拿到锁为止
250 
251 --由于太多用户发来类似请求,越来越多的线程进入了被阻塞的状态。最后所有的线程都被使用完,而他们
252 --几乎都在等待锁资源
253 
254 --阻塞源头的那个SPID的用户发来请求,要求commit这个事务。如果事务能commit,阻塞就会被解除
255 --。但是这时候SQL已经没有空闲的线程来运行事务提交这个task。所以这个SPID进入了等待thread状态
256 
257 --这种情况下,SQL进入了死锁状态。大量线程被阻塞,而阻塞源头找不到线程来帮他commit事务
258 
259 
260 --SQL配置的最大线程数256中,其中一部分用来运行系统任务!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
261 --由于这种死锁资源不是单单是锁资源,SQL传统的死锁检测机制无法起作用。但是SQL任务调度检测机制
262 --可以检测到
263 
264 --如果SQL发现每个scheduler都没有thread能够有进展,就会报告17884错误
265 
266 --如果SQL发现50%以上的thread等待都是类似的资源,例如:锁,网络等,就会报告:17888错误
267 
268 
269 --常见的17884/17888错误产生原因:
270 --所有scheduler都遇到了17883错误
271 --所有的worker都被某个关键资源阻塞
272 --所有的worker都在运行一个很长时间才能返回的语句
273 
274 
275 
276 --17884/17888错误比17883影响大,由于是所有的scheduler
277 --一般在错误日志里看到17883/17884的警告,很多情况下,17883错误是由于磁盘响应太慢导致的
278 --磁盘瓶颈消失,17883问题也会自动消失,SQL任务调度会自动恢复正常。偶尔有一个17883错误
279 --用户没有所谓,也可以忽略,如果用户有所谓就要好好分析了
280 
281 
282 
283 
284 -------------------------案例分析------------------------------------------------------------
285 --不可否认,分析17883/17884问题,对dump文件的debug是非常重要的
286 
287 --在SQL上运行下面的指令,了解scheduler和用户连接所使用的thread的情况
288 DBCC SQLPERF(umsstats)
289 SELECT * FROM sys.sysprocesses WHERE kpid<>0
290 
291 --联机丛书说kpid就是 线程ID
292 
293 --其他DMV
294 SELECT * FROM sys.dm_os_schedulers
295 SELECT * FROM sys.dm_os_workers
296 SELECT * FROM sys.dm_os_threads
297 SELECT * FROM sys.dm_os_tasks
298 SELECT * FROM sys.dm_os_waiting_tasks
299 SELECT * FROM sys.dm_os_ring_buffers
300 
301 
302 
303 
304 
305 --收集日志-----------------------------------------------
306 --由于出问题的时间不固定,SQL又非常繁忙,收集SQL Trace的话日志很大,不太可行
307 --开销太大,对正常的性能也会有影响。
308 --我们收集性能监视器里的日志,以及用Osql.exe每隔15秒运行一段TSQL脚本,查询
309 --各个关键DMV,将结果输出到文本文件里。
310 
311 --使用这样的方法,对系统性能影响比较小,用户基本不会有感觉,使得长时间收集变为可能
312 
313 
314 
315 ---------------------------SQL CPU100%问题---------------------------------------------------
316 --这是除了server hang以外另一个和CPU相关的问题。SQL对自己任务调度的检测是非常严格的。
317 --如果errorlog里没有报告17883/17884这一类错误,但是SQL的CPU很高,一般都是工作负载太高
318 --导致的,SQL本身没有什么问题,只不过在辛苦地完成用户发过来的各项请求
319 
320 
321 
322 
323 --如果一台SQL服务器的CPU使用率大部分时间超过60~70%,就已经算是比较高的了。
324 
325 --SQL做下面操作会集中使用CPU资源
326 --1、编译和重编译
327 --不是所有的执行计划都可以被重用。在很多时候,由于数据量发生了变化,统计信息发生了变化,或者数据结构发生了变化,
328 --同样一句话执行,还有再次把执行计划做一遍。这个过程叫重编译recompile,重编译会提高CPU使用量
329 
330 
331 --2、排序sort和聚合计算aggregation
332 --在查询的时候,经常会做order by ,distinct这样的操作,也会做avg,sum,max,min这样的聚合计算,在数据
333 --已经加载到内存以后,就要使用CPU把这些计算做完。所以这些语句操作使CPU使用量增多
334 
335 
336 --3、表连接操作join
337 --做两张表连接的时候,SQL常常会选择nested loop或hash算法。算法的完成要运行CPU,有时候join也会带来
338 --CPU使用,尤其是SQL选择错了连接算法的时候。
339 
340 
341 --和CPU有关的设置主要都在sp_configure
342 
343 --1、priority boost 提升SQLSERVER的优先级
344 --如果设置为1,SQL进程会以比较高的优先级创建,在Windows进程调度里,会比较优先被运行
345 
346 --但是这个设置是不推荐的,因为会打乱Windows正常的进程调度。如果SQL进程优先级较高,那么当
347 --SQL出现CPU100%时,Windows上的正常优先级的进程会受到影响,甚至会影响到Windows的健康,从而
348 --影响SQL的正常运行。
349 
350 --2、affinity mask 自动设置所有处理器的处理器关联掩码
351 --设置SQL固定使用某几个CPU。当服务器上除了SQL,还要运行其他同样重要的应用服务时,为了防止
352 --SQL使用掉所有CPU资源,可以设置affinity mask,让SQL只使用其中几个CPU。还有一种情形,就是
353 --当DBA怀疑SQL的scheduler经常在不同的CPU上切换,进而影响性能时,也可以使用affinity mask,
354 --让SQL的每个scheduler固定在CPU上,看看是不是对整体性能有帮助
355 --按实际情况设置
356 
357 
358 --3、lightweight pooling 使用Windows纤程(轻型池)
359 --是否使用纤程技术,默认是关闭的。开启以后,SQL会在使用线程(thread)的时候使用纤程(fiber)
360 --纤程调度技术能够降低系统的context switch数目,所以对一些应用来讲,能提高性能
361 --但是SQL本身已经用scheduler技术,有意识地在SQL一级降低context switch的需求,所以
362 --SQL是个context switch不严重的应用(相对于他的工作负荷)。在实际应用中,很少遇到
363 --lightweight pooling 能提高系统性能的案例。纤程调度毕竟比普通线程调度要复杂,所以
364 --这个设置一般很少推荐使用
365 
366 
367 --4、max degree of parallelism
368 --定义SQL最多使用多少个线程来并行执行一条指令
369 --当SQL发现一条指令比较复杂时,会决定用多个线程并行执行,从而提高整体响应时间。
370 --例如:一条指令要读入100W条记录。如果一个线程做,需要10秒钟,如果10个线程做
371 --每个线程读10W,每个线程需要一秒,再加上线程间同步时间,总共2秒就做完了。
372 
373 --这个设置是不是总是有好处,要DBA去判断
374 --在这2秒钟里,有10个CPU需要全力运行这10个线程,别的用户发过来的指令会受到影响,甚至可能
375 --会拿不到CPU执行。
376 --所以对于并发度要求高,每个用户都要求有及时响应的OLTP系统,一般会建议设置每个指令都只用
377 --一个线程执行,从而保证SQL在任何时间点,都有多个CPU可以响应多个请求。要把
378 --max degree of parallelism设置为1
379 
380 --对于并发用户少的,经常有复杂查询的(例如:数据仓库),用户希望查询早点做完。
381 --要把max degree of parallelism设置为CPU的数量值。例如:SQL服务器有16个CPU,就
382 --设置成16。如果也要考虑并发用户数,可以设置小一点,例如:8,4 等
383 
384 
385 
386 
387 --5、cost threshold of parallelism
388 --当SQL在做编译的时候,同时会计算候选执行计划的cost。SQL总是先做非并行的执行计划。
389 --当他发现这个执行计划的值将大于cost threshold of parallelism的设定值,SQL就会改用
390 --并行执行计划
391 --所以如果提高cost threshold of parallelism的值,SQL会更不容易选择并行执行,从而
392 --有更好的并发度
393 --他的默认值是5,一般很少去修改他
394 
395 
396 --6、max worker threads 最大工作线程数
397 --定义SQL进程最多线程数.max worker threads的默认值是0,允许SQL在启动时自动配置
398 --工作线程数。对于大多数系统而言,该设置为最佳设置,一般很少去修改他
399 
400 
401 
402 
403 
404 --SQL2005 /2008自动设置的最大工作线程数
405 --各种CPU和SQLSERVER版本组合自动配置的最大工作线程数
406 --CPU数                 32位计算机                        64位计算机
407 --<=4                    256                               512
408 --8                       288                              576
409 --16                      352                              704
410 --32                      480                              960
411 
412 
413 
414 
415 --解决CPU 100%问题------------------------------------------------------------------------
416 --1、确定服务器CPU使用率到底是多少,其中多少是SQL贡献的
417 --如果SQL的CPU使用率不是很高,而是其他应用导致的,那也不用再检查SQL了
418 
419 
420 --2、确定当时SQL是否工作正常,看有没有17883/17884之类的问题发生,有没有访问越界access violation
421 --之类的严重问题发生
422 --这个看errorlog
423 
424 
425 --3、找出CPU 100%的时候SQL里正在运行的最耗CPU资源的语句,对它们进行优化
426 --这个比较艰难,尤其是没有DMV的SQL2000
427 --如果有SQL Trace一般可以找出这些语句,但是SQL系统的CPU使用率已经很高了,再开SQL Trace
428 --负载会更高。
429 
430 --SQL2005 DMV SQL启动以来累计使用CPU资源最多的语句 前50名
431 SELECT
432 highest_cpu_queries.*,
433 highest_cpu_queries.total_worker_time,
434 DB_NAME(q.dbid)  AS dbname,
435 q.[text] AS qtext
436 from
437 (SELECT TOP 50 qs.* from sys.dm_exec_query_stats qs ORDER BY qs.total_worker_time DESC) AS highest_cpu_queries
438 CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
439 ORDER BY highest_cpu_queries.total_worker_time DESC
440 
441 
442 
443 --找到最经常做重编译的存储过程
444 SELECT TOP 25
445 sql_text.text AS sqltext,
446 sql_handle AS sqlhandle,
447 plan_generation_num AS plangenerationnum,
448 execution_count AS execcount,
449 DB_NAME(dbid) AS dbname,
450 objectid AS objectid
451 from sys.dm_exec_query_stats a
452 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sql_text
453 WHERE plan_generation_num>1
454 ORDER BY plan_generation_num DESC
455 
456 --局限性:
457 --服务器出问题时,DBA不在现场,DBA到现场分析的时候已经时过境迁了
458 --问题发生时,常常不允许DBA做长时间分析,而采取一些手段(关闭一些程序,重启SQL,归档历史数据)
459 --DMV始终无法替代SQL Trace的功能,很多时候还需要收集一份SQL Trace
460 
461 
462 --4、降低系统负载或升级硬件
463 --如果修改程序和数据库设计是一件非常耗时的事,那么解决方向就是
464 --1把系统一部分负载移到其他服务器上
465 --2升级硬件
466 
467 --但是随着负载的继续提高,总有一天问题会再次出现,所以在问题暂时消失后,有必要
468 --做系统设计审核。从设计上优化,往往是解决问题的最有效方法
469 
470 
471 
472 
473 
474 ----------------OLTP系统和数据仓库系统的差别和常用性能阀值-----------------------------------
475 --SQL支持两大类应用模式:OLTP和数据仓库
476 --这两大类应用模式有非常大的差别,用户发过来的请求类型会很不一样,用户期望的响应时间
477 --也很不相同,他们对系统资源的使用也有差别。
478 
479 --所以要设计一套优化的数据库应用,必须要把这两类应用分开,让他们使用不同的数据库,不同的
480 --数据库服务器。从而防止两类应用相互影响
481 
482 
483 --OLTP系统----------------------------------------------------------------------------------------
484 --特点是应用有大量的并发程度较高的小事物,包括select ,insert,update,delete。这些操作简单,
485 --事务时间不会很长,但是要求返回时间很严格,基本上要在几秒钟之内必须返回
486 
487 
488 
489 
490 
491 --支持生产流水线的数据库应用
492 --一件产品从原材料到组装成最后的产品,中间有很多工序。
493 --每道工序并不复杂,不会花很多时间。工厂需要数据库应用记录和监督每一道工序。在流水线上,
494 --工人可以扫描产品上的条形码,快速地输入产品加工、处理或检验结果。这些输入和修改过程
495 --都很简单,而且很多在数据库里会是insert,update,delete动作。但是应用的响应速度要求非常高,
496 --最好等待时间可以忽略不计。如果工人输入一个条形码以后要等几秒钟,那么他在处理每一件产品
497 --的时候,都会多花几秒钟。如果他要花几十秒,整个流水线的运转就会很慢。如果系统出问题,
498 --他每处理一个产品都要花几分钟,那么流水线就会瘫痪,工人们可以去喝茶了。
499 --DBA将面对心急如焚的管理高层
500 
501 
502 
503 --OLTP系统在设计的时候要非常小心,由于一条语句导致整个服务器范围的阻塞,是绝对要避免的
504 
505 --OLTP系统要注意避免出现的问题主要体现如下:
506 --1、数据库设计
507 --经常运行语句超过4个表做join      降低数据库设计范式级别,增加一些冗余字段,用空间换数据库效率
508 --经常更新的表有超过3个索引        索引太多会影响更新效率
509 --语句会做大量I/O  表扫描          语句缺少合适索引
510 --未使用的索引                     避免定义没有用的索引,凭空增加SQL的维护负担
511 
512 --返回最经常运行的100条语句
513 SELECT TOP 100
514 cp.cacheobjtype,
515 cp.usecounts,
516 cp.size_in_bytes,
517 qs.statement_start_offset,
518 qs.statement_end_offset,
519 qt.dbid,
520 qt.objectid,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 statement
521 from sys.dm_exec_query_stats qs
522 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
523 INNER JOIN sys.dm_exec_cached_plans AS cp ON qs.plan_handle=cp.plan_handle
524 WHERE cp.plan_handle=qs.plan_handle
525 AND cp.usecounts>4
526 ORDER BY dbid,usecounts DESC
527 
528 
529 --返回最经常被修改的100个索引
530 --通过他们的databaseid,objectid,indexid和partitionnumber
531 --可以找到他们是哪个数据库上的哪个索引
532 
533 SELECT TOP 100 DB_NAME(database_id),*
534 FROM sys.dm_db_index_operational_stats(NULL,NULL,NULL,null)
535 ORDER BY leaf_insert_count+leaf_delete_count+leaf_update_count DESC
536 
537 --返回做I/O数目最多的50条语句以及他们的执行计划
538 SELECT TOP 50
539 total_logical_reads/execution_count AS avg_logical_read,
540 total_logical_writes/execution_count AS avg_logical_write,
541 total_physical_reads/execution_count AS avg_phys_read,
542 execution_count AS execcount,
543 statement_start_offset AS stmt_start_offset,
544 statement_end_offset AS stmt_end_offset,
545 SUBSTRING(sql_text.text,(statement_start_offset/2)),CASE
546 WHEN (statement_end_offset-statement_start_offset)/2<=0 THEN 64000
547 ELSE (statement_end_offset-statement_start_offset)/2 END) AS exec_statment,
548 sql_text.text AS text,
549 plan_text.*
550 from sys.dm_exec_query_stats
551 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sql_text
552 CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS plan_text
553 ORDER BY
554 (total_logical_reads+total_logical_writes)/execution_count DESC
555 
556 
557 
558 --2、CPU
559 --signal waits   指令等待CPU资源的时间占总时间的百分比,如果超过25%,说明CPU资源紧张
560 --执行计划重用率   OLTP系统的核心语句,必须有大于95%的执行计划重用率
561 --并行运行的cxpacket等待状态     并行意味着SQL在处理一句代价很大的语句,要不就是没有合适
562 --的索引,要不就是筛选条件没能够筛选掉足够的记录,使得语句要返回大量的结果。这个在OLTP系统
563 --里都是不允许的
564 --其次,并行运行会影响OLTP系统整体响应速度,也是不推荐的
565 
566 --计算signal wait占整个wait时间的百分比
567 SELECT CONVERT(NUMERIC(5,4),SUM(signal_wait_time_ms)/SUM(wait_time_ms))
568 FROM sys.dm_os_wait_stats
569 
570 
571 --计算cxpacket占整个wait时间的百分比
572 DECLARE @cxpacket BIGINT
573 DECLARE @sumwait BIGINT
574 SELECT
575 @cxpacket=wait_time_ms
576 FROM sys.dm_os_wait_stats
577 WHERE wait_type='cxpacket'
578 
579 SELECT
580 @sumwait=SUM(wait_time_ms)
581 FROM sys.dm_os_wait_stats
582 
583 SELECT CONVERT(NUMERIC(5,4),@cxpacket/@sumwait)
584 
585 --3、内存
586 --page life expectancy       OLTP系统的操作都比较简单,所以他们不应该要访问太多的数据
587 --如果数据页不能长时间缓存在内存里,势必会影响性能,同时也说明某些语句没有合适的索引
588 
589 --memory grants pending        等待内存授予的用户数目,如果大于1,一定有内存压力
590 
591 --sql cache hit ratio          这个值不能长时间例如:60秒钟小于90%。否则,常常意味着内存有压力
592 
593 
594 --4、I/O
595 --average disk sec/read           在没有I/O压力的情况下,读操作应该在4~8ms以内完成
596 --average disk sec/write          对于像日志文件这样的连续写,应该在1ms内完成
597 --big IOs table scan/range scan   语句缺少合适的索引
598 --排在前两位的等待状态有下面几个
599 --asynch_io_completion,            这些等待状态意味着有I/O等待
600 --io_completion,
601 --logmgr
602 --writelog
603 --pageiolatch_x
604 
605 
606 
607 --5、阻塞
608 --阻塞问题在OLTP系统里危害巨大,是要严格避免的
609 --阻塞发生频率          阻塞发生频率
610 --阻塞事件报告          在SQL Trace里自动报告超过30秒钟的阻塞语句
611 --平均阻塞时间          阻塞发生的长短
612 --排在前两位的等待状态以这样开头LCK_M_??              说明系统经常有阻塞
613 --经常有死锁    每个小时超过5个       打开/t 1204      死锁往往伴随着阻塞同时发生
614 
615 --查询阻塞
616 SELECT TOP 2 wait_type FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC
617 
618 
619 --查询当前数据库上所有用户表在row lock上发生的阻塞频率
620 use GPOSDB   --要查询阻塞的数据库
621 DECLARE @dbid INT
622 SELECT @dbid=DB_ID()
623 SELECT
624 dbid=database_id,
625 objectname=OBJECT_NAME(s.object_id),
626 indexname=i.name,
627 i.index_id,
628 partition_number,
629 row_lock_count,
630 row_lock_wait_count,
631 [block%]=CAST(100*row_lock_wait_count/(1+row_lock_count)AS NUMERIC(15,2)),
632 row_lock_wait_in_ms,
633 [avg row lock waits in ms]=CAST(1*row_lock_wait_in_ms/(1+row_lock_wait_count)AS NUMERIC(15,2))
634 FROM sys.dm_db_index_operational_stats(@dbid,NULL,NULL,null) AS s,
635 sys.indexes AS i
636 WHERE OBJECTPROPERTY(s.object_id,'IsUserTable')=1
637 AND i.object_id=s.object_id
638 AND i.index_id=s.index_id
639 ORDER BY row_lock_wait_count DESC
640 
641 
642 
643 
644 --6、网络传输
645 --网络有延时,或应用太频繁地和数据库交互           网络不能支持应用和数据库服务器的交互流量
646 --网络带宽用尽                  由于网络太忙,有packet在传输中丢失
647 
648 
649 --优化方面
650 --对于经常update  insert  delete的表,在设计时要选择最小数量的索引
651 --可以通过提高执行计划重用和降低join的数目降低CPU使用率
652 --可以通过优化索引设计,降低join数目和提高页面在内存里的缓存生命周期,缓解I/O瓶颈
653 --如果Page life expectancy不会突然下降的话,说明内存的database page部分没有瓶颈
654 --可以通过优化索引和缩短事务大小来减少阻塞
655 
656 
657 
658 
659 
660 --data warehouse系统------------------------------------------------------------------------------------
661 --对于一个数据库,用户总会有数据分析的需求,总是要基于一些历史数据的报表来做业务分析
662 --这也是数据库应用的重要需求。可是这样的大查询天生与insert update delete操作相冲突
663 --他们会互相阻塞,最后双方的速度都会大受影响
664 
665 --这是数据仓库技术产生的一个重要背景,对于一个重要的OLTP系统,用户都会开发一套平行的
666 --数据仓库系统,定期把OLTP系统数据更新同步到数据仓库系统。在数据仓库系统里会存放
667 --所有历史数据,而OLTP系统只存放当前业务所需的数据,历史数据会被定期归档,以确保事务
668 --尽可能简单。所有数据分析请求都会指向数据仓库,在数据仓库系统里,并发用户会比
669 --OLTP系统少,语句数量也会少很多。但是语句的平均复杂度比OLTP系统高很多。而且用户对
670 --这些复杂的语句,能容忍十几秒,几十秒甚至更长时间
671 
672 --优化:
673 --1、数据库设计
674 --对于经常要运行的查询,他们要做的排序或RID lookup操作可以用covered indexes来优化            数据仓库数据更新一般以周期性的任务进行,而终端用户只做查询工作。所以可以建立比较多的索引,最大程度地优化查询速度。
675 
676 
677 --尽可能少的碎片,最好小于25%                       数据页面碎片会增加读取同等数据所要读取的页面数,增加内存和I/O负荷,要用重建索引的方式严格控制碎片比率
678 
679 --由于会有一些很复杂的查询,全表扫描是难免的,但是要注意不要缺少重要的索引                    缺少索引会大大降低查询的性能
680 
681 --要避免没有用的索引                              没有用的索引会凭空增加SQLSERVER的维护负担
682 
683 
684 --返回当前数据库所有碎片率大于25%的索引并进行重建索引
685 --运行本语句会扫描很多数据页面
686 --避免在系统负载比较高时运行
687 -------------------------------------------------------------------------------------------------------------
688 USE master       --改为你要扫描索引碎片的那个数据库
689 DECLARE @dbid INT
690 SELECT @dbid=DB_ID()
691 SELECT * FROM sys.dm_db_index_physical_stats(@dbid,NULL,NULL,NULL,null)
692 WHERE  avg_fragmentation_in_percent>25
693 ORDER BY avg_fragmentation_in_percent DESC
694 
695 --查索引名,某个object_id里面有几个索引,即某个表里有几个索引
696 SELECT i.*
697 FROM sys.indexes i INNER JOIN sys.objects o ON i.object_id=o.object_id
698 WHERE i.object_id=1115151018
699 
700 --查表名
701 SELECT name AS N'表名',* FROM sys.objects WHERE type='u' and object_id=1115151018  --用户表
702 
703 --重建索引
704 USE master   ----改为你要扫描索引碎片的那个数据库
705 ALTER INDEX spt_valuesclust ON dbo.spt_values REBUILD WITH(online=on)
706 ALTER ix2_spt_values_nu_nc ON dbo.spt_values REBUILD WITH(online=on)
707 
708 -----------------------------------------------------------------------------------------------------------------
709 
710 
711 --当前数据库可能缺少的索引
712 SELECT
713 d.*,
714 s.avg_total_user_cost AS avgtotalusercost,
715 s.avg_user_impact AS avguserimpact,
716 s.last_user_seek AS lastuserseek,
717 s.unique_compiles AS uniquecompile
718 FROM sys.dm_db_missing_index_group_stats s,
719 sys.dm_db_missing_index_groups g,
720 sys.dm_db_missing_index_details d
721 WHERE s.group_handle=g.index_group_handle
722 AND d.index_handle=g.index_handle
723 ORDER BY s.avg_user_impact DESC
724 
725 
726 --推荐建立索引的字段
727 DECLARE @handle INT
728 SELECT @handle=d.index_handle
729 FROM sys.dm_db_missing_index_group_stats s,
730 sys.dm_db_missing_index_groups g,
731 sys.dm_db_missing_index_details d
732 WHERE s.group_handle=g.index_group_handle
733 AND d.index_handle=g.index_handle
734 SELECT * FROM sys.dm_db_missing_index_columns(@handle)
735 ORDER BY column_id
736 
737 --2、CPU
738 --signal waits          指令等待CPU资源运行的时间占总时间的百分比。如果超过25%,说明CPU资源紧张
739 
740 --避免执行计划重用      数据仓库系统里用户发过来的指令量比OLTP要少很多,但是每一句都会复杂很多,
741 --要做多得多的I/O动作,所以保证使用最贴切的执行计划比避免compile要重要得多
742 
743 --并行执行计划应该被广泛使用,cxpacket应该是最常见的等待状态           并行执行计划对主要运行复杂查询的数据仓库系统
744 --比较合适。如果不是这个等待状态最多,要不就是查询还不够复杂,不用并行就已经能达到良好的速度,要不就是系统还有其他
745 --瓶颈
746 
747 
748 
749 
750 
751 
752 
753 --3、内存
754 --memory grants pending计数器               SQLSERVER MEMORY MANAGER计数器        等待内存分配的用户数目。如果有这样的情况发生
755 --,一定有内存压力
756 
757 --page life expectancy计数器                SQLSERVER BUFFER MANAGER              由于查询会访问一些历史数据,很难保证SQL能够
758 --将所有要访问的数据都缓存在内存里,所以在数据仓库里,时不时有一些database paging的动作是难免的。在这方面的要求比OLTP系统要低得多。但是如果page life expectancy经常地下降,说明内存很缺乏,整体性能会受影响,还是要检查一下是不是可以通过索引来优化
759 
760 
761 
762 --4、I/O
763 --数据仓库的磁盘读要比OLTP系统要高很多,这是难免的。但是只要是I/O瓶颈,就会影响系统的性能。所以还是要检查,
764 --是否有优化的空间
765 
766 --average disk  sec/read计数器           physical disk    在没有I/O压力的情况下,读操作应该在4~8ms以内完成
767 
768 --average disk sec/write                 pyhsical disk    对于像日志文件这样的连续写,应该在1ms以内完成
769 
770 --big scans                                语句缺少合适的索引
771 
772 --if top 2 values for wait stats are any of the following:
773 --asynch_I/O_completion
774 --I/O_completion
775 --logmgr
776 --writelog
777 --pageiolatch_x                         select top 2 wait_type from sys.dm_os_wait_stats order by wait_time_ms desc
778 --这些等待状态意味着有I/O瓶颈
779 
780 
781 
782 
783 
784 --5、阻塞
785 --和其他资源不同,阻塞对数据仓库系统的危害和对OLTP系统一样严重,一样要严格避免。唯一有点不同的是,
786 --由于数据仓库系统的修改量在工作时间一般比较少,可以考虑使用row versioning(行版本)技术,避免写阻塞读的情况
787 
788 --阻塞发生频率                可以检查用户表在row lock 上发生阻塞的频率
789 --阻塞事件报告                在SQL Trace 里自动报告超过30秒的阻塞语句
790 --平均阻塞时间                阻塞发生时间的长短
791 --排在前两位的等待状态以这样开头:LCK_M_??   select top 2 wait_type from sys.dm_os_wait_stats order by wait_time_ms desc
792 --说明系统经常有阻塞,可以考虑使用row versioning行版本技术,避免写阻塞读的情况
793 
794 
795 --和OLTP系统相反,数据仓库系统里指令量比较少,并发度低,以查询为主,但是每条指令很复杂。
796 
797 --1、数据仓库里的数据库的表可以建立多一些索引
798 --这是因为在非工作时间里数据一般不会被修改,主要的数据同步工作都以批处理任务的方式在工作时间进行
799 --2、宁可多做一些recompile,少重用他人的执行计划
800 --对于复杂的查询,执行时间通常会远大于compile时间,而执行时间的长短和执行计划的优劣密切相关。因此,每次执行都做一次编译,
801 --确保SQLSERVER能够选择最好的执行计划常常是合算的
802 
803 --3、如果有很大结果集的排序,可以考虑加一个索引来避免
804 --4、对每个SQL认为缺少的索引,都应该加以分析,看看应该怎麽解决
805 --5、如果大的扫描是难以避免的,那么数据在磁盘上连续存放对性能会极有帮助。同时要用reindex的方法把碎片降低到最小限度
806 --6、通常情况下,并发执行对数据仓库里的语句会有帮助

 

 

 

 

--SQL2005 DMV SQL启动以来累计使用CPU资源最多的语句 前50名
SELECT
highest_cpu_queries.*,
highest_cpu_queries.total_worker_time,
DB_NAME(q.dbid)  AS dbname,
q.[text] AS qtext
from
(SELECT TOP 50 qs.* from sys.dm_exec_query_stats qs ORDER BY qs.total_worker_time DESC) AS highest_cpu_queries
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
ORDER BY highest_cpu_queries.total_worker_time DESC

 

f

 

posted @ 2013-07-27 16:04  桦仔  阅读(1549)  评论(0编辑  收藏  举报