forcing sql server to do more query optimization work for me

All Replies

• Saturday, July 28, 2012 3:06 PM

   

   
  

  1

  1.  I never thought so, but recently I thought someone might have mentioned a way to set the compilation-thought limit for the overall server.

  2.  Not exactly.  However, here's something I've seen in operation but never documented!  You know that execution plans now represent one or more statements, but that a long SP might comprise several execution plans, so that required recompilations are kept local to just the lines that need to be recompiled, not the entire SP.  So, each fragment of code must have a "signature", and SQL Server seems to recognize if ANOTHER SP, even in another SPID by another user, compiles that same signature with better execution parameters!

  What I WISH SQL Server would do is (optionally) keep several execution plans around and choose them by parameters, to eliminate problems with parameter sniffing and sub-optimal plans.

  3.  That could be very expensive.  Anyway, the answer AFAIK is no.

  Josh

  • Reply
  • Quote

   
• Saturday, July 28, 2012 4:10 PM

   

   
  

  0

  Josh, thanks for your comments.  Some additional notes:

  1.  If anyone knows how to do (1), I'd like to see if changing that limit improves the execution plans.  Ideally it would be on a per query basis, but any way of testing it would be helpful.  The idea would be to give a lot more analysis time to sql server on very complex (many joins, many ctes or derived tables, nested table functions) and long-running queries.  I assume there must be some way they do these sorts of things in the internal version of sql server, but perhaps it's stripped out in production.  Don't know how other databases do the optimization (and with open source ones, one could look at the code that does it, though I doubt I'd make much sense of it); may be that would give one a sense of whether increasing the time spent on optimization would make much difference.

  2.  Yes, it does have all sorts of ways of avoiding recompile, but not afaik a way to force multiple or super-long compiles/passes and add a hint that selects the best one found next time.  I think the problem with all of this is that if stats change or the engine changes or parameters used change, what's ideal wouldn't be ideal next time (the usual objections to giving hints).  I can't think of an objection to (1) offhand though, unless Sql Server rarely sub-optimizes because of time pressure.    

  3.  Yes, expensive, especially in large databases with many big tables and indexes, but it would only be an option clause on the query.  Mainly would be a testing thing, but on bad queries I might leave it on.    

  This is all for complex queries that run only a few times a day and run for a longish time (half an hour to an hour), not the vanilla queries that run in a flash and are constantly called.

  p.s. In #2 "with better execution parameters": you mean that sql server knows the second execution plan for the same query statement in (another) sproc B is better than the one it found in sproc A and picks the one for B in executing A?  (I thought only that it sees the same query including parameters already has an execution plan in B so doesn't bother finding a plan again when it runs A.)
  
  
  
  
  

  • Edited by TechVsLife2 Saturday, July 28, 2012 4:19 PM
  •  
  • Reply
  • Quote

   
• Saturday, July 28, 2012 7:50 PM

   

   
  

  1

  I could make a fair list of enhancements I'd like to make to just options you could set for the compiler/optimizer.

  The claim I've heard is that there are SO many possible plans, that it's impractical to let the engine search for them, so compilation of ANYTHING over a couple of seconds of exploration is just not done.  That's the claim.  I dunno.   Especially if it could be done offline, as you suggest just for big, critical SPs, seems to me it would be very practical.  Current hardware has a honkin' lot of CPU power, I'd say a minute with 8 cores working could do big SPs a lot better than today.  SQL Server frequently mis-optimizes, especially having to do with parameter sniffing - it optimizes a plan for one value, locks in the plan, and then it is way non-optimal for other values.  There is NO plan that is equally efficient across values, but there are several good plans corresponding to several classes of values.  When this occurs, SQL Server is no help at all.  All you can do is create three different SPs and determine yourself which one to call for what value.  Bleh.

  --

  Just when that plan-stealing "feature" cuts in, is a little hard to tell.  Unexpectedly, is my experience!  I was working on a long SP, fiddling with one series of statements right in the middle.  I made some progress, then went back to the base model in another session and ran that for the test case - and it ran as well as the new one!  What????  Clearing the plan cache, running the old/bad one gave the expected old/bad results, then running the new/good one gave the new/good results, and then running the old/bad one gave new/good results!  I got right online here and blathered about it.  Nobody said they were certain I was crazy, so I guess I saw what I saw.  And I think I saw it again just recently, as a matter of fact.

  Yeah maybe I could have printed out and collated all the plans, but I wasn't that ambitious, that's a lot of work for long routines and easy to misread.

  Josh

  • Reply
  • Quote

   
• Sunday, July 29, 2012 1:23 AM

  Moderator

   

   
  

  1

  You can disable Trivial plan generation by using an undocumented trace flag, as explained by Paul White in his Query Optimizer Deep Dive series (http://sqlblog.com/blogs/paul_white/archive/2012/04/28/query-optimizer-deep-dive-part-1.aspx) but I wouldn't recommend doing this on a production server and even Paul states it's just for experimentation.  I'd say that 99% of the time I see a long running query, it isn't that the query optimizer generated a sub-optimal plan.  

  However, it would be a nice feature, especially since Oracle has a tool that does it, to be able to submit a query to a server and have the optimizer find the absolute best plan and then output that to you so that you could force it with a Plan Guide.  I suggested this to Microsoft, specifically to Conor Cunningham who leads the optimizer team for SQL Server, and it is not something that provides a significant enough benefit to the vast majority of the SQL Server user base for it to become a part of the product.  The optimizer does not tell you what hints would make a specific plan get selected, hints in general shouldn't be used except in rare cases because they aren't hints, they are explicit directives that force the optimizer to generate a specific type of plan.  They also aren't a be all end all fix to a problem and with changes to data density, can cause the optimizer to not be able to generate a plan in some cases.

  If the statistics are out of data when the query compiles, the stats are updated if auto update stats is enabled.  The only time this doesn't happen is if you enabled auto update stats async on the database which continues with generating the plan off the bad stats and defers the update asynchronously.  There is no option to force a stats update for a query, that would be incredibly expensive to have enabled and I never expect to see something like that added to the product.  If you have a specific query that requires this your only mechanism would be to manually update the stats before executing that statement.  

  It sounds to me like you have design problems that you want the engine to simply fix for you, but that's never going to be something that is possible.  The majority of systems on SQL don't have the problems that you are asking about features to fix.  I can count on both of my hands, the number of times I have had to hint queries to force a better plan from the optimizer in my time working with SQL Server that weren't related to parameter sniffing and using OPTION RECOMPILE or OPTION OPTIMIZE FOR.

  ---

  Jonathan Kehayias | Principal Consultant, SQLSkills.com
  SQL Server MVP | Microsoft Certified Master: SQL Server 2008
  Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
  Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
  Please click the Mark as Answer button if a post solves your problem!

  • Marked As Answer by TechVsLife2 Sunday, July 29, 2012 8:52 AM
  •  
  • Reply
  • Quote

   
• Sunday, July 29, 2012 8:51 AM

   

   
  

  0

  Thanks for the comments.

  The post by Paul White was very helpful and I'll have to try it out and see what I learn.  I wouldn't want to disable the normal behavior across the board, but simply have it as an option for certain queries to see if I get a much better plan telling sql server to go for broke.  These problem queries are rare, and they're not breaking down because of parameter sniffing, table design, or any clear reason (well, other than the complexity of the query, number of joins and nests etc.).  In troubleshooting, it can be helpful to simply rule out the optimizer as a factor, so for these queries I'd like to be able to tell it to do its best (giving it x minutes to think about it) and exhaust the possibilities.  They tend to get solved by breaking them up into less complex ones, but some are recalcitrant, and I'd like to see the best possible execution plan giving the engine plenty of time, rather than thinking it may have given up prematurely.  (I suspect it's true that the optimizer wouldn't do much better even given much more time, but I'd like to see it myself.)

  I suppose if one wanted to take this to the extreme, one could set up different database engines on the same computer and compare the execution plans they generate for the same query.

  (Re stats, even with auto update on and async off, I think there are sometimes cases where the threshold for update stats would not be met and query performance might suffer.  A stat option on the query would be used for troubleshooting, but I can always do a stat update manually--would be easier with an option though.)

  
  
  
  

  • Edited by TechVsLife2 Sunday, July 29, 2012 8:56 AM
  •  
  • Reply
  • Quote

   
• Sunday, July 29, 2012 2:42 PM

   

   
  

  0

  They tend to get solved by breaking them up into less complex ones, but some are recalcitrant, ...

  Exactamundo.  Time after time, I've gone in and found a perfectly well written SQL query joining a bunch of tables, all indexes in place and updated, and horrible execution.  SQL Server has not been able to determine that a join of just the first couple of tables is highly selective and the rest are there for further elimination.  Frankly, one could say that's even a bug in SQL itself, maybe using relational multiple and divide operators instead of the ambivalent join would be more appropriate??  Anyway hand-coding it into first the selective statement and then the elaborative statement tends to work wonders.

  Though it does not always solve the parameter sniffing problems.  Even BOL has a line warning that for big tables you frequently get a bad plan when an important parameter is the identity field on a large and growing table, which is alternately a small bucket and then a big bucket, with these most recent values the most commonly queried, where auto-stats will not update as often as you might like.  Of course you can schedule your own stats update for such tables in your schema, but maybe something in the engine could be changed to watch and fix just those cases, since they have been so clearly identified and are VERY common.

  Josh
  

  • Reply
  • Quote

   
• Sunday, July 29, 2012 4:15 PM

  Moderator

   

   
  

  0

  In cases where this happens, the cardinality estimates may be off because you don't have the necessary column level statistics to tell the optimizer the selectivity.  Tuning is not just about having indexes, the optimizer makes use of statistics as well, and looking at the cardinality estimate versus actual row count in the Actual plan can tell you where it doesn't have enough information.  Keep in mind that the table order you wrote in the query is not the order that the final plan will use since the optimizer performs rewrites as a part of optimization, and the more tables being joined together the higher the compile cost is and the more options the optimizer has to investigate.  The estimates higher up the tree can easily become incorrect and you get a suboptimal execution plan as a result of the misestimation of the output from a previous step leading to further estimation issues.  By breaking a large complex query down into smaller portions you get around the cardinality issues and provide the optimizer better information to work with when optimizing the steps.  IF you know that the join order written provides the best elimination of rows, you can force that order with the FORCE ORDER hint, to tell the optimizer not to perform the rewrites of the join order.

  If you think you have hit a bug, you should open a product support case since they don't charge you for actual bugs in the product, but I don't think you've hit a bug at all, and you shouldn't spread incorrect information by calling the behavior a bug.  For circumstances where parameter sniffing is a problem, the product group already provided a number of methods to work around the parameter sensitivity issues including OPTIMIZE FOR UNKNOWN and Trace Flag 4136 (http://blogs.msdn.com/b/queryoptteam/archive/2006/03/31/565991.aspx), but in general it is better to have parameter sniffing enabled and for the majority of SQL Server installations it is NEVER a major problem.

  ---

  Jonathan Kehayias | Principal Consultant, SQLSkills.com
  SQL Server MVP | Microsoft Certified Master: SQL Server 2008
  Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
  Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
  Please click the Mark as Answer button if a post solves your problem!

  • Reply
  • Quote

   
• Monday, July 30, 2012 3:01 PM

   

   
  

  0

  Thanks.  Parameter sniffing is not an issue, as I have the queries set to recompile every time and there are no parameters being passed.  However, the estimates are way off--the plans have estimated rows of only one or a handful, but actual rows are up around the million range.  The statistics are up to date, since I forced them to update.  I'll see if I can do with changing the query/tables/indexes around.           

  • Reply
  • Quote