將多個表的目前數據庫中的數據移動 到新數據庫的最快方法？

這是我的第一個問題和文章，所以讓我知道我是否可以改進。如果提供太多資訊，我會犯錯。

我是我公司的一名相當新的 DBA，在 DBA 團隊工作不到兩年，我的任務是想出一種方法，將數據從一個特定實體的現有數據庫移動到一個尚不存在的新數據庫. 該數據庫位於執行 SQL Server 2012 SP1 標準版的實例上。

我們目前有一個名為 purge-entitydata 的自定義 PowerShell cmdlet。此 cmdlet 將根據實體值在目前數據庫中的所有 160 個使用者表中執行刪除。

例子

purge-entitydata -entity <Entity>

但是，在我們刪除目前數據庫中的所有實體數據之前，我們只需將該實體的數據移動​​到一個名為

$$ Entity_Backup $$以防下游流程失敗。 以下是一些注意事項：

1. 160 個使用者表每年可能會更改數次。有些可能會被刪除或添加新的。我正在考慮採用 SSIS 路線，但不想每次添加或刪除新表時都必須使用新的數據流任務更新包。
2. 只有數據和表本身需要移動。我不需要將視圖、儲存過程或索引從目前數據庫移動到臨時數據庫。
3. 目前的數據庫和使用者表包含超過一千個實體的數據。我只需要將一個實體的數據移動​​到臨時數據庫。
4. 如果下游程序成功，則臨時數據庫將被刪除。
5. 數據庫很大，超過 200 GB。此外，此伺服器是拉合併複製訂閱的發布者，也是兩個事務複製訂閱的發布者。我認為進行備份會大大降低複製速度。

我正在考慮使用 sp_msforeachtable ‘SELECT * INTO

$$ Entity_Backup $$.? 從 ？WHERE Entity = $Entity’ 和 Invoke-Sqlcmd 但我擔心這會花費太長時間，因為它不會並行執行。一個實體大約有三到五百萬行。 將數據從 160 個使用者表移動到可以從 PowerShell cmdlet 內部完成的新數據庫的最有效/最快的方法是什麼？有沒有人有任何可能比 ms_foreachtable 更快的建議？

您說您擔心您提出的解決方案不會起作用，因為它不會並行執行。您已經有一個 PowerShell cmdlet 可以從所有 160 個表中刪除，對吧？刪除行通常比插入它們花費更長的時間，尤其是當您要從中刪除的表上有索引時。如果該 cmdlet 不並行執行，那麼為什麼需要插入命令並行執行？我建議從一個相對簡單的解決方案開始，進行性能測試，並在必要時使其變得更複雜。

我不熟悉可以在 PowerShell 中執行的限制。但是，似乎可以從 PowerShell 執行儲存過程，因此我將提供一個 T-SQL 解決方案。您應該能夠輕鬆地將其轉換為您需要的內容，但我可能將程式碼稱為“儲存過程”。

如果您的數據庫大約 200 GB 並且您有大約 1000 個條目，那麼平均而言您只需要移動 200 MB 的數據。如果表上有合適的索引（例如Entity列上的索引），那絕對是可行的。我將假設您已經進行了該分析，並且您擁有加快SELECT語句速度所需的索引。

1）為插入創建數據庫

我建議您使用簡單的恢復模型創建臨時數據庫。對於最少記錄的查詢，這將減少寫入事務日誌的數據。我也認為使用不同的恢復模式沒有任何優勢，因為數據的壽命很短。我還建議您創建一個具有正確設置的數據庫並保留它。將數據和日誌文件增長到適當的大小以防止自動增長事件。您可以刪除數據庫中的所有表，因為它們不再需要。每次創建和刪除數據庫時，您真的需要完成這些工作嗎？

2) 找出你的 DML 策略

我可以想到三種不同的方式將數據插入臨時數據庫。其中兩個需要更改您的刪除小程序。

所有這些都插入到堆中並利用最少的日誌記錄。因為您只需要數據而不需要其他任何東西，這將最大限度地減少插入的成本。在 SQL Server 2012 上，您不能進行並行插入。如果您升級到 SQL Server 2014 並使用SELECT INTO.

第一種方法使用該OUTPUT子句從表中刪除行並將刪除的行插入到單個查詢中。這可能是最快的方法，因為您只需為每個表讀取一次基礎數據。您不能SELECT INTO用於插入語句（但您可以預先執行一個來創建目標表）。您的查詢可能如下所示：

INSERT INTO [other_db]..[Entity_Table_1] WITH (TABLOCK)
SELECT t.*
FROM 
(
   DELETE FROM [source_db]..[Entity_Table_1]
   OUTPUT deleted.*
) t;

第二種方法與第一種方法類似，但您在刪除查詢之前處理表的插入查詢。理論上，插入查詢會將您需要的數據從表中移動到緩衝區記憶體中。這將減少您的程式碼所需的磁碟訪問次數，從而執行得更快。這將需要更改刪除小程序的程式碼。

第三種方法只是進行插入。對於這種方法，您不需要修改刪除小程序。如果您不使用SELECT INTO，您將希望使用TABLOCK提示來獲得最少的日誌記錄。

3）弄清楚循環的方法和順序

我不建議使用sp_MSforeachtable. 它沒有記錄，Microsoft 可以隨時更改或刪除它。相反，您可以使用游標遍歷sys.tables中的相關表。應該有很多關於如何線上執行此操作的範例，但如果您需要進一步的幫助，請告訴我。

您處理表格的順序可能很重要。例如，假設您需要為刪除語句和插入語句分別應用程序。如果您在兩個過程中以相同的順序處理表，則目前表的數據可能會在您再次處理之前從緩衝區記憶體中推出。這是因為您在再次查詢同一個表之前要查詢 159 個其他表。如果您以與從表中刪除的相反順序處理表，則在讀取數據時可能會減少物理讀取。

4) 必要時實現並行。

測試後你可能會發現你的程式碼太慢了，需要同時執行多個insert語句。要做到這一點，首先使儲存過程始終以確定的順序循環遍歷表。向過程添加兩個輸入參數。第一個應該是執行緒總數，第二個應該是該過程呼叫的執行緒。如果您呼叫的過程總共有 4 個執行緒並且活動執行緒為 1，那麼該過程將處理第 1、第 5、第 9、第 13、… 表。對於 N 個執行緒，您可以打開 N 個 PowerShell 視窗並更改每個執行緒的第二個參數。

快樂插入！

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