sql - 已知问题？ : SQL Server 2005 stored procedure fails to complete with a parameter

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选项以避免参数嗅探(这在开发中从来没有必要，因为它可以正常工作)

一个可以在一两分钟内完成，另一个从未完成 - 只是坐在那里几个小时。

这个问题在开发服务器(build 9.00.3282.00)上没有发生过，生产服务器是build 9.00.3068.00

我已经从 procs 中删除了所有类型的代码，以尝试使用仍然存在问题的最小版本，并且非常小心地保持 SP 的两个版本相同，除了那个参数。

我有很多其他带有参数的 SP，它们运行良好。我也 DROP ped 和 re 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

我想 this article解释...

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

编辑:

New link on query plans and parameters .它仍然是参数嗅探是否指定了默认值。

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