主页 / dba / 问题 / 187276
Accepted
Edmund
Asked: 2017-09-30 05:17:24 +0800 CST

如何重新编译 SQL Server 数据库中的所有存储过程和表值函数？

  • 772

在 SQL Server 数据库sp_recompile上可以运行一个存储过程来更新执行计划。我想在数据库中的所有存储过程上运行它。另外，我想在所有表值函数上运行它的等价物，但我不知道要运行哪个 sys 过程。

是否可以在不sp_recompile为 SQL Server Management Studio 中的所有存储过程名称手动输入一行的情况下执行此操作？同样对于表值函数？

我相信我需要这样做，因为 VM SQL Server 的内存显着增加，但我只看到执行时间略有增加。执行计划显示 80% 以上的运行时间是在聚集索引搜索上，所以我认为我无法做更多的事情来优化存储过程。

sql-server stored-procedures

6 个回答

  1. Best Answer

    如果您认为这会有所帮助，您可以sp_recompile通过使用游标为每个内容生成临时 SQL 并运行它来运行所有内容：

    DECLARE C CURSOR FOR (SELECT [name] FROM sys.objects WHERE [type] IN ('P', 'FN', 'IF'));
DECLARE @name SYSNAME;
OPEN C;
FETCH NEXT FROM C INTO @name;
WHILE @@FETCH_STATUS=0 BEGIN
    EXEC sp_recompile @name;
    FETCH NEXT FROM C INTO @name;
END;
CLOSE C;
DEALLOCATE C;

    或者您可以生成临时 SQL 并通过 运行它EXEC，需要更少的代码，这可能会稍微更有效：

    DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql += 'EXEC sp_recompile '''+[name]+''''+CHAR(10) FROM sys.objects WHERE [type] IN ('P', 'FN', 'IF');
EXEC (@sql);

    （虽然我发现这种形式有时会因为看起来基于集合但迭代地构建字符串而不是标准的 SQL 模式而引起人们的注意）

    另一组可能与此处类似的对象是视图。您可以类似地将它们标记为需要重新评估，以确保存储的计划和其他元数据不会过时sp_refreshview，只需对上面显示的游标或临时 SQL 方法进行小的修改：

    DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql += 'EXEC sp_refreshview '''+[name]+''''+CHAR(10) FROM sys.objects WHERE [type] IN ('V');
EXEC (@sql);

    执行计划显示 80% 以上的运行时间是在聚集索引搜索上，所以我认为我无法做更多的事情来优化存储过程。

    有时优化比优先搜索而不是扫描等等，有时索引扫描比许多执行搜索操作更有效，并且计算您正在查看的百分比数字的成本估计是（估计）在最好的（一个有用的指南，但有时完全不准确）。

    虽然“投入更多内存”可以帮助解决一些数据库性能问题，至少是暂时的，但如果您的瓶颈受 CPU 限制而不是内存和/或 IO 限制，那么添加更多内存将几乎没有效果。

    • 9

  2. 该解决方案基于此处的另一个答案，但考虑了模式并考虑了名称中的特殊字符。 它重新编译所有模式中的所有过程、函数和内联函数。

    create procedure dbo.RecompileAllProcedures
as
begin
    declare cur cursor for 
    (
        select quotename(s.name) + '.' + quotename(o.name) as procname
        from 
           sys.objects o
           inner join sys.schemas s on o.schema_id = s.schema_id
        where  o.[type] in ('P', 'FN', 'IF')
    );

    declare @procname sysname;

    open cur;
    fetch next from cur into @procname;
    while @@fetch_status=0 
    begin
        exec sp_recompile @procname;
        fetch next from cur into @procname;
    end;
    close cur;
    deallocate cur;
end;

    然后可以按如下方式调用它：

    exec dbo.RecompileAllProcedures

    PS：我是 Allman 编码风格的忠实粉丝；）

    编辑：quotename在名称包含方括号的情况下，使用更安全的方法改进了程序。

    • 7

  3. 如果您添加内存（即使它是热添加到虚拟机），并增加最大服务器内存以匹配，您的计划缓存将被清除。

    这实际上是在“重新编译”您提到的所有这些内容，因为它们不会在缓存中存储计划以供重复使用。SQL Server 将不得不构建一个新的。

    不过，您可能从未设置过 Max Server Memory。如果您对此不确定，可以运行DBCC FREEPROCCACHE以清除计划缓存。

    您在生产中执行此操作需要您自担风险。我不能保证新计划会更好。

    内存并不能解决 SQL Server 中的所有性能问题，Seek 不一定是性能调整的终点。

    如果您需要有关特定查询的帮助，您应该提出一个单独的问题。

    • 5

  4. 如果您有不同模式中的对象，则更新语句

    declare @sql nvarchar(max) = (
select String_Agg(
    'exec sp_recompile '''+
    OBJECT_SCHEMA_NAME(object_id)+'.'+[Name]+'''', Char(10))
from sys.Objects where [Type] in ('P', 'FN', 'IF'));

exec (@sql);
    • 1

  5. 我知道这是一个老问题，但是您可以遵循另一种记录在案的方法。

    查询优化器在决定使用当前查询执行计划或编译新 QEP 之前检查表的统计信息。

    QO 已经“执行”统计信息来决定 QEP，但您可以自己更新统计信息，这会自动发出 QO 在下次运行时重新编译的信号。

    这样您就可以避免运行存储过程只是为了重新编译它，因为它将在下次运行时重新编译。您可以逐表更新统计信息表，也可以使用以下命令更新整个数据库

    EXEC sp_updatestats;

    来源：

    https://learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver15

    https://learn.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql?view=sql-server-ver15

    • 0

  6. 基于David Spillett 的回答，如果运行 SQL Server 2017 或更高版本，您可以使用String_Agg 函数并进一步简化：

    declare @sql nvarchar(max) = (select String_Agg('exec sp_recompile '''+[Name]+'''', Char(10)) from sys.Objects where [Type] in ('P', 'FN', 'IF'));
exec (@sql);
    • -1

相关问题