Rock的SQL筆記本

這一次的LAB我們分別在[AdventureWorksDW]及[AdventureWorksDW_Simple]兩個資料庫做測試，其中[AdventureWorksDW]是完整復原模式，而[AdventureWorksDW_Simple]則是簡單復原模式，如下圖所示。

 

在下圖中我們先檢視一下目前兩個資料庫的交易紀錄使用情形，兩個資料庫的LDF約略50MB而使用率都在5%內

 

這次測試的資料來源資料表如下，筆數約77萬筆，而整張資料表約47MB。

 

 

一.首先我們用Select Into方式來測試目的資料表不存在的大量寫入

首先我們先在完整復原模式下測試，下圖中紅色圈選處，我們利用Select Into的方式將資料寫入並產出目的資料表並檢視一下交易紀錄檔的變化。我們可以看見檔案大小不變，依然是50MB左右，但是其使用量由1MB成長到46MB，共成長了45MB。

 

接下來我們用相同方式改在簡單復原模式下測試，如下圖中我們可以看見交易紀錄檔一樣約略50MB，但使用量僅由0MB成長至1MB，整個交易僅成長1MB而已。

 

二.接下來我們在完整模式下用Insert Into的方式將資料寫入到已存在的資料表中，看看交易紀錄會有甚麼樣變化。

 

如下圖所示，我們改用Insert Into來將資料寫入到已存在的資料表中，其交易紀錄檔由51MB成長至285MB。而其使用量則由3MB成長至116MB，所以整個交易產生了113MB的交易紀錄。

 

相同的測試方式我們在簡單模式下測試完，如下圖所示交易紀錄檔由50MB成長至278MB。而使用量也從0MB成長至113MB，整個交易也產生了113MB的交易紀錄。因此基本上Insert Into大量資料不管在完整的復原模式或簡單復原模式都會造成大量的交易記錄成長。

 

三.如何減少Insert Into大量資料所產生的交易紀錄

 

其實要降低Insert Into大量資料到已存在的資料表所產生的交易紀錄非常簡單，就是我們將整個寫入交易用TABLOCK鎖住目的資料表。這樣SQL就不會紀錄逐筆Insert的資料操作進去交易紀錄檔中，這樣一來當然可以達到降低交易紀錄的目的。如下圖紅色圈選處我們加了With(TABLOCK)後整個交易紀錄的使用量由12MB成長至58MB共46MB。

 

相同方式在簡單模式下的DB會是甚麼情況呢?如下圖所示在簡單模式DB中的Insert Into中加入With(TABLOCK)後，整個寫入交易讓交易紀錄檔的使用量僅由8MB成長至9MB，僅僅成長1MB而已。

 

由上面幾個簡單的測試後，我們表列結果如下。日後在遇見需要大量寫入資料的交易時能夠幫助您選擇交易紀錄成長較少的方式。

復原模式	大量寫入方式	交易紀錄成長量
完整模式	Select Into	45MB
完整模式	Insert Into	113MB
完整模式	Insert Into With(TABLOCK)	46MB
簡單模式	Select Into	1MB
簡單模式	Insert Into	113MB
簡單模式	Insert Into With(TABLOCK)	1MB

 

補充

下圖是Insert Into大量資料的交易紀錄內容統計，由紅色圈選處可以看見紀錄了大量的Insert Row操作。

 

下圖是Insert Into With(TABLOCK)大量資料的交易紀錄內容統計，由紅色圈選處可以看見SQL只紀錄了分配頁面的操作並不會紀錄Insert操作紀錄。

The few constraints are:

• The database must be in bulk logged recovery model.(我個人測試在Simple Model也是可以)
• The destination table must be empty or without clustered index.
• There is no non-clustered index on the destination table.
• 來源資料表要跟目的資料表是不同的才可以，像 Insert Into tbA With(TABLOCK) Select * From tbA這種目的來源皆是同一張表的，LDF還是會長。<--2017/12/1補充 

 

我是ROCK

rockchang@mails.fju.edu.tw