已知问题？：SQL Server 2005存储过程无法使用参数完成

Question

基本SP带有默认参数：

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

具有本地编码的相同SP。

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

您可以看到我在（冗余，偶数）WITH RECOMPILE选项中的位置，以避免出现参数嗅探（这在开发正常的情况下从来没有必要）

一个工作一两分钟就可以很好地完成，另一个则永远不会完成-只是坐在那里几个小时。

在开发服务器（内部版本9.00.3282.00）上从未发生此问题，生产服务器是内部版本9.00.3068.00

我从proc中删除了所有代码，以尝试降至仍然存在问题的最低版本，并且非常小心地将两个版本的SP保持相同，除了一个参数。

我还有很多其他带参数的SP，它们运行得很好。我还DROP踩CREATE了一下，然后重新播放了SP。

有任何想法吗？

是的，我有一个DBA在查看它，我没有SHOWPLAN或生产中的任何有用权利来查看是否存在阻塞（如果我的计划导致锁定升级，我想，同样，唯一的区别是参数）

我已经查看了所有SQL Server构建信息，并且没有看到有关此问题的已知问题，因此，在我弄清楚它或DBA弄清楚它之前，我有点被卡住了。

更新

这也无法完成（这实际上是这些SP的正常形式-我只是添加了一个默认值，以使其在测试期间来回切换更加容易）

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

但是，此操作完成了（这可以作为一种解决方法，尽管我要修改其中大约25个具有相同格式的SP）：

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

尝试掩盖输入参数。

我猜因为在编译时嗅到了指定的默认值（ EDIT ：或首次调用时发送的参数），所以重新编译无法正常进行。因此，重新编译无效。

我已经看到了估计的计划之间的巨大差异，只需将默认值（例如，从零更改为NULL或不包含一个）就可以了。

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

我认为这篇文章解释了…

…在编译或重新编译期间会嗅探参数值…

编辑：

有关查询计划和参数的新链接。仍然是参数嗅探是否指定了默认值。

上面的GetRecentSales存储过程中指定的WITH RECOMPILE选项不能消除基数估计错误

有关常量和计划的相关文章种类