SQL Server 2017 – Understanding Parameter Sniffing

If you can anticipate typical values, run your stored procedure with such values whenever you (re-)create it. An execution plan will be created based on these values, and stored for future use. Most of my stored procedure .sql files end with an EXEC command with reasonable values, for exactly this purpose (that, and to identify basic errors in the code).

Of course this won't help if your stored procedure is very long running, enough that you can't reasonably execute it during production hours. Note that it's not enough to start the job, it has to complete successfully for a plan to be stored.

But why the recompile didn't help?

Using OPTION (RECOMPILE) is not magic. It does often produce a better-performing execution plan (at the cost of a full compilation each time) because the query is compiled each time for the specific values of any parameters at the time, and also enables parameter embedding, which may produce further benefits.

Determining the specific reasons that OPTION (RECOMPILE) did not help in your particular case would require details you are unable to provide.

Local variables. The procedure was faster than ever.

Using local variables prevents sniffing of parameter values, so queries are compiled based on average distribution statistics. This was the workaround for some types of parameter sensitivity problem before OPTION (OPTIMIZE FOR UNKNOWN) and trace flag 4136 became available.

For more details please see:

Parameter Sniffing, Embedding and the RECOMPILE Options