Stored procedure performance - this is WILD?

Question

This question is not so much about finding a solution as about getting an explanation for the bizarrest behavior I have ever seen from SQL Server.

I had a stored procedure with the following signature:

alter procedure MySP @param1 uniqueidentifier, 
                     @param2 uniqueidentifier, 
                     @param3 uniqueidentifier

Given a certain set of parameters, this proc was taking a very long time to run from C# (using SqlCommand.ExecuteReader()) - about 2 minutes. Using the same parameters in a direct query session, the SP ran in under 2 seconds.

It took a long time, and I won't even try to explain how we stumbled upon this solution, but this is what we did:

At the beginning of the SP, we declared 3 local variables and assigned them to the values of the parameters, like so:

declare @param1_copy uniqueidentifier, 
        @param2_copy uniqueidentifier, 
        @param3_copy uniqueidentifier

select @param1_copy = @param1,
       @param2_copy = @param2,
       @param3_copy = @param3

And then, in the rest of the SP we substituted all references to the input parameters with the local copies.

Voila. SP executed in under 2 seconds. And the team here is gobsmacked.

Now, ladies and gentlemen, can anyone explain this behavior?

Answer 1

This sounds like parameter sniffing.

From Microsoft's definition:

"Parameter sniffing" refers to a process whereby SQL Server's execution environment "sniffs" the current parameter values during compilation or recompilation, and passes it along to the query optimizer so that they can be used to generate potentially faster query execution plans. The word "current" refers to the parameter values present in the statement call that caused a compilation or a recompilation.

Looks like you've already figured out one fix, another would be to use EXEC... WITH RECOMPILE:

When executing a stored procedure with atypical parameter values, "EXEC ... WITH RECOMPILE" can be used to ensure that the fresh query plan does not replace an existing cached plan that was compiled using typical parameter values.