如何重新編譯 SQL Server 數據庫中的所有儲存過程和表值函式？

在 SQL Server 數據庫sp_recompile上可以執行一個儲存過程來更新執行計劃。我想在數據庫中的所有儲存過程上執行它。另外，我想在所有表值函式上執行它的等價物，但我不知道要執行哪個 sys 過程。

是否可以在不sp_recompile為 SQL Server Management Studio 中的所有儲存過程名稱手動輸入一行的情況下執行此操作？同樣對於表值函式？

我相信我需要這樣做，因為 VM SQL Server 的記憶體顯著增加，但我只看到執行時間略有增加。執行計劃顯示 80% 以上的執行時間是在聚集索引搜尋上，所以我認為我可以做更多的事情來優化儲存過程。

如果您認為這會有所幫助，您可以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 限制，那麼添加更多記憶體將沒有什麼影響。

引用自：https://dba.stackexchange.com/questions/187276