[SQL SERVER][Maintain]管理交易記錄檔(4)

2012-12-03

6815
0
SQL_SERVER

實務中我常常會有資料轉移需求，而我當然不希望產生肥大的交易記錄檔，

但如果匯入資料量龐大，那麼產生大量交易記錄檔紀錄是無可避免的，

雖然SQL 2005以後，SQL SERVER對於資料匯入所產生紀錄有所改善(可採用最低限度記錄)，

如果資料量真的相當龐大的話，個人還是建議使用Partition Table並切換到Simple 底下來處理，

但有些人會覺得用Partition Table來處理很麻煩，而且可能也無法隨性切換資料庫復原模式的話，

那麼大量匯入採用最低限度記錄方法也是很值得推薦使用的，

下面我將簡單測試一下該方法，

而你將發現這方法在簡單復原模式下確實大大改善匯入資料時所產生的交易記錄檔大小。

1.條件

擷取MSDN。

2.影響因素

擷取MSDN。

--create source table
CREATE TABLE [dbo].[source](
    [c1] [int] NULL,
    [c2] [char](200) NULL
) 
--insert data
declare @i int
SET NOCOUNT on
set @i=1
WHILE(@i<=30000)
begin
insert into source with(tablock) select @i,'rico'
set @i=@i+1
end

--完整復原模式
alter database mydemo set recovery full

--create heap table
CREATE TABLE [dbo].[myheap](
    [c1] [int] NULL,
    [c2] [char](200) NULL
) 

--create nonheap table
create table dbo.nonmyheap
(
[c1] [int] NULL,
    [c2] [char](200) NULL
)
--create clustered index
create clustered index cidx on nonmyheap(c1)

完整復原模式

測試無資料Heap Table

insert into myheap select * from [source]

查詢所產生交易紀錄檔大小


SELECT
COUNT(*) as numrecords,
COALESCE(SUM([Log Record Length]), 0) / 1024. / 1024. as 'size(mb)'    
FROM sys.fn_dblog(NULL, NULL)
WHERE AllocUnitName = 'dbo.myheap' 
OR AllocUnitName LIKE 'dbo.myheap%';

約使用7.2 mb。

查詢所產生交易記錄檔內容


select operation,context, [log record fixed length], [log record length], 
AllocUnitName,sum(1) as '數量'
from fn_dblog(null, null) 
where allocunitname='dbo.myheap'
group by operation,context, [log record fixed length], [log record length], 
AllocUnitName 
order by [Log Record Length] Desc

LCX_HEAP 完整記錄30000筆操作細節(6085+23915=30000)，操作:LOP_INSERT_ROWS。

使用Table Lock Hint

先刪除 myheap 再重新新增資料


insert into myheap with(tablock)  select * from [source]

查詢所產生交易紀錄檔大小

交易操作紀錄筆數雖然減少很多，但整體大小只少掉1 MB。

查詢所產生交易記錄檔內容

可以看到使用TABLOCK後(預設是rowLock)，將採取LOP_FORMAT_PAGE(分配頁面)操作，

交易記錄檔中將不會記錄資料插入操作(減少操作數量)，進而降低交易記錄檔大小。

測試無資料NonHeap Table


insert into nonmyheap select * from [source] order by c1



查詢所產生交易紀錄檔大小

約花9.2mb。

ps1:依照clustered index 順序匯入資料，

可以減少頁面分割發生頻率、邏輯碎片和排序時間。

ps2:針對有索引資料表使用TABLOCK Hint，

則SQL Server將不執行平行大量匯入。

查詢所產生交易記錄檔內容

LCX_CLUSTERED完整記錄30000筆操作，操作:LOP_INSERT_ROWS。

使用Table Lock Hint


insert into nonmyheap with(tablock)  select * from [source] order by c1

查詢所產生交易紀錄檔大小

和前次相比少了快3mb。

查詢所產生交易記錄檔內容

可以看到使用TABLOCK Hint可以減少交易記錄檔大小，

交易記錄檔中將不會記錄資料插入操作。

簡單復原模式


--簡單復原模式
alter database mydemo set recovery simple


測試無資料Heap Table


insert into myheap select * from [source]


查詢所產生交易紀錄檔大小


約花8.7 mb(比完整復原模式下還要大)。



查詢所產生交易記錄檔內容


LCX_HEAP 完整記錄30000筆操作細節(6085+23915=30000)，操作:LOP_INSERT_ROWS。



使用Table Lock Hint


insert into myheap with(tablock)  select * from [source]



查詢所產生交易紀錄檔大小


約花0.05 mb。



查詢所產生交易記錄檔內容


LCX_PFS 最小記錄操作細節，操作:LOP_MODIFY_ROWS。



測試無資料NonHeap Table


insert into nonmyheap select * from [source] order by c1


查詢所產生交易紀錄檔大小


約花 9.2 mb。



查詢所產生交易記錄檔內容


LCX_CLUSTERED 完整記錄30000筆操作細節，操作:LOP_INSERT_ROWS。



使用Table Lock Hint


insert into nonmyheap with(tablock)  select * from [source] order by c1



查詢所產生交易紀錄檔大小


約花0.06 mb，和前一次相比少約9mb以上。



查詢所產生交易記錄檔內容


LCX_PFS 最小記錄操作細節，操作:LOP_MODIFY_ROWS。


匯入30000筆資料所產生交易記錄檔大小

目標資料表類型	使用TABLOCK	沒使用TABLOCK	資料庫復原模式
Heap(無資料)	6.2(mb)	7.2(mb)	完整
NonHeap(無資料)	6.3(mb	9.2(mb	完整
Heap(無資料)	0.05(mb)	8.7(mb)	簡單
NonHeap(無資料)	0.06(mb)	9.2(mb	簡單