The DISABLE_PARAMETER_SNIFFING hint makes one query ignore the parameter value it was first compiled for. This post compares it with the default, OPTIMIZE FOR UNKNOWN and RECOMPILE on a table where one customer owns 40 percent of the rows. The reads show which variant fits which workload.
How to Recompile Stored Procedure? – Interview Question of the Week #260
Question: How to Recompile Stored Procedure?
Answer: The answer to this question is here:
EXEC sp_recompile ‘NameofSP’
Let us read more about it.
Why a Filtered Index Is Ignored by Parameterized Queries
Explain why a filtered index is ignored with parameters, inspect plan warnings, and compare literal filters, RECOMPILE, and coverage fixes.
Parameter Sniffing Fixes Compared: Which One to Use
Parameter sniffing fixes compared on one table show a clear order, from a cheap guess to a better index. This post runs the plain procedure, a local variable, OPTIMIZE FOR UNKNOWN, a scoped setting, OPTION (RECOMPILE) and a covering index on the same skewed table, and prints the page reads of each in both call orders.
OPTION (RECOMPILE) Hint: When a Fresh Plan Pays Off
The OPTION (RECOMPILE) hint tells SQL Server to build a new plan every time a statement runs. This post measures the page reads it saves on skewed data, the compile cost it adds, the filtered index it unlocks, and how to apply it to one statement of a procedure.
Database Scoped Configuration: Turn Off Parameter Sniffing
A database scoped configuration lets you turn off parameter sniffing for one database with a single statement. This post measures a procedure with the setting on and off, shows that the change clears the cached plans of the database, and explains what the switch does not fix.
SQL SERVER – Parameter Sniffing and OPTIMIZE FOR UNKNOWN
I consider myself fortunate that I receive a follow-up question from my blog posts. Let’s discuss Parameter Sniffing and OPTIMIZE FOR UNKNOWN.