Esat Erkec
Execution plan of a stored procedure

Symptoms of Parameter Sniffing in SQL Server

March 17, 2021 by

In this article, we will focus on how we can detect the parameter sniffing issues with different techniques.

Introduction

Sometimes very surprising problems can arise for database administrators while working on an ordinary working day and they cannot understand these problems. For instance, the sales department calls and starts to complain about the performance of their report. They also say that this report was completed just under 1 second 2 hours ago for the same parameters. The database administrator knows that there is not any obvious change that has been made in the database. After a little investigation, the DBA finds out the stored procedure which is using the underlined report. As a database administrator instinct, he tries to execute this stored procedure in the SQL Server Management Studio with the same parameters, and the procedure is completed in only 1 second. As a result, the situation will become even more complex. However, all these situations have an understandable reason in SQL Server.

What is the plan re-use in SQL Server?

When a query is submitted into SQL Server for execution, it goes through some phases, and in the query optimization phase, an execution plan is generated. The execution plan generation process is a very costly phase, for this reason, SQL Server keeps and reuses plans if it is possible.

What is parameter sniffing in SQL Server?

When we invoke a stored procedure for the first time the query optimizer generates an optimal execution plan for this stored procedure according to its input parameters. As we stated in the previous section, the generated query plan of this stored procedure will be stored in the plan cache for the next execution of the same procedure. So that, SQL Server avoids generating a new execution plan for the same procedure and saves on their resource usage. Up to this point, everything seems perfect but the other side of the coin is a little different. However, the cached execution plan could not be optimal for other parameters and can affect the performance of the procedure negatively. The explicitness symptoms of parameter sniffing can be:

  • The stored procedure performance execution performance shows inconsistency. It means that the procedure runs efficiently sometimes, but inefficiently at other times
  • Sudden degradation of procedure performance
  • If there is fixing in the procedure performance after updating the statistics
  • If there is fixing in the procedure performance after recompiling the procedure statistics
  • If it shows different performances for the same parameters in SSMS and application
  • If the procedure query shows better performance than the stored procedure

When we experience the above symptoms, we can take into consideration the parameter sniffing issue. Now let’s reinforce this issue with an example. At first, we will create the following stored procedure in the enlarged version of the Adventureworks database.

After creating the GetAllTaxAmount procedure, we will execute it with a parameter that value is 100 and analyze the execution plan.

Execution plan of a stored procedure

The execution time of the stored procedure is only 4 seconds. Now we will clean the procedural execution plan cache with the help of the FREEPROCCACHE command so the query optimizer generates a new query plan for the new parameter.

FREEPROCCACHE clears the procedure cache and causes to recompile the invoked procedures.

The new execution plan is different than the previous one. In this case, we can realize that the query optimizer generates different execution plans for the different parameters for the GetAllTaxAmount procedure. As the last step, we will invoke the procedure for our first parameter with value 100.

Execution plan of a stored procedure which shows bad performance

As a result, we can say that the cached execution plan is not optimal for the last parameter of the procedure and performance degradation is hidden related to the parameter sniffing issue. The following table shows the dramatic difference between the optimal plan and sub-optimal plan performance statistics.

 

Optimal Plan

Sub-Optimal Plan

Logical Read

105.468

30.483.293

Execution Time

1483

16611

The Parameter List attribute of the select operator shows for which parameter value the execution plan was generated.

Parameter list attribute of the select operator

Identifying the parameter sniffing

The sys.dm_exec_query_stats dynamic management view returns performance statistics about the cached query plans. To identify the parameter sniffing issues, we can use this view but this view returns the execution plan statistics in an aggregated manner, for this reason, we need to compare the minimum and maximum resource consumption values of the queries. At this point, if there is a huge gap between the maximum and minimum values of a query, we can suspect a parameter sniffing issue. The following query can detect a 100 times difference between the max and minimum resource consumption of a query therefore we can detect which queries have different resource usage.

Detecting the high regressed queries

On the other hand, we can use the Queries with High Variation report in Query Store to identify the sniffing issues. This report can catch the queries which run sometimes very fast and sometimes slow so the query statistics standard deviation value shows anomalies. We open the Queries with High Variation under the Query Store folder.

Queries with high variation

We change the Based on to Std Dev and click the View high variation queries in a grid format.

Queries with high variation report in the query store

In this report, we clearly see that there is a huge gap between the min duration and max duration values of the query so we can suspect the parameter sniffing issue for this stored procedure.

Why an application runs a stored procedure slower than SSMS?

After opening a connection into the SQL Server, some settings are applied on a connection level by default and we can override this setting. Another interesting thing about the cached query plans is that these options and the query optimizer wait for the same connection settings to use the cached plans. The following query truncate the stored procedure cache and then executes the same procedure with the same parameters but it will change the ANSI_PADDING option of the query before the second invocation of the query. In this case, the query optimizer will cache two different execution plans because of the different settings.

Different connection settings cause different execution plan attributes

When we compare the attributes of the execution plans, we can see the set options difference in the cached execution plans.

Comparison of the different execution plan attributes

In a brief, different connection settings cause to prevent using the cached execution plans by the invoked procedure.

How can we handle parameter sniffing issues?

There are many ways to handle this issue:

  • Adding a RECOMPILE option when creating a stored procedure
  • Using OPTIMIZE FOR UNKNOWN hint;
  • Using OPTIMIZE FOR hint for the particular parameter values
  • Using local variables in the stored procedures
  • Trace flag 4136;
  • Disabling the Parameter Sniffing option of the Database Scoped Configuration

The simplest solution to overcome this problem is disabling the parameter sniffing option at the database level so that we don’t require any code changing. However, before we decide to use this option, we must make sure that this option benefits the test database. This option is available for the SQL Server 2016 and later versions and the earlier version can use Trace Flag 4136. We can find out this parameter under the Database Scoped Configuration.

Database Scoped Configuration

After disabling this option, we will obtain more stable execution plans because the execution does not sniff any particular parameter and invoke procedure at the average row count statistics. When we re-execute the GetAllTaxAmount stored procedure, we don’t see the Parameter Compiled Value under the Parameter List property.

Parameter list attribute of the execution plan

On the other hand, enabling this option may cause performance problems in the range scans or tempdb spills issues.

Conclusion

In this article, we have learned the major symptoms of the parameter sniffing issue and how we can identify this problem easily.

Esat Erkec
Latest posts by Esat Erkec (see all)
168 Views