SQL2014 SP2新功能 DBCC CLONEDATABASE 複製資料庫

2016-08-29

日前在FB看見楊老師分享SQL2014 SP2後新功能DBCC CLONEDATABASE，可以快速複製一個Database ( 只複製Schema和Statistics資料，不含實體資料 )。

Rock很有興趣的玩了一下並寫下這一篇分享

Rock選了AdventureWorksDW這一個資料庫來測試，如下圖所示，該DB目前mdf檔約376MB(自動成長為每次16MB)，ldf檔則為56MB。

我透過指令DBCC CLONEDATABASE ( 如下圖 ) 快速將AdventureWorksDW複製另一個名為AdventureWorksDW_Clone的資料庫。而花費時間不到1秒鐘，如下圖紅色圈選處。

完成複製後我們可以在物件總管看到複製出來的DB AdventureWorksDW_Clone，該複製的資料庫預設會是唯讀。

我們比對AdventureWorksDW及AdventureWorksDW_Clone兩個DB的mdf及ldf檔案，如下圖AdventureWorksDW_Clone的mdf只有5MB而ldf只有3MB。根據官網文件表示只會從來源資料庫複製Schema和Statistics。而複製的資料庫預設檔案大小及成長參數是會根據model資料庫。

完成複製後我們利用sp_spaceused比較兩個DB的FactProductInventory_New資料表筆數。我們可以發現回傳結果顯示兩個資料庫的該資料表筆數及使用空間皆相同。但事實是如此嗎?

我們實際Count資料表筆數後就會發現AdventureWorksDW_Clone的FactProductInventory_New資料表實際筆數是0筆而非776286筆。

其實微軟提供這一項功能的目的就是透過複製一個不含資料的資料庫來提供一個TroubleShooting的環境，也可以利用這一個複製DB來檢視Query的執行計畫而不會耗用Server的IO ( 因為沒實際資料 )。如下圖所示，我在複製資料庫AdventureWorksDW_Clone執行一個Select Count的語法，從下圖紅色圈選處的執行計畫中可以看見SQL預估會有776286筆資料但實際資料是0筆。而整個Query並沒有耗費任何IO ( 如藍色圈選處 )，CPU使用時間也只是0秒。

而相同的語法我在正式DB測試，我們可以看見執行計畫完全跟複製資料庫一樣，但實際筆數不是0筆而是776286筆 ( 如下圖藍色圈選處 )。而在正式DB上執行Query會耗費Server不少IO及Cpu Time ( 如下圖紅色圈選處 )。

透過上述簡易測試我們可以知道該複製之資料庫就是讓我們專做測試分析用，目的就是降低直接在正式資料庫做TroubleShooting造成的效能影響。也許大家會想我們可以利用這一項功能取代原有的資料庫複製功能(例:產生指令碼或擷取資料層應用程式)，因為實在太方便了。但微軟建議該複製DB在使用完應立即刪除，並不建議用複製資料庫來當正式使用。

我再延伸做一點小測試，也許大家就知道為什麼了。如下圖我們Count資料表FactProductInventory_New後發現實際筆數是0筆，但該資料表的統計值顯示是776286筆。這錯誤的統計值會導致SQL編譯成錯誤的執行計畫。也許你會認為我用sp_updatestats來更新該DB的所有統計值不就好了，但ROCK實際執行更新後發現統計資料還是沒有變化 ( 記得要先將複製DB改為Read_Write才能做sp_updatestats )。

接下來我們在複製資料庫的FactProductInventory_New塞入776286筆資料，然後讓SQL更新一下該資料表的統計值。

FactProductInventory_New資料表更新完統計後，我們重新查詢一下相關內容。我們可以在下圖中看見該資料表實際筆數已有776286筆了，但統計資料更新後居然變成155萬筆左右。統計值是否正確對於資料庫是很重要的，經過簡單測試就可以知道微軟為何強調用DBCC CLONEDATABASE建立出來的資料庫只能測試用了。要知道資料庫是不是Clone出來的可以利用DATABASEPROPERTYEX這一個Function來查詢。Select DATABASEPROPERTYEX( 'DBNAME' , 'IsClone' )，如果回傳值是1就表示該DB是複製的囉。

而資料庫複製只會複製部分物件，並不是全都複製，表列如下:

APPLICATION ROLE
AVAILABILITY GROUP
COLUMNSTORE INDEX
CDB
CDC
DATABASE PROPERTIES
DEFAULT
FILES AND FILEGROUPS
FUNCTION
INDEX
LOGIN
PARTITION FUNCTION
PARTITION SCHEME
PROCEDURE
Note T-SQL procedures only. Natively compiled stored procedures and CLR procedures won't be copied.
ROLE
RULE
SCHEMA
SEQUENCE
SPATIAL INDEX
STATISTICS
SYNONYM
TABLE
Note Only user and filestream tables are copied. Memory optimized tables and File Tables won't be copied.
TRIGGER
TYPE
UPGRADED DB
USER
VIEW
XML INDEX
XML SCHEMA COLLECTION