Index Choice and Parameter Sniffing

Roundhouse

Rounding out a few posts about SQL Server’s choice of one or more indexes depending on the cardinality estimates of literal values.

Today we’re going to look at how indexes can contribute to parameter sniffing issues.

It’s Friday and I try to save the real uplifting stuff for these posts.

Procedural

Here’s our stored procedure! A real beaut, as they say.

Here are the indexes we currently have.

Looking at these, it’s pretty easy to imagine scenarios where one or the other might be chosen.

Heck, even a dullard like myself could figure it out.

Rare Score

Running the procedure for an uncommon score, we get a tidy little loopy little plan.

It’s hard to hate a plan that sinishes in 59ms

Of course, that plan applied to a less common score results in tomfoolery of the highest order.

Lowest order?

I’m not sure.

Except when it takes 14 seconds.

In both of these queries, we used our “smooth” index.

Who created that thing? We don’t know. It’s been there since the 90s.

Sloane Square

If we recompile, and start with 0 first, we get a uh…

Well darnit

We get an equally little loopy little plan.

The difference? Join order, and now we use our chunky index.

Running our proc for the uncommon value…

Don’t make fun of me later.

Well, that doesn’t turn out so bad either.

Pound Sand

When you’re troubleshooting parameter sniffing, the plans might not be totally different.

Sometimes a subtle change of index usage can really throw gas on things.

It’s also a good example of how Key Lookups aren’t always a huge problem.

Both plans had them, just in different places.

Which one is bad?

It would be hard to figure out if one is good or bad in estimated or cached plans.

Especially because they only tell you compile time parameters, and not runtime parameters.

Neither one is a good time parameter.

Thanks for reading!

1 thought on “Index Choice and Parameter Sniffing”

Leave a Reply

Your email address will not be published. Required fields are marked *