Known issue?: SQL Server 2005 stored procedure fails to complete with a parameter

Question

Your basic SP with a default parameter:

ALTER PROCEDURE [usp_debug_fails]
    @DATA_DT_ID AS int = 20081130
WITH RECOMPILE
AS
BEGIN
    /*
        Usage:
        EXEC [usp_debug_fails] WITH RECOMPILE
    */
    -- Stuff here that depends on DATA_DT_ID
END

The same SP with a local that is hardcoded.

ALTER PROCEDURE usp_debug_works]
WITH RECOMPILE
AS
BEGIN
    /*
        Usage:
        EXEC [usp_debug_works] WITH RECOMPILE
    */

    DECLARE @DATA_DT_ID AS int
    SET @DATA_DT_ID = 20081130
    -- Stuff here that depends on DATA_DT_ID
END

You can see where I put in the (redundant, even) WITH RECOMPILE options in order to avoid parameter sniffing (this was never necessary in development where this thing worked fine)

The one that works completes fine in a minute or two, the other never completes - just sits there for hours.

This problem never happened on the development server (build 9.00.3282.00), the production server is build 9.00.3068.00

I've removed all kinds of code from the procs to try to get down to the minimal version which still exhibits the problem, and have been very careful to keep both versions of the SP the same except for that one parameter.

I have plenty of other SPs which take parameters and they do run fine. I've also DROPped and reCREATEed the SPs.

Any ideas?

And yes, I have a DBA looking at it and I do not have SHOWPLAN or any useful rights on production to see if there is blocking (in case one's plan results in a lock escalation I guess - again, the only difference is the parameter)

I've reviewed all the SQL Server build information and don't see a known issue about this, so until I figure it out or the DBA figures it out, I'm kind of stuck.

UPDATE

This also fails to complete (this is actually the normal form for these SPs - I just put a default in to make it easier to switch back and forth during testing)

ALTER PROCEDURE [usp_debug_fails]
    @DATA_DT_ID AS int
WITH RECOMPILE
AS
BEGIN
    /*
        Usage:
        EXEC [usp_debug_fails] 20081130 WITH RECOMPILE
    */
    -- Stuff here that depends on DATA_DT_ID
END

however this one completes (which may work as a workaround, although I have about 25 of these SPs to modify which all have the same form):

ALTER PROCEDURE [usp_debug_fails]
    @DATA_DT_ID_in AS int
WITH RECOMPILE
AS
BEGIN
    /*
        Usage:
        EXEC [usp_debug_fails] 20081130 WITH RECOMPILE
    */

    DECLARE @DATA_DT_ID AS int
    SET @DATA_DT_ID = @DATA_DT_ID_in
    -- Stuff here that depends on DATA_DT_ID
END

Answer 1

Try masking the input parameter.

I guess the recompile isn't working because of the specified default (EDIT: Or parameter sent on first call) being sniffed at compile time. So, recompile has no effect.

I've seen huge difference between estimated plans simply by changing the default from say, zero to NULL, or not having one.

ALTER PROCEDURE [usp_debug_mightwork]
    @DATA_DT_ID AS int = 20081130
AS
BEGIN
    DECLARE @IDATA_DT_ID AS int
    SET @IDATA_DT_ID = @DATA_DT_ID
    -- Stuff here that depends on IDATA_DT_ID
END

I think this article explains...

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

EDIT:

New link on query plans and parameters. It's still parameter sniffing whether a default is specified or not.

The WITH RECOMPILE option specified on the GetRecentSales stored procedure above does not eliminate the cardinality estimation error

Kind of related article about constants and plans

Answer 2

Prevent parameter sniffing or you are toast when statistics change. I have 500+ sps and all of them start with:

DECLARE @_Param1 ..., @_ParamN

--- prevent pameter sniffing
SELECT @_Param1 = @Param1, @_ParamN = @ParamN

cheers,
</wqw>

Answer 3

What was the solution to this issue? I face a similar issue and cant seem to find a solution. The stored procedure executes perfectly find in dev environment. It does not execute completely on the production environment.

Appreciate your guidance