刪除超過 x 天的行而不鎖定表

我們有一個相當大的 MS SQL 數據庫，有數百萬行。我創建了一個簡單的腳本來刪除超過 1 個月的行，但這似乎會鎖定表並給應用程序帶來麻煩。

該表有一個索引的“ID”PK，還有一個我將用於此任務的“日期”列。

在不導致鎖定的情況下執行此操作的最佳方法是什麼？我正在考慮分區，但不確定它是否是最好的方法。提前致謝。

您需要批量刪除行以避免鎖升級。當 SQL Server 從行鎖或頁鎖切換到鎖定整個表時，就會發生鎖升級。當 SQL Server 檢測到大量行或頁鎖已被佔用時，鎖升級可以節省記憶體，並且需要更多鎖才能完成操作。這可能就是您遇到阻塞問題的原因。如果我沒記錯的話，當涉及超過 5,000 行時，行鎖會升級為表鎖。

如果 ID 列也是聚集索引，您可能會受益於使用 ID 列執行刪除而不是使用日期列。為此，請獲取您要使用的日期的 ID 值，然後循環刪除每批少於 5,000 行，其中 ID 值小於或等於所需 ID。這僅適用於假設行是按日期順序插入的，這樣遞增的 ID 值與遞增的日期值匹配。

您可以DELETE通過檢查@@rowcount變數來確定受 影響的行數，並且可以使用 設置受影響的最大行數SET ROWCOUNT，然後循環直到DELETE不再刪除行：

SET ROWCOUNT 4500;
DECLARE @i int;
SELECT @i = 1;
   
WHILE @i > 0
BEGIN
   Delete from dbo.my_table Where ID < 4356;
   SELECT @i = @@rowcount;
END

消除使用已棄用 SET ROWCOUNT語句的另一種方法是TOP在查詢中添加一個子句DELETE，如下所示：

DECLARE @i int = 1;
DECLARE @id int;
SET @id = COALESCE((
   SELECT TOP(1) mt.ID
   FROM dbo.my_table mt
   WHERE mt.date < '2021-01-04T00:00:00.000'
   ORDER BY mt.date DESC
   ), 0);
WHILE @i > 0
BEGIN
   DELETE TOP(5000) mt
   FROM dbo.my_table mt
   WHERE mt.ID <= @ID;
   SET @i = @@ROWCOUNT;
END

在上面的程式碼中，我添加了一個查詢來獲取 2021 年 1 月 4 日或之前的行的最大 ID 值，只是為了說明這是如何工作的。

上述兩種方法都會產生相同的結果，所有在給定@ID值之前的行都會從表中刪除。鎖定應該減少為使用行級鎖，從而防止表鎖導致阻塞其他並發程序。

作為替代方案，您可以在數據庫級別查看 Read Committed Snapshot Isolation (RCSI)，也稱為row-versioning 。RCSI 阻止寫入器阻止讀取器，但是使用行版本控制可能會產生許多影響。如果您決定走這條路，您應該徹底測試使用數據庫的應用程序。在盲目啟用 RCSI 之前，請仔細閱讀並考慮對您的應用程序的其他影響。

另一種處理此問題的方法可以最大限度地減少停機時間（假設大部分數據正在被刪除），您可以INSERT將上個月的所有內容放入一個新表中。DROP舊表，並用於sp_rename立即將新表重命名為舊表的名稱。

例如：

SELECT *
INTO NewTable
FROM OldTable
WHERE OldTable.[date] >= '2021-01-04';

DROP TABLE OldTable;

EXEC sp_rename 'NewTable', 'OldTable'; -- Renames the new table with 1 month of data to the old table's name

當我不得不從一個表中刪除數十億條記錄時，這個*技巧對我很有幫助。*希望您已經在該date列上有一個索引，否則您可以批量INSERTS處理新表，直到您擁有 1 個月的數據（類似於 Mo64 的回答所建議的內容）。

順便說一句，我在研究其他內容時偶然發現了 Aaron Bertrand的這篇相關文章。它有一些關於批量刪除性能調整的好資訊，具有諷刺意味的是，一種解決方案就是我上面提到的那個。

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