搜尋本站文章

2017-07-01

[Performance Tuning][Replication]:採用「卸除現有物件並建立新物件(Drop existing object and create a new one)」


在初始化複寫時,以效能觀點,應採取
卸除現有物件並建立新物件(Drop existing object and create a new one)」,
使用截斷現有物件中的所有資料(Truncate all data in the existing object)」。


分段式的初始化複寫,比起資料表上已先建置索引的方式,快上許多,有數倍的效能差異。
也就是說,有效率的做法是:先匯入資料後,再建立索引

情境

在訂閱者端的資料表上,已經建立3~5個索引。
某些資料表的索引資料量比資料表還要大。

經過測試後,請參考以下實驗結果

有高達 8 倍的費時差距。方法正確,僅需 1/8 的時間就可以完成任務。

方式 說明 費時(秒)
drop
卸除現有物件並建立新物件 
卸除現有物件並建立新物件
再手動建立索引
290
truncate
截斷現有物件中的所有資料
訂閱者端資料表已先有索引
先 truncate 訂閱者端資料表,再匯入資料
2436

Creating index after data insert is more efficient way

-- 01_使用:卸除現有物件並建立新物件


 -- 02_使用 Drop existing object and create a new one





分段式的初始化複寫:先匯入資料後,再建立索引

其作法流程如下:
1. 建立資料表,但不含:建立索引、條件約束等。
2. 再執行大量資料的匯入、異動等作業。
3. 最後,建立索引、條件約束等。

若事先建立資料表、索引、條件約束,會讓後續大量資料的匯入、異動等作業,其執行效率不佳。



@pre_creation_cmd 參數 - sp_addarticle

在 sp_addarticle 使用 @pre_creation_cmd 參數 來設定:

-- 11_@pre_creation_cmd 參數的說明


-- 12_使用 @pre_creation_cmd 參數。預設使用:drop



-- 13_使用 @pre_creation_cmd 參數。效能觀點,不使用 truncate




參考資料

對啟用「資料壓縮(Data Compression)」的資料表,執行大量匯入、修改作業之建議
http://sharedderrick.blogspot.tw/2010/09/page-compressionrow-compression.html

sp_addarticle (Transact-SQL)
https://technet.microsoft.com/zh-tw/library/ms173857(v=sql.110).aspx

sp_articlecolumn (Transact-SQL)
sp_addarticle (Transact-SQL)
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addarticle-transact-sql

Performance Tuning SQL Server Transactional Replication: A Checklist

Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide