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.
|
1 2 3 4 5 6 7 8 |
CREATE PROCEDURE dbo.GetAllTaxAmount @ParamProductId AS INT AS SELECT SUM([UnitPrice]*SalesHeader.SubTotal) FROM Sales.SalesOrderHeaderEnlarged SalesHeader INNER JOIN sales.SalesOrderDetailEnlarged SalesDetail ON SalesDetail.SalesOrderID = SalesHeader.SalesOrderID WHERE SalesDetail.ProductID >@ParamProductId |
After creating the GetAllTaxAmount procedure, we will execute it with a parameter that value is 100 and analyze the execution plan.
|
1 |
EXECUTE GetAllTaxAmount @ParamProductId = 100 |
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.
|
1 2 3 |
DBCC FREEPROCCACHE GO EXECUTE GetAllTaxAmount @ParamProductId = 10000 |
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.
|
1 |
EXECUTE GetAllTaxAmount @ParamProductId = 100 |
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.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
WITH Execution_Detail AS ( SELECT SUBSTRING(ST.text, (QS.statement_start_offset / 2) + 1, ((CASE statement_end_offset WHEN-1 THEN DATALENGTH(ST.text) ELSE QS.statement_end_offset END - QS.statement_start_offset) / 2) + 1) AS [Query Statment], ST.text AS 'Procedure Batch', min_worker_time, max_worker_time, ISNULL((max_worker_time - min_worker_time) / NULLIF(min_worker_time, 0), 0) AS LogicalCpuRatio, min_logical_reads,max_logical_reads, ISNULL((max_logical_reads - min_logical_reads) / NULLIF(min_logical_reads, 0), 0) AS LogicalReadsDevRatio, max_elapsed_time, min_elapsed_time, ISNULL((max_elapsed_time - min_elapsed_time) / NULLIF(min_elapsed_time, 0), 0) AS LogicalElapsedTimDevRatio FROM sys.dm_exec_query_stats AS QS CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) AS ST) SELECT * FROM Execution_Detail WHERE LogicalCpuRatio >=100 AND LogicalReadsDevRatio>=100 AND LogicalElapsedTimDevRatio>=100 |
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.
We change the Based on to Std Dev and click the View high variation queries in a grid format.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
DBCC FREEPROCCACHE SET ANSI_PADDING ON; EXECUTE GetAllTaxAmount @ParamProductId = 10000 GO SET ANSI_PADDING OFF; EXECUTE GetAllTaxAmount @ParamProductId = 100 SELECT cp.plan_handle, cp.cached_time ,cp.last_execution_time, DB_NAME(st.dbid) AS [DatabaseName], st.text FROM sys.dm_exec_procedure_stats AS cp CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st WHERE cp.object_id =object_id('GetAllTaxAmount') |
When we compare the attributes of the execution plans, we can see the set options difference in the cached execution plans.
|
1 2 3 |
select * from sys.dm_exec_plan_attributes (0x0500170065A7A84E40CA92E58301000001000000000000000000000000000000000000000000000000000000) select * from sys.dm_exec_plan_attributes (0x0500170065A7A84ED0C292E58301000001000000000000000000000000000000000000000000000000000000) |
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.
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.
|
1 |
EXECUTE GetAllTaxAmount @ParamProductId = 100 |
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.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023