笔记59-徐 问题定位与解决方法

 笔记59-徐  问题定位与解决方法  

  1 --问题定位与解决方法     第13章 2013-2-6
  2 --思路:
  3 --1、确认是否因为做了物理I/O而导致性能不佳
  4 --如果一条语句所要访问的数据页面没有事先缓存在内存里,那么数据
  5 --页面要在语句运行的过程中,从磁盘读到内存里,这是一个很昂贵的动作。
  6 --如果语句要访问的数据页面比较多,就会产生比较长的时间消耗。
  7 --多数情况下,有数据缓存时的语句运行时间,会比没有数据缓存的时间,
  8 --相差一个到几个数量级
  9 
 10 
 11 --所以语句调优的前提,是要确认数据页面能够事先缓存在内存里。如果这个
 12 --问题得到解决后,性能还不能达到要求,才有后续调优必要。如果这个
 13 --问题解决后就能跑得足够快,那说明这个问题更是一个系统资源瓶颈问题
 14 --而不是语句本身问题。
 15 
 16 
 17 --2、确认是否是因为编译时间长而导致的性能不佳
 18 --语句的执行总时间,由编译时间和运行时间两部分组成。大部分情况下,
 19 --编译时间会远小于运行时间。而且很多时候SQL会重用执行计划,所以
 20 --语句的第一次执行有编译时间,后面的执行常常就只有运行时间了。因此,
 21 --调优的大部分精力,会花在降低运行时间上。
 22 --但是,有些语句处理的数据量比较小,可以很快做完,但是语句本身并不很
 23 --简单。SQL还是要“想一想”才能找到合适的执行计划。这样的语句,其编译
 24 --时间可能会占总时间的50%甚至更高
 25 
 26 --所以对于这类执行总时间比较短,但是在应用程序里会被反复调用的语句,就有
 27 --必要检查他们编译时间占总时间的比重。如果编译相对比较昂贵,而运行速度
 28 --又很快,调优的重点会转向如何避免重编译,或者降低编译时间。语句运行
 29 --调优的空间,往往不是很大
 30 
 31 --如果语句要访问的数据都已经缓存在内存里,而且编译时间很短,那么调优就进入
 32 --如何降低语句运行时间这个比较纯粹的课题了。而这个工作,也要分两步进行
 33 
 34 --3、确认SQLSERVER是否正确地预估了每一步的cost,选择了正确的执行计划
 35 --语句要能高效运行,前提是SQL选择的执行计划是正确的。而选择正确的执行计划
 36 --的前提,又是SQL正确地预估了每一个执行子句的cost,SQL是根据EstimateRows
 37 --的大小来预估cost的。所以简单来讲,要判断一个执行计划是否合适,最重要
 38 --的就是要判定SQL是不是正确预估了执行计划每一步返回的结果集的大小。
 39 --如果预估值和实际值相差很多(这倒是经常发生的),就要判定他是否造成
 40 --SQL选择了一个错误的执行计划。这里的判断需要一些经验,有时候也要做一些
 41 --测试,才能下结论
 42 
 43 --如果这一步SQL也没问题,就说明以现有的表结构和索引,SQL无法做到在预期
 44 --时间内完成语句运行。这时就进入最后一步,也就是纯粹的语句调优步骤
 45 
 46 --4、检查表结构和语句逻辑,确认是否有调优空间,提高语句的运行速度
 47 --调整索引,当然是语句调优的重要途径。可以通过执行计划里的每个子句的cost
 48 --找到最花时间的部分,看看是否可以通过调整索引的方法,提高运行效率。
 49 --但是对于一些语句,可能天生就比较复杂,很难降低他的运行时间,有可能
 50 --无法通过调整索引来达到目的。但是,做同样的业务逻辑可能会有不同的做法
 51 --调整表格结构或者语句的写法,往往能起到事半功倍的效果。所以要提醒DBA和
 52 --开发者,有时候要灵活解决问题。修改数据库或程序设计虽然比较麻烦,但是
 53 --可能会比光调SQL设置效果好很多
 54 
 55 
 56 
 57 --1、是否是因为做了物理I/O而导致性能不佳
 58 --用什么办法能知道一条语句的运行有没有做物理I/O呢?怎样才知道,一条语句
 59 --在数据提前缓存在内存里的前提下,要运行多久呢?这个信息DBA要打开
 60 SET STATISTICS IO ON
 61 --
 62 SET STATISTICS TIME ON
 63 --开关以后,运行语句,才能得到
 64 --例如,运行下面这段话,因为他在运行之前使用
 65 DBCC DROPCLEANBUFFERS
 66 --指令清除了缓冲池里所有缓存的页面,所以一定会做物理I/O动作。开启
 67 SET STATISTICS IO ON
 68 --
 69 SET STATISTICS TIME ON --就能知道问题的答案
 70 
 71 DBCC DROPCLEANBUFFERS
 72 --清除buffer pool里的所有缓存的数据页面
 73 GO
 74 
 75 SET STATISTICS IO ON
 76 GO
 77 
 78 SET STATISTICS TIME ON
 79 GO
 80 
 81 USE [AdventureWorks]
 82 GO
 83 
 84 SELECT
 85 DISTINCT [ProductID],
 86 [UnitPrice]
 87 FROM [dbo].[SalesOrderDetail_test]
 88 WHERE [ProductID]=777
 89 GO
 90 
 91 --DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。
 92 --SQL Server 分析和编译时间:
 93 --   CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
 94 --
 95 --SQL Server 执行时间:
 96 --   CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
 97 --SQL Server 分析和编译时间:
 98 --   CPU 时间 = 16 毫秒,占用时间 = 188 毫秒。
 99 --SQL Server 分析和编译时间:
100 --   CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
101 --
102 --(4 行受影响)
103 --表 'SalesOrderDetail_test'。扫描计数 5,逻辑读取 15064 次,物理读取 0 次,预读 15064 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
104 --
105 --SQL Server 执行时间:
106 --   CPU 时间 = 187 毫秒,占用时间 = 2108 毫秒。
107 
108 --检查SET STATISTICS IO ON的输出,会发现表[SalesOrderDetail_test]的
109 --“物理读取”和“预读”不为0.这意味着语句做了物理I/O。运行这个例子的机器是台
110 --I/O很慢的机器,所以语句花了11009ms才跑完
111 
112 
113 --立刻再运行一次查询,不清空缓存区
114 SET STATISTICS IO ON
115 GO
116 SET STATISTICS TIME ON
117 GO
118 USE [AdventureWorks]
119 GO
120 SELECT DISTINCT [ProductID],[UnitPrice]
121 FROM [dbo].[SalesOrderDetail_test]
122 WHERE [ProductID]=777
123 GO
124 
125 --这次由于语句刚刚跑过,内存里的数据页还在,可以感觉到速度明显不同
126 --SQL Server 分析和编译时间:
127 --   CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
128 --
129 --SQL Server 执行时间:
130 --   CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
131 --SQL Server 分析和编译时间:
132 --   CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
133 --
134 --SQL Server 执行时间:
135 --   CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
136 --SQL Server 分析和编译时间:
137 --   CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
138 --
139 --SQL Server 执行时间:
140 --   CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
141 --SQL Server 分析和编译时间:
142 --   CPU 时间 = 16 毫秒,占用时间 = 125 毫秒。
143 --SQL Server 分析和编译时间:
144 --   CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
145 --
146 --(4 行受影响)
147 --表 'SalesOrderDetail_test'。扫描计数 5,逻辑读取 15064 次,物理读取 0 次,预读 15064 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
148 --
149 --SQL Server 执行时间:
150 --   CPU 时间 = 218 毫秒,占用时间 = 1743 毫秒。
151 
152 
153 
154 --检查SET STATISTICS IO ON的输出,会发现表[SalesOrderDetail_test]的
155 --“物理读取”和“预读”都是0。这意味着语句完全没有做物理I/O
156 --语句花了262ms就跑完。可见在这台服务器上,做不做物理I/O的差别有多大
157 
158 --如果你在单个语句调优时发现性能问题,只在物理I/O的时候才出现,那需要做的事情
159 --有下面几件:
160 
161 --(1)检查生产服务器是否有内存瓶颈,是否存在经常换页的现象
162 --如果生产环境下,内存没有瓶颈,或者很少有page out/page in的动作,那说明
163 --SQL能够把数据页面维护在内存里,你看到的性能问题就不太会发生,所以无须太过忧虑
164 --如果有明显的内存瓶颈,那首先要解决的是SQL服务器层面的内存瓶颈。因为这个
165 --瓶颈不但会造成这句话运行慢,也会影响其他语句的性能
166 
167 --(2)检查这句话,和他访问的数据,是被经常使用的,还是偶尔使用的
168 --如果问题语句经常会被不同的用户调用到,那按道理他说访问的数据应该缓存在
169 --内存中。如果这句话只是被某个特定的用户或任务偶尔调用到,而他访问的数据量既大
170 --其他人又不经常使用,那SQL没有把他们放在内存里也是正常的。对这样的语句,
171 --其运行时间里有物理I/O时间是合理的。这个可能要和最终用户做一下沟通,设定
172 --他们合理的性能期望值。并且安排他们在非业务高峰期时运行,以避免影响
173 --其他关键业务
174 
175 
176 --(3)检查语句执行计划,是否能减少其访问的数据量
177 --语句访问的数据量越小,要做的物理I/O就越少,当然能够越快。从这个角度
178 --做语句本身的调优也是有用的。
179 
180 --(4)检查磁盘子系统的性能
181 --如果语句访问的数据很可能就不在内存里,而其数据量也很大,还一定要提高其性能,
182 --那唯一的出路就是提高磁盘子系统的性能了。这种情况,SQL内部的设置可能帮不了
183 --什么忙。要做的工作可能更多地会在硬件层面
184 
185 
186 
187 
188 --2、是否是因为编译时间长而导致性能不佳
189 --一般DBA需要对两类语句重点检查编译时间。一种是比较简单,长度比较短
190 --涉及表格比较少,但是在应用或任务里反复调用的语句。比较简单的语句执行
191 --起来会比较快。如果一句话执行只要100毫秒,编译也要100毫秒,那SQL
192 --花这麽多时间编译似乎有点不划算。如果能够通过执行计划重用来去除
193 --编译时间,或者通过调整数据库设计来降低编译时间,那整体的效率就能够
194 --提高40%~50%。如果语句只执行一次,从200毫秒降低到100毫秒似乎意义不大
195 --但是,如果一个任务要调用同样的语句成千上百次,效果就很明显了。还有
196 --一类,是语句本身比较复杂,或者其所基于的表格上有太多的索引可供
197 --选择,使得编译时间超过1秒,甚至更长
198 
199 --比较好的是,用户可以有两种方法检查语句的编译时间。SET STATISTICS TIME ON
200 --当然是一种方法,但是其缺点是只能在测试连接里收集,不能收集到应用发过来
201 --的语句信息。使用SQL Trace,也可以收集到语句的编译时间,而且没有限制,
202 --可以收集到任何一个连接的信息
203 
204 --下面用一个存储过程,来模拟一句会有比较长的编译时间的语句。存储过程
205 --的主体是一个三张表的查询,其中有一个“in”子句。根据代入参数@i的值,
206 --脚本会为其代入相应个数的SaleOrderID值。如果代入的值很多,“in”子句很长,
207 --语句本身就变得很长,SQL就要花比较多的时间和资源做编译
208 USE [AdventureWorks]
209 GO
210 DROP PROC LongCompile
211 GO
212 
213 CREATE PROC LongCompile(@i INT)
214 AS
215 DECLARE @cmd VARCHAR(MAX)
216 DECLARE @j INT
217 SET @j =0
218 SET @cmd='
219 select count(b.SalesOrderID),sum(p.Weight)
220 from dbo.SalesOrderHeader_test a
221 inner join dbo.SalesOrderDetail_test b
222 on a.SalesOrderID=b.SalesOrderID
223 inner join Production.Product p
224 on b.ProductID=p.ProductID
225 where a.SalesOrderID in(43659'
226 WHILE @j<@i
227 BEGIN
228 SET @cmd=@cmd+','+STR(@j+43659)
229 SET @j=@j+1
230 END
231 SET @cmd=@cmd +')'
232 EXEC(@cmd)
233 GO
234 
235 --SET STATISTICS TIME ON的检查方法前一章已经介绍过,这里不重复了。现在使用
236 --SQL Trace里的下面事件来跟踪语句的编译时间。这种方法得到的结果,虽然没有
237 --SET STATISTICS TIME ON的那么精确,但还是可以拿来参考的
238 
239 --编译会发生下面情况:
240 --(1)一个新的批处理batch开始运行前
241 --(2)一个新的存储过程stored procedure开始运行前
242 --(3)语句发生重编译recompile的时候
243 
244 --SQL Trace的事件不会直接打印出编译时间,但是你可以比较某些事件开始时间点starttime
245 --之间的间隔,算出当时的编译时间
246 
247 --如果你发现语句性能问题和编译有关系,必须考虑的方向有:
248 --(1)检查语句本身是否过于复杂,长度太长
249 --如果是,可以考虑把一句话拆成几句更简单的语句,或者用temp table来替代大的
250 --“in”子句。
251 --因为SQL是先编译整个batch,然后再开始运行第一句话
252 --因为SQL是先编译整个SP,然后再运行第一句话
253 
254 --(2)检查语句使用的表格上是不是有太多的索引
255 --索引越多,SQL要评估的执行计划就越多,花的时间越长。作为一个设计严谨的数据库,
256 --要把没有用的索引及时删除
257 
258 --(3)引导SQL尽量多重用执行计划,减少编译
259 
260 
261 --3、判断执行计划是否合适
262 --如果SQL选错了执行计划,那当然会直接影响到语句的性能。在排除了数据缓存和
263 --编译这两大因素以后,用户就要来检查SQL现在选择的执行计划是否正确。但是,
264 --什么是“正确”的执行计划呢?大部分情况下SQL计算得会比人脑要准确,他选择
265 --的执行计划是有道理的。要找出一个比他选的更好的执行计划,不是那么容易
266 
267 --但是SQL还是会犯错误。通常可以从以下这几个角度,判断现在得到的执行计划是否
268 --准确,以及有没有提高的空间
269 
270 ---------------------------------------------------------------------------------------------------
271 --预估cost的准确性
272 --之前介绍过SQL计算一个候选的执行计划开销的算法,他是根据EstimateRows、AvgRowSize
273 --和每一步要做的事情,估算出EstimateIO和EstimateCPU,然后再根据这两个值算出
274 --TotalSubtreeCost。SQL在候选的执行计划中,挑一个他算出来TotalSubtreeCost
275 --最低的。而一般AvgRowSize是不会估错的,所以,如果SQL选择的执行计划有问题,
276 --常常是因为EstimateRows估错。
277 
278 --因此判断每一个子句的TotalSubtreeCost是否准确,常常就转化为检查EstimateRows和
279 --真实Rows的差别。这两个数据,可以通过
280 SET STATISTICS PROFILE ON
281 --的输出获得,也可以在SQL Trace里showplan statistics profile事件里得到
282 
283 --需要说明的是,当SQL预估某一步不会记录返回时,他不是把EstimateRows设置为0,
284 --而是设置为1。所以EstimateRows等于1,常常意味着SQL认为该步骤不会有记录返回。
285 --如果实际的Rows不是0,也不是1,而EstimateRows等于1,那就要好好检查SQL在
286 --这里的预估开销是否准确,是否会影响到执行计划的准确性
287 
288 --之前讲过一个案例突然很慢,重建索引以后性能就会恢复正常的查询。当时说,通过
289 --分析语句执行计划和统计信息,作者将问题定位到了两个表的联接动作上。是怎麽
290 --定位到这一点呢?
291 
292 --查询的执行计划很复杂,下面的拷屏是其中的主体部分
293 
294 --可以看到查询做了很多联接动作,而且都是用nested loops完成的。在拿到执行计划
295 --以后,第一步要做的事情,不是去看哪一部分的TotalSubtreeCost最高,而是要
296 --比较Rows和EstimateRows这两列的值差别大不大。我们把EstimateRow这一列,拷贝
297 --到执行计划的开头。这样比较起来就方便多了。
298 
299 
300 --在这个执行计划里,Rows的值和EstimateRows的值有明显差距。根据SQL的预估,
301 --联接最里面的几层应该不返回记录(EstimateRows=1.0)。而实际上,他们返回
302 --了9653条记录。这麽大的结果集,用nested loops是不太合适的。直接的结果,
303 --是Executes这一列的值在很多步骤上很高。从这种现象,笔者开始怀疑他是不是
304 --问题的直接原因
305 
306 --很有利的是,还有一份查询速度正常的时候的执行计划。从这份执行计划里,可以
307 --看到正确的做法和那时的EstimateRows值
308 
309 --很明显,在运行正常的时候,SQL预估的值要高得多,所以SQL选择的是HASH MATCH JOIN
310 --和MERGE JOIN的方式。这证明了前面的猜想。在查询有性能问题的时候,SQL没有正确
311 --地估计联接返回记录的多少,所以错误地估计了nested loops联接动作的开销,选择了
312 --一个错误的执行计划
313 
314 --要强调的是,执行计划没有绝对的对错。只是一种执行计划在某种上下文,是合适的。
315 --但是在另一种上下文,可能又是不合适的。如果能够拿到一份既有EstimateRows列,
316 --又有Rows列的执行计划,那比较一下两列的差别,问题就能比较清楚。如果因为
317 --种种限制而拿不到两列的值,可能就要靠经验来猜测了
318 
319 --现在再来做一个案例。在这个案例里,读者不但要用到读取执行计划的知识,还要
320 --用到编译和重编译的知识
321 
322 --有一个查询,会返回大约12万条记录。请运行他们三次。第一次是正常运行,
323 --第二次运行之前,会先清空执行计划,然后设置语句只返回一条记录(set rowcount 1)
324 --第三次运行之前,不清空执行计划,但是设置语句返回所有记录(set rowcount 0)
325 --按道理,第一次运行和第三次运行应该速度相当,因为他们都要返回12万条记录。
326 --第二次会最快,因为他只返回一条记录
327 
328 --第一次
329 USE [AdventureWorks]
330 GO
331 DBCC DROPCLEANBUFFERS --清空缓存数据页面 buffer pool
332 GO
333 
334 DBCC freeproccache  --清空执行计划
335 GO
336 
337 SET ROWCOUNT 0
338 GO
339 SET STATISTICS PROFILE ON
340 SELECT p.[ProductID],p.[Weight]
341 FROM [dbo].[SalesOrderHeader_test] a
342 INNER JOIN [dbo].[SalesOrderDetail_test] b
343 ON a.[SalesOrderID]=b.[SalesOrderID]
344 INNER JOIN [Production].[Product] p
345 ON b.[ProductID]=p.[ProductID]
346 WHERE a.[SalesOrderID]=75124
347 
348 GO
349 ---------------------------------------------------------
350 --第二次
351 USE [AdventureWorks]
352 GO
353 DBCC freeproccache
354 GO
355 SET ROWCOUNT 1
356 GO
357 SET STATISTICS PROFILE ON
358 SELECT p.[ProductID],p.[Weight]
359 FROM [dbo].[SalesOrderHeader_test] a
360 INNER JOIN [dbo].[SalesOrderDetail_test] b
361 ON a.[SalesOrderID]=b.[SalesOrderID]
362 INNER JOIN [Production].[Product] p
363 ON b.[ProductID]=p.[ProductID]
364 WHERE a.[SalesOrderID]=75124
365 
366 GO
367 
368 -----------------------------------------------------
369 --第三次
370 USE [AdventureWorks]
371 GO
372 SET ROWCOUNT 0
373 GO
374 SET STATISTICS PROFILE ON
375 SELECT p.[ProductID],p.[Weight]
376 FROM [dbo].[SalesOrderHeader_test] a
377 INNER JOIN [dbo].[SalesOrderDetail_test] b
378 ON a.[SalesOrderID]=b.[SalesOrderID]
379 INNER JOIN [Production].[Product] p
380 ON b.[ProductID]=p.[ProductID]
381 WHERE a.[SalesOrderID]=75124
382 
383 GO
384 
385 
386 --第三次运行代价比第一次高的原因是,他重用了set rowcount1时的执行计划,而在
387 --set rowcount 0这个执行计划是不合适的。SQL在重用执行计划的时候没有考虑到
388 --set rowcount的情况,是他不够聪明的地方。希望在将来版本里,SQL能够有所改进
389 
390 -----------------------------------------------------------------------------------------------------
391 --是Index Seek还是Table Scan
392 --检查执行计划的第二个重点,是要检查SQL从表格里检索数据的时候,是否选择了
393 --合适的方法。前面介绍过SQL检索数据的方法。在不同的数据结果上,SQL会根据
394 --检索数据的多少,以及索引的结构,选择是用Index Seek还是Table Scan
395 
396 --不同结构上的数据检索方法          扫描                查找
397 --堆(没有聚集索引的表格数据页)  表扫描                 无
398 --聚集索引                        聚集索引扫描         聚集索引查找
399 --非聚集索引                      索引扫描             索引查找
400 
401 
402 --一般来说,如果检索返回的数据量,占整个表格的数据量比较小,那么用seek还是划算的
403 --所以常常说,seek会比scan要好。但是,如果检索返回的数据量差不多是整个表格的数据
404 --的一大部分,那么索引上的seek不会有什么帮助,甚至直接用scan可能会更快一些。
405 --所以也不是scan就绝对地比seek要差,不能看到scan就要想办法变成seek。关键还是要
406 --看EstimateRows和实际的Rows的大小
407 
408 --如果某个数据检索动作实际返回的行数不多,但是SQL选择了scan方法,那就要重视了,
409 --因为这种scan,会带来比较大的性能影响,会慢几十倍,几百倍,甚至上千倍。
410 --下面是一个例子
411 --第一个查询
412 USE [AdventureWorks]
413 GO
414 SET STATISTICS PROFILE ON
415 GO
416 SELECT COUNT(b.[CarrierTrackingNumber])
417 FROM dbo.[SalesOrderDetail_test] b
418 WHERE b.[SalesOrderDetailID]>10000 AND
419 b.[SalesOrderDetailID]<=10100
420 GO
421 -------------------------------------
422 --第二个查询
423 USE [AdventureWorks]
424 GO
425 SET STATISTICS PROFILE ON
426 GO
427 SELECT COUNT(b.[CarrierTrackingNumber])
428 FROM [dbo].[SalesOrderDetail_test] b
429 WHERE CONVERT(NUMERIC(9,3),b.[SalesOrderDetailID]/100)=100
430 GO
431 
432 --第一个查询很简单,[SalesOrderDetailID]字段是表格的聚集索引所在字段
433 --所以很自然地,SQL使用的是clustered index seek
434 
435 --第二个查询的意思,和第一个一样,但是写法不同,在[SalesOrderDetailID]
436 --字段上做了计算。其结果是直接妨碍了SQL在这个字段的索引上使用seek
437 --SQL将不得不用scan的方法,找出所有符合条件的记录。但是这里,
438 --SQL做得还是比较聪明的
439 
440 --SQL知道,如果要去scan整个表,是一件非常浩大的工程.所以他找找自己
441 --有没有其他索引覆盖了[SalesOrderDetailID]这个字段。因为索引只
442 --包含了表格的一小部分字段,占用的页面数量会比表格本身要小很多。
443 --去scan这样的索引,可以大大降低scan的消耗。非聚集索引SalesOrderDetail_test_NCL
444 --就是这样的一个索引。SQL选择了在他身上做scan。这是上面这个
445 --index scan的来源。美中不足的是,这个非聚集索引没有覆盖[CarrierTrackingNumber]
446 --这个字段。所以SQL还要根据挑出来的记录的[SalesOrderDetailID]
447 --值,到聚集索引SalesOrderDetail_test_CL上去找[CarrierTrackingNumber]
448 --这是第二个clustered index seek的来源
449 
450 --这样做,显然比第一种做法要费事很多,所以两句话的性能是有很大差别的
451 --第一句话只要3个毫秒就做完了,而第二句话用953毫秒。两者相差300多倍,
452 --CPU和Reads的消耗也有很大差别
453 
454 --那scan就一定比seek差麽?还是用这张表,再试一个例子。先创建一个存储过程,
455 --在[SalesOrderDetail_test]表上返回指定的6个SalesOrderID的所有记录的
456 --COUNT(b.[CarrierTrackingNumber])值
457 
458 USE [AdventureWorks]
459 GO
460 DROP PROC Scan_Seek
461 GO
462 CREATE PROC Scan_Seek(@i INT)
463 AS
464 SELECT COUNT(b.[CarrierTrackingNumber])
465 FROM [dbo].[SalesOrderDetail_test] b
466 WHERE b.[SalesOrderDetailID]>@i AND
467 b.[SalesOrderID]<@i+7
468 GO
469 
470 
471 --在[SalesOrderID]等于43659到75123之间的记录里,每个[SalesOrderID]
472 --平均有三四条记录。但是在75124到75132之间,每个[SalesOrderID]都有
473 --12万条记录。所以对于这个查询,如果@i的值小于75115,那么检索条件
474 --只会返回几十条记录,占表格的很小一部分。如果@i的值等于75124,那
475 --这个查询就会返回表格的绝大多数记录。在这两种情况下,SQL会选择
476 --同样的执行计划麽?如果是的,结果会怎样?
477 
478 --用下面的脚本测试一下
479 USE [AdventureWorks]
480 GO
481 [sys].[sp_recompile] @objname = N'Scan_Seek' -- nvarchar(776)
482 GO
483 SET STATISTICS PROFILE ON
484 EXEC [dbo].[Scan_Seek]  @i = 75124 -- int
485 GO
486 --------------------------------------------------------------
487 [sys].[sp_recompile] @objname = N'Scan_Seek' -- nvarchar(776)
488 GO
489 SET STATISTICS PROFILE ON
490 EXEC [dbo].[Scan_Seek]  @i = 43659 -- int
491 GO
492 -------------------------------------------------------------------
493 SET STATISTICS PROFILE ON
494 EXEC [dbo].[Scan_Seek]  @i = 75124 -- int
495 GO
496 
497 --第一次运行和第二次运行前,因为都运行了[sp_recompile],存储过程
498 --都发生了编译。第一次要计算的数据量比较大,用了808毫秒。第二次
499 --数据量小,只用了6毫秒。这个差异是数据量的差异造成的。
500 
501 --但是第三次运行,代入的参数和第一次一样,却用了5391毫秒。CPU时间
502 --和Reads要高很多。这是为什麽呢?分析一下两次的执行计划差异
503 
504 --第一次运行的时候,SQL选择的是一个clustered index scan的检索方法,
505 --直接scan整个表格,把要取的数据都找出来,最后返回727902行。相对于
506 --数据量,这是一个比较合适的做法
507 
508 --第二次运行和第三次运行的执行计划是一样的,SQL选择SalesOrderDetail_test_NCL
509 --这个索引,找出所有 b.[SalesOrderDetailID]>@i AND b.[SalesOrderID]<@i+7
510 --的记录。索引能够返回SalesOrderID的值,但不能返回CarrierTrackingNumber
511 --的值。所以光seek这个索引不能完成查询。SQL还要根据挑出来的记录的
512 --SalesOrderDetailID值,到聚集索引SalesOrderDetail_test_CL上去找
513 --CarrierTrackingNumber。所以这里有一个clustered index seek,也有
514 --一个nested loops(其实这个nested loops就是一个bookmark lookup)
515 
516 --这样的方法当b.[SalesOrderDetailID]>@i AND b.[SalesOrderID]<@i+7
517 --的记录比较少的时候,是比较有效的。但是像现在这种情况,符号条件
518 --的记录有727902行,SQL在聚集索引上就不得不做727902次lookup。所以
519 --最后的代价,反而比第一次运行,做整个表格的scan还要慢。这是一个
520 --很好的seek反而比scan要慢的例子
521 
522 --因此,用户还是要比较实际返回行数和表格的整体行数,具体看,到底
523 --在语句的上下文,是scan好,还是seek好
524 
525 
526 --------------------------------------------------------------------------------
527 --是nested loops还是hash (merge) join
528 
529 
530 --之前详细介绍了SQL的三种join方式,也提到了,nested loops比较合适于
531 --联接的双方结果集比较小的情况,而hash(merge)join 适合结果集比较大
532 --的情况。做这麽详细的介绍,是因为很多SQL没有选对执行计划,而导致
533 --性能问题,都是因为误选了nested loops导致的。当outer table比较大
534 --时,使用nested loops方法,inner table会被loop很多次,导致执行
535 --复杂度急剧增加
536 
537 --前面的例子,已经对这个问题有所体现。现在再来写一个例子,加深一下印象
538 --同样还是一个存储过程
539 USE [AdventureWorks]
540 GO
541 DROP PROC Sniff
542 GO
543 CREATE PROC Sniff(@i INT)
544 AS
545 SELECT *
546 FROM [dbo].[SalesOrderHeader_test] a
547 INNER JOIN [dbo].[SalesOrderDetail_test] b
548 ON a.[SalesOrderID]=b.[SalesOrderID]
549 INNER JOIN [Production].[Product] p
550 ON b.[ProductID]=p.[ProductID]
551 WHERE a.[SalesOrderID]=@i
552 GO
553 
554 --然后运行一下
555 USE [AdventureWorks]
556 GO
557 DBCC freeproccache
558 GO
559 SET STATISTICS TIME ON
560 SET STATISTICS PROFILE ON
561 EXEC [dbo].[Sniff] @i = 50000 -- int
562 GO
563 ----------------------------------------------------
564 USE [AdventureWorks]
565 GO
566 DBCC freeproccache
567 GO
568 SET STATISTICS TIME ON
569 SET STATISTICS PROFILE ON
570 EXEC [dbo].[Sniff] @i = 75124 -- int
571 GO
572 
573 
574 --第二次以75124为参数的运行要花3125毫秒。执行计划里都是用nested loops
575 
576 --一般来说,如果看到一个执行计划的Executes值很大,又和一个nested loops
577 --相关,那就应该好好研究一下这个nested loops是否合适
578 
579 
580 ------------------------------------------------------------------------------
581 --Filter运算的位置
582 --在一句查询里,常见的是几个表格做联接,同时又有一些where子句filter掉
583 --一些记录。那么是先filter掉记录,再做联接好呢,还是先做联接,再filter呢?
584 --两种方法都能计算出正确结果。但一般来讲,先filter掉一些记录,使得做联接
585 --的记录集小一点,会大大降低联接的消耗。所以filter先做,会提高查询效率
586 --在检查执行计划的时候,用户也要看看,是不是SQL及时做了filter
587 
588 --现在有两个查询。对用户来讲,他们是一样的,只不过第二个查询把p.ProductID
589 --加了个1以后再做比较。返回的结果集也会一样
590 --查询一
591 USE [AdventureWorks]
592 GO
593 SET STATISTICS PROFILE ON
594 GO
595 SELECT COUNT(b.[ProductID])
596 FROM [dbo].[SalesOrderHeader_test] a
597 INNER JOIN [dbo].[SalesOrderDetail_test] b
598 ON a.[SalesOrderID]=b.[SalesOrderID]
599 INNER JOIN  [Production].[Product] p
600 ON b.[ProductID]=p.[ProductID]
601 WHERE p.[ProductID] BETWEEN 758 AND 800
602 GO
603 -----------------------------------------------
604 --查询二
605 USE [AdventureWorks]
606 GO
607 SET STATISTICS PROFILE ON
608 GO
609 SELECT COUNT(b.[ProductID])
610 FROM [dbo].[SalesOrderHeader_test] a
611 INNER JOIN [dbo].[SalesOrderDetail_test] b
612 ON a.[SalesOrderID]=b.[SalesOrderID]
613 INNER JOIN  [Production].[Product] p
614 ON b.[ProductID]=p.[ProductID]
615 WHERE (p.[ProductID]+1) BETWEEN 759 AND 801
616 GO
617 
618 
619 --但是两条语句的执行效率却有很大差别,第二条比第一条慢一倍还要多,
620 --CPU时间也高。这是为什麽呢?
621 --这里要分析这两句话的执行计划。先大致看一下,SQL在做返回结果集大小预估
622 --的时候,做得是不是准确。为了方便比较,我把EstimateRows和TotalSubtreeCost
623 --这两列拷贝到了前面
624 
625 --两个查询的执行计划区别
626 --不管是第一句还是第二句,他们的EstimateRows和Rows的值都比较接近,所以SQL
627 --在cost预估上是准确的。现在可以通过分析TotalSubtreeCost,来找到语句最花
628 --资源的地方。
629 --这两句的主要消耗,都在一个hash match的联接上。但是两句的消耗又不一样。
630 --第一句的hash match,是一个31474的结果集和一个225990的结果集联接,cost是
631 --15.59,结果集大小是225990行。第二句的hash match,是一个31474的结果集和
632 --一个1213170的结果集联接,cost是19.09,大小是1213170行。他们的子步骤的
633 --cost是一样的,0.63和12.49。所以cost的差别就在hash match这个动作上,
634 --而原因就是联接双方的结果集,第二句的要比第一句大得多
635 
636 --让我们仔细研究这两个执行计划,看看是什么导致了他们的差别
637 
638 --第一句的做法,是dbo.SalesOrderDetail_test和dbo.SalesOrderHeader_test
639 --这两张表先做join,再和product表做join。有意思的是,虽然语句里只有一个where
640 --子句:WHERE p.[ProductID] BETWEEN 758 AND 800,但是执行计划里可以看到
641 --两个filter动作。一个在dbo.SalesOrderDetail_test上,一个在Product上。
642 --这是因为SQL发现这两张表将要通过ON b.[ProductID]=p.[ProductID]
643 --做联接,所以在Product上的条件,同样适合在dbo.SalesOrderDetail_test上
644 --这样,SQL先在dbo.SalesOrderDetail_test上做一个filter,结果集就小得多
645 --再做join,花费就能节省不少
646 
647 
648 --第二句的做法,也是SalesOrderHeader_test和SalesOrderDetail_test
649 --这两张表先做join,再和Product做join。但是filter动作只发生在Product上
650 --没有发生在SalesOrderDetail_test上。所以
651 --SalesOrderHeader_test和SalesOrderDetail_test做联接的时候,结果集会大一些
652 --产生这种现象的原因,是语句的where子句的写法:
653 --WHERE (p.[ProductID]+1) BETWEEN 759 AND 801
654 --SQL没有办法把这样的filter也适用在SalesOrderDetail_test这张表上
655 
656 --从这个例子,可以看出filter动作的位置对语句性能的影响
657 
658 ---------------------------------------------------------------------------------------------
659 --确认问题产生的原因
660 --现在总结一下产生问题的原因有哪些:
661 --(1)预估返回结果集大小(EstimateRows)不准确,导致执行计划实际TotalSubtreeCost
662 --比预估的高很多
663 --统计信息不存在,或者没有及时更新,是产生这个问题的主要原因。
664 --应对办法:开启“自动创建统计信息”,“自动更新统计信息”
665 --如果这样还不能保证统计信息的精确性,可以定义一个任务,定期更新统计信息
666 
667 
668 --子句太过复杂,也可能使SQL猜不出一个准确的值,只好猜一个平均数。比如
669 --where子句里对字段做计算,代入函数等行为,都可能会影响SQL预估的准确性。
670 --如果发现这种情况,就要想办法简化语句,降低复杂度,提高效率
671 --当语句代入的变量值是一个参数,而SQL在编译的时候可能不知道这个参数的值,
672 --只好根据某些规则,“猜”一个预估值,这也会影响到预估的准确性。这个话题
673 --就是“参数嗅探”
674 
675 
676 --(2)语句重用了一个不合适的执行计划
677 --SQL的执行计划重用机理,是一次编译,多次重用。根据代入的第一个参数值进行编译
678 --以后不管参数值是多少,都重用根据前面的那个值编译出来的执行计划。这对一些
679 --数据分布比较均匀的表格是没有问题的,例如我们的dbo.SalesOrderHeader_test
680 --不管你代入什么值,返回的结果集数量都差不多
681 
682 --但是,有些表格的数据分布不均匀,例如前面使用的dbo.SalesOrderDetail_test。
683 --他在某些值上,重复的记录很少,但是在另外一些值上,重复的记录又很多。这
684 --导致了对于不同的值,SQL必须使用不同的执行计划才能达到最优效率。如果
685 --重用的执行计划不合适,就会出现性能问题。
686 
687 --关于这个话题:参数嗅探会说到
688 
689 
690 --(3)筛选子句写得不太合适,妨碍SQL选取更优的执行计划
691 --当语句要筛选掉一些记录时,索引会帮上忙。一般来讲,筛选动作做得越早,越能
692 --提高效率。SQL对筛选条件(search argument/SARG)的写法有一定建议
693 --筛选条件应该采用以下格式之一:
694 --列名 运算符 <常量或变量>
695 --<常量或变量>  运算符 列名
696 
697 --例如:
698 name='annie'
699 amount>4000
700 6000<amount
701 department='hr'
702 
703 --SARG运算符包括:=、>、<、>=、<=、in、between、like(在进行前缀匹配时,
704 --如like'john%')
705 --SARG可以包括由AND联接的多个条件。
706 
707 --SARG不但可以是匹配特定值的查询,
708 --例如:
709 name='annie'
710 
711 --还可以是匹配一定范围的值的查询,例如:
712 amount>4000 and amount<6000
713 customerid IN('action','about')
714 
715 --对于不是有SARG运算符的表达式,索引是没有用的,SQL对它们很难使用
716 --比较优化的做法。非SARG运算符包括NOT、<>、NOT EXISTS、NOT IN、NOT LIKE
717 --和内部函数,例如:
718 CONVERT(),UPPER()等
719 
720 --当确认了执行计划出问题的原因,就可以对症下药,引导SQL总是选择一个
721 --好的执行计划,当然在有些情况下,基于现有语句和数据库结构,很难
722 --选择更好的执行计划。那就要通过修改数据库结构,或者修改语句设计
723 --来解决问题,提高效率

 

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