笔记52-徐 读懂语句运行的统计信息

笔记52-徐 读懂语句运行的统计信息

  1 --读懂语句运行的统计信息 2013-1-2 P397
  2 
  3 --对于语句的运行,除了执行计划本身,还有一些其他因素要考虑,例如
  4 --语句的编译时间、执行时间、做了多少次磁盘读等。如果DBA能够把
  5 --问题语句单独测试运行,可以在运行前打开下面这三个开关,收集
  6 --语句运行的统计信息。这些信息对分析问题也很有价值
  7 
  8 SET STATISTICS TIME ON
  9 SET STATISTICS IO ON
 10 SET STATISTICS PROFILE ON
 11 
 12 --要说明的是,后面的测试结果,是在一台很普通的PC上获得的。读者机器
 13 --上得到的结果,可能会比书上的快
 14 
 15 --SET STATISTICS TIME ON-------------------------------------------
 16 --请先来看看SET STATISTICS TIME ON会返回什么信息。先运行语句:
 17 DBCC DROPCLEANBUFFERS
 18 --清除buffer pool里的所有缓存数据
 19 DBCC freeproccache
 20 GO
 21 
 22 --清除buffer pool里的所有缓存的执行计划
 23 SET STATISTICS TIME ON
 24 GO
 25 USE [AdventureWorks]
 26 GO
 27 SELECT DISTINCT([ProductID]),[UnitPrice] FROM [dbo].[SalesOrderDetail_test]
 28 WHERE [ProductID]=777
 29 --UNION
 30 --SELECT [ProductID],[UnitPrice] FROM [dbo].[SalesOrderDetail_test]
 31 --WHERE [ProductID]=777
 32 GO
 33 SET STATISTICS TIME OFF
 34 GO
 35 
 36 
 37 --除了结果集之外,SQL还会返回下面这两段信息
 38 --SQL Server 分析和编译时间:
 39 --   CPU 时间 = 15 毫秒,占用时间 = 104 毫秒。
 40 --SQL Server 分析和编译时间:
 41 --   CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
 42 --
 43 --(4 行受影响)
 44 --
 45 --SQL Server 执行时间:
 46 --   CPU 时间 = 171 毫秒,占用时间 = 1903 毫秒。
 47 --SQL Server 分析和编译时间:
 48 --   CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
 49 
 50 --1、CPU时间
 51 --这个值的含义指的是在这一步,SQL所花的纯CPU时间是多少。也就是说,语句花了
 52 --多少CPU资源
 53 
 54 --2、占用时间
 55 --此值指这一步一共用了多少时间。也就是说,这是语句运行的时间长短
 56 --有些动作会发生I/O操作,产生了I/O等待,或者是遇到阻塞、产生了阻塞等待。
 57 --总之时间用掉了,但是没有用CPU资源。所以占用时间比CPU时间长是很正常的
 58 --但是CPU时间是语句在所有CPU上的时间总和。如果语句使用了多颗CPU,而其他
 59 --等待几乎没有,那么CPU时间大于占用时间也是正常的
 60 
 61 --3、分析和编译时间
 62 --这一步,就是语句的编译时间。由于语句运行之前清空了所有执行计划,SQL必须要对
 63 --他编译。这里的编译时间就不为0了。由于编译主要是CPU的运算,所以一般CPU时间
 64 --和占用时间是差不多的。如果这里相差比较大,就有必要看看SQL在系统资源上有没有
 65 --瓶颈了。
 66 
 67 --这里他们是一个15毫秒,一个是104毫秒
 68 
 69 
 70 --4、SQL执行时间
 71 --语句真正运行的时间。由于语句是第一次运行,SQL需要把数据从磁盘读到内存里,这里
 72 --语句的运行发生了比较长的I/O等待。所以这里的CPU时间和占用时间差别就很大了,一个
 73 --是171毫秒,而另一个是1903毫秒
 74 
 75 --总的来讲,这条语句花了104+1903+186=2193毫秒,其中CPU时间为15+171=186毫秒。语句的主要时间
 76 --应该是都花在了I/O等待上
 77 
 78 --现在再做一遍语句,但是不清除任何缓存
 79 SET STATISTICS TIME ON
 80 GO
 81 
 82 SELECT DISTINCT([ProductID]),[UnitPrice] FROM [dbo].[SalesOrderDetail_test]
 83 WHERE [ProductID]=777
 84 --UNION
 85 --SELECT [ProductID],[UnitPrice] FROM [dbo].[SalesOrderDetail_test]
 86 --WHERE [ProductID]=777
 87 GO
 88 SET STATISTICS TIME OFF
 89 GO
 90 
 91   
 92 --这次比上次快很多。输出时间统计信息是:
 93 --SQL Server 分析和编译时间:
 94 --   CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
 95 --SQL Server 分析和编译时间:
 96 --   CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
 97 --
 98 --(4 行受影响)
 99 --
100 --SQL Server 执行时间:
101 --   CPU 时间 = 156 毫秒,占用时间 = 169 毫秒。
102 --SQL Server 分析和编译时间:
103 --   CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
104 
105 
106 --由于执行计划被重用,“SQL分析和编译时间” CPU时间是0,占用时间是0
107 
108 
109 --由于数据已经缓存在内存里,不需要从磁盘上读取,SQL执行时间 CPU时间是156
110 --占用时间这次和CPU时间非常接近,是169。这里省下运行时间1903-169=1734毫秒
111 --从这里可以再次看出,缓存对语句执行性能起着至关重要的作用
112 
113 --为了不影响其他测试,请运行下面的语句关闭SET STATISTICS TIME ON
114 SET STATISTICS TIME OFF
115 GO
116 
117 
118 
119 --SET STATISTICS IO ON--------------------------------------------------
120 
121 --这个开关能够输出语句做的物理读和逻辑读的数目。对分析语句的复杂度有很重要的作用
122 --还是以刚才那个查询作为例子
123 DBCC DROPCLEANBUFFERS
124 GO
125 SET STATISTICS IO ON
126 GO
127 
128 SELECT DISTINCT([ProductID]),[UnitPrice] FROM [dbo].[SalesOrderDetail_test]
129 WHERE [ProductID]=777
130 GO
131 
132 --他的返回是:
133 
134 --(4 行受影响)
135 --表 'SalesOrderDetail_test'。扫描计数 5,逻辑读取 15064 次,物理读取 0 次,预读 15064 次,lob 逻辑读取 0 次,
136 --lob 物理读取 0 次,lob 预读 0 次。
137 
138 
139 --各个输出的含义是:
140 --表:表的名称。这里的表就是SalesOrderDetail_test
141 
142 --扫描计数:执行的扫描次数。按照执行计划,表格被扫描了几次。一般来讲大表扫描的次数
143 --越多越不好。唯一的例外是如果执行计划选择了并发运行,由多个thread线程同时做一个表
144 --的读取,每个thread读其中的一部分,但是这里会显示所有thread的数目。也就是有几个
145 --thread在并发做,就会有几个扫描。这时数目大一点没问题的。
146 
147 --逻辑读取:从数据缓存读取的页数。页数越多,说明查询要访问的数据量就越大,内存消耗
148 --量越大,查询也就越昂贵。可以检查是否应该调整索引,减少扫描的次数,缩小扫描范围
149 
150 
151 --物理读取:从磁盘读取的页数
152 
153 
154 --预读:为进行查询而预读入缓存的页数
155 
156 
157 --物理读取+预读:就是SQL为了完成这句查询而从磁盘上读取的页数。如果不为0,说明数据没有
158 --缓存在内存里。运行速度一定会受到影响
159 
160 
161 --LOB逻辑读取:从数据缓存读取的text、ntext、image、
162 --大值类型(varchar(max)、nvarchar(max)、varbinary(max))页的数目
163 
164 
165 --LOB物理读取:从磁盘读取的text、ntext、image、大值类型页的数目
166 
167 
168 --LOB预读:为进行查询而放入缓存的text、ntext、image、大值类型页的数目
169 
170 
171 --然后再来运行一遍,不清空缓存
172 SET STATISTICS IO ON
173 GO
174 
175 SELECT DISTINCT([ProductID]),[UnitPrice] FROM [dbo].[SalesOrderDetail_test]
176 WHERE [ProductID]=777
177 GO
178 
179 --结果集返回:
180 --表 'SalesOrderDetail_test'。扫描计数 5,逻辑读取 15064 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,
181 --lob 物理读取 0 次,lob 预读 0 次。
182 
183 --这次逻辑读取不变,还是15064页。但是物理读取和预读都是0了。说明数据已经缓存在内存里
184 --第二次运行不需要再从磁盘上读一遍,节省了时间
185 
186 
187 --为了不影响其他测试,请运行下面语句关闭SET STATISTICS IO ON
188 SET STATISTICS IO OFF
189 GO
190 
191 
192 
193 --SET STATISTICS PROFILE ON--------------------------------------------
194 --这是三个设置中返回最复杂的一个,他返回语句的执行计划,以及语句运行在每一步的实际
195 --返回行数统计。通过这个结果,不仅可以得到执行计划,理解语句执行过程,分析语句
196 --调优方向,也可以判断SQL是否选择了一个正确的执行计划。如果不正确,原因又大概是什么呢?
197 
198 --本章前面已经使用过这个设置的返回结果来分析执行计划。现在再来完整介绍一遍。
199 --先从最简单的查询谈起
200 
201 SET STATISTICS PROFILE ON
202 GO
203 SELECT DISTINCT([ProductID]),[UnitPrice] FROM [dbo].[SalesOrderDetail_test]
204 WHERE [ProductID]=777
205 GO
206 
207 --返回的结果集很长,下面说一下重要字段
208 --注意:这里是从最下面开始向上看的,也就是说从最下面开始一直执行直到得到结果集
209 --所以(行1)里的rows字段显示的值就是这个查询返回的结果集。而且有多少行
210 --表明SQL执行了多少个步骤
211 
212 
213 --rows:执行计划的每一步返回的实际行数
214 
215 --executes:执行计划的每一步被运行了多少次
216 
217 --stmttext:执行计划的具体内容。执行计划以一棵树的形式显示。每一行,都是运行的一步
218 --都会有结果集返回,也都会有自己的cost
219 
220 --estimaterows:SQL根据表格上的统计信息,预估的每一步的返回行数。在分析执行计划时,
221 --我们会经常将rows和estimaterows这两列做对比,先确认SQL预估得是否正确,以判断
222 --统计信息是否有更新
223 
224 --estimateio:SQL根据estimaterows和统计信息里记录的字段长度,预估的每一步会产生的I/O cost
225 
226 
227 
228 --estimatecpu:SQL根据estimaterows和统计信息里记录的字段长度,以及要做的事情的复杂度,预估
229 --的每一步会产生的CPU cost
230 
231 
232 --totalsubtreecost:SQL根据estimateio和estimatecpu通过某种计算公式,计算出每一步执行
233 --计划子树的cost(包括这一步自己的cost和他的所有下层步骤的cost总和),下面介绍的cost
234 --说的都是这个字段值
235 
236 
237 --warnings:SQL在运行每一步时遇到的警告,例如,某一步没有统计信息支持cost预估等。
238 
239 
240 --parallel:执行计划的这一步是不是使用了并行的执行计划
241 
242 --所以上面这个简单的执行计划意思是:
243 --(1)执行计划分成两步:
244 --步骤a(行3)
245 --使用clustered index scan(全表扫描)的方式找出所有[ProductID]=777的记录
246 --这一步SQL预测会返回522.8行,实际返回484行。所以这一步预估的还算准确。预估cost
247 --是2.502
248 
249 --步骤b (行2)
250 --s使用sort的方式把步骤a返回的484行做一个排序,从中选出distinct的值
251 
252 --这一步SQL预测会返回146.1行,实际只返回了4行。所以这一步的预估有些不准确。这和
253 --SQL在([ProductID]),[UnitPrice] )上没有直接的统计信息有关系。好在这个结果集
254 --没有用来做其他事情,直接就是查询的结果。所以预估不太准也没有什么影响
255 
256 
257 --这一步的预估cost是2.637。除掉他的子步骤a的cost2.502,这一步自己的cost是2.637-2.502=0.135
258 
259 
260 --(2)执行计划是单线程运行的,也没有报出什么warning。应该讲,是一个正常的语句
261 
262 --(3)比较rows和estimaterows这两列,SQL没有少估计某一步的返回。而多估计的那一步(步骤b)
263 --在多估的情况下,cost依然比较小,自己cost只有0.135。实际cost因为返回结果集小,应该更低。
264 --所以可以讲,整个cost估计的还是比较准确的。主要的cost都花在了全表扫描上了(步骤a)
265 --如果要对这个语句进行调优,就要引导SQL不要使用clustered index scan这样cost大的动作。
266 --在[ProductID]上加一个索引是一个比较自然的想法
267 
268 --让我们再运行一句稍微复杂一点的语句,看看结果会怎麽样
269 SET STATISTICS PROFILE ON
270 GO
271 SELECT COUNT(b.[SalesOrderID])
272 FROM [dbo].[SalesOrderHeader_test] a
273 INNER JOIN [dbo].[SalesOrderDetail_test] b
274 ON a.[SalesOrderID]=b.[SalesOrderID]
275 WHERE a.[SalesOrderID]>43659 AND a.[SalesOrderID]<53660
276 GO
277 
278 --从这个结果看出执行计划分成4步,其中第一步又分成并列的两个子步骤
279 
280 --步骤a1(第5行):从[SalesOrderHeader_test]表里找出所有a.[SalesOrderID]>43659 AND a.[SalesOrderID]<53660
281 --的值
282 --因为表在这个字段上有一个聚集索引,所以SQL可以直接使用这个索引的seek
283 
284 --SQL预测返回10000条记录,实际也就返回了10000条记录.。这个预测是准确的。这一步的cost是0.202(totalsubtreecost)
285 --果然比前面的clustered index scan要低很多
286 
287 --步骤a2(第6行):从[SalesOrderDetail_test]表里找出所有 a.[SalesOrderID]>43659 AND a.[SalesOrderID]<53660
288 --的值
289 --因为表在这个字段上有一个非聚集索引,所以SQL可以直接使用这个索引的seek
290 
291 --这里能够看出SQL聪明的地方。虽然查询语句只定义了[SalesOrderHeader_test]表上有
292 --a.[SalesOrderID]>43659 AND a.[SalesOrderID]<53660过滤条件,但是根据语义分析,
293 --SQL知道这个条件在[SalesOrderDetail_test]上也为真。所以SQL选择先把这个条件过滤
294 --然后再做join。这样能够大大降低join的cost
295 
296 --在这一步SQL预估返回50561条记录,实际返回50577条。cost是0.127,也不高
297 
298 --步骤b(第4行):将a1和a2两步得到的结果集做一个join
299 --因为SQL通过预估知道这两个结果集比较大,所以他直接选择了Hash Match的join方法。
300 --SQL预估这个join能返回50313行,实际返回50577行。因为SQL在两张表的[SalesOrderID]上
301 --都有统计信息,所以这里的预估非常准确
302 
303 --这一步的cost等于totalsubtreecost减去他的子步骤,0.715-0.202-0.127=0.386。由于预估值
304 --非常准确,可以相信这里的cost就是实际每一步的cost
305 
306 
307 --步骤c(第3行)在join返回的结果集基础上算count(*)的值
308 --这一步比较简单,count(*)的结果总是1,所以预测值是正确的。其实这一步的cost是根据上一步
309 --(b)join返回的结果集大小预估出来的。我们知道步骤b的预估返回值非常准确,所以这一步的预估
310 --cost也不会有什么大问题
311 
312 --这棵子树的cost是0.727,减去他的子节点cost,他自己的cost是0.727-0.697=0.03。是花费很小
313 --的一步
314 
315 --步骤b(第2行):将步骤c返回的值转换为int类型,作为结果返回
316 
317 --这一步是上一步的继续,更为简单。convert一个值的数据类型所要的cost几乎可以忽略不计。
318 --所以这棵子树的cost和他的子节点相等,都是0.745。也就是说,他自己的cost是0
319 
320 --通过这样的方法,用户可以了解到语句的执行计划、SQL预估的准确性、cost的分布
321 
322 
323 
324 --------------执行计划里cost的值绝对大小问题----------------------------------
325 --经常有人会问,两句查询的cost,是不是一句比另一句高,就一定是高的那一句比较耗时?
326 --SQL是根据预测结果集(estimaterows)大小和结果集里的数据类型推算出estimateio和estimatecpu
327 --然后再根据estimateio和estimatecpu推算出totalsubtreecost。计算方法是所有SQLSERVER都一样的
328 --(只要SQLSERVER版本一样),所以在同一个SQLSERVER里,可以说,cost高的那一句一定会比较复杂。
329 --当然,前提是SQL要estimate得准确
330 
331 --对SQL来讲,他也是选择cost最低的执行计划。在前面联接一节里,那三条查询,cost最低的是使用
332 --hash match的联接方式。所以如果让SQL自己选择而不用join hint,他会选择hash match
333 
334 --但是对于不同的SQLSERVER,由于硬件差异,SQL能够调动的内存,CPU,磁盘资源也会有所差异。
335 --同样cost的语句,执行的速度会有很大不同。所以一般不会去对比从不同SQL服务器上取得的
336 --执行计划里的cost

 

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