参数嗅探 Parameter Sniffing

 1 --参数嗅探 Parameter Sniffing  2013-2-8  2   3 --当使用存储过程的时候，总是要使用到一些变量。变量有两种，一种  4 --是在存储过程的外面定义的。当调用存储过程的时候，必须要给他代入  5 --值。这种变量，SQL在编译的时候知道他的值是多少。  6   7 --例如：  8 USE [AdventureWorks]  9 GO 10 DROP PROC Sniff 11 GO 12 CREATE PROC Sniff(@i INT) 13 AS 14 SELECT COUNT(b.[SalesOrderID]),SUM(p.[Weight]) 15 FROM [dbo].[SalesOrderHeader_test] a 16 INNER JOIN [dbo].[SalesOrderDetail_test] b 17 ON a.[SalesOrderID]=b.[SalesOrderID] 18 INNER JOIN [Production].[Product] p 19 ON b.[ProductID]=p.[ProductID] 20 WHERE a.[SalesOrderID]=@i 21 GO 22  23 --这里的变量@i，就是要在调用的时候代入值的 24 EXEC [dbo].[Sniff] @i = 50000 -- int 25 GO 26  27  28 --还有一种变量是在存储过程里面定义的。他的值在存储过程的语句执行的过程中得到的。 29 --所以对这种本地变量，SQL在编译的时候不知道他的值是多少。 30 --例如： 31 USE [AdventureWorks] 32 GO 33 DROP PROC Sniff2 34 GO 35 CREATE PROC Sniff2(@i INT) 36 AS 37 DECLARE @j INT 38 SET @j=@i 39 SELECT COUNT(b.[SalesOrderID]),SUM(p.[Weight]) 40 FROM [dbo].[SalesOrderHeader_test] a 41 INNER JOIN [dbo].[SalesOrderDetail_test] b 42 ON a.[SalesOrderID]=b.[SalesOrderID] 43 INNER JOIN [Production].[Product] p 44 ON b.[ProductID]=p.[ProductID] 45 WHERE a.[SalesOrderID]=@j 46 GO 47  48 EXEC [dbo].[Sniff2] @i = 500000 -- int 49 GO 50  51  52  53 --这里的变量@j，是SQL在运行的过程中算出来的 54 --已经谈到过多次，SQL在处理存储过程的时候，为了节省编译时间， 55 --是一次编译，多次重用的。用sp_executesql的方式调用的指令也是 56 --这样。那么执行计划重用就有两个潜在问题 57  58  59 --（1）对于第一类变量，根据第一次运行时代入的值生成的执行计划，是不是 60 --就能够适合所有可能的变量值呢？ 61  62  63 --（2）对于第二类本地变量，SQL在编译的时候并不知道他的值是多少，那怎么 64 --选择“合适”的执行计划呢？ 65  66 --对于第一个问题，会引出对 “参数嗅探”问题的定义。而对于第二个问题，本节 67 --将介绍使用本地变量对执行计划选择的影响。最后介绍参数嗅探问题的候选 68 --解决方案 69  70  71 ------------------------------------------------------------------------ 72 --什么是参数嗅探 73 --带着第一个问题，请做下面这两个测试 74 --测试一： 75 USE [AdventureWorks] 76 GO 77 DBCC freeproccache 78 GO 79 EXEC [dbo].[Sniff] @i = 500000 -- int 80 --发生编译，插入一个使用nested loops联接的执行计划 81 GO 82  83 EXEC [dbo].[Sniff] @i = 75124 -- int 84 --发生执行计划重用，重用上面的nested loops的执行计划 85 GO 86  87 --测试二： 88  89 USE [AdventureWorks] 90 GO 91 DBCC freeproccache 92 GO 93 EXEC [dbo].[Sniff] @i = 75124 -- int 94 --发生编译，插入一个使用hash match联接的执行计划 95 GO 96  97 EXEC [dbo].[Sniff] @i = 50000 -- int 98 --发生执行计划重用，重用上面的hash match的执行计划 99 GO100 101 102 --从上面两个测试可以清楚地看到执行计划重用的副作用。由于数据分布差别很大103 --参数50000和75124只对自己生成的执行计划有好的性能，如果使用对方生成的104 --执行计划，性能就会下降。参数50000返回的结果集比较小，所以性能下降105 --不太严重。参数75124返回的结果集大，就有了明显的性能下降，两个执行计划106 --的差别有近10倍107 108 --对于这种因为重用他人生成的执行计划而导致的水土不服现象，SQL有一个专有109 --名词，叫“参数嗅探 parameter sniffing”是因为语句的执行计划对变量的值110 --很敏感，而导致重用执行计划会遇到性能问题111 112 113 --本地变量的影响114 --那对于有parameter sniffing问题的存储过程，如果使用本地变量，会怎样呢？115 --下面请看测试3。这次用不同的变量值时，都清空执行计划缓存，迫使其116 --重编译117 --第一次118 USE [AdventureWorks]119 GO120 DBCC freeproccache121 GO122 SET STATISTICS TIME ON123 SET STATISTICS PROFILE ON124 EXEC [dbo].[Sniff] @i = 50000 -- int125 GO126 ------------------------------127 --第二次128 USE [AdventureWorks]129 GO130 DBCC freeproccache131 GO132 SET STATISTICS TIME ON133 SET STATISTICS PROFILE ON134 EXEC [dbo].[Sniff] @i = 75124 -- int135 GO136 --------------------------------137 --第三次138 USE [AdventureWorks]139 GO140 DBCC freeproccache141 GO142 SET STATISTICS TIME ON143 SET STATISTICS PROFILE ON144 EXEC [dbo].[Sniff2] @i = 50000 -- int145 GO146 ---------------------------------147 --第四次148 USE [AdventureWorks]149 GO150 DBCC freeproccache151 GO152 SET STATISTICS TIME ON153 SET STATISTICS PROFILE ON154 EXEC [dbo].[Sniff2] @i = 75124 -- int155 GO156 157 158 --来看他们的执行计划：159 --对于第一句和第二句，因为SQL在编译的时候知道变量的值，所以在做EstimateRows的时候，160 --做得非常准确，选择了最适合他们的执行计划161 162 163 --但是对于第三句和第四句，SQL不知道@j的值是多少，所以在做EstimateRows的时候，164 --不管代入的@i值是多少，一律给@j一样的预测结果。所以两个执行计划是完全一样的。165 --这里EstimateRows的大小，在语句1和语句2的值之间，所以他选择的执行计划，和166 --语句1与语句2都不一样167 168 --我们再来比较一下不同执行计划下的速度169 --------------------------------------170 --第一次171 SQL Server 执行时间:172    CPU 时间 = 0 毫秒，占用时间 = 715 毫秒。173 174 SQL Server 执行时间:175    CPU 时间 = 16 毫秒，占用时间 = 1775 毫秒。176 --------------------------------------------177 --------------------------------------178 --第二次179 SQL Server 执行时间:180    CPU 时间 = 998 毫秒，占用时间 = 9821 毫秒。181 182 SQL Server 执行时间:183    CPU 时间 = 998 毫秒，占用时间 = 9906 毫秒。184 --------------------------------------------185 --------------------------------------186 --第三次187 SQL Server 执行时间:188    CPU 时间 = 15 毫秒，占用时间 = 57 毫秒。189 190 SQL Server 执行时间:191    CPU 时间 = 15 毫秒，占用时间 = 66 毫秒。192 --------------------------------------------193 --------------------------------------194 --第四次195 SQL Server 执行时间:196    CPU 时间 = 1981 毫秒，占用时间 = 6926 毫秒。197 198 SQL Server 执行时间:199    CPU 时间 = 1997 毫秒，占用时间 = 6933 毫秒。200 --------------------------------------------201 202 --有参数嗅探的情况，语句三和语句四作出来的执行计划是一种比较中庸的方法，不是最快的203 --也不是最慢的。他对语句性能的影响，一般不会有参数嗅探那么严重，他还是解决204 --参数嗅探的一个候选方案205 206 207 ---------------------------------------------------------------------------------208 --参数嗅探的解决方案209 210 --参数嗅探的问题发生的频率并不高，他只会发生在一些表格里的数据分布很不均匀，或者211 --用户带入的参数值很不均匀的情况下。例如，查询一个时间段数据的存储过程，如果212 --大部分用户都只查1天的数据，SQL缓存的也是这样的执行计划，那对于那些要查一年213 --的数据，可是SQL碰到“参数嗅探”问题的风险了。如果系统里大部分用户都要查一年的数据214 --可是SQL碰巧缓存了一个只查一天数据的存储过程，那大部分用户都会遇到“参数嗅探”的问题215 --这个对性能的影响就大了216 217 --有什么办法能够缓解，或者避免参数嗅探问题呢？在SQL2005以后，可以有很多种方法可供218 --选择219 220 --（1）用exec()的方式运行动态SQL221 --如果在存储过程里不是直接运行语句，而是把语句带上变量，生成一个字符串，再让exec()这样222 --的命令做动态语句运行，那SQL就会在运行到这句话的时候，对动态语句进行编译。这时SQL223 --已经知道了变量的值，会根据生成优化的执行计划，从而绕过参数嗅探问题224 225 --例如前面的存储过程Sniff，就可以改成这样226 USE [AdventureWorks]227 GO228 DROP PROC NOSniff229 GO230 CREATE PROC NOSniff(@i INT)231 AS232 DECLARE @cmd VARCHAR(1000)233 SET @cmd='SELECT COUNT(b.[SalesOrderID]),SUM(p.[Weight])234 FROM [dbo].[SalesOrderHeader_test] a235 INNER JOIN [dbo].[SalesOrderDetail_test] b236 ON a.[SalesOrderID]=b.[SalesOrderID]237 INNER JOIN [Production].[Product] p238 ON b.[ProductID]=p.[ProductID]239 WHERE a.[SalesOrderID]='240 EXEC(@cmd+@i)241 GO242 243 --然后再用上面的顺序调用会得到比他更好的性能244 245 USE [AdventureWorks]246 GO247 DBCC freeproccache248 GO249 SET STATISTICS TIME ON250 SET STATISTICS PROFILE ON251 EXEC [dbo].NOSniff @i = 50000 -- int252 GO253 --------------------------------254 USE [AdventureWorks]255 GO256 SET STATISTICS TIME ON257 SET STATISTICS PROFILE ON258 EXEC [dbo].NOSniff @i = 75124 -- int259 GO260 261 262 263 --使用exec()的方式产生动态编译264 --在查询语句执行之前，都能看见SP:CacheInsert事件。SQL做了动态编译，根据变量的值，265 --正确地估计出每一步的返回结果集大小。所以这两个执行计划，是完全不一样的266 267 --执行结果很快，这种方法的好处，是彻底避免参数嗅探问题。但缺点是要修改存储过程268 --的定义，而且放弃了存储过程一次编译，多次运行的优点，在编译性能上有所损失269 270 271 --（2）使用本地变量local variable272 --在前面，提到了如果把变量值赋给一个本地变量，SQL在编译的时候是没办法知道这个本地273 --变量的值的。所以他会根据表格里数据的一般分布情况，“猜测”一个返回值。不管用户274 --在调用存储过程的时候代入的变量值是多少，做出来的执行计划都是一样的。而这样275 --的执行计划一般比较“中庸”，不会是最优的执行计划，但是对大多数变量值来讲，也不会276 --是一个很差的执行计划277 278 --存储过程[Sniff2]就是这样一个例子279 --这种方法的好处，是保持了存储过程的优点，缺点是要修改存储过程，而且执行计划也不是280 --最优的281 282 283 284 --（3）在语句里使用query hint，指定执行计划285 --在select,insert,update,delete语句的最后，可以加一个"option(<query_hint>)"的子句286 --对SQL将要生成的执行计划进行指导。当DBA知道问题所在以后，可以通过加hint的方式，引导287 --SQL生成一个比较安全的，对所有可能的变量值都不差的执行计划288 289 --现在SQL的query hint还是很强大的，有十几种hint。完整的定义是：290 --msdn：http://msdn.microsoft.com/zh-cn/library/foo66fb1520-dcdf-4aab-9ff1-7de8f79e5b2d.aspx291 <query_hint > ::=292 { { HASH | ORDER } GROUP293   | { CONCAT | HASH | MERGE } UNION294   | { LOOP | MERGE | HASH } JOIN295   | FAST number_rows296   | FORCE ORDER297   | MAXDOP number_of_processors298   | OPTIMIZE FOR ( @variable_name = literal_constant [ , ...n ] )299   | PARAMETERIZATION { SIMPLE | FORCED }300   | RECOMPILE301   | ROBUST PLAN302   | KEEP PLAN303   | KEEPFIXED PLAN304   | EXPAND VIEWS305   | MAXRECURSION number306   | USE PLAN N'xml_plan'307 }308 309 310 --这些hint的用途不一样。有些是引导执行计划使用什么样的运算的，比如，311 --  | { CONCAT | HASH | MERGE } UNION312 --  | { LOOP | MERGE | HASH } JOIN313 314 --有些是防止重编译的，例如315 --  | PARAMETERIZATION { SIMPLE | FORCED }316 --  | KEEP PLAN317 --  | KEEPFIXED PLAN318 319 320 --有些是强制重编译的，例如321 --  | RECOMPILE322 323 324 --有些是影响执行计划的选择的，例如325 --| FAST number_rows326 --| FORCE ORDER327 --| MAXDOP number_of_processors328 --| OPTIMIZE FOR ( @variable_name = literal_constant [ , ...n ] )329 330 --所以他们适合在不同的场合。具体的定义，请看SQL的联机帮助331 332 --为了避免参数嗅探的问题，有下面几种常见的query hint使用方法333 334 --（1）recompile335 --recompile这个查询提示告诉SQL，语句在每一次存储过程运行的时候，都要重新编译一下。336 --这样能够使SQL根据当前变量的值，选一个最好的执行计划。对前面的那个例子，我们可以337 --这样写338 USE [AdventureWorks]339 GO340 DROP PROC NoSniff_QueryHint_Recompile341 GO342 CREATE PROC NoSniff_QueryHint_Recompile(@i INT)343 AS344 SELECT COUNT(b.[SalesOrderID]),SUM(p.[Weight])345 FROM [dbo].[SalesOrderHeader_test] a346 INNER JOIN [dbo].[SalesOrderDetail_test] b347 ON a.[SalesOrderID]=b.[SalesOrderID]348 INNER JOIN [Production].[Product] p349 ON b.[ProductID]=p.[ProductID]350 WHERE a.[SalesOrderID]=@i351 OPTION(RECOMPILE)352 GO353 354 --------------------------------------355 USE [AdventureWorks]356 GO357 DBCC freeproccache358 GO359 EXEC NoSniff_QueryHint_Recompile 50000360 GO361 -------------------------------------362 USE [AdventureWorks]363 GO364 EXEC NoSniff_QueryHint_Recompile 75124365 GO366 367 368 --在SQL Trace里我们能够看到,语句运行之前,都会有一个SQL:StmtRecompile的事件发生369 --而使用的执行计划，就是最准确的那种370 --和这种方法类似，是在存储过程的定义里直接指定“recompile”，也能达到避免371 --参数嗅探的效果372 373 USE [AdventureWorks]374 GO375 DROP PROC NoSniff_SPCreate_Recompile376 GO377 CREATE PROC NoSniff_SPCreate_Recompile(@i INT)378 WITH RECOMPILE379 AS380 SELECT COUNT(b.[SalesOrderID]),SUM(p.[Weight])381 FROM [dbo].[SalesOrderHeader_test] a382 INNER JOIN [dbo].[SalesOrderDetail_test] b383 ON a.[SalesOrderID]=b.[SalesOrderID]384 INNER JOIN [Production].[Product] p385 ON b.[ProductID]=p.[ProductID]386 WHERE a.[SalesOrderID]=@i387 GO388 389 390 -------------------------------------------------391 USE [AdventureWorks]392 GO393 DBCC freeproccache394 GO395 EXEC NoSniff_SPCreate_Recompile 50000396 GO397 398 ------------------------------------------------399 USE [AdventureWorks]400 GO401 EXEC NoSniff_QueryHint_Recompile 75124402 GO403 404 405 --在SQL Trace里,我们能看到,存储过程在执行的时候已经找不到前面的执行计划406 --SP:CacheMiss,所以要生成新的。而使用的执行计划，就是最准确的那种407 408 --这两种“Recompile”提示的差别是，如果在语句层次指定OPTION(RECOMPILE)，409 --那存储过程级别的计划重用还是会有的。只是在运行到那句话的时候，才会发生重编译。410 --如果存储过程里有if-else之类的逻辑，使得发生问题的语句没有执行到，那重编译就不会发生。411 --所以，这是一种问题精确定位后，比较精细的一种调优方法。如果在存储过程级别412 --指定WITH RECOMPILE，那整个存储过程在每次执行的时候都要重编译，这个重复工作量就比较大了413 --但是，如果问题没有精确定位，可以用这种方法快速缓解问题414 415 416 417 --（2）指定join运算 { LOOP | MERGE | HASH } JOIN418 --很多时候，参数嗅探问题是由于SQL对一个该用merge join/hash join的情况误用了419 --nested loops join。确定了问题后，当然可以用查询提示，指定语句里所有join方法。420 --但是这种方法一般很不推荐，因为不是所有的join，SQL都能够根据你给的提示做出来421 --执行计划的。如果对例子存储过程的那个查询最后加上“option(hash join)”，运行422 --的时候会返回这样的错误。SQL不接受这个查询提示：423 424 消息 8622，级别 16，状态 1，第 1 行425 由于此查询中定义了提示，查询处理器未能生成查询计划。请重新提交查询，426 并且不要在查询中指定任何提示，也不要使用 SET FORCEPLAN。427 428 429 --更常见的是，在特定的那个join上使用join hint。这种方法成功几率要高得多430 --例如：431 USE [AdventureWorks]432 GO433 DROP PROC NoSniff_QueryHint_JoinHint434 GO435 CREATE PROC NoSniff_QueryHint_JoinHint(@i INT)436 AS437 SELECT COUNT(b.[SalesOrderID]),SUM(p.[Weight])438 FROM [dbo].[SalesOrderHeader_test] a439 INNER JOIN [dbo].[SalesOrderDetail_test] b440 ON a.[SalesOrderID]=b.[SalesOrderID]441 INNER hash JOIN [Production].[Product] p442 ON b.[ProductID]=p.[ProductID]443 WHERE a.[SalesOrderID]=@i444 GO445 446 447 USE [AdventureWorks]448 GO449 DBCC freeproccache450 GO451 SET STATISTICS PROFILE ON452 GO453 EXEC NoSniff_QueryHint_JoinHint 50000454 GO455 ---------------------------------------------456 USE [AdventureWorks]457 GO458 SET STATISTICS PROFILE ON459 GO460 EXEC NoSniff_QueryHint_JoinHint 75124461 GO462 463 464 465 --这种方法的好处，是保持了存储过程一次编译，后续多次使用的特性，节省编译时间。466 --但是缺点也很明显，使用这样的方法生成执行计划，不一定就是一个好的执行计划467 --而且表格里的数据量变化以后，现在合适的join方式将来可能就不合适，到时候还会468 --造成性能问题，所以使用的时候要很小心469 470 471 472 473 --（3）OPTIMIZE FOR ( @variable_name = literal_constant [ , ...n ] )474 --当确认了参数嗅探问题后，发现，根据某些变量值生成的执行计划，快和慢会相差475 --很大，而根据另外一些变量生成的执行计划，性能在好和坏的时候，相差并不很大。476 --例如当变量等于50000的时候，他用最好的nested loops执行计划，用时十几毫秒，477 --用hash join的那个执行计划，也不过300多毫秒。但是变量等于75124的时候，478 --hash join执行计划需要500多毫秒，用nested loops的时候，要用4000多。479 --从绝对值来讲，让用户等几百毫秒一般是没问题的。但是等上几秒钟，就容易480 --收到抱怨了。所以hash join是一个比较“安全”的执行计划。如果SQL总是使用75124481 --这个值做执行计划，会对大部分查询都比较安全。482 483 --使用OPTIMIZE FOR这个查询指导，就能够让SQL做到这一点。这是SQL2005以后的一个新484 --功能。485 486 USE [AdventureWorks]487 GO488 DROP PROC NoSniff_QueryHint_OptimizeFor489 GO490 CREATE PROC NoSniff_QueryHint_OptimizeFor(@i INT)491 AS492 SELECT COUNT(b.[SalesOrderID]),SUM(p.[Weight])493 FROM [dbo].[SalesOrderHeader_test] a494 INNER JOIN [dbo].[SalesOrderDetail_test] b495 ON a.[SalesOrderID]=b.[SalesOrderID]496 INNER hash JOIN [Production].[Product] p497 ON b.[ProductID]=p.[ProductID]498 WHERE a.[SalesOrderID]=@i499 OPTION(optimize FOR(@i=75124))500 GO501 ---------------------------------502 USE [AdventureWorks]503 GO504 DBCC freeproccache505 GO506 SET STATISTICS PROFILE ON507 GO508 EXEC NoSniff_QueryHint_OptimizeFor 50000509 GO510 ---------------------------------------------511 USE [AdventureWorks]512 GO513 SET STATISTICS PROFILE ON514 GO515 EXEC NoSniff_QueryHint_OptimizeFor 75124516 GO517 518 519 --从SQL Trace里看，存储过程第一次运行时有编译，但是第二次就出现了执行计划重用520 --分析执行计划会发现SQL在第一次编译时，虽然变量值是50000，他还是根据75124521 --来做预估的，EstimateRows的值很大。正因为这样，第二次运行也有不错的性能522 523 --这种方法的优点是，既能够让SQL作出有倾向性的执行计划，又能保证SQL选择执行计划524 --时候的自由度，所以得到的执行计划一般是比较好的。相对于用join hint，这个方法525 --更精细一些。缺点是，如果表格里的数据分布发生了变化，比如用户有一天把526 --75124的记录全删除了，那SQL选择的执行计划就不一定继续正确了。所以他也有他527 --的局限性。528 529 530 --上面介绍的方法，都有一个明显的局限性，那就要去修改存储过程定义。有些时候没有531 --应用开发组的许可，修改存储过程是不可以的。对用sp_executesql的方式调用的指令，532 --问题更大。因为这些指令可能是写在应用程序里，而不是SQL里。DBA是没办法去修改533 --应用程序的。534 535 --好在SQL2005和2008里，引入和完善了一个种叫Plan Guide的功能。DBA可以告诉SQL，当536 --运行某一个语句的时候，请你使用我指定的执行计划。这样就不需要去修改存储过程或537 --应用了。538 539 540 541 --（4）Plan Guide542 --例如可以用下面的方法，在原来那个有参数嗅探问题的存储过程“Sniff”上，解决sniffing问题543 USE [AdventureWorks]544 GO545 EXEC [sys].[sp_create_plan_guide]546 @name=N'Guide1',547 @stmt=N'SELECT COUNT(b.[SalesOrderID]),SUM(p.[Weight])548 FROM [dbo].[SalesOrderHeader_test] a549 INNER JOIN [dbo].[SalesOrderDetail_test] b550 ON a.[SalesOrderID]=b.[SalesOrderID]551 INNER JOIN [Production].[Product] p552 ON b.[ProductID]=p.[ProductID]553 WHERE a.[SalesOrderID]=@i',554 @type=N'OBJECT',555 @module_or_batch=N'Sniff',556 @params=NULL,557 @hints=N'option(optimize for(@i=75124))';558 GO559 560 -----------------------------------561 USE [AdventureWorks]562 GO563 SET STATISTICS PROFILE ON564 GO565 DBCC freeproccache566 GO567 EXEC [dbo].[Sniff] @i = 50000 -- int568 GO569 570 --使用了75124的hash match的join方式571 572 --对于Plan Guide，他还可以使用在一般的语句调优里。在后面的章节会介绍573 574 575 -----------------------各种方法的比较-----------------------------------576 --      方法                 是否修改存储过程             是否每次运行都要重编译          执行计划准确度577 --用exec()方式运行动态SQL          需要                        会                             很准确578 --使用本地变量local variable       需要                        不会                            一般579 --query hint+"recompile"           需要                        会                              很准确580 --query  hint指定join运算          需要                        不会                            很一般581 --query hint optimize for          需要                        不会                            比较准确582 --Plan Guide                       不需要                      不会                            比较准确583 584 585 --DBA可以根据实际情况，选择对他最合适的解决方案