Parameter Sniffing Performance Impact

in SQL Server

This post can help you in understanding the “Parameter Sniffing Performance Impact in SQL Server”. Parameter sniffing is one of the major factors that impact the performance in SQL Server. Every SQL DBA or SQL Developer should aware of the side effects of parameter sniffing. Before going to see how parameter sniffing affects the performance first we’ll quickly see what it is.

Q. What is Parameter Sniffing in SQL Server?

Ans:

When a stored procedure is compiled or recompiled, SQL Server optimizer designs the execution plan by using (sniffing) the input parameters. This is known as parameter sniffing. The designed execution plan is perfectly works for the input parameters which are sent in the first go. This execution plan is stored in cache and the same plan is used when the next time the procedure is called.

Q. Why parameter sniffing is bad?

Ans:

Did I say it’s bad? Parameter Sniffing is absolutely expected. But the problem comes in when there is a huge variation in data distribution for the key values. When the first time the execution plan is generated that completely optimized for the parameter values passed. When the next time the same procedure is called with a different parameter values, the same execution plan is used. If there is a huge difference in data volumes between first and second executions there we’ll see the problem. Let me explain with an example:

• Procedure executed with the parameter @p = 1
• Then the query optimizer creates an execution plan based the parameter value @p = 1
• Let’s say when @p = 1 there are 15 rows then it considers the estimated rows are 15 and then generates the execution plan with clustered index seek.
• This execution plan stored in cache and the same plan used when the next time the same procedure called irrespective of the parameter value passed.
• Let’s say this time @p = 8 and when @p = 8 there are 1.2 million records.
• But the same execution plan (the plan that created based on @p = 1) is used for @ p = 8.
• Does 15 seeks is equal to 1.2 million seeks? Absolutely no. And this is the place where parameter sniffing becomes BAD and causes the performance slowdown of your query.

Q. What are the symptoms that indicate the Parameter Sniffing impact on performance?

Ans:

Below are the major symptoms of bad parameter sniffing. Remember there might be other reasons that cause the performance slow down but you should consider bad parameter sniffing in RCA (Root Cause Analysis) in case of below symptoms.

Abnormal behavior Ex: A procedure is working fine for some parameters but taking time for some parameters

Sudden slowdown Ex: A procedure has been executing well from long back and the execution time got increased suddenly.

Suspected Execution Plan: If you find no proper indexes, operators are using even when all required conditions are met. Ex: A query is running against a table with million rows and returning 10 rows as a result set. Indexed columns are using in where clause and select list but still Index Scan is happening where Index Seek can be the best possible operator.

Q. Can you give an example on how “parameter sniffing” impacts the performance in SQL Server?

Ans:

We’ll take a simple example and see how “Parameter Sniffing” becomes bad. We are using “[AdventureWorks2014]” database in development environment.

• Create a table “Sales_Test” and insert data. Here is the script.
• Create a parameterized stored procedure with a simple select query.
• Execute the stored procedure with different inputs and see the execution plan, I/O, CPU time

Creating a Simple Stored Procedure:

/*** Get the Sales Details for the given Product ID***/

CREATE PROCEDURE USP_GetSales (@ProductID INT)
AS
BEGIN
SELECT
     OrderID,
     OrderQty,
     ProductID
FROM dbo.Sales_Test
WHERE ProductID = @ProductID;
END

Execute the stored procedure:

We have cleared the cache for more accurate results. And switch on STATISTICS IO and TIME.

Execution 1

EXECUTE USP_GetSales 897;

Execution Plan:

Input: ProductID = 897

Estimated Row Count: 34.9583

Actual Number of Rows: 14

I/O and CPU: logical reads 87, physical reads 1, CPU Time: 0 ms

Query Execution Time: 26 ms

Observations:

• Execution plan is generated based on the ProductID = 897.
• Index seek is happening with a key lookup for the columns which are not covered by index.
• This execution plan is stored in cache, when next time this procedure is called optimizer uses the plan from the cache instead of generating a new execution plan.

Execution 2

EXECUTE USP_GetSales 870;

Execution Plan:

Input: ProductID = 870

Estimated Row Count: 34.9583

Actual Number of Rows: 525056

I/O and CPU: logical reads 1662110, physical reads 99, CPU Time: 1140 ms

Query Execution Time: 3410 ms

Observations:

• We can clearly see that the execution plan is used from the cache.
• A huge difference between estimated and actual row counts.
• Since estimated row count is 34.9583 optimizer selected index seek, but imagine if index seek has to be happens for more than 5 lakh times. IT definitely impacts the performance which increases the query execution time.
• The plan generated based on productID = 897 is stored in cache and the same plan is used for the productID = 870.
• This is the place where Parameter Sniffing becomes bad and impacts the query performance.
• Now we’ll clear the cache and execute the procedure with ProductID = 870 then we can see the difference between EXECUTION -2 and EXECUTION – 3.

Execution 3

Clear the procedure cache: Execute the below or simply alter the procedure

DBCC DROPCLEANBUFFERS
GO
DBCC FREEPROCCACHE
GO

Execute the procedure

EXECUTE USP_GetSales 870;

Execution Plan:

Input: ProductID = 870

Estimated Row Count: 517910

Actual Number of Rows: 525056

I/O and CPU: logical reads 8356, physical reads 1, CPU Time: 326 ms

Query Execution Time: 2514 ms

Observations:

• We have executed the procedure with the same productID as in Execution 2
• Since we cleared the buffer cache it compiled the procedure and generated a new query plan based on the parameter passed (ProductID = 870)
• Compare the resource utilization and execution times between EXECUTION 2 and EXECUTION 3
• Estimated row count is more accurate than in Execution 2.

Q. How to handle the bad Parameter Sniffing Problems?

Ans:

Below listed the possible ways to handle the bad parameter sniffing issues:

Use local Variables instead of directly using the parameters:

Declare local variables and map procedure parameters to the local variables. Use these local variables in queries so that optimizer generates a generic execution plan instead of a parameter based execution plan. Please be noted that this generic plan may not be the best plan.

Use “OPTION OPTIMIZE FOR A Value”:

Instead of forcing SQL Server to recompile for every data value we can assist query optimizer to recompile when input parameter matches a specific given value.

Ex:

WHERE ProductID = @ProductID

OPTION (OPTIMIZE FOR (@ProductID=870));

Use “OPTION OPTIMIZE FOR UNKNWON”:

When we use OPTIMIZE FOR UNKNOWN it also acts as the same like using local variables and this option is added from SQL Server 2008 to reduce the code modification complexity. When we specify this option query optimizer ignores the parameter value and generates a generic execution plan.

Ex:

WHERE ProductID = @ProductID

OPTION (OPTIMIZE FOR UNKNOWN)

Stored Procedure WITH RECOMPILE:

Every time when a new call initiated the stored procedure get compiled and generates a new execution plan which causes an extra CPU loads. Sometimes this might improve the performance for a temporary purpose but it might impact the performance in long running.

Ex:

CREATE PROCEDURE USP_GetSales (@ProductID INT)

WITH RECOMPILE

AS

BEGIN

xxxxx

END

SQL Server Query Hint RECOMPILE:

Sometimes we may not find any other way except the option ‘RECOMPILE’ in that case we can use the query level option “RECOMPILE” instead of compiling the entire stored procedure. When we specify this then optimizer recompiles the concern query only instead of compiling the entire stored procedure. This is useful when dealing with the complex stored procedures.

EX:

WHERE ProductID = @ProductID

OPTION (RECOMPILE);

Create separate procedures:

Recompiling stored procedure every time means we are losing the basic advantages of a stored procedure. To avoid that we can use the branching logic:

• Identify the data distribution range: Identify the data ranges for which the returning result sets are identical.
• Create separate stored procedures: We’ll create two stored procedures, one is to handle the tiny datasets and the other one is to handle the huge dataset.
• Thereof we can avoid the recompilation of a stored procedure every time it is called.

Disable parameter sniffing at the instance level:

This option is added to make sure that we are covering all possible ways to handle the bad parameter sniffing otherwise we never go or suggest this option. We can do this by enabling the trace flag 4136.

Disable Parameter Sniffing at Database Level:

In SQL Server 2016 we can control the parameter sniffing behavior at the database level using the database scoped configurations.

Ex: ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = { ON | OFF | PRIMARY}

Disable Parameter Sniffing for a Specific SQL Query:

This is also not a good option as it might work temporary basis but it will create problems in near future when data increases. This can be done using the query hint OPTION (QUERYTRACEON 4136).