Microsoft Business Intelligence: Parameter Sniffing in SQL Server

Sunday 13 October 2024

Parameter Sniffing in SQL Server

Parameter Sniffing in SQL Server occurs when the query optimizer creates an execution plan for a stored procedure or query based on the parameter values used during the initial execution. This plan is then reused for subsequent executions, which can lead to suboptimal performance if the initial parameter values are not representative of later values.

When a query or stored procedure is executed for the first time, SQL Server "sniffs" the parameter values provided and generates an execution plan based on these specific values. This plan is then stored in the plan cache and reused in subsequent executions to save the overhead of re-creating a plan each time.

While parameter sniffing can improve performance by allowing SQL Server to optimize the query based on the actual data distribution for the provided parameters, it can lead to issues when the parameters vary significantly across different executions. If a plan optimized for one set of parameters is reused for a different set, it may not perform optimally.

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

See the example

We are using the AdventureWorks2019 DB for demo.

Here we are creating the sp for the demo

CREATE PROCEDURE P_getcustomershipdate

(@Shipdate_start DATETIME,
   @Shipdate_end   DATETIME)
AS
  BEGIN
      SELECT customerid,
                  salesorderid
      FROM   sales.salesorderheader
      WHERE  shipdate BETWEEN @Shipdate_start AND @Shipdate_end;
  END;

Now creating a non-cluster index on the ship column for index seek.

create nonclustered index IX_salesOrderHeader_ship

on sales.SalesOrderHeader (ShipDate)

This table has 31465 records.

Let’s run the sp with below parameters.

Turn on the execution plan and running the first query.

exec P_getcustomershipdate '2011/07/07','2014/07/07'

Executed and return the 31286 records. See the execution plan.

Here we are expecting that seek will happen but not here index scan happen because when optimizer is creating the execution plan it look the statics and based on it create the execution plan. In our case here we have requested more than 90% of data due to that optimizer go with scan instead of index seek.

Let’s run the second query

exec P_getcustomershipdate '2011/06/07','2011/07/07'