The official PASS Blog is where you’ll find the latest blog posts from PASS community members and the PASS Board. Contributors share their thoughts and discuss a wide variety of topics spanning PASS and the data community.

Parameter Sniffing and Performance

If you were using SQL Server in 2008, you would have heard about parameter sniffing at that time. If you are using SQL Server now, I am very confident that you may have encountered this topic as it is quite common in the SQL Server environment. Parameter Sniffing has been an evergreen topic for discussion for SQL Server Performance Tuning experts.

Before we continue talking more about parameter sniffing, let us understand what parameter sniffing is and how it affects SQL Server performance.

Parameter Sniffing

Parameter Sniffing is when SQL Server creates an optimal plan for a stored procedure by using the parameters that are passed the first time when the stored procedure is executed.

SQL Server does this exercise to build the execution plan once and uses it multiple times without recreating the execution plan again and again. The compilation process of the stored procedure can take some precious time as the execution of the procedure is very large. This entire exercise is made to save those repeated moments recompiling the stored procedures. While this works out well in most cases, there is often a negative impact of parameter sniffing for the stored procedure.

Parameter Sniffing – Good, Bad, or Ugly?

One of the most popular questions that SQL Server experts throughout the years have been receiving is if parameter sniffing is good for your system or not. Throughout the year there have been quite a lot of different solutions out to work with the parameter sniffing issue in the SQL Server.

The ancient question still remains current – Do you want parameter sniffing for your code or not? While we all want to answer  “It depends”, the reality is that in the real world, we are often lost when we try to discover the answer.

The experts who consider parameter sniffing as a good feature argue for reusing the execution plan and edge-case scenario when there is poor performance due to this feature. The experts who consider parameter sniffing as a bad feature argue that reusing the execution plan with a bad parameter on the first run can degrade the performance of the query for all subsequent runs until it gets recompiled. You can read more about this issue here. The fighting and split opinions among the people who consider parameter sniffing as good or bad has made the entire discussion about the parameter sniffing very ugly.

Many Solutions Without Conclusion

Whenever parameter sniffing is mentioned, the universal goal is to get the most from the stored procedure where it does not take the initial parameter and builds the execution plan. The goal is to build an execution plan that works for most of the queries and not for some specific parameters. Throughout the years, many different solution have surfaced about parameter sniffing. However, the two options that received the most attention were Declaring the Local Variable in SP and Query Hint Optimize for Uknown.

The most recent solution that has created additional confusion on this topic is related to Database Scoped Configuration. If you are using SQL Server 2017 or SQL Server 2019, and go to the options page under database properties, you will find the new configuration (settings) related to Parameter Sniffing. Here is how it will look in SSMS:

Here is the challenge; when you look at the configuration, the default setting is ON. The question that most people have is “what should be the ideal settings that would work for most situations”. Should they continue to use the default settings or change them to gain more performance? The answer is very simple, but not obvious.

If you want to disable the parameter sniffing at the database level and want your queries to use the average value for your parameter from statistics, you can disable this setting. Once disabled, the SQL Server query engine will not use initially passed parameters but will start behaving exactly like Declaring the Local Variable in SP or Query Hint Optimize for Uknown. In simple words, instead of declaring a local variable for every single SP or passing hint forever single query, you can now easily accomplish it at the database level by just changing this one setting.

While we actually disable the parameter sniffing for this configuration, the original question still remains unanswered. What is the correct value for our queries? Should we keep the parameter sniffing on or off.

Parameter Sniffing OFF or ON?

It is extremely easy to overcome the problem of parameter sniffing. However, it is extremely difficult to overcome the performance problem that is introduced due to the parameter sniffing issue. There is no sure solution to overcome the problem if your stored procedure has sniffed the parameter and used that to build the execution plan. It is quite possible that even though we use the average value due to statistics, the performance problem has not yet resolved.

The best possible solution, which I often implement for my clients who are struggling with this issue, is as following:

  1. Keep the database scoped configuration settings for the parameter sniffing ON.
    This helps most queries cache the execution plan and reuse it, which is actually an advantage in most cases.
  2. Identify the stored procedures that are not performing well, and for those stored procedures implement the ancient solution of query hint OPTION (RECOMPILE). There is only one sure resolution of the performance issue if the SQL Server execution plan is building an execution plan that is not efficient for the parameter passed: the recompilation of the stored procedure at every single run with the query hint OPTION (RECOMPILE).

With the above two solutions, you can achieve the best of both worlds. The queries that are behaving well can use the power of parameter sniffing and the queries that are behaving badly can use the query hint OPTION (RECOMPILE).

Summary

As mentioned earlier - it is extremely easy to overcome the problem of parameter sniffing. However, it is extremely difficult to overcome the performance problem that is introduced due to the parameter sniffing issue. It is important that you find the right balance between the two options discussed. If you start recompiling every single query, you will end up using lots of important resources to run every query and sub-utilize the power of plan and data caching.

You can always reach out to me if you have any question about this blog post at pinal@sqlauthority.com

 

Pinal Dave
About the author

Pinal Dave is a SQL Server Performance Tuning Expert and an independent consultant. He has authored 12 SQL Server database books, 31 Pluralsight courses and has written over 5000 articles on the database technology on his blog at https://blog.sqlauthority.com. Along with 16+ years of hands on experience, he holds a Masters of Science degree and a number of database certifications.

Please login or register to post comments.

Theme picker

Back to Top
cage-aids
cage-aids
cage-aids
cage-aids