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.

Mart 2014-01-08 13:33:11

I've done quite a bit of digging with this and over the time the sproc is executed and it sometimes gets a new plan that has a parameter value that's not null, this then causes it to take a long time to execute.

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?

/storage/attachments/731-tmp460E_Anonymized.queryanalysis

AmitL 2014-01-08 14:14:37

Hey.

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

Mart 2014-01-08 14:22:38

Thanks for the ideas Amit, both of those work fine as you say. I'd still prefer to keep things simple and with a minimum of recompiles. The 'optimize for' query hint seems to work the best so far but I really wanted to get rid of that too.

Thanks again

Aaron Bertrand 2014-01-08 14:33:33

If I'm reading the plan right (and it's possible I'm not), the compile time for these queries was next to nothing. I would consider testing running the queries with OPTION (RECOMPILE) (do this at the statement level, not the procedure level) to see if this remains true – I'd rather flesh that out than automatically assume compiles will be expensive. Sometimes it's just the lesser of all evils.

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.

Mart 2014-01-08 14:56:06

Thanks Aaron,

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.

Mart 2014-01-08 13:37:33

Here's the better running version, thanks in advance. link text

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

/storage/attachments/732-tmp59df_anonymized.queryanalysis

Dan Holmes 2014-01-08 13:53:11

to attempt an answer to the question, you could try filtered stats/indexes but i haven't had any luck with them. There are too many restrictions.

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'.

Mart 2014-01-08 14:16:20

Thanks Dan

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

Hans 2014-01-08 17:16:07

Parametersniffing will always happen as long as plans get stored and reused with other parameter values. You would normally fix this just for the problem statement, and then the best way to always get a plan without parameter sniffing is OPTION (RECOMPILE), for the statement. You should normally not use optimize for hint if your name is'nt Adam Mechanic. Optimize for will get you a sniffing issue for every other value than the one you optimize for 🙂

January 8, 2014

No Comments