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
ALTERon the object) - Knowledge of the stored procedure or parameterized query causing the issue
Step-by-Step
- Identify the slow query. Use
sys.dm_exec_query_statsandsys.dm_exec_sql_textto 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; - Confirm parameter sniffing. Check the execution plan for the slow query. Look for a
SELECToperator with a warning icon or aParameterListshowing the compiled value. If the plan is optimal for one value but terrible for another, that’s sniffing. - Test with
OPTION (RECOMPILE). AddOPTION (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. - 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 RECOMPILEto the procedure definition, or addOPTION (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.
- For a single query: Keep
- 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)';
- 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). - 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.
- Monitor over time. Keep an eye on the query’s performance using
sys.dm_exec_query_statsto 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 UNKNOWNwhen 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 STATISTICSor 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_helptextor the execution plan’sParameterList. - 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
RECOMPILEtoOPTIMIZE 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 usingsys.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
- Hogwarts Legacy Microsoft Visual C Runtime Error Fix
- Set Up UPI Payments Through WhatsApp
- Pair Oppo Enco Earbuds with Android, iPhone & Windows
- Fix Administrator Account Disabled Error in Windows 11
- How to Play Kodi on Smart TV from PC
- New iPhone Fall Case: How to Pick a Fashionable One