DataZip

SQL DBA Tools: Sudden Performance Drops via Parameter Sniffing

Simon Dang
Aug 06, 2026By Simon Dang

SQL Server Monitoring Tool: www.SQLBrainBox.com

The Issue

Parameter sniffing occurs when SQL Server creates an execution plan based on the specific parameters used during the first compilation of a stored procedure. While this initial plan optimizes performance for that specific parameter value, it can cause severe performance degradation when other parameters require a different data distribution retrieval strategy.

The Diagnosis

You can identify parameter sniffing by observing a stored procedure that usually runs instantly but suddenly takes minutes to execute without any changes to the underlying database code.

Solution1: Recompile

  • Force re-evaluation: Add OPTION (RECOMPILE) inside the specific query or use sp_recompile '[sproc name]'
  • Calculate fresh: SQL Server throws away the cached plan and builds a brand new plan for every single execution.
  • Best use case: Choose this for complex queries that run infrequently or have highly volatile input parameters

Solution 2: Apply the OPTIMIZE FOR

  • Target typical values: Append OPTION (OPTIMIZE FOR (@Param = 'TypicalValue')) to the query.
  • Standardize plan: SQL Server will always build the execution plan based on this hardcoded, highly predictable value.
  • Alternative choice: Use OPTIMIZE FOR UNKNOWN to force the optimizer to use statistical averages instead of a specific value.

Solution 3: Map to Local Variables

  • Mask parameters: Declare local variables inside the stored procedure body.
  • Assign inputs: Set the local variables equal to the incoming parameters.
  • Blind the optimizer: SQL Server cannot sniff the values at compile time and relies entirely on average density statistics.

Solution 4: Update Database Statistics

  • Refresh data: Execute UPDATE STATISTICS SchemaName.TableName WITH FULLSCAN.
  • Clear bad data: Outdated statistics force the optimizer to make poor assumptions during plan compilation.
  • Automate maintenance: Set up regular SQL Server Agent jobs to keep distribution statistics current. [1, 2, 3, 4]