笔记61-徐 编译与重编译 P372

笔记61-徐  编译与重编译 P372  

  1 --编译与重编译 P372 2012-12-28
  2 
  3 --当SQL收到任何一个指令,包括查询(query)、批处理(batch)、存储过程、触发器(trigger)
  4 --预编译指令(prepared statement)和动态SQL语句(dynamic SQL Statement)
  5 --要完成语法解释、语句解释,然后再进行“编译(compile)”,生成能够运行的“执行计划(execution plan)”
  6 --在编译的过程中,SQL会根据所涉及的对象的架构(schema)、统计信息以及指令的具体内容,估算
  7 --可能的执行计划,以及他们的成本(cost),最后选择一个SQL认为成本最低的执行计划
  8 
  9 --执行计划生成之后,SQL通常会把他们缓存在内存里,术语统称他们叫“plan cache”
 10 --以后同样的语句执行,SQL就可以使用同样的执行计划,而无须再做一次编译。这种
 11 --行为叫“重用(reuse)”但是有时候,哪怕是一模一样的语句,SQL下次执行还是要
 12 --再做一次编译。这种行为叫“重编译(recompile)”。执行计划的编译和重编译都是要
 13 --消耗资源的
 14 
 15 
 16 --执行计划的好坏当然决定了语句最终的执行速度。对于同样的一条语句,使用好的执行计划可能
 17 --会比差的要快几百倍,甚至上千倍。所以从这一个角度来讲,每运行一条语句,都把
 18 --他先编译一遍当然是最好的。他能够保证使用的执行计划是SQL能找到的最优的。但是SQL
 19 --每秒钟可能会运行成百上千的指令。如果每个都编译一遍,是资源的一种浪费。所以
 20 --SQL在这里也试图寻找一个平衡点,使用有限的compile/recompile,得到最好的整体性能
 21 
 22 
 23 --运行下面的指令,就能够看到SQL当前缓存的执行计划有哪些(请别在生产服务器上直接运行
 24 --因为上面往往有庞大的缓存)
 25 SELECT * FROM sys.[syscacheobjects]
 26 
 27 --对不同的指令调用方法,SQL做执行计划缓存和重用机制也有所不同。下面介绍最常见的几种
 28 --以及怎样通过跟踪sys.[syscacheobjects]视图和SQL Trace里的事件来分析是否有编译或者
 29 --执行计划重用
 30 
 31 
 32 --1、Adhoc语句
 33 --一组包含select,insert,update,delete的批处理指令。对这样的指令,只有前后完全一致
 34 --包括字母的大小写,空格,回车换行都一致,SQL才认为是两条一样的语句,才能够重用
 35 --执行计划。所以这个要求还是挺高的
 36 
 37 --例如,运行下面的指令,连续两次查询sys.[syscacheobjects]视图
 38 DBCC freeproccache
 39 GO
 40 SELECT * FROM sys.[syscacheobjects]
 41 go
 42 SELECT * FROM [sys].[syscacheobjects]
 43 go
 44 
 45 
 46 --在第二个结果集里会看到两个adhoc的执行计划。他们的差别就在于一个有回车
 47 --一个没有回车
 48 
 49 
 50 --但是如果语句是一样的,那么第二句话就可能会重用前面的执行计划。所以adhoc
 51 --语句并不一定没有执行计划的重用
 52 
 53 --例如下面,把同样的一句话跑两遍,会发现只有一个执行计划,而且他被使用过两次
 54 --(字段usecount=2)
 55 
 56 DBCC freeproccache
 57 GO
 58 SELECT  * FROM sys.[syscacheobjects]
 59 go
 60 SELECT * FROM sys.[syscacheobjects]
 61 go
 62 
 63 
 64 --在SQL Trace里,也能看到第一次执行有过编译事件(SP:CacheInsert),第二次执行就
 65 --重用了先前的执行计划(SP:CacheHit)
 66 
 67 
 68 --2、用EXEC()的方式运行动态SQL语句
 69 --有些应用程序为了开发上的灵活度,在程序运行过程中,动态地拼接一个语句字符串,然后
 70 --用exec()的方式执行。这种调用方法被称为“动态SQL dynamic SQL”。他的好处是灵活
 71 --可以根据客户的选择,动态生成指令,而不仅限于预先定义的那几种。但是他的缺点也是
 72 --太灵活,客户发过来的语句每次都不一样,或者语句主体部分是一样的,但是参数不一样
 73 --SQL都要做编译。这点和adhoc语句是一样的
 74 
 75 
 76 --例如下面这个例子,同一句话运行了三遍。第一遍和第二遍用的是一样的参数值,所以第二次
 77 --没有再做编译(有事件SP:CacheHit)。但是第三次的参数值不同,他就再编译了一遍
 78 --(SP:CacheMiss和SP:CacheInsert)
 79 
 80 
 81 DBCC freeproccache
 82 GO
 83 DECLARE @dbid VARCHAR(10)
 84 SET @dbid='9'
 85 EXEC('select * from sys.syscacheobjects where dbid<='+@dbid)
 86 SET @dbid='9'
 87 EXEC('select * from sys.syscacheobjects where dbid<='+@dbid)
 88 SET @dbid='6'
 89 EXEC('select * from sys.syscacheobjects where dbid<='+@dbid)
 90 GO
 91 
 92 
 93 
 94 --第二次运行和第三次运行SQL Trace里的事件不同
 95 
 96 --在应用里经常会有同样的语句被不同的客户以不同的参数调用到。如果一定要参数一样才能重用
 97 --执行计划,那执行计划重用的机会就很低了。所以adhoc语句和dynamic SQL不是最佳的调用
 98 --方法。在SQL里,还有其他一些方法,可以对结构相同但是参数值不同的指令做执行计划重用
 99 
100 
101 --3、自动参数化的查询(auto-parameterized query)
102 --对于一些比较简单的查询,SQL2005自己就可以做自动参数化,把语句里的参数用一个变量代替
103 --以提高执行计划的可重用性。
104 --例如,运行下面这几条语句:
105 
106 USE [AdventureWorks]
107 GO
108 DBCC freeproccache
109 go
110 SELECT [ProductID],[SalesOrderID]
111 FROM sales.[SalesOrderDetail]
112 WHERE [ProductID]>1000
113 GO
114 
115 SELECT [ProductID],[SalesOrderID]
116 FROM sales.[SalesOrderDetail]
117 WHERE [ProductID]>2000
118 GO
119 
120 SELECT * FROM sys.[syscacheobjects]
121 go
122 
123 
124 --两条查询语句一模一样,只是参数的值不同。当察看缓存里的执行计划时,就能发现
125 --SQL不但缓存了两条语句自己的执行计划,还缓存了一个参数化了的执行计划
126 
127 --如果再运行一句类似的语句:
128 SELECT [ProductID],[SalesOrderID]
129 FROM sales.[SalesOrderDetail]
130 WHERE [ProductID]>3000
131 GO
132 
133 
134 --在SQL Trace里,可以看到这句话在开始之前,先有一个SP:CacheHit的事件。语句在缓存
135 --中找到了一个参数化后的执行计划。然后他根据这个执行计划,做出了自己的adhoc执行计划
136 --(SP:CacheInsert)。这也能节省一部分编译时间
137 
138 
139 
140 --4、用sp_executesql的方式调用的指令
141 --查询自动参数化在很多种条件下是不支持的,而且他还是要为每句查询生成一个adhoc的执行计划
142 --所以他并不是减少编译的最优手段。改用sp_executesql能够更有效地增加执行计划重用。用
143 --下面这个例子,两个查询结构一样,仅参数不同
144 
145 USE [AdventureWorks]
146 GO
147 DBCC freeproccache
148 GO
149 EXEC [sys].[sp_executesql] N'SELECT p.[ProductID],p.[Name],p.[ProductNumber] FROM [Production].[Product] p
150 INNER JOIN [Production].[ProductDescription] pd
151 ON p.[ProductID]=pd.[ProductDescriptionID]
152 WHERE p.[ProductID]=@a' ,N'@a int' ,170
153 GO
154 
155 
156 EXEC [sys].[sp_executesql] N'SELECT p.[ProductID],p.[Name],p.[ProductNumber] FROM [Production].[Product] p
157 INNER JOIN [Production].[ProductDescription] pd
158 ON p.[ProductID]=pd.[ProductDescriptionID]
159 WHERE p.[ProductID]=@a' ,N'@a int' ,1201
160 GO
161 
162 SELECT * FROM sys.[syscacheobjects]
163 GO
164 
165 --在执行计划缓存里,只会看到一份执行计划(prepared),不再有adhoc的那一份
166 
167 --用SQL Trace,可以看到第一次执行时有SP:CacheInsert事件,第二次就只有SP:CacheHit事件了
168 --说明第二次语句执行已经完全不需要做编译。从而提高了执行计划重用性
169 
170 
171 --5、存储过程(stored procedure)
172 --对用户经常要调用的指令,把他们做成存储过程,既方便管理,规范脚本,又能够大大提高
173 --执行计划重用率,是值得推荐的一种做法。从SQL的角度,最好绝大多数指令都能够以存储
174 --过程的方式调用,尽量少使用Dynamic SQL的方式
175 
176 --例如把前面的那个查询做成一个存储过程,然后用EXEC加参数的方式调用。可以清楚地看见
177 --第二次调用重用了第一次执行计划生成的执行计划
178 
179 
180 
181 
182 
183 
184 -------------------------重编译----------------------------------------------------------
185 --但是有些时候,SQL为了确保返回正确的值,或者有性能上的顾虑,有意不重用缓存在内存里
186 --的执行计划,而现场编译一份。这种行为,被称为重编译(recompile)。下面是比较常见
187 --的会发生重编译的情形:
188 
189 
190 --1、当指令或者批处理所涉及的任何一个对象(表格或者视图)发生了架构(schema)变化
191 --例如,在表或者视图上添加或删除了一个字段,添加或者删除了一个索引,在表上添加或者
192 --删除了一个约束条件(constraints)等。定义发生了变化,原来的执行计划就不一定正确
193 --当然要重编译
194 
195 
196 
197 --2、运行过sp_recompile
198 --当用户在某个存储过程或者触发器上运行过sp_recompile后,下一次运行他们就会发生
199 --一次重编译。如果用户在某个表或者视图上运行了sp_recompile,那么所有引用到
200 --这张表(或者视图)的存储过程在下一次运行前,都要做重编译
201 
202 
203 
204 --3、有些动作会清除内存里的所有执行计划,迫使大家都要做重编译
205 --例如,下列动作会清除整个SQL服务器缓存的所有执行计划:
206 --Detach一个数据库
207 --对数据库做了升级,在新的服务器上,会发生执行计划清空
208 --运行了DBCC freeproccache
209 --运行了reconfigure语句
210 --运行了alter database..collate语句修改了某个数据库的字符集(collation)
211 
212 
213 --下列动作会清除SQL服务器缓存的某个数据库的执行计划:
214 --DBCC FLUSHPROCINDB
215 --* DBCC FLUSHPROCINDB (db_id)
216 --清除SQL Server 2000服务器内存中的某个数据库的存储过程缓存内容
217 DECLARE @a INT
218 SELECT @a=DB_ID('gposdb')
219 DBCC flushprocindb(@a)
220 --ALTER DATABASE ...MODIFY NAME语句
221 --ALTER DATABASE ...SET ONLINE语句
222 --ALTER DATABASE...SET OFFLINE语句
223 --ALTER DATABASE...SET EMERGENCY语句
224 --DROP DATABASE 语句
225 --当一个数据库自动关闭时
226 --DBCC CHECKDB语句结束时
227 
228 
229 
230 --4、当下面这些SET 开关值变化后,先前的那些执行计划都不能重用
231 --ansi_null_dflt_off,ansi_null_dflt_on,ansi_nulls,_ansi_padding
232 --ansi_warnings,arithabort,concat_null_yields_null,datefirst,dateformat,
233 --forceplan,language,no_browsetable,numeric_roundabort,quoted_identifier
234 
235 --这是因为这些SET开关会影响语句的执行的行为,甚至带来不同的结果。他们发生变化了
236 --SQL就要根据新的设置重做执行计划
237 
238 
239 --5、当表格或者视图上的统计信息发生变化后
240 --当统计信息被手动更新后,或者SQL发现某个统计信息需要自动更新时,SQL会对所涉及
241 --的语句都做重编译
242 
243 
244 
245 
246 --需要说明的是,在SQL里,执行计划重用并不一定是一件好事,而编译/重编译也不一定
247 --是一件坏事。计划重用可以帮助SQL节省编译时间,对降低CPU使用率和减少阻塞都有好处
248 --但是缺点是每次重用的计划并不一定是最合适的计划。后面的参数嗅探parameter sniffing
249 --就是典型的计划重用带来的负效应。编译和重编译当然能给当前运行的语句带来尽可能准确
250 --执行计划,但是对于经常运行的语句,尤其是一些执行速度比较快的语句,可能其编译时间
251 --占最后总时间的相当大比例。这对资源来讲是一个很大的浪费
252 
253 
254 --一般来说,SQL能够很好地在编译与重编译之间做平衡,大部分情况下没什么问题。如果发现
255 --系统因为编译和重编译太多,或者重用了不准确的计划,导致了性能问题,那就要根据具体
256 --情况做调整。在SQL里,能对计划重用和编译/重编译产生影响的功能主要有:
257 
258 
259 --1、使用存储过程,或者[sys].[sp_executesql]的方式调用会被重复使用的语句,而不要直接
260 --用adhoc语句或者dynamic SQL
261 
262 --2、在语句里引用对象(表,视图,存储过程等),要带上他的schema名字(full qualified object name)
263 --而不光是对象自己的名字
264 
265 --例如:对于一个对象名,[SalesOrderDetail],dbo调用他,调用到的是[dbo].[SalesOrderDetail]
266 --而用户Sales调用他,调用到的可能是[Sales].[SalesOrderDetail]。所以对于同一句语句,SQL
267 --可能会更倾向于保存多份执行计划。使用对象全名,能够更好地“引导”SQL重用执行计划
268 
269 --3、将数据库parameterization属性设置为forced
270 --SQL2005以后,可以在单个数据库上开启强制参数化。也就是说,对于在这个数据库下运行的
271 --大部分语句,SQL都会先参数化,再运行。如果应用经常用adhoc方式调用一样的语句,强制
272 --参数化可能会有所帮助
273 
274 --4、统计信息更新
275 --统计信息手工或者自动更新后,对和他有关的执行计划都不再能重用,而会产生重编译
276 --这样能够保证下次运行的语句能够根据数据变化使用新的执行计划
277 
278 --5、create procedure...with recompile选项和exec ....with recompile选项
279 --在创建或者调用存储过程的时候使用“with recompile”,会强制SQL在调用这个存储过程
280 --的时候,永远都先编译,再运行。哪怕有合适的缓存执行计划,也不会重用
281 
282 --这个选项一般用在DBA已经确定了对某个特定的存储过程,或者是特定语句执行计划重用
283 --会带来负面影响时(例如parameter sniffing后果比较严重)
284 
285 
286 --6、用户调用了sp_recompile
287 USE [pratice]
288 GO
289 EXEC [sys].[sp_recompile] @objname = N'dbo.bulkinserttest' -- nvarchar(776)
290 
291 --sp_recompile也会产生相关对象的重编译。当DBA怀疑某个对象上现在缓存的计划
292 --不合适,已经造成性能问题时,可以运行sp_recompile,让SQL重新编译
293 
294 --7、用户在调用语句的时候,使用了“keep plan”或者“keepfixed plan”这样的查询提示(Hint)
295 --keep plan放宽了对临时表的重编译阀值,使得SQL像对普通表一样对待临时表,不会在上面
296 --做额外的重编译。用户可以像下面这样使用他:
297 SELECT b.col4,SUM(a.col1)
298 FROM [dbo].permtable a INNER JOIN #temptable b ON a.col1=b.col2
299 WHERE b.col3<100
300 GROUP BY b.col4
301 OPTION(KEEP PLAN)
302 
303 
304 --keepfixed plan强制查询优化器不因统计信息的更改而重新编译查询。只有在基础表的结构
305 --发生变化后,或者有人运行过sp_recompile以后,才会发生重编译。用户可以像下面这样
306 --使用他
307 USE [AdventureWorks]
308 GO
309 SELECT c.[TerritoryID],COUNT(*) AS number,c.salespersonid
310 FROM sales.[Store] s INNER JOIN sales.[Customer] c
311 ON s.[CustomerID]=c.[CustomerID]
312 WHERE s.[Name] LIKE '%Bike%' AND [c].salespersonid>285
313 GROUP BY c.[TerritoryID],c.salespersonid
314 ORDER BY number DESC
315 OPTION(KEEPFIXED PLAN)
316 
317 
318 
319 --总之,DBA可以根据实际情况,选择某一种或某几种方法来影响SQL的编译/重编译行为
320 --在SQL里,也提供了很多跟踪和分析编译与执行计划重用行为的方法。有些前面已经使用
321 --过了。现在在这里再总结一下
322 
323 
324 --1、系统管理视图sys.syscacheobjects
325 --从sys.syscacheobjects里可以看到SQL缓存的所有执行计划,以及他们的相关信息
326 
327 --一些比较常用的字段有:
328 --cacheobjtype:缓存中的对象类型
329 --compiled plan:编译计划,也就是我们通常讲的执行计划
330 --executable plan:可执行计划
331 --parse tree:分析树
332 --cursor:游标
333 --extended stored procedure:扩展存储过程
334 
335 --objtype:对象的类型
336 --可以缓存执行计划的对象很多,包括有:
337 
338 --proc:存储过程
339 --prepared:预定义语句
340 --adhoc:即席查询(由sqlcmd或osql实用工具而不是远程过程调用作为语言事件提交的TSQL)
341 --repproc:复制筛选过程
342 --trigger:触发器
343 --view:视图
344 --default:默认值
345 --usrtab:用户表
346 --systab:系统表
347 --check:check约束
348 --rule:规则
349 --setopts:影响编译计划的SET选项设置
350 --refcounts:引用该缓存对象的其他缓存对象数,计数1为基数。每被重新引用一次,加1
351 --usecounts:自开始以来使用该缓存对象的次数。refcounts和usecounts这两个值越大,
352 --说明缓存重用得越多,这个缓存越有用
353 
354 --pagesused:缓存对象占用的页数。也就是缓存消耗的内存数目
355 
356 --唯一再次强调的是,在一个生产环境里,SQL缓存的执行计划可能会很多。要去查询这张表
357 --最好带上一些过滤条件,或者加上top 1000之类的子句,控制返回集的大小
358 
359 
360 --2、清除执行计划缓存的语句
361 --如果DBA有充分的理由怀疑当前的问题和内存里缓存的执行计划有关,可以用下面两句话之一
362 --清除执行计划,顺便也释放内存(所以在和内存相关的问题上有时也有用)
363 DBCC freeproccache
364 DECLARE @a INT
365 SELECT @a=DB_ID('gposdb')
366 DBCC flushprocindb(@a)
367 
368 --3、SQL Trace里的一些和编译有关的事件
369 --在SQLTrace里有一些事件可用很有效地跟踪编译和重编译行为
370 --SQLTrace里的一些和编译有关的事件
371 --cursors -cursorrecompile:当游标所基于的对象发生架构变化,导致的TSQL游标做的重编译
372 --performance -auto stats:发生自动创建或者更新统计信息的事件
373 
374 --stored procedures 下面有好几个很有用的事件:
375 --SP:CacheHit:说明当前语句在缓存里找到了一个可用的执行计划
376 --SP:CacheInsert:当前有一个新执行计划被插入到缓存里
377 --SP:CacheMiss:说明当前语句在缓存里找不到一个可用的执行计划
378 --SP:CacheRemove:有执行计划被从缓存里移除。内存有压力的时候会发生这样的行为
379 --SP:Recompile:一个存储过程发生了重编译。这个事件有一个数据字段叫EventSubClass
380 --记录了重编译发生的原因。如果DBA要跟踪为什麽一条指令老是重编译,跟踪这个事件
381 --的这个字段很有用,他一共有11种可能的原因。所以在处理重编译问题时,这个事件
382 --很有帮助
383 
384 
385 --1=架构已更改
386 --2=统计已更改
387 --3=重新编译DNR
388 --4=所设置的选项已更改
389 --5=临时表已更改
390 --6=远程行集已更改
391 --7=浏览Perm的方式已更改
392 --8=查询通知环境已更改
393 --9=MPI视图已更改
394 --10=光标选项已更改
395 --11=使用重编译选项
396 
397 
398 --而SQL2000中,只要批处理中的任何一句语句导致重新编译,就会重新编译整个批处理,
399 --无论此批处理是通过存储过程、触发器、即席批查询,还是通过预定义的语句进行提交
400 --在SQL2005和更高版本中,只会重新编译批处理中导致重新编译的语句。从这一点来看
401 --SQL2005比SQL2000更节约一点。因为这种变化,在SQL2005以后,要改用“TSQL-SQL:StmtRecompile”
402 --这个事件来跟踪重编译,因为现在的重编译都发生在语句一级
403 
404 
405 --4、一些性能监视器计数器
406 --相关计数器
407 --性能对象                                   计数器
408 --SQLSERVER:BUFFER MANAGER:    buffer cache hit ratio,lazy writes/sec ,procedure cache pages,total pages
409 --SQLSERVER:Cache Manager:    cache hit ratio,cache object counts,cache pages ,cache use counts/sec
410 --SQLSERVER:MEMORY MANAGER:    sql cache memory(kb)
411 --SQLSERVER:SQL STATISTICS:    auto-param attmpts/sec,batch request/sec,failed auto-params/sec,safe auto
412 --                             -param/sec, sql compilations/sec,sql re-compilations/sec,unsafe auto-params/sec

 

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