Known issue?: SQL Server 2005 stored procedure fai

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

回答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...