March 6, 2012 at 10:15 am
Hi all,
I'm hoping someone here can answer a question I seem to be having real problems finding the answer to myself. The issue concerns parameter sniffing. I have been aware of this phenomenon for a few years now and I have read a number of articles on the subject. I understand the signs, why it happens and the various options available to me to 'resolve' it, though i use the word resolve in quotes as I don't believe any of the options are a resolution, merely a work-around but that's another issue.
To explain the scenario, I am the main developer on a reasonably large web app with a SQL Server back-end (at least, it's large in my opinion, with over 100 tables and getting on for 1000 stored procedures). Every now and again, but especially in the last 6 months, a random procedure that has always worked perfectly and hasn't been touched in months, even years, will start to misbehave with the parameter sniffing issue. Typically we perform an sp_recompile to start with and if it won't stop having the issue on a regular basis (often if it crops up once, it'll crop up several times a week), we add option (recompile) to the end of the procedure and all is well again. Almost all of the procedures (but not every single one) have been procedures returning lists of data and using the common filtering format
WHERE (MY_COLUMN = @CONDITION OR @CONDITION IS NULL)
What I don't understand is the why. Why this procedure? Why now? If it's been working fine for six months, a year, two years, more even, what was the final straw? This isn't to solve any immediate problem, what I was hoping for was some ideas to use to look at what made procedure XYZ go wrong, when similar procedure ABC is working fine. I have dozens of procedures that work in the same way, so why was that procedure the one to go wrong?
Hopefully I have explained my question sufficiently well. I have already looked at a number of sites explaining what parameter sniffing is, the tell-tale signs and how to stop it from occurring. What I am interested in is more fundamental than that; I want to understand what the trigger might be that would turn a normal functional procedure that is doing the same job every day into a procedure that is frequently going wrong.
Any thoughts anyone can give would be appreciated
Thanks
Paul
March 6, 2012 at 10:21 am
This is a classic catch-all query.
To work out why these can provide bad plans and what the possible solutions are, I recommend checking out Gail's comprehensive blog post on the topic:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Careful using OPTION(RECOMPILE) if you're on a version of 2008 prior to SP2 as you can get incorrect results due to a bug in earlier builds.
March 6, 2012 at 10:28 am
It's worth noting that parameter sniffing itself is not really the problem here, it's that the conditional logic limits that types of plans that can be safely cached in the first place. So even if your first execution was with your most prevalent set of parameters and that plan was cached, you can still end up with a duff plan as it has to be able to cater for future changes in parameters, including handling the NULL condition all within the same plan.
March 6, 2012 at 10:30 am
Hi HowardW,
I have read just read through that and had previously read the Erland Sommarskog page on the same topic that she refers to and unfortunately it's no help. We already use Option (recompile) as our solution and Dynamic SQL is not an option as the industry I work in is quite security conscious, so I am not allowed to code for that
Thanks
Paul
March 6, 2012 at 11:15 am
Just food for thought. If the procedure(s) have been working well for months (or longer) and then suddenly start having issues, it could be a result in changes in data. You may be hitting the tipping point in how plans work and what was once good is no longer.
March 6, 2012 at 11:40 am
Lynn Pettis (3/6/2012)
Just food for thought. If the procedure(s) have been working well for months (or longer) and then suddenly start having issues, it could be a result in changes in data. You may be hitting the tipping point in how plans work and what was once good is no longer.
To go along with that, it almost sounds like a statistics issue. How often do you update statistics?
Jared
CE - Microsoft
March 6, 2012 at 3:08 pm
paul.goldstraw (3/6/2012)
...Dynamic SQL is not an option as the industry I work in is quite security conscious, so I am not allowed to code for that...
Are you allowed to use internet? Can't believe it! :hehe:
There are no security issues with using Dynamic SQL if it is used securely (properly) eg. via using parametrised sp_executesql calls.
March 6, 2012 at 3:39 pm
paul.goldstraw (3/6/2012)
We already use Option (recompile) as our solution
If you use Option(Recompile) then you cannot have a parameter sniffing problem. Parameter sniffing problems occur when a plan is compiled for a certain parameter value, cached and then reused inappropriately. With option(recompile) the plan is never cached and can't be reused.
Check that statistics are up to date, check that indexes are appropriate (could be the data volume has reached a point where the optimiser is generating a different plan that's maybe not as optimal as it could be with better indexes)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 7, 2012 at 2:24 am
Hi Gail,
Sorry maybe I didn't phrase it very well. The usual series of events is
- Procedure starts suffering from parameter sniffing (symptoms are time-outs in the web-app, usual speed in SSMS; visible change in the execution plan when using the same set options as the web app)
- plan is recompiled once to solve the problem and continues as normal for a while, then breaks again. This repeats for a few days
- as a means to keep it working, option (recompile) is added to the code. Parameter sniffing goes away
However, I don't consider this, or any of the other solutions online to be true solutions. A real solution would be code that just works. Whether this is even possible, I don't know, but I don't think dumping parameters into variables or recompiling every time is an answer, it's just a work-around.
Unfortunately, I am the developer and have no control over the live product. I have enquired about the statistics from our DBA team and will ask them about the indexes as well.
March 7, 2012 at 2:26 am
Eugene Elutin (3/6/2012)
paul.goldstraw (3/6/2012)
...Dynamic SQL is not an option as the industry I work in is quite security conscious, so I am not allowed to code for that...
Are you allowed to use internet? Can't believe it! :hehe:
There are no security issues with using Dynamic SQL if it is used securely (properly) eg. via using parametrised sp_executesql calls.
Unfortunately this is the hand i'm dealt here, and annoyingly i'm allowed to use the internet less than i'd like. Numerous SQL and tech related websites are not available to me
March 7, 2012 at 2:32 am
Lynn Pettis (3/6/2012)
Just food for thought. If the procedure(s) have been working well for months (or longer) and then suddenly start having issues, it could be a result in changes in data. You may be hitting the tipping point in how plans work and what was once good is no longer.
Lynn,
I think I get what you're saying, this is related to the statistics others have been asking about. How might we go about resolving that though? Assuming the statistics are up-to-date, the data is the data, what other avenues are there short of a full redesign which may be equally susceptible to the problem?
Thanks
Paul
March 7, 2012 at 4:02 am
paul.goldstraw (3/7/2012)
However, I don't consider this, or any of the other solutions online to be true solutions. A real solution would be code that just works. Whether this is even possible, I don't know, but I don't think dumping parameters into variables or recompiling every time is an answer, it's just a work-around.
The solutions to parameter sniffing problems are either option recompile, option optimise for (with an appropriate value or with unknown) or using variables instead of parameters.
If you don't consider those solutions, then I'm afraid I have nothing to offer you.
It's not about code (unless you're talking about the code in the query optimiser), parameter sniffing problems are a data issue actually, they happen when a cached plan is reused with a different parameter value to what it was compiled with and that different parameter value results in a massively different row count to what the plan was optimised for.
For catch-all queries ( the pattern you showed in your first post), option recompile is a good solution on SQL 2008 as it allows the optimiser to come up with a plan optimal for the exact parameters passed, without option recompile it has to come up with a generic plan that is safe for reuse.
If you can get hold of the book SQL Server MVP Deep Dives 2, have a look at Grant Fritchey's chapter on parameter sniffing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 7, 2012 at 4:13 am
GilaMonster (3/7/2012)
paul.goldstraw (3/7/2012)
However, I don't consider this, or any of the other solutions online to be true solutions. A real solution would be code that just works. Whether this is even possible, I don't know, but I don't think dumping parameters into variables or recompiling every time is an answer, it's just a work-around.The solutions to parameter sniffing problems are either option recompile, option optimise for (with an appropriate value or with unknown) or using variables instead of parameters.
...
I guess what Paul is looking after is MS to change default stored proc behaviour. By default it would always recompile, untill option "do-not-recompile" is used :w00t:
That would be the "true" solution for this problem from Paul prospective. Yes, it would create some problems with stored proc execution plan is not re-used by default, but using new option "do-not-recompile" would solve them. Or... would it be then not "a solution" again, but "a work around"... Few complains to MS and they change it back... :hehe:
Or make it configurable, that's it! :w00t:
March 7, 2012 at 4:31 am
There is a wider point about whether a specific optimisation could be put in place for catch-all queries. It's true that there's no one plan that fits all for these types of queries, but it's not an absolute rule of RDBMS design that only one plan can be cached for a specific procedure/parameterised statement.
Oracle doesn't do this. It can cache as many different plans as the algorithm deems prudent, so it can pick a different one for drastically different parameter sets.
Having said that, it's a moot point since SQL Server doesn't do this (even in 2012) and I haven't seen any drive from MS to do so, so we're looking for work-arounds that do the same thing. The dynamic SQL approach is the one that most closely matches this in that it will cache plans and therefore not take the hit in compilation on each execution, but that you can design it so that you'll get the best plan for each set of optional parameter combinations.
You should bear in mind that recompiling each time isn't that expensive, so it's a valid option.
If running sp_recompile (rather then using OPTION(RECOMPILE) which is very different) fixes the problem though (even temporarily), then it is clearly able to find a good plan (I'm not quite sure why with optional parameters). Putting an OPTIMISE FOR hint could make sure that the cached plan is always the one you want for your most commonly occuring queries. You may also want to look why there's plan movement in the first place. E.g. are you under a lot of memory pressure and it's forcing cached plans out of the procedure cache too frequently.
But if the question is how can you make things better, by the way, I can't change anything? Well, you can't. 🙂
March 7, 2012 at 5:12 am
Eugene Elutin (3/7/2012)
GilaMonster (3/7/2012)
paul.goldstraw (3/7/2012)
However, I don't consider this, or any of the other solutions online to be true solutions. A real solution would be code that just works. Whether this is even possible, I don't know, but I don't think dumping parameters into variables or recompiling every time is an answer, it's just a work-around.The solutions to parameter sniffing problems are either option recompile, option optimise for (with an appropriate value or with unknown) or using variables instead of parameters.
...
I guess what Paul is looking after is MS to change default stored proc behaviour. By default it would always recompile, untill option "do-not-recompile" is used :w00t:
That would be the "true" solution for this problem from Paul prospective. Yes, it would create some problems with stored proc execution plan is not re-used by default, but using new option "do-not-recompile" would solve them. Or... would it be then not "a solution" again, but "a work around"... Few complains to MS and they change it back... :hehe:
Or make it configurable, that's it! :w00t:
What I would like is for the compiler to be intelligent enough to spot the problems in advance and do something about it, whether that's recompiling or whatever. For instance, from Gail's blog, she uses the following example (I hope you don't mind me borrowing this to illustrate my case Gail)
SQL in the Wild - Parameter sniffing, pt 3[/url]
Now, let's see if I can get the optimiser to make the wrong choice.
CREATE PROCEDURE TestSniffing3 @StringVar VARCHAR(10) ASIF @StringVar not like 'a%'
SET @StringVar = 'abc'
SELECT * from largetable where somestring = @StringVar
GO
Looks simple enough. It the parameter isn't within a certain range, set it to some default value. This kind of stored proc construction isn't unusual. I often see it in search procs. Pass in a NULL if you want all rows to match. Let's see what the optimiser makes of it.
EXEC TestSniffing3 'zzz'It returns 1667 rows, as expected. Now take a look at the execution plan. It's doing an index seek. Not what was expected and not optimal. On my machine the key lookup's in at 87% of the query.
So, what went wrong?
A look at the properties of the index seek give a clue. Estimated rows - 9 actual rows - 1667. The xml form of the execution plan give another clue.
<ColumnReference Column="@StringVar" ParameterCompiledValue="'zzz'" ParameterRuntimeValue="'abc'" />
What I would want is for the compiler to see somehow (don't ask me how) that the compiled value and the runtime value aren't the same and had we compiled with the runtime value, a different plan would have been generated, and to instead either generate another plan or do something that is a bit more generic, that it may not be the best plan but it's not going to be the worst.
However i'm digressing, to be clear, i'm not asking for solutions to parameter sniffing, I know that there are few options at the moment, but what I really wanted to ask was something a little more hypothetical.
If i have 4 stored procedures, all doing the same sorts of things, returning filtered sets of data about 1 or more tables in a format suitable for the application calling it, then why might one suddenly start suffering from this problem? This is the crux of what i'm after, what is the tipping point? Obviously from a developer perspective, I look at this as 'all these work and this one doesn't, so it's a code issue'. That was what I meant when I said how can I recode them to not suffer with this problem? It might be that I will have to split out each of the filters into a new procedure so the front end calls a different procedure for each filter, but that would get messy and mean changes would need to be rolled out against many procedures, not just one.
Hopefully that makes it clearer what I was trying to ask 🙂
Thanks
Paul
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply