使用批量刪除時應該刪除多少條記錄？

我要將migrate大量數據從一張表轉移到另一張表。目標數據源表有外鍵約束，所以我不能使用DELETE with OUTPUT clause，但需要有一個buffer表來代替。

該data source表不會被其他使用者讀取，但是new table（我在其中插入記錄將在操作期間經常使用） - 所以，我需要在不阻塞其他使用者活動的情況下進行遷移。

數據庫處於FULL恢復模式，SQL Server 2012每分鐘備份一次15事務日誌（操作過程中事務日誌的增長無論如何都不是問題）。

我有以下T-SQL聲明：

CREATE TABLE #DataBuffer
(
   ....
);

DECLARE @RowCount INT;

WHILE 1 = 1 
BEGIN 

   BEGIN TRY

         BEGIN TRANSACTION

           TRUNCATE TABLE #DataBuffer;

           DELETE TOP (4000) 
           FROM [dbo].[OldSourceTable]
           OUTPUT [deleted]. ...
           INTO #DataBuffer (...)
           WHERE ... ;

           INSERT INTO [dbo].[NewSourceTable] (...)
           SELECT ...
           FROM #DataBuffer;

           SET @RowCount = @@ROWCOUNT;

           COMMIT TRANSACTION

           IF @RowCount = 0 
           BEGIN
               BREAK
           END

   END TRY

   BEGIN CATCH 

       IF @@TRANCOUNT > 0
       BEGIN
           ROLLBACK TRANSACTION
       END

   END CATCH

END

我想知道在批量刪除語句中要刪除的行的最佳值是什麼？

我看到了不同的變體，我發現的唯一推薦是從這裡：

如果您在單個命令中刪除 4000 或更多行，則整個表將被鎖定（或者如果您在分區表上啟用了整個分區，則鎖定整個分區）。如果該表需要可供其他使用者使用，請勿批量刪除 10000 行！因此，在一次刪除中刪除例如 2000 行…

但我無法在MSDN或其他地方找到關於此的確認。

請注意，無論您是否觸發表鎖，一個 1000+ 記錄的 INSERT 事務都會在您的索引中鎖定許多頁，並且很有可能導致死鎖或其他問題。好消息是，您的文章似乎沒有為進行如此大規模的交易操作提供商業案例。

數據源表不會被其他使用者讀取…

對於業務來說，確保大塊記錄已經被大批量傳輸，這樣訪問源表的任何人都不會在確切的時間點看到它們，任何訪問目標時間的人都能夠看到它們，是否至關重要？或者，目標表的使用者是否有必要同時看到目標表中出現的大塊記錄？

如果這兩個都不是，我支持 Spörri 的以下陳述……

如果您在標識列上有一個聚集索引，該索引沒有碎片，並且您正在從舊的範圍中刪除記錄，您可以安全地要求引擎鎖定舊記錄正在使用的擴展並一次刪除約 64Kb 而不必擔心阻止讀者。

現在 64KB 並不是一個很大的值。如果您的記錄是每條 1000 字節，這意味著您最多在談論 64 條記錄。因此，我建議您一次執行一項記錄。如果遷移因錯誤而終止，其中一條記錄將被回滾，您當然可以從中斷的地方繼續，因為源表和目標表將對齊。這將確保為您的使用者提供最大的可用性。

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