SQL Server Stored Proc takes (much) longer to run than the same query executed from text in Management Studio

Question

So here is a bit of a strange one... I have a stored proc that takes 40 seconds to run. I copy the contents of the stored proc to a new query window, change the 2 input parameters (which are both dates) so they are declared and set, and run the query. It is basically instant, sub 1 second execution. The only difference is the stored proc takes the 2 dates as parameters.

Anyone got any idea what can make that happen?

(I am running SQL Server 2005)

Answer 1

Recompilation sniffs the parameters, so it can make no difference.

This article explains my statement...

...parameter values are sniffed during compilation or recompilation...

You need to mask the parameters:

ALTER PROCEDURE [uspFoo]
    @Date1 datetime,
    @Date2 datetime
AS
BEGIN
    DECLARE @IDate1 datetime, @IDate2 datetime;
    SELECT @IDate1 = @Date1, @IDate2 = @Date2;
    -- Stuff here that depends on @IDate1 and @IDate2
END

Answer 2

Run the profiler and capture the query plans for the execution. Check to see what the differences are - you may be able to tune the query or force a particular plan.