Bad Parameter Sniffing Decision Flow Chart

Home / SQL Server 2005 / Bad Parameter Sniffing Decision Flow Chart

Lots of people are confused by how to deal with bad parameter sniffing when it occurs. In an effort to help with this, I’m going to try to make a decision flow chart to walk you through the process. This is a rough, quite rough, first draft.

I would love to hear any input. For this draft, I won’t address the things I think I’ve left out. I want to see what you think of the decision flow and what you think might need to be included. Click on it to embiggen.

Thanks to the attendees at my SQLSaturday Louisville pre-con for the great questions and the inspiration to get this done.

Thank you in advance for any and all feedback.

7 Comments

  • ScaryDBA

    Yeah, but it’s kind of rare. In some circumstances a filtered index or even filtered statistics can help address parameter sniffing. It’s one of the options I left off what’s above. Another option is creating wrapper procedures that look for certain values and then call other procedures using different hints to arrive at different plans based on the values passed. There are a few others too. I’m also aware the inequality predicates could result in a different flow chart entirely.

    A solution for bad parameter sniffing is a complex topic. I’m trying to simplify it as much as possible, however, I’m not sure if I’m going to be able to. It’s the main reason I put this out in an “unfinished” form. I’d like to get as much feedback as possible to see if we can hit 95% of the use cases with it.

  • Peter

    As a software developer that has to write SQL (typically stored procedures, create/alter table statements, data migration etc…) but is not a DBA, it would be really great if you could write a companion piece that gave instructions on how to perform the actions on the diagram. For example “use compile time values to capture statistics” – ermmm… no idea.

  • Vern Rabe

    Nice chart. How about expanding the “Use RECOMPILE hint” to include deciding between WITH RECOMPILE at the procedure level or OPTION (RECOMPILE) at the statement level?

    • Not critical, just curious. Presumably a healthy percentage of stored procedures have multiple statements in them. You find that adding the RECOMPILE hint at the procedure level is a healthy method of addressing bad parameter sniffing through recompiles? The reason I usually point to the OPTION(RECOMPILE) hint is because it’s targeted. Like I said, just curious, not in any way critical.

OK, fine, but what do you think?