I've got a nice issue with paramater sniffing here and I'd like to know how to work out if it's best to use query hints, add some statistics or something else.
Running the query with the hint of parameter = null solves the issue but I'm wondering if it would be possible to still take advantage of good parameter sniffing by providing some extra statistics.
When the query runs well the values are considerably different, I'll try and post the good run up shortly.
Thanks in advance and sorry if I'm not very clear, feel free to point me in the direction of better info if need be.
Addition: How do I find out the stats that are causing the issue and therefore give me a greater insight into the issue. I thought it would be the index stats but the parameter that causes the issue isn't used there and the stats on the table where the parameter is used look pretty good?
Assuming the code runs in a stored procedure, I have two ideas for you:
- You can add "with recompile" so that for every run you will have a fresh plan.
- You could switch to dynamic SQL.
–Amit
I also agree with Amit that the dynamic SQL approach can have merit in many scenarios and should also be tested – just make sure if there are a lot of different potential predicates that you experiment with the optimize for ad hoc workloads server setting as well.
That could be the best option. I've checked the xml and it appears as though the optimization is finished early due to a timeout, this occurs on both plans. Perhaps it would be best to tune the other elements first and see if that brings the overall cost down so the optimization can finish?
Thanks again for your thoughts, I'll poke at it some more, get it back into a sproc and upload any new plans that are relevant.
I've tried updating the stats on the specific index and this didn't have an effect really, I suspect last time it was coincidental that, when next execute, the new plan was generated with the parameter = null rather than a value.
Thanks again
You might be able to code around it with a
IF @parameter IS NULL SELECT... ELSE SELECT...
construct.
When the parameter is null the SQL is different so you get a different plan for the non-null case.
That aside, you might also be able to avoid the issue completely by adding indexes. There are quite a few index scans, even in the good plan, that if were able to be converted to seeks, might produces a plan that also answers your question without resorting to stat or code 'tricks'.
I had thought of that approach but ideally wanted to get around that and see how stats analysis could be used to help.
As you mention there are many areas of improvement within the query and I'll certainly be looking at these but I really wanted to understand if stats could be better used here – as you mention it could be that the extra indexes also bring with the more statistics which in turn help resolve the issue.
By updating all the stats used I do end up with a good plan again but I think that plan also has the paramater = null again so it's probably just the recompile that has the main effect.
thanks again
Thanks again