如何加快從頭開始/腳本重建數據庫

我們的數據庫存在於我們的原始碼控制中的許多不同腳本中。iE 每個表都有它自己的用於創建表、索引等的腳本。需要時，我們可以通過啟動一個 powershell 腳本來重建整個開發數據庫，該腳本將通過將每個腳本提供給 sqlcmd.exe 來執行它。

這個過程最近大約需要 30 到 40 分鐘。我們發現我們使用的是舊的 2008R2 版本的 sqlcmd，這要歸功於指向舊版本的本地路徑變數中的錯誤。解決這個問題後，整個過程減少到大約 8 分鐘。

我現在想知道是否有任何我可能忽略的“開關”可以進一步加快這個過程。

數據庫目前設置為兼容級別 100（出於愚蠢的原因）、多使用者模式和簡單恢復模式。數據庫在本地執行，在重建過程中不會有使用者連接。

我確實嘗試將其設置為單使用者模式，但遇到了問題。顯然，一個接一個地觸發一個 sql 腳本的順序過程似乎太快了（？），這對它自己來說是好的。不知何故，在建立下一個連接之前，一個連接沒有足夠快地關閉。

在這種情況下，查詢或數據庫級別是否有任何其他選項可以加快數據庫重建過程？

應用模式腳本最終會成為大量小事務，這可能會導致大量日誌文件等待。您可以嘗試在事務中執行腳本，但並非所有更改都與事務兼容。

因此，使腳本從不等待日誌刷新的一種簡單方法是在執行腳本時打開延遲持久性。

例如

ALTER DATABASE current SET DELAYED_DURABILITY = disabled;

go

declare @i int = 0
while @i < 10000
begin

   exec ('
   drop table if exists mt;
   create table mt(id int, a int, b datetime, c char(2), d uniqueidentifier);
   ')
   set @i += 1;

end

go

ALTER DATABASE current SET DELAYED_DURABILITY = forced;

go

declare @i int = 0
while @i < 10000
begin

   exec ('
   drop table if exists mt;
   create table mt(id int, a int, b datetime, c char(2), d uniqueidentifier);
   ')
   set @i += 1;

end

這個問題的範圍很廣，所以答案也會很廣泛和通用，所以請耐心等待。

您可能會遇到一些不同的問題：

• 設置查詢可能會做很多工作（例如創建數據庫並增加其日誌文件）
• SQL Server 可能功能不足
• 應用伺服器可能正在執行大量查詢，一次一個，等待網路往返

為了解決這個問題，我讓部署執行，然後使用開源First Responder Kit中的 sp_BlitzCache 。（免責聲明：我也參與了該項目。） sp_BlitzCache 可以分析您的計劃記憶體以查找長期執行的查詢，如下所示：

EXEC sp_BlitzCache @SortOrder = 'duration', @MinutesBack = 10;

這將為您提供安裝腳本中執行時間最長的前 10 個查詢的列表（假設它在過去 10 分鐘內開始。）這將告訴您其中一個查詢的執行時間是否比其他查詢長得多。

查看 sp_BlitzCache 輸出中的 Duration 列。獲取執行時間最長的查詢，並單獨嘗試該查詢。例如，如果它正在創建數據庫並增加其日誌文件，那麼您可以努力使該查詢執行得更快。或者，如果沒有一個長時間執行的查詢超過 5 秒，您可能會開始查看部署腳本中查詢的絕對數量：我們是在談論數百、數千還是數万？

這就是我用來縮小根本原因所在的思考過程。

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