Understanding Parameter Sniffing in SQL with Example

By /

Parameter sniffing is a common performance issue in SQL Server and other relational databases. It happens when the query optimizer caches an execution plan based on the first set of parameters. This plan is then reused for subsequent executions, which may not always be efficient. This article explain into parameter sniffing, its impact, and how to mitigate it.

What is Parameter Sniffing

When a stored procedure or parameterized query (queries that specify parameter instead of vales) is executed, the SQL Server query optimizer generates an execution plan based on the provided parameters.
If the execution plan is cached and reused for different parameter values, performance issues may arise when those values vary significantly.

Example of Parameter Sniffing

Consider a table Orders with an index on CustomerID

CREATE PROCEDURE GetOrdersByCustomer
    @CustomerID INT
AS
BEGIN
    SELECT * FROM Orders WHERE CustomerID = @CustomerID;
END;

Execution Scenarios:

  • First Execution:
EXEC GetOrdersByCustomer @CustomerID = 1;

If CustomerID = 1 returns only a few rows, the optimizer may choose an Index Seek

  • Second Execution
      EXEC GetOrdersByCustomer @CustomerID = 50000;

      If CustomerID = 50000 returns thousands of rows, an Index Scan or Table Scan might be more efficient. However, SQL Server reuses the previously cached plan, leading to performance impact.

      Detecting Parameter Sniffing Issues

      Use EXEC sp_recompile to force a fresh execution plan:

      EXEC sp_recompile 'GetOrdersByCustomer';

      Or check the execution plan using:

      SET STATISTICS IO ON;
EXEC GetOrdersByCustomer @CustomerID = 50000;
SET STATISTICS IO OFF;

      Mitigate Parameter Sniffing

      1. Use OPTION (RECOMPILE)

      Forces the optimizer to generate a new plan for each execution

      SELECT * FROM Orders WHERE CustomerID = @CustomerID OPTION (RECOMPILE);

      2. Use Local Variables

      This prevents SQL Server from using sniffed parameter values:

      DECLARE @LocalCustomerID INT = @CustomerID;
SELECT * FROM Orders WHERE CustomerID = @LocalCustomerID;

      3. Use Plan Guides or Query Store

      • Plan guides enforce specific plans.
      • Query Store helps analyze performance and force a stable plan.

      4. Manually Flushing the Cache

      If performance issues arise, you can clear the execution plan cache.

      DBCC FREEPROCCACHE;

      It reset all plans so be careful it will impact other queries performance

      Summary

      Parameter sniffing can significantly impact SQL Server performance by caching inefficient execution plans. Understanding how to detect and mitigate this issue using recompilation, local variables, or query store ensures more consistent query performance.

      Scroll to Top