[SQL]解決大量資料刪除，造成資料庫交易紀錄檔案容量過大且耗費時間之處理

2015-05-16

這幾天遇到一個蠻特別的狀況，因此將相關處理和數據模擬整理一下，避免以後遇到類似狀況的時候，就可以請相關人員自己來看說明了，能有多一點時間來看 R 語言了。

前置準備

話說這個案例是這樣的，有一個數據庫內有不少的資料表，會定時將內部很多的資料給刪除，然後再重新匯入新的資料。由於其中部份的資料表數據都至少有數百萬筆以上，因此時常因為刪除資料的時候，交易紀錄檔案成長非常的大，耗用掉非常大的硬碟空間，因此希望能找到比較好的解決方式，為了重現這個問題，我們先用以下的指令來建立一個模擬的資料庫。

-- 刪除範例資料庫
 IF DB_ID('SPTEST') IS NOT NULL DROP DATABASE [SPTEST];
 GO 
  
 -- 建立資料庫
 CREATE DATABASE [SPTEST]
 GO
  
 -- 變更復原模式為簡單
 ALTER DATABASE [SPTEST] SET RECOVERY SIMPLE;
 GO
  

 USE [SPTEST]
 GO
  
 -- 建立資料表
 CREATE TABLE [BigTable]
 (
     A1    INT,
     A2    NVARCHAR(10),
     A3    VARCHAR(10),
     A4    NCHAR(200),
 )
 GO
  
 DECLARE @I INT;
 DECLARE @J INT;
 DECLARE @K INT;
  
  
 SET @J = 0;
  
 -- 產生一千萬筆的資料，為了避免交易記錄檔過大，每一萬筆資料放在一個 Transaction 內
 SET NOCOUNT ON;
 WHILE @J < 1000
 BEGIN
     SET @I = 0;
     BEGIN TRAN
     WHILE @I < 10000 
     BEGIN
         SET @K = @J*10000+@I ;
         INSERT INTO [BigTable] ( A1,A2,A3,A4 ) VALUES ( @K, RIGHT('0000000000'+LTRIM(STR(@K)),10), RIGHT('0000000000'+LTRIM(STR(@K)),10), NEWID())
         SET @I += 1; 
     END    
     COMMIT
     SET @J += 1; 
 END
 GO

為了方便後續測試有一樣的基準，因此我先將該資料庫給備份起來，留做每次都可以還原來使用

USE [master]
GO

BACKUP DATABASE [SPTEST] TO DISK= 'D:\Temp\SPTEST_FULL.BAK'  WITH INIT, FORMAT, COMPRESSION
GO

這樣的資料庫差不多占用 5GB 的磁碟空間

進行測試

基本上交易紀錄檔案會成長，主要跟資料庫復原模式有關係，因此我們先測試在復原模式是「完整」和「簡單」的狀況下，當刪除資料會耗用多少時間與占用多大的交易紀錄檔案。首先我們先測試復原模式是「完整」

USE [master]
GO
RESTORE DATABASE [SPTEST] FROM DISK = 'D:\TEMP\SPTEST_FULL.BAK' WITH REPLACE
GO

ALTER DATABASE [SPTEST] SET RECOVERY FULL WITH NO_WAIT
GO

BACKUP DATABASE [SPTEST] TO DISK='NUL'
GO

USE [SPTEST]
GO

DECLARE @Start DATETIME = GETDATE()
DELETE FROM  [dbo].[BigTable]
PRINT DATEDIFF( ss, @Start, GETDATE() )

結果如下，可以看到執行刪除指令耗用 102 秒的時間

而交易紀錄檔案大小成長到 14GB 的大小

那如果復原模式是採用「簡單」的時候，狀況會是如何呢 ?

USE [master]
GO
RESTORE DATABASE [SPTEST] FROM DISK = 'D:\TEMP\SPTEST_FULL.BAK' WITH REPLACE
GO

ALTER DATABASE [SPTEST] SET RECOVERY SIMPLE WITH NO_WAIT
GO

BACKUP DATABASE [SPTEST] TO DISK='NUL'
GO

USE [SPTEST]
GO

DECLARE @Start DATETIME = GETDATE()
DELETE FROM  [dbo].[BigTable]
PRINT DATEDIFF( ss, @Start, GETDATE() )

耗用時間差不多

而占用的空間也差異不大

主要是因為不管是復原模式是「完整」或「簡單」，因為交易資料都要先寫入到交易紀錄檔案內，而刪除資料筆數和環境都相同，因此兩者占用的空間和時間都差異不大。而如果真的要講求快速的話，那麼使用 TRUNCATE TABLE 是最快的，但限制就是只能全部刪除的狀況下使用，由於我們的案例只是刪除部分資料，加上又不是 Enterprise 的版本的情況下，是沒有辦法使用 Partition Table 的方式，因此下面的案例中我們都使用 DELETE 來做處理。

平行成本

而為了避免交易紀錄檔案占用那麼多的空間和時間，我們先查看一下刪除指令的「估計執行計畫」，從下圖中可以看出這樣的刪除指令很大量的耗用 IO 成本，使得運算子成本很容易高於系統「平成處理原則的成本臨界值」的預設值 5，因此我們可以試試看利用 MAXDOP 限制只使用單核心來做處理。

USE [master]
GO
RESTORE DATABASE [SPTEST] FROM DISK = 'D:\TEMP\SPTEST_FULL.BAK' WITH REPLACE
GO

ALTER DATABASE [SPTEST] SET RECOVERY FULL WITH NO_WAIT
GO

BACKUP DATABASE [SPTEST] TO DISK='NUL'
GO

USE [SPTEST]
GO

DECLARE @Start DATETIME = GETDATE()
DELETE FROM  [dbo].[BigTable] OPTION ( MAXDOP 1 )
PRINT DATEDIFF( ss, @Start, GETDATE() )

看起來對時間有點幫助，縮減了不少

就算復原模式是簡單的時候也能有幫助

批次刪除

但因為交易紀錄檔案還是會成長，因此我們下面會採用批次刪除的方式，配合復原模式是簡單的狀況下，用不同的批量來進行測試。

首先是一次 10,000 筆資料，將耗用 497 秒，交易紀錄檔案會是 43MB

當批量為 50,000 筆的時候，時間是 54 秒，交易紀錄檔案會是 199MB

當批量為 100,000 筆的時候，時間是 42 秒，交易紀錄檔案會是 320MB

總結

為了方便了解相關變化，我將上面的測試數據整理成為表格，當然這樣的測試數據會跟許多環境變數而所有改變，像是磁碟的速度、電腦運算的速度、記憶體大小、一筆記錄的大小等原因，都有可能造成不同的結果出現。但一般狀況下如果沒有需要做到交易紀錄備份或者是其他 HADR 的解決方案時，當採用復原模式是簡單的狀況下，如果有大量資料需要刪除的時候，最好加上核心數的限制和採用批量刪除，將可以更有效的提升效能，也避免交易紀錄檔案過大的問題。

回首頁

五餅二魚工作室

James Fu 的技術學習之路

[SQL]解決大量資料刪除，造成資料庫交易紀錄檔案容量過大且耗費時間之處理

系列文章

標籤雲