In environments where complex queries can get bad plans due to parameter sniffing, it would help to say that all queries with an estimated cost over X should be recompiled every time they run.
For example, in environments where most of my workload is small OLTP queries, I’m fine with caching queries that cost under, say, 500, but above 500, I don’t care how good we think the plan is – it’s worth recompiling because those high-cost reports don’t run often, and I need them to get accurate plans for their own parameters every time they run.
This would help environments with parameter-sensitive queries where Adaptive Memory Grants backfire (like reports sometimes use small date ranges and sometimes years of data.)
I would love this feature. If you would too, cast an upvote.
30 Comments. Leave new
Is that not just OPTION RECOMPILE?
How would that be different in your eyes
Matthew – that requires a query change, and knowledge ahead of time what the query cost will be.
This setting would allow much more flexibility when dealing with third party apps (which can often run the same code base for different client sizes on different servers) and reporting apps (which often have no idea what the cost of the query will be.)
Fair enough.
Would hate to have to make the modification to sp_blitzcache fire that ?
Matthew – I’m not sure what you’re asking. Can you rephrase the question? This isn’t about running sp_BlitzCache (which already has a ton of recompile hints.)
Is it not blitzcache that looks at many plans for 1 query?
If it recompiled every time for different parameters there could be a few plans for 1 query, but by design this time.
Apologies if that doesn’t make sense
Nah, when queries are recompiled, the plan cache doesn’t have cumulative numbers for them. You might be thinking of sp_BlitzQueryStore, which wouldn’t require any changes. It handles that already.
Parameter sniffing is a bug. It should be fixed.
I don’t think I can agree with that. Yes, parameter sniffing makes troubleshooting harder but it’s better than ending up with multiple plans for essentially the same query when the exact value doesn’t matter so much, and it’s better than recompiling every query with no caching. Parameter sniffing is an outgrowth of an attempt at allowing SQL Server to make the most of the memory it’s allotted. The trick is to narrow down the scenarios in which parameter sniffing leads to issues, not throw out the baby with the bath water.
The assumption that exactly one plan can handle all combinations of parameters is weak.
The query optimizer should look at the statistics it is using.
If statistics all have a smooth distribution, then one plan is good.
If 90% of a column has one value, and all other values show up twice then two plans are justified.
(Like user votes in the stack overflow database.)
Someone had a great picture that shows all combinations of 2 input parameters and
the 6 execution plans that are optimal for different combinations of inputs.
SQL should figure this out and make 6 execution plans for one query.
A plan has an estimated cost. If the actual cost while running the query is 10x more
then SQL could abort the query and make a second plan. It should keep both plans
because just one is obviously not working.
If this is too hard for SQL to figure this out, they should give us a simple way to tell it we
want different plans for these ranges of input parameters.
Great suggestion. That’s true for a few parameters, but you may end up with many more combinations of plans with many parameters. So some limit has to be taken into account. The number of possible access plans depends on the amount of stats discrepancies about parameters. So a query plan should keep an indicator about how wide the distribution of values applies to each parameter. If the actual parameter falls into a similar distribution width of values as an unknown previous parameter, the plan should stay the same. This suggests that the optimizer would have to look for stats about each parameter every time it looks on a query. If the distribution seems about the same for each parameter as the one of a previous plan, the optimizer could then use again the same plan.
Better yet, use the power of multi-threading and always kick off a query recompile in a separate thread. If the query recompile in the second thread completes before the query results in the first thread are available, and the new query plan looks more efficient for the parameters given, abort the query in the first thread and restart it with the new plan. This way you get the benefit of cached query plans but avoid the parameter sniffing problem if a better plan is found while the cached query is running.
Sounds like an enterprise feature waiting to happen. Meanwhile, us poor folks trying to subsist down here in standard-land tuned you out after “separate thread.”
It guarantees that parameter sniffing is no more necessary, but it implies that processing will double at each query. Not sure if statistically, it will be better.
Wow we can’t even look at uservoice issues now without signing in first? Used to only have to sign in to vote for something/comment, but could browse all I wanted.
And it doesn’t give me the option to switch accounts first (for those of use with more than one MS account).
Using uservoice just became more painful. I’ll be on it less often now. Which is a shame.
I get this: “windowsazure.uservoice.com needs permission to access resources in your organization that only an admin can grant. Please ask an admin to grant permission to this app before you can use it.” I guess Microsoft really doesn’t want to hear from us.
Are you using an @live.com account or similar ? Getting the same error. Ridiculous.
No, I’m using my regular account in my employer’s domain, which uses (I think) Azure Active Directory Single Sign-on. I have no involvement with any of that.
I get “AADSTS50020: User account from identity provider ‘live.com’ does not exist in tenant ‘UserVoice, Inc.’ and cannot access the application ’91a42e81-999b-4cf1-aa36-bb33f25ff53b'(windowsazure.uservoice.com) in that tenant. The account needs to be added as an external user in the tenant first. Sign out and sign in again with a different Azure Active Directory user account.
(trying multiple accounts)
You have my vote!
This seems like it should be a no brainer! The only check I would maybe add is if the cost is above the threshold and there is a parameter but that’s just nitpicking.
This doesn’t take into account terrible apps that frequently execute high-cost queries. It isn’t just the occasional big report. Chris’ idea to only recompile if there is a parameter would help, but not solve the entire problem.
I was at a London conference sometime post-SQL2000, pre-SQL2005, and the presenter started talking about the benefits of parallelization. You could feel the mood of the crowd turn ugly. Over 70% of us were setting to MAXDOP = 1, and the rest couldn’t be bothered to put their hands up. I think we were all running OLTP systems and we didn’t want to deal with parallelization. And any reporting queries that could benefit from parallelism just wasn’t worth the effort.
MAXDOP = 1 was the wrong setting of course. The rise of OLAP, better parallel plans, and smarter more tolerant people saying “Cost Threshold for Parallelism” is the setting to use changed the Best Practice of the herd.
It feels that a Cost Threshold for recompiles isn’t the right tool. Is it just high cost queries that are the issue, or is it a function of cost and frequency of execution. I’ve got my ETL queries that run every hour, and I don’t give a hoot about plan re-use, the data volume pattern is all over the place. But for a more mixed environment, would I really want my reports to keep recompiling? |
We look for queries where the estimated and actual number of rows are significantly out. Would it not be a benefit if the optimiser did the same and invalidate the plan after the fact? Or do you think we’d end up in the same sort of flip-flop that you’ve seem with AMG?
I really shouldn’t be try to have a technical discussion with the likes of Brent after this many beers. It’s a shame you won’t be in the UK for SQLBits this year. Do you still enjoy drinking perries?
Unproductive Comment, because apparently I enjoy the sound of my own typing:
This is something that Oracle appears to handle pretty well. I DO NOT suggest MS engage in corporate espionage and see how they do it, but if it SQL Server were to somehow handle this in exactly the same way as does Oracle, I’d be completely OK with that 😉
(Incidentally, when last I worked with DB2 it seemed to have the same problem SQL Server has, but that was Db2 9.5 / 9.7, so it was a while back)
You could query sys.dm_query_stats to get a list of all the high cost queries and the remove the plans for those queries from cache. In the scenario of expensive reports that don’t run very often, if you ran this every minute, the likelihood of a plan being in cache would be pretty low.
The option to have a database setting to keep plans from ever getting to cache is better, but if I can get 80% of the way there without waiting for Microsoft, that’s a win.
Every query has a plan until they get punched in the mouth.
HAH! 😀
I’d like this, and will upvote, but I’d like more metrics built into the system at a low level as well. How awesome would it be if we knew what queries hit which tables, indexes, etc, so at any time you wanted to make a change you knew what would be affected. I know it’d be more data storage and more cost tho.
One thing I have been wondering, is how much SQL can make use of CUDA cores, or how much it could if it was allowed to. When you think of how much AI processing and the “cheap” cost of graphics processors, I wonder if a lot of the math processing in SQL could be done that way at a low level.
Top voted item is…..dark mode. There must be an army of dba’s out in the world who don’t have production problems to solve.
It’s a fair point, to be sure! But, as an old guy with tired eyes, I’d just (respectfully) point out that it’s hard to fix production issues when your eyes are bleeding 😉
Just thinking some more about this… I’d like to be able to flag (annotate) a parameter for building separate query plans.
For example, if you had a parameter that causes branching, but instead of making separate stored procedures that call each other, or some dynamic trick, or using recompile – the query engine knew to store a plan per value based off that one parameter.
Of course, it gets a bit more costly for multiple flagged parameters, but buyer beware and all that.
upvoted!