Why RECOMPILE doesn't fix parameter sniffing, but local variables do?

Question

A developer was asking me about why his procedure wasn't running, but the query was ok.

When I run the query, it was fast.But running the procedure, it was executing forever.

What I did was to add at the end of the query:

OPTION(RECOMPILE)

It doesn't help.

The procedure was like this (the slow one):

CREATE procedure [dbo].[pr_ProcedureName]  
    @CodOnda int  
AS
BEGIN
  set nocount on  
  ...

Then I did this:

CREATE procedure [dbo].[pr_ProcedureName]  
    @CodOnda int  
AS
BEGIN
   set nocount on  

   declare @localCodOnda int
   set @localCodOnda = @CodOnda
   ...

And BAM! Local variables. The procedure was faster than ever. I know this is something about parameter sniffing. But why the recompile didn't help?

I really would like to have the old query plan, but I don't have.

And the problem wasn't about locking or blocking. I was tracking with sp_WhoIsActive and some queries that I found in some answers here (thanks to Aaron Bertrand and Brent Ozar for them). I am using SQL Server 2008 R2.

Answer 1

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