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.
[…] Grant Fritchey is asking for input on a new flowchart he has created: […]
Just curious: have you ever had a parameter sniffing scenario make you re-think the indexing strategy?
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.
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.
Yeah, good point. I will work on that. I just want to get the overall flow done correctly.
BTW, use DBCC SHOW_STATISTICS.
HA!
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.
Grant can you put a better picture of your flowchart here? Hard to read the one you uploaded. Thanks
Grant, great post !! i have a situation that in 2016 SP1 a query with sniffing executing inside sp_executesql. Even when i set to force the query store with sp_query_store_force_plan , the query is bad, without the sp_execute is 100x more faster. There isn’t solution for a cenario like that ?
Thank you
So, is the plan being successfully forced? Check the plan properties (in the first operator SELECT/INSERT/UPDATE) to see if you see the “Use plan” property there.
On the other hand, what is sp_executesql executing? If it’s a completely ad hoc query with hard coded values, you may not be able to find a way to force the plan. If it’s using parameters within sp_executesql, you should be able to.
[…] depending on the value you pass for ProductID (parameter sniffing, different story), you’ll get the following […]