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.
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.
You might be able to code around it with a
IF @parameter IS NULL SELECT... ELSE SELECT...
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'.