sodcalculator

10 Tech How-To

Fix Slow SQL Queries from Parameter Sniffing

Guide 10-54093 · Updated · Step by step

Parameter sniffing can turn a normally fast SQL Server query into a slow one when the cached execution plan was built for an atypical parameter value. This guide shows you how to diagnose, fix, and prevent the problem using query hints, plan guides, and other reliable techniques.

What You’ll Need

  • SQL Server Management Studio (SSMS) or another query tool with access to the database
  • Permissions to view and clear the plan cache (typically VIEW SERVER STATE)
  • Permission to modify the query or create plan guides (often ALTER on the object)
  • Knowledge of the stored procedure or parameterized query causing the issue

Step-by-Step

  1. Identify the slow query. Use sys.dm_exec_query_stats and sys.dm_exec_sql_text to find queries with high average duration or high CPU time. For example, run:
    SELECT TOP 10
      qs.total_elapsed_time / qs.execution_count AS avg_elapsed_ms,
      qs.execution_count,
      SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS query_text
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
    ORDER BY avg_elapsed_ms DESC;
  2. Confirm parameter sniffing. Check the execution plan for the slow query. Look for a SELECT operator with a warning icon or a ParameterList showing the compiled value. If the plan is optimal for one value but terrible for another, that’s sniffing.
  3. Test with OPTION (RECOMPILE). Add OPTION (RECOMPILE) to the end of the query or inside the stored procedure. This forces a fresh plan each execution, using the current parameter values. If performance improves, you’ve confirmed the issue.
  4. Apply the fix permanently. Choose one of these based on your situation:
    • For a single query: Keep OPTION (RECOMPILE) if the query runs infrequently or the data distribution varies widely.
    • For a stored procedure: Add WITH RECOMPILE to the procedure definition, or add OPTION (RECOMPILE) at the end of the problematic statement.
    • For a parameterized query from an ORM: Use OPTION (OPTIMIZE FOR UNKNOWN) to use the average distribution instead of a specific value.
    • For a specific known bad value: Use OPTION (OPTIMIZE FOR (@param = 'typical_value')) to force a plan for a representative value.
  5. If you cannot modify the query code, use a plan guide. Create a plan guide that applies the hint without changing the application. For example:
    EXEC sp_create_plan_guide
      @name = N'FixSniffing_Proc1',
      @stmt = N'SELECT ... FROM ... WHERE ...',
      @type = N'SQL',
      @module_or_batch = NULL,
      @params = NULL,
      @hints = N'OPTION (RECOMPILE)';
  6. Clear the cached plan (if needed). After applying a fix, remove the old bad plan so it doesn’t continue to be reused. Use DBCC FREEPROCCACHE (with caution in production) or specifically for the plan handle: DBCC FREEPROCCACHE(plan_handle).
  7. Test thoroughly. Run the query with several different parameter values, including edge cases (e.g., very common and very rare values). Verify that performance is acceptable for all.
  8. Monitor over time. Keep an eye on the query’s performance using sys.dm_exec_query_stats to ensure the fix holds as data changes.

Common Mistakes

  • Overusing RECOMPILE: This increases CPU usage because the plan is rebuilt every execution. Use it only for queries that run infrequently or have highly variable parameters.
  • Using OPTIMIZE FOR UNKNOWN when a specific value would be better: If you know the most common parameter value, forcing that is often more effective than the average.
  • Forgetting to clear the old plan: After adding a hint, the cached plan may still be the bad one. Always test after clearing the relevant plan.
  • Applying hints to every query: This can degrade overall performance. Only fix the queries that actually suffer from sniffing.
  • Ignoring statistics: Parameter sniffing is often worse with outdated statistics. Update statistics regularly to reduce the chance of bad plans.

Tips

  • Use OPTION (RECOMPILE) for queries that run less than once per second; the CPU cost is usually negligible.
  • For stored procedures with multiple statements, place OPTION (RECOMPILE) only on the problematic statement, not the whole procedure.
  • Consider using OPTION (OPTIMIZE FOR (@param UNKNOWN)) for parameters that are rarely filtered on (e.g., a ‘status’ column with mostly one value).
  • Use plan guides as a non-invasive way to fix issues in third-party applications.
  • Keep statistics up to date with UPDATE STATISTICS or the auto-update feature, but be aware that auto-update can still miss skewed distributions.

Troubleshooting

If the fix doesn’t improve performance:

  • Verify that the hint is actually being applied. For stored procedures, check with sp_helptext or the execution plan’s ParameterList.
  • Check if the slow query is not actually the one you think. Use the actual execution plan to confirm.
  • Look for other issues like missing indexes, blocking, or excessive I/O that may be the real cause.

If performance gets worse:

  • Revert the hint and try a different approach. For example, switch from RECOMPILE to OPTIMIZE FOR UNKNOWN.
  • Check if the query is now generating a plan that is bad for the most common parameter values.

If you cannot clear the plan cache due to production concerns:

  • Use DBCC FREEPROCCACHE(plan_handle) to remove only the specific plan. Find the handle using sys.dm_exec_query_stats.
  • Apply the fix during a maintenance window to avoid any temporary performance hit.

Frequently Asked Questions

What exactly is parameter sniffing?

SQL Server creates an execution plan based on the parameter values passed on the first call. If those values are not representative, the plan may be inefficient for other values.

Why does OPTION (RECOMPILE) fix it?

It forces SQL Server to compile a new plan every time, using the current parameter values, so the plan is always tailored to the specific execution.

Is OPTION (RECOMPILE) always the best solution?

No. It adds CPU overhead. For frequently executed queries, consider OPTIMIZE FOR UNKNOWN or a plan guide instead.

Can I fix parameter sniffing without changing the query?

Yes, you can use a plan guide to apply hints without modifying the application code.

Does updating statistics help?

Yes, fresh statistics help SQL Server make better cardinality estimates, which reduces the chance of a bad plan being cached.

What is the difference between OPTIMIZE FOR UNKNOWN and OPTIMIZE FOR (@param = value)?

UNKNOWN uses the average distribution across all values, while the latter forces a specific representative value. Choose based on your data distribution.

Related

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →