Parameter Sniffing

Question

So I have found some really good web sites as well as YouTube which finally explains what "Parameter Sniffing" is all about. I think I'm finally beginning to understand what is happening with "Parameter Sniffing"

So I believe in the web sites and discussions I have seen in order to alleviate "Parameter Sniffing", there are two approaches

1. Use local variables which "tells SQL Server to use static densities instead of static histograms...hence avoiding the parameter sniffing problem"
2. Using OPTION(OPTIMIZE FOR UNKNOWN) to have the query optimized to use statistics

Just wondering what is the preferred and "Best Practice" approach.

Thanks for your review and am hopeful for a reply.

Answer 1

There is no such thing as a "best approach". :-) Also, parameter sniffing isn't a "problem", to be solved. Sometimes it doesn't work out in your favor, and those cases is up to you to handle.

One thing is to identify those cases, and Query Store might be useful for this.

Putting the parameter in a variable and then use that variable was something we did before we got OPTIMIZER FOR UNKNOWN - they result in the very same thing. Another option is OPTIMIZE FOR <value>. Yet another is RECOMPILE (at the statement level). Again, which one is best for a particular situation is for you to determine. :-)

Answer 2

Hi anonymous userP-1695,

"Parameter Sniffing" is because the execution plan of the statement is very sensitive to the value of the variable, which leads to performance problems when reusing the execution plan.

In fact, the frequency of Parameter Sniffing is not high. It only occurs when the data distribution in some tables is very uneven, or the parameter values brought in by the user are very uneven. So if there is a parameter sniffing problem, what is the solution? What are the advantages and disadvantages of these solutions?

First, let’s take a look at the following comparison list:

For your case, let's compare the difference between using local variables and optimize for in detail:

1.Local variables: If you assign a variable value to a local variable, SQL Server has no way to know the value of this local variable when compiling, so it will "guess" a return value based on the general distribution of the data in the table. Regardless of the value of the variable that the user substitutes when calling the stored procedure, the execution plan made is the same. And such an execution plan is generally "moderate" and will not be the optimal execution plan, but for most variable values, it will not be a poor execution plan.

The advantage of this method is to maintain the advantages of the stored procedure,the disadvantage is to modify the stored procedure, and the execution plan is not optimal.

2.Optimize for: The advantage of this method is that it not only allows SQL Server to make a biased execution plan, but also ensures the freedom of SQL Server to choose the execution plan, so the execution plan obtained is generally better. The disadvantage is that if the data distribution in the table changes, for example, the user deletes all the records one day,the execution plan selected by SQL Server may not continue to be correct, so it also has its limitations.

Therefore, in short, there is no best solution, only the most suitable solution can be selected according to the actual situation.

More information: query-optimization-techniques-in-sql-server-parameter-sniffing

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi anonymous userP-1695,

Is the reply helpful?

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Hi anonymous userP-1695,

Just recommend one helpful doc. : query-plan-mysteries

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.