主页 / dba / 问题 / 334375
Accepted
J. Mini
Asked: 2023-12-28 06:49:52 +0800 CST

除了显式刷新缓存或要求重新编译之外，什么会重新编译 SQL Server 2019 中的完整存储过程？

  • 772

我最近读完 《SQL Server 2008 中的计划缓存》 ，我感到很困惑。看起来，除了完全刷新计划缓存或明确要求重新编译存储过程之外，从 SQL Server 2008 开始，存储过程的重新编译都是在语句级别而不是存储过程级别完成的。

那么，除了显式刷新缓存或要求重新编译（例如WITH RECOMPILE）之外，什么可以在 SQL Server 2019 中重新编译完整的存储过程，而不仅仅是重新编译单个语句呢？

举一个我感到困惑的例子，请考虑以下过程。

CREATE PROCEDURE FOO AS
BEGIN
   SELECT * INTO #temp1 FROM table1
   INSERT BAR1 SELECT * FROM #temp1
   INSERT BAR2 SELECT * FROM #temp1
END

我可以想到很多可能导致SELECT * INTO #temp1 FROM table1重新编译的事情，但是如果没有下一行也重新编译，那么重新编译会很奇怪。这让我觉得 SQL Server 中一定有一些东西会导致整个存储过程重新编译。

t-sql

2 个回答

  1. Best Answer

    不多

    除了您的问题中列出的内容之外，存储过程确实没有常见的通用原因总是为每个语句重新编译新计划，至少对于您的示例中的语句来说是这样。

    SQL Server 提供了几种不同的方法来跟踪重新编译，但它们都是语句级的。也许最容易获得答案的是扩展事件。我遇到过在对象级别没有跟踪重新编译的事件。

    SELECT
     d.map_value
FROM sys.dm_xe_map_values AS d
WHERE d.name = 'statement_recompile_cause'
ORDER BY
    d.map_key;

    截至 SQL Server 2022，这列出了 23 个重新编译的原因，其中包括“不是重新编译”。

    • 架构已更改
    • 统计数据发生变化
    • 延迟编译
    • 设置选项更改
    • 临时表已更改
    • 远程行集已更改
    • 对于浏览权限已更改
    • 查询通知环境已更改
    • 分区视图已更改
    • 光标选项已更改
    • 请求选项（重新编译）
    • 参数化计划已刷新
    • 测试计划线性化
    • 影响数据库版本的计划已更改
    • 查询存储计划强制策略已更改
    • 查询存储计划强制失败
    • 查询存储缺少计划
    • 交错执行需要重新编译
    • 不是重新编译
    • 查询存储提示已更改
    • 查询存储提示应用程序失败
    • 查询存储重新编译以捕获游标查询
    • 重新编译以清理多计划调度程序计划

    然而

    我知道有几件事会阻止计划缓存，这会使模块看起来每次都需要重新编译（尽管实际上我觉得这更像是每次都进行编译）。

    1. 对称密钥

    正如保罗在他的帖子中指出的那样：

    第二个选项是包含一条将整个批次标记为不可缓存的语句。合适的语句通常与安全相关或在某种程度上敏感。

    这听起来可能不切实际，但有一些缓解措施。首先，敏感语句不需要被执行——它只需要存在即可。当满足该条件时，运行批处理的用户甚至不需要执行敏感语句的权限。请仔细注意，影响仅限于包含敏感语句的批次。

    请点击链接查看几个示例。

    1. 不存在的物体

    我还没有发布关于此问题的帖子，但总体思路是，当IF存储过程中存在未探索的分支引用不存在的对象时，整个批次是不可缓存的。

    请随意跟随下面的演示。请注意，如果您尝试获取如下所示的存储过程的估计计划，或者如果您以探索具有不存在对象的分支的方式运行它，它将引发错误。

    /*Create a table if you need to*/
CREATE TABLE 
    dbo.DinnerPlans
(
    id bigint IDENTITY,
    name nvarchar(40) NOT NULL,
    seat_number tinyint NULL,
    is_free bit NOT NULL,
);
GO

/*First example, with an object that doesn't exist*/
CREATE OR ALTER PROCEDURE
    dbo.i_live
(
    @decider bit = NULL
)
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;
    
    IF @decider = 'true'
    BEGIN
        SELECT
            dp.*
        FROM dbo.DinnerPlans AS dp;
    END;
    
    IF @decider = 'false'
    BEGIN
        SELECT
            whatever.*
        FROM dbo.AnObjectThatDoesntEvenPretendToExist AS whatever;
    END;
    
    IF @decider IS NULL
    BEGIN
        SELECT
            result = 
                'please make a decision.'
    END;
END;
GO 

/*Say goodbye!*/
DBCC FREEPROCCACHE;

/*This runs without an error*/
EXEC dbo.i_live 
    @decider = 'true';

/*But there's no query plan!*/
SELECT
    object_name =
       OBJECT_NAME(deps.object_id, deps.database_id),   
    deps.type_desc,
    deps.last_execution_time,
    deps.execution_count,
    dest.text,
    query_plan =
        TRY_CAST(detqp.query_plan AS xml)
FROM sys.dm_exec_procedure_stats AS deps
OUTER APPLY sys.dm_exec_sql_text(deps.plan_handle) AS dest
OUTER APPLY sys.dm_exec_text_query_plan(deps.plan_handle, 0, -1) AS detqp;
GO
    • 4

  2. 这让我觉得 SQL Server 中一定有一些东西会导致整个存储过程重新编译。

    当然，您可以显式定义存储过程以在每次运行时重新编译，使用重新编译选项，如下所示：

    CREATE PROCEDURE FOO
WITH RECOMPILE
AS
BEGIN
   SELECT * INTO #temp1 FROM table1
   INSERT BAR1 SELECT * FROM #temp1
   INSERT BAR2 SELECT * FROM #temp1
END

    替代选项还可以使用重新编译提示执行存储过程：

    EXEC FOO WITH RECOMPILE;

    或者显式调用系统过程sp_recompile：

    EXEC sp_recompile N'dbo.FOO';
    • 2

相关问题