SQL Server 大量資料歸檔實戰：700 萬筆資料高效刪除與索引重建策略

歸檔 700 萬筆、130 欄位的寬表資料，單次 DELETE 會導致記錄檔暴增與長時間鎖定。分批刪除配合索引管理是關鍵。

標準化歸檔流程

1. 停用非叢集索引（保留叢集索引與主鍵）：

   -- 查詢索引大小
   SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName, i.name AS IndexName,
          8 * SUM(a.used_pages)/1024 AS 'IndexSize(MB)'
   FROM sys.indexes i
   JOIN sys.partitions p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
   JOIN sys.allocation_units a ON a.container_id = p.partition_id
   WHERE OBJECT_NAME(i.OBJECT_ID) = 'orders'
   GROUP BY i.OBJECT_ID, i.index_id, i.name;
   
   -- 停用索引（排除 PK 與叢集索引）
   ALTER INDEX [IndexName] ON orders DISABLE;

2. 分批刪除：

   DBCC DROPCLEANBUFFERS; DBCC FREEPROCCACHE;
   SET NOCOUNT ON;
   BEGIN TRANSACTION;
   WHILE 1=1 BEGIN
       DELETE TOP(20000) FROM dbo.orders WITH(TABLOCK)
       WHERE ordertime < '2010-1-1';
       IF @@ROWCOUNT < 20000 BREAK;
   END
   COMMIT;

   • TOP(20000)：控制單次交易規模，減少鎖升級與記錄檔衝擊。
   • TABLOCK：取得表級鎖，減少鎖管理開銷，加速刪除。
   • @@ROWCOUNT 判斷結束條件。
3. 重建索引與更新統計：

   ALTER INDEX ALL ON orders REBUILD;

   恢復索引可用性並更新統計資訊，確保後續查詢計畫準確。

關鍵效能考量

• 刪除前 DROPCLEANBUFFERS/FREEPROCCACHE 清理快取，模擬冷啟動狀態測試真實效能。
• 批次大小依記錄檔空間與鎖容忍度調整（1萬-5萬常見）。
• 若有分區表，優先使用 SWITCH PARTITION 瞬間歸檔。