笔记58-徐 调整数据库设计来优化语句性能

笔记58-徐  调整数据库设计来优化语句性能

  1 --调整数据库设计来优化语句性能 P436  2013-2-9
  2 
  3 --当一个语句出现性能问题,而且已经排除了系统资源瓶颈、阻塞与死锁
  4 --物理I/O、编译与重编译、参数嗅探这些因素以后,那所能做的,要不
  5 --就是调整数据库设计,提高语句性能,要不就是修改语句本身,以达到
  6 --更高效率
  7 
  8 --调整数据库设计基本是两个思路:调整索引,或者使用 Plan Guide
  9 --这两个方法都不用修改语句本身
 10 
 11 
 12 ----------------------------调整索引---------------------------------
 13 --索引设计的优劣很大程度上决定了数据库的性能,也决定了一套系统的最大能力。
 14 --运气比较好的时候,一套数据库系统性能本来很差,可加一两个索引后就会有
 15 --很大的改善。所以做语句调优,调整索引是必备的功课
 16 
 17 --什么样的索引才是好的索引呢?这个问题已经被广泛地讨论。
 18 
 19 
 20 --1、聚集索引
 21 --聚集索引是一个表格里最宝贵的资源。一张表格只能有一个聚集索引,他决定
 22 --了表格里所有记录排列的顺序。如果一张表经常用某种顺序检索数据,那按照
 23 --这个顺序建立聚集索引无疑是最理想的
 24 
 25 --为了提高可检索性,聚集索引最好要建立在重复值很少的字段上。但是并不是
 26 --说,聚集索引的字段就不可以有重复值。选择什么样的字段,值重复不重复是
 27 --一个标准,表格的常见检索方式也是一个重要的标准。例如一个Employee表格
 28 --里,有姓名字段,也有工号字段。一般来讲,姓名可能偶尔会有重复,但是
 29 --工号不会重复。如果我们对Employee表的检索,通常只用姓名,很少使用工号
 30 --那就应该选姓名作为聚集索引。如果检索经常使用的是工号,不是姓名,就
 31 --应该用工号作为聚集索引
 32 
 33 
 34 --2、非聚集索引
 35 --一个表格有多种检索方式。例如Employee表,不但会按照姓名、工号,还会按照
 36 --年龄段、性别、职务等方面来检索。一个聚集索引是无法覆盖所有要求的,需要
 37 --有非聚集索引来辅助。在一张表格上,可以有多个非聚集索引。
 38 
 39 --建立非聚集索引的字段,不用一定要很少不重复。像性别、职务这样的字段,肯定
 40 --会有很多重复记录,但是为了提高检索性能,也可以在上面建立非聚集索引。但是
 41 --非聚集索引里面没有存储记录的所有信息。如果查询要使用的字段没有完全包含在
 42 --非聚集索引里,那SQL就要做Bookmark lookup。这会有额外的开销
 43 
 44 
 45 --3、Covering Index(复合索引/涵盖索引)
 46 --一个索引可以建立在一组字段上。这样的好处有:
 47 --(1)查询的检索条件,本身是一组字段的组合。直接在上面建索引,检索更快
 48 --(2)如果查询用到的字段索引都能够覆盖,那就不用做书签查找,能节省时间
 49 
 50 --但是设计Covering Index的时候需要注意的是:
 51 --(1)要慎重选择索引的第一个字段,最好选择一个重复记录最少的字段。这是因为
 52 --索引上的统计信息只保存第一个字段的数据直方图。如果选一个重复数据很多的字段
 53 --这个索引的可选度就比较低了,会影响索引的价值
 54 
 55 
 56 --(2)SQL会按照索引里字段的组合值,建立B-树。字段越多,排序的代价就越高,
 57 --维护这个索引的代价也就越高。所以选取字段的时候要有合理的要求,不能随便
 58 --什么字段都往上放。一般不要把长度很长的字段放在索引字段里
 59 
 60 --4、Index with Included Columns(具有包含性列的索引)
 61 --有些查询语句根据一个字段的条件,查询另一个字段的值。例如:返回
 62 --所有职务是“Marketing Manager”的人生日
 63 
 64 USE [AdventureWorks]
 65 GO
 66 DBCC freeproccache
 67 GO
 68 DBCC DROPCLEANBUFFERS
 69 GO
 70 SET STATISTICS PROFILE ON
 71 GO
 72 SELECT [BirthDate] FROM [HumanResources].[Employee]
 73 WHERE [Title]='Marketing Manager'
 74 
 75 --如果建立一个covering index在(Title+[BirthDate])上,数据会按照(Title+[BirthDate])
 76 --的方式排序。其实不需要这种排序,我只需要按Title的排序。在[BirthDate]上
 77 --的排序是多做的。在SQL2005以后,就可以建立一个有包含性列(Included Columns)的索引
 78 
 79 --老张"数据库命名规则"
 80 --索引(聚集) ic icAuthorFullName
 81 --索引(非聚集) in inClientStateCity
 82 --索引
 83 --idx+字段标识
 84 
 85 USE [AdventureWorks]
 86 GO
 87 --只能在非聚集索引上建立包含性列索引 因为包含性列索引不需要维护排序和维护这个索引
 88 --不同于聚集索引,SQL要按照索引里字段的组合值,建立B-树。字段越多,排序的代价就越高,
 89 --维护这个索引的代价也就越高
 90 CREATE INDEX idx_Employee_Title_ConverIndex ON [HumanResources].[Employee]([Title]) INCLUDE([BirthDate])
 91 
 92 -------------------------------------------------------------------------
 93 USE [AdventureWorks]
 94 GO
 95 DBCC freeproccache
 96 GO
 97 DBCC DROPCLEANBUFFERS
 98 GO
 99 SET STATISTICS PROFILE ON
100 GO
101 SELECT [BirthDate] FROM [HumanResources].[Employee]
102 WHERE [Title]='Marketing Manager'
103 
104 --根据执行计划使用了索引查找
105 --stmt text列
106   |--Index Seek(OBJECT:([AdventureWorks].[HumanResources].[Employee].[idx_Employee_Title_ConverIndex]), SEEK:([AdventureWorks].[HumanResources].[Employee].[Title]=CONVERT_IMPLICIT(nvarchar(4000),[@0],0)) ORDERED FORWARD)
107  
108 --Argument列
109 --OBJECT:([AdventureWorks].[HumanResources].[Employee].[idx_Employee_Title_ConverIndex]), SEEK:([AdventureWorks].[HumanResources].[Employee].[Title]=CONVERT_IMPLICIT(nvarchar(4000),[@0],0)) ORDERED FORWARD
110 
111 --这样的索引,既可以节省书签查找的消耗,也不需要做不必要的排序。是covering index
112 --的一种替代方式
113 
114 
115 --5、Indexed View(索引视图)
116 --视图只是一个逻辑定义,他里面并没有存储任何数据。对他的查询会转向对他所
117 --基于的表格的查询。如果一个视图的定义比较复杂,那么对他的查询会比较耗时
118 
119 --为了提高视图操作的性能,SQL允许在一些视图上建立索引。索引里面的字段值会
120 --被重新存放一份。如果索引能够覆盖查询要使用到的所有字段,那查询就不必去
121 --访问基础表格。这样会大大提高检索速度。所以这是一个提高和视图相关的语句
122 --性能的好办法
123 
124 --但是,SQL对索引视图有限制。不是所有的视图都能建索引。视图要符合一定的
125 --先决条件
126 
127 
128 --6、Primary Key(主键)
129 --很多人会把Primary Key和聚集索引搞混起来,或者认为这是同一个东西。
130 --这个概念是非常错误的。主键是一个约束(constraint),他依附在一个
131 --索引上,这个索引可以是聚集索引,也可以是非聚集索引。所以在一个
132 --(或一组)字段上有主键,只能说明他上面有个索引,但不一定就是
133 --聚集索引。而一个聚集索引里,是可以有重复值的。只要他没有被同时
134 --设为主键
135 
136 --强调这一点,是因为有些用户觉得自己的表格上设置了主键,就认为表格上
137 --有聚集索引,按照B-树的方式管理了。如果没有指定主键是个聚集索引,可能
138 --表格还是会以堆的方式管理,效率低下
139 
140 --那面对一个性能不理想的语句,怎麽调整索引,才能提高性能呢?当确认
141 --TotalSubtreeCost这一列是准确的以后,应该找对cost贡献最多的子句。
142 --如果他用的是Table Scan,或者Index Scan,请比较他返回的行数和表格
143 --实际行数。如果返回行数远小于实际行数,那就说明SQL没有合适的索引
144 --供他做seek。这时候加索引就是一个比较好的选择
145 
146 
147 --例如下面这个查询
148 use [AdventureWorks]
149 GO
150 SET STATISTICS PROFILE ON
151 GO
152 SELECT *
153 FROM [dbo].[SalesOrderDetail_test]
154 WHERE [ProductID]=777
155 GO
156 
157 --在ProductID上加一个索引是一个比较自然的想法
158 --但是不是所有的查询都是这麽简单的。面对一个复杂的语句,找到一个优化
159 --的索引组合对人脑来讲,真的不是一件很简单的事情。好在SQL提供了两种
160 --“自动”功能,给你建议,该怎么调整索引。
161 
162 --1、Missing Index
163 --从SQL2005以后,在SQL对任何一句语句做编译的时候,都会去评估一下,这句话
164 --是不是缺少什么索引的支持,如果他认为是,他还会预估,如果有这麽一个索引
165 --他的性能能提高多少。对单条语句,这个信息可以通过SQL Trace里的
166 --Performance-Showplan XML Statistics Profiles这个事件得到
167 
168 --在SQL Trace里Performance-Showplan XML Statistics Profiles这个事件的内容
169 --会以一个执行计划图显示,我们想要的信息看不到。你需要选择这个事件,
170 --点击右键,选择Extract Event Data,把数据输出到一个.SQLPlan结尾的文件里
171 
172 --用记事本打开(默认使用SSMS以图形方式打开)。在XML格式的文本里,如果
173 --你能找到<MissingIndexes>这个Tag,就说明SQL对你现在的数据库设计有些意见了。
174 --例如我们刚才那个查询,他的意见是:
175 
176 --也就是说,他希望你在dbo.SalesOrderDetail_test的ProductID上建立一个索引,
177 --而这个索引最好能够include[UnitPrice]这个字段。他认为,如果加了这个索引
178 --性能可以提高99.6099%!!
179 
180 
181 --这里的这个建议还是比较合理的。但是,DBA还是需要去确认一下建议。因为这个
182 --建议完全是根据这个语句本身给出的,没有考虑对其他语句的影响,也没有考虑
183 --维护索引的成本,所以是很片面的。其准确性,也要再确认一下
184 
185 --SQL有一个动态管理视图sys.dm_db_missing_index_details,记录了当前数据库
186 --下所有的missing index的信息。他针对的是SQL从启动以来所有运行的语句,
187 --而不是针对某一个查询。DBA可以看看,哪些表格SQL是最有“意见”的
188 USE [Monitoring] --要查询索引缺失的数据库
189 GO
190 SELECT * FROM sys.[dm_db_missing_index_details]
191 SELECT * FROM sys.[dm_db_missing_index_groups]
192 SELECT * FROM sys.[dm_db_missing_index_group_stats]
193 SELECT * FROM sys.[dm_db_missing_index_columns](index_handle)
194 
195 --msdn:http://msdn.microsoft.com/zh-cn/library/ms345434.aspx
196 --[dm_db_missing_index_details]各列含义(返回有关缺失索引的详细信息,不包括空间索引):
197 --1、index_handle:标识特定的缺失索引。该标识符在服务器中是唯一的。index_handle 是此表的密钥
198 --2、database_id :标识带有缺失索引的表所驻留的数据库
199 --3、object_id :标识索引缺失的表
200 --4、equality_columns:构成相等谓词的列的逗号分隔列表 即哪个字段缺失了索引会在这里列出来(简单来讲就是where 后面的筛选字段),谓词的形式如下:table.column =constant_value
201 --5、inequality_columns :构成不等谓词的列的逗号分隔列表,例如以下形式的谓词:table.column > constant_value “=”之外的任何比较运算符都表示不相等。
202 --6、included_columns:用于查询的涵盖列的逗号分隔列表(简单来讲就是 select 后面的字段)。
203 --7、statement:索引缺失的表的名称
204 
205 
206 --例如下面查询结果要创建下面的索引
207 --column_id               column_name             column_usage
208 --  5                      ProductID                equality
209 --  1                      SalesOrderID             include
210 
211 CREATE INDEX idx_SalesOrderDetail_test_ProductID_IncludeIndex ON SalesOrderDetail_test(ProductID) INCLUDE(SalesOrderID)
212 ----------------------------------------------------------------------------
213 USE [AdventureWorks]
214 GO
215 SELECT [SalesOrderID] FROM [dbo].[SalesOrderDetail_test] WHERE [ProductID]=69685
216 -------------------------------------------------------------------------------------
217 USE [AdventureWorks]
218 GO
219 DROP INDEX idx_SalesOrderDetail_test_ProductID_IncludeIndex ON [SalesOrderDetail_test]
220 
221 --注意事项:
222 --由 sys.dm_db_missing_index_details 返回的信息会在查询优化器优化查询时更新,因而不是持久化的。
223 --缺失索引信息只保留到重新启动 SQL Server 前。如果数据库管理员要在服务器回收后保留缺失索引信息,
224 --则应定期制作缺失索引信息的备份副本
225 
226 
227 --msdn:http://msdn.microsoft.com/zh-cn/library/ms345364.aspx
228 --sys.[dm_db_missing_index_columns]([sql_handle])
229 --返回与缺少索引(不包括空间索引)的数据库表列有关的信息。sys.dm_db_missing_index_columns 是一个动态管理函数
230 --sys.dm_db_missing_index_columns(index_handle)
231 --index_handle:唯一地标识缺失索引的整数。
232 
233 
234 
235 
236 --msdn:http://msdn.microsoft.com/zh-cn/library/ms345407.aspx
237 --sys.[dm_db_missing_index_groups]
238 --返回有关特定缺失索引组中包含的缺失索引(不包括空间索引)的信息
239 
240 
241 --msdn:http://msdn.microsoft.com/zh-cn/library/ms345421.aspx
242 --sys.[dm_db_missing_index_group_stats]
243 --返回缺失索引组的摘要信息,不包括空间索引
244 --这个视图说白了就是预估有这麽一个索引,他的性能能提高多少
245 
246 --avg_user_impact: 实现此缺失索引组后,用户查询可能获得的平均百分比收益。该值表示如果实现此缺失索引组,则查询成本将按此百分比平均下降。
247 --MSDN给出的示例
248 --缺失索引组句柄为 2
249 
250 --查询提供缺失索引的数据库、架构和表的名称。它还提供应该用于索引键的列的名称
251 USE [AdventureWorks]
252 GO
253 SELECT migs.group_handle, mid.*
254 FROM sys.dm_db_missing_index_group_stats AS migs
255 INNER JOIN sys.dm_db_missing_index_groups AS mig
256     ON (migs.group_handle = mig.index_group_handle)
257 INNER JOIN sys.dm_db_missing_index_details AS mid
258     ON (mig.index_handle = mid.index_handle)
259 WHERE migs.group_handle = 2
260 
261 
262 
263 
264 USE [AdventureWorks] --要查询索引缺失的数据库
265 GO
266 SELECT * FROM sys.[dm_db_missing_index_details]
267 SELECT * FROM sys.[dm_db_missing_index_groups]
268 SELECT * FROM sys.[dm_db_missing_index_group_stats]
269 SELECT * FROM sys.[dm_db_missing_index_columns](1) --1 :1是根据dm_db_missing_index_details查出来的
270 
271 
272 
273 
274 
275 
276 --2、Database Engine Tuning Advisor(DTA)数据库引擎优化顾问
277 --这也是一个非常好用的工具,强力向大家推荐。在SSMS里选择
278 --工具-》数据库引擎优化顾问,就可以开启这个工具
279 
280 
281 --虽然DTA接受一个SQL Trace文件作为工作语句的输入,但是这种方法不太有效
282 --比较好的方法,是先找出你想要优化的语句,把他们存成一个.sql文件,作为
283 --输入。还要选好默认的数据库(否则DTA找不到表格)和你想要优化的数据库
284 
285 --请看新开的文章
286 
287 
288 --------------------------------------------------------------------------------------------
289 --SQLSERVER Plan Guide(计划指南)
290 --前面说参数嗅探的时候,曾经提到在SQL2005以后,用户可以给一些语句定义
291 --计划指南,让他们在编译的时候,自动使用你想要的执行计划。这是提高
292 --语句性能在SQL层面上可以做的另外一件事。
293 --但是,使用这个方法要满足几个前提:
294 
295 --(1)问题语句固定,你已经知道了他们的格式,而且应用程序就一直使用这种格式
296 --在指定执行计划指南的时候,需要告诉SQL完整的语句。当SQL发现任何一个用户将要
297 --运行同样的一句话时,就会应用计划指南。如果问题语句是动态生成的,每一次都
298 --不一样的话,那计划指南可能就不那么有用了
299 
300 
301 --(2)语句的性能问题可以通过选用一个固定的执行计划得到解决
302 --也就是说,存在一个“万能”的执行计划,他对任何参数值、任何用户,其执行效率
303 --都是稳定可接受的,而且也不会随着数据量的变化,执行效率变得不可接受。如果
304 --你找不到这样一个执行计划,那就没有办法使用这个方法。
305 
306 
307 --(3)使用这种方法后,需要有人不断监视语句的性能
308 --如果数据量或数据分布发生很大的变化,可能先前好的执行计划现在反而会导致更大
309 --的问题。需要DBA能够知道计划指南设置的合理,在必要的时候做相应的调整
310 
311 --创建计划指南的语句是:
312 --msdn:http://msdn.microsoft.com/zh-cn/library/ms179880.aspx
313 sp_create_plan_guide [ @name = ] N'plan_guide_name'
314     , [ @stmt = ] N'statement_text'
315     , [ @type = ] N'{ OBJECT | SQL | TEMPLATE }'
316     , [ @module_or_batch = ]
317       {
318                     N'[ schema_name. ] object_name'
319         | N'batch_text'
320         | NULL
321       }
322     , [ @params = ] { N'@parameter_name data_type [ ,...n ]' | NULL }
323     , [ @hints = ] { N'OPTION ( query_hint [ ,...n ] )'
324                  | N'XML_showplan'
325                  | NULL }
326 
327 --定义看起来有点羞涩。简单来说,用户可以给一个batch里的一句话定义计划指南,
328 --也可以给一个存储过程里的一句话定义。这句话可以有参数,也可以没有参数。
329 --计划指南可以是一个Query Hint,也可以直接是一个执行计划
330 
331 
332 --下面举一些例子,来展示一下计划指南的使用方法
333 --1、对单个固定语句定义计划指南
334 --假设有这麽一张表格:
335 USE [AdventureWorks]
336 GO
337 CREATE TABLE table1(
338 NAME NVARCHAR(256),
339 id INT
340 )
341 GO
342 
343 --有下面这个不带参数的语句(注意,他没有以回车符结束)
344 USE [AdventureWorks]
345 GO
346 INSERT [dbo].[table1]
347 SELECT a.[name],a.[id]
348 FROM [sys].[sysobjects] a
349 INNER JOIN [sys].[sysindexes] b
350 ON a.id=b.[id]
351 WHERE b.[indid]=2
352 
353 
354 --默认,这个查询将会使用merge join的方法。如果用户想要规定,这个join
355 --必须使用nested loops,除了直接在查询里加query hint以外,使用计划指南
356 ---也可以达到同样效果。创建计划指南的语句应该是:
357 USE [AdventureWorks]
358 GO
359 EXEC [sys].[sp_create_plan_guide]
360 @name=N'Guide2',
361 @stmt=N'INSERT [dbo].[table1]
362 SELECT a.[name],a.[id]
363 FROM [sys].[sysobjects] a
364 INNER JOIN [sys].[sysindexes] b
365 ON a.id=b.[id]
366 WHERE b.[indid]=2',
367 @type=N'SQL',
368 @module_or_batch=N'INSERT [dbo].[table1]
369 SELECT a.[name],a.[id]
370 FROM [sys].[sysobjects] a
371 INNER JOIN [sys].[sysindexes] b
372 ON a.id=b.[id]
373 WHERE b.[indid]=2',
374 @params=NULL,
375 @hints=N'option(loop join)'
376 
377 --创建计划指南的时候,@stmt和@module_or_batch参数所带的字符串,必须
378 --和语句本身一模一样,不能少一个空格,也不能多一个回车。SQL在这里
379 --做的是精确匹配
380 
381 --计划指南创建完毕后,再运行这句话
382 USE [AdventureWorks]
383 GO
384 SET STATISTICS XML ON
385 GO
386 EXEC [sys].[sp_executesql] N'INSERT [dbo].[table1]
387 SELECT a.[name],a.[id]
388 FROM [sys].[sysobjects] a
389 INNER JOIN [sys].[sysindexes] b
390 ON a.id=b.[id]
391 WHERE b.[indid]=2'
392 GO
393 
394 
395 --在以XML格式输出的执行计划里,就能看到执行计划是根据计划指南生成的
396 
397 <StmtSimple StatementText="INSERT [dbo].[table1]&#xD;&#xA;SELECT a.[name],a.[id] &#xD;&#xA;FROM [sys].[sysobjects] a&#xD;&#xA;INNER JOIN [sys].[sysindexes] b&#xD;&#xA;ON a.id=b.[id]&#xD;&#xA;WHERE b.[indid]=2" StatementId="1" StatementCompId="2" StatementType="INSERT" PlanGuideDB="AdventureWorks" PlanGuideName="Guide2"
398 
399 
400 --就在XML的开头
401 PlanGuideDB="AdventureWorks" PlanGuideName="Guide2"
402 
403 
404 --2、对批处理里一个带有参数的语句定义计划指南
405 --一般来讲,用户发过来的批处理会不止一句话,而且语句里也会有参数。这个怎麽写
406 --假设下面这个批处理
407 USE [AdventureWorks]
408 GO
409 DECLARE @id INT
410 SET @id=2
411 DECLARE @Table TABLE(NAME NVARCHAR(256),id INT)
412 INSERT INTO @Table
413 SELECT a.[name],a.[id]
414 FROM [sys].[sysobjects] a
415 INNER JOIN [sys].[sysindexes] b
416 ON a.[id]=b.[id]
417 WHERE b.[indid]= @id
418 
419 
420 --这里在@stmt参数里,还是代入语句本身。但是在@module_or_batch里,要代入
421 --整个批处理的每一句话。但是语句的参数,不用写在@params里
422 
423 USE [AdventureWorks]
424 GO
425 EXEC [sys].[sp_create_plan_guide]
426 @name=N'Guide3',
427 @stmt=N'INSERT INTO @Table
428 SELECT a.[name],a.[id]
429 FROM [sys].[sysobjects] a
430 INNER JOIN [sys].[sysindexes] b
431 ON a.[id]=b.[id]
432 WHERE b.[indid]= @id',
433 @type=N'SQL',
434 @module_or_batch=N'DECLARE @id INT
435 SET @id=2
436 DECLARE @Table TABLE(NAME NVARCHAR(256),id INT)
437 INSERT INTO @Table
438 SELECT a.[name],a.[id]
439 FROM [sys].[sysobjects] a
440 INNER JOIN [sys].[sysindexes] b
441 ON a.[id]=b.[id]
442 WHERE b.[indid]= @id',
443 @params=NULL,
444 @hints=N'option(loop join)'
445 
446 
447 
448 --3、使用带参数的方式调用批处理语句,给其中一句指定计划指南
449 --用户也可以用sp_executesql的方法调用这句话
450 
451 EXEC [sys].[sp_executesql] N'DECLARE @Table TABLE(NAME NVARCHAR(256),id INT)
452 INSERT INTO @Table
453 SELECT a.[name],a.[id]
454 FROM [sys].[sysobjects] a
455 INNER JOIN [sys].[sysindexes] b
456 ON a.[id]=b.[id]
457 WHERE b.[indid]= @id' ,N'@id int',@id=2
458 
459 --而这个时候,计划指南要这样写
460 EXEC [sys].[sp_create_plan_guide]
461 @name=N'Guide5',
462 @stmt=N'INSERT INTO @Table
463 SELECT a.[name],a.[id]
464 FROM [sys].[sysobjects] a
465 INNER JOIN [sys].[sysindexes] b
466 ON a.[id]=b.[id]
467 WHERE b.[indid]= @id',
468 @type=N'SQL',
469 @module_or_batch=N'DECLARE @Table TABLE(NAME NVARCHAR(256),id INT)
470 INSERT INTO @Table
471 SELECT a.[name],a.[id]
472 FROM [sys].[sysobjects] a
473 INNER JOIN [sys].[sysindexes] b
474 ON a.[id]=b.[id]
475 WHERE b.[indid]= @id',
476 @params=N'@id int',
477 @hints=N'option(loop join)'
478 
479 
480 --用批处理的时候,要把整个批处理都抄在[sp_create_plan_guide]后面,比较麻烦
481 --如果是存储过程,就好办多了
482 
483 
484 --4、对一个存储过程里的一条语句定义计划指南
485 --假设存储过程是这样的
486 USE [AdventureWorks]
487 GO
488 CREATE PROC Demo_Plan(@id INT)
489 AS
490 DECLARE @Table TABLE(NAME VARCHAR(256),id INT)
491 INSERT INTO @Table
492 SELECT a.[name],a.[id]
493 FROM [sys].[sysobjects] a
494 INNER JOIN [sys].[sysindexes] b
495 ON a.[id]=b.[id]
496 WHERE b.[indid]=@id
497 GO
498 
499 
500 --那么计划指南就可以这样定义
501 USE [AdventureWorks]
502 GO
503 EXEC [sys].[sp_create_plan_guide]
504 @name=N'Guide6',
505 @stmt=N'INSERT INTO @Table
506 SELECT a.[name],a.[id]
507 FROM [sys].[sysobjects] a
508 INNER JOIN [sys].[sysindexes] b
509 ON a.[id]=b.[id]
510 WHERE b.[indid]=@id',
511 @type=N'Object',
512 @module_or_batch=N'Demo_Plan',
513 @params=NULL,
514 @hints=N'option(loop join)'
515 
516 
517 --在上面这些例子里,用的都是Query Hint,并没有直接指定执行计划。
518 --能不能直接指定SQL在跑这句话的时候,就固定地使用某个执行计划呢?
519 --这个也是支持的。
520 
521 --5、通过[sp_create_plan_guide]指定某个执行计划
522 --[sp_create_plan_guide]的@hints参数,在SQL2008里可以直接带入
523 --一个XML格式的执行计划(在SQL2005的时候,要使用N'OPTION(use plan...)'的方法)
524 --下面是一个例子
525 
526 --首先我们创建一个存储过程。里面insert语句的join,默认会使用merge join的方式
527 USE [AdventureWorks]
528 GO
529 CREATE PROC TestPlan
530 AS
531 DECLARE @id INT
532 SET @id=2
533 DECLARE @Table TABLE(NAME VARCHAR(256),id INT)
534 INSERT INTO @Table
535 SELECT a.[name],a.[id]
536 FROM [sys].[sysobjects] a
537 INNER JOIN [sys].[sysindexes] b
538 ON a.id=b.[id]
539 WHERE b.[indid]=@id
540 
541 
542 --然后我们搞一个使用nested loops的执行计划,把他作为这个存储过程里
543 --insert语句的计划指南
544 
545 USE [AdventureWorks]
546 GO
547 DBCC freeproccache
548 --清空所有缓存的执行计划
549 GO
550 DECLARE @id INT
551 SET @id = 2
552 DECLARE @Table TABLE
553     (
554       NAME NVARCHAR(256) ,
555       id INT
556     )
557 INSERT  INTO @Table
558         SELECT  a.[name] ,
559                 a.[id]
560         FROM    [sys].[sysobjects] a
561                 INNER JOIN [sys].[sysindexes] b ON a.[id] = b.[id]
562         WHERE   b.[indid] = @id
563 OPTION  ( LOOP JOIN )
564 --使用query hint运行语句,让SQL生成一个nested loops的执行计划
565 GO
566 
567 --DECLARE @xml_showplan NVARCHAR(MAX)
568 --SET @xml_showplan=(SELECT [query_plan] FROM
569 --sys.[dm_exec_query_stats] AS qs
570 --CROSS APPLY sys.[dm_exec_sql_text](qs.[sql_handle]) AS st
571 --CROSS APPLY sys.[dm_exec_text_query_plan](qs.[plan_handle],DEFAULT,DEFAULT) AS qp
572 --WHERE st.[text] LIKE N'declare @id int%' AND SUBSTRING(st.[text],(qs.[statement_start_offset]/2)+1,
573 --((case statement_end_offset when -1 then detalength(st.text) else qs.statement_start_offset/2)+1)
574 --like 'INSERT into @table%'
575 ----从缓存里把这个执行计划取出来
576 --);
577 
578 
579 USE [AdventureWorks]
580 GO
581 EXEC [sys].[sp_create_plan_guide]
582 @name=N'Guide7',
583 @stmt=N'insert into @table
584 select a.name,a.id from sysobjects a
585 inner join sysindexes b
586 on a.id=b.id
587 where b.indid=@id',
588 @type=N'Object',
589 @module_or_batch=N'TestPlan',
590 @params=null,
591 @hints=@xml_showplan
592 --使用执行计划的内容直接定义计划指南
593 GO
594 
595 --以后再运行存储过程,就能够使用到nested loops的执行计划了。在SQL2008的SQL Trace里,
596 --新加入了两个事件:Performance-Plan Guide Successful和Performance-Plan Guide Unsuccessful
597 --可以用来跟踪语句的执行有没有正确地使用计划指南
598 
599 --如果想要关闭,或者删除某个计划指南,可以调用[sp_control_plan_guide]系统存储过程
600 --例如:
601 USE [AdventureWorks]
602 GO
603 EXEC [sys].[sp_control_plan_guide] N'drop',N'Guide5'
604 GO
605 --删除guide3这个计划指南
606 USE [AdventureWorks]
607 GO
608 EXEC [sys].[sp_control_plan_guide] N'disable all'
609 GO
610 ---------------------------------------------------
611 USE [AdventureWorks]
612 GO
613 EXEC [sys].[sp_control_plan_guide] N'enable all'
614 GO
615 --关闭当前数据库里的所有计划指南
616 
617 
618 --计划指南这个功能,在急着调整执行计划,数据库和应用设计又不能修改的时候,能
619 --起到应急的作用,还是值得DBA掌握的。但是和使用query hint的局限性一样,计划指南
620 --更多用作短期的解决方案,长期来讲可能会有风险。数据变化后,计划指南可能就不再
621 --准确

 

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