BULK INSERT Performance
一直以為BULK INSERT就不會記Log,結果今天跌了一跤...
我下的語法如下(錯誤示範,小朋友不要學)
TRUNCATE TABLE RESD
BULK INSERT RESDE
FROM 'C:/DataProc/Output/RESD.txt'
WITH
(
FIELDTERMINATOR = '/t',
ROWTERMINATOR = '/n'
)
RESD.txt約1.4G,結果BULK INSERT花了30分鐘還沒做完,Log檔就長到20G,把HD空間給吃光。急忙Cancel,但依DB的運作,此時SQL會將剛才未做完的INSERT Rollback回去,20G的Log! 等了40分鐘還等不到Cancel完成。
請教了DBA,DBA認為剛才有HD空間不足的變數,SQL Server 2005可能已經不正常,建議我重新啟動SQL試試。重新啟動SQL後,順道見識了SQL 2005的新功能,SQL還是會將剛才沒做完的Rollback繼續做完,此時正在Rollback的DB會顯示(in recovery)而暫時不能用,但其他的DB則Ready了,花了約兩分鐘,in recovery的DB也回到可用狀態。(SQL 2000需要等Rollback做完,整台Server才能用)
在苦等DB Rollback的過程裡,我Google了一下,發現幾件事:
- Nonlogged BULK INSERT只有在一些條件下才成立(我一直以為BULK INSERT==No Log):
- DB選項必須開啟SELECT INTO/BULK INSERT
- 不可設Index;如果有,開始BULK INSERT時,TABLE必須是空的
- 需加上TABLOCK提示
- 資料表沒有設定複寫(Replication)
http://www.mssqlcity.com/Tips/bulk_copy_optimization.htm - 由於誤認BULK INSERT不會有Log Issue、加上將DB設成Simple Recovery Mode(Truncate Log At Checkpoint),我沒意會到讓2.5億筆資料變成一個Transaction是多可怕的事。因此才會爆出可怕的Log量,其實我只要透過BATCHSIZE=1000,讓BULK INSERT過程中每1000筆Commit一次,Log量就會小很多了。不過資料匯入後,不過2G,Index也只有一組,我還是無法理解為什麼可以產生10倍大小的Log?
綜合以上,BULK INSERT SQL可以改成:
BULK INSERT RESDE
FROM 'C:/DataProc/Output/RESD.txt'
WITH
(
BATCHSIZE = 1000,
FIELDTERMINATOR = '/t',
ROWTERMINATOR = '/n',
TABLOCK
)