橄欖葉子

就以前經驗，會使用預存程序來處理資料，常是基於公司政策(為降低SQL Injection風險)、可利用Procedure Cache改善效能(使用預先編譯好的execution plan)、隱藏商業邏輯處理(讓你看不到如何存取資料表)、降低T-SQL指令碼的長度以節省網路頻寬等等原因，所以心中想著當發行者端(Publisher)執行大量資料修改時，若能用預存程序來同步處理資料，也許可以減少Log Reader Agent及Distribution Agent的工作量，即降低複寫時所套用的交易(Transactions)及命令(Commands)數量。

為了建立一個交易式複寫來測試，首先我在Northwind資料庫中建了一個[Order DetailsTemp]資料表，內含2155筆資料，記得建立Primary Key，否則無法將該資料表指定為發行項(Article)喔！

--建Order DetailsTemp資料表
use [Northwind]
go
select * into [dbo].[Order DetailsTemp] from [dbo].[Order Details]

--建clustered index
ALTER TABLE dbo.[Order DetailsTemp] ADD CONSTRAINT
	[PK_Order DetailsTemp] PRIMARY KEY CLUSTERED 
	(
	OrderID,
	ProductID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

接著透過UI介面手動建立交易式複寫的發行集及訂閱(建立步驟就不贅述了)，主要是將[Order DetailsTemp]資料表由USER-PS\SQL2012STD(以下稱Publisher)同步到USER-PS\SQL2012STD_2(以下稱Subscriber)，完成後如下圖。

可開啟[檢視記錄讀取器代理程式的狀態]及[檢視同步處理的狀態]這兩個視窗，並配合執行一段update語法，來確認複寫功能是否正常運作

begin
update [dbo].[Order DetailsTemp] set [UnitPrice]=[UnitPrice] + 10
update [dbo].[Order DetailsTemp] set [Quantity]=[Quantity] + 5 
end

後面打算用兩個情境來測試：(1)直接以update語法執行測試  (2)將update語法改寫成預存程序並套用至複寫機制來測試，藉以觀察兩者對交易(Transactions)及命令(Commands)數量的變化有何差異，另外輔以複寫監視器、SQL Server Profiler及效能監視器等工具來觀察。

<情境一>直接以update語法執行測試

在開始執行update語法前，我們要先做一些事前準備的工作

(1)設定SQL Server Profiler：

在散發者端(Distributor)，開啟Profiler來追蹤散發代理程式(Distribution Agent)的排程，以觀察它從distribution系統資料庫擷取哪些資料，選用預設的Standsrd範本即可

過濾條件為在 ApplicationName 欄位的[類似]項目，填上 '散發代理程式的名稱'，例如：我的環境是鍵入'%USER-PC\SQL2012STD-Northwind-SQL2012STD_to%'

可以參考下圖，在SQL Server Agent中找到散發代理程式的名稱

接著在訂閱者端(Subscriber)，另外開啟一個Profiler來追蹤[同步處理程式]的處理(你在SQL Server Agent中看不到同步程式的排程，但可以在Profiler找到該[同步處理程式]的名稱)，以觀察它如何套用指令(Commands)至Subscriber，跟前面一樣選用預設的Standsrd範本即可

因為後面的測試只會跑update的語法，為縮小範圍條件以方便檢視，所以在TextData欄位的[類似]項目，填上 '%sp_MSupd_%' 即可

散發代理程式主要分成兩塊在處理複寫事務，第一個部分是透過sp_MSget_repl_commands預存程序，從distribution資料庫讀取需要執行同步的命令，另一個部分是透過SP_MSins%、SP_MSupd%、SP_MSdel%預存程序，藉以異動訂閱者端資料表的資料。

(2)設定效能監視器：

請依下列清單設定效能計數器，來觀察Log Reader Agent及Distribution Agent (在我的Lab環境，發行者及散發者都在同一個執行個體，即"MSSQL$SQL2012STD")

物件:   MSSQL$SQL2012STD:Replication Dist.
計數器:  Dist:Delivered Cmds/sec
       Dist:Delivered Trans/sec
       Dist:Delivery Latency
        
指定例項: USER-PC\SQL2012STD-Northwind-SQL2012STD_to_SQL2012-USER-PC\SQL2012STD_2-11  -->記得要明確指定要追蹤的[散發代理程式排程的名稱]

------------------------------------------------------------------------------------------

物件:   MSSQL$SQL2012STD:Replication Logreader  
計數器:  Logreader:Delivered Cmds/sec
       Logreader:Delivered Trans/sec
       Logreader:Delivery Latency
        
指定例項: USER-PC\SQL2012STD-Northwind-7   -->記得要明確指定要追蹤的[記錄讀取器代理程式排程的名稱]

請參考以下表格說明，來瞭解這6個計數器所代表的意義

記錄讀取器代理程式	SQL Server︰ 複寫記錄讀取器	Logreader:Delivered Cmds/sec	每秒傳送到散發者的命令數。
記錄讀取器代理程式	SQL Server︰ 複寫記錄讀取器	Logreader:Delivered Trans/sec	每秒傳送到散發者的交易數。
記錄讀取器代理程式	SQL Server︰ 複寫記錄讀取器	Logreader:Delivery Latency	「發行者」套用交易時，該交易傳送至「散發者」所花費的時間，以毫秒為單位。
散發代理程式	SQL Server: Replication dist.	Dist:Delivered Cmds/sec	每秒傳遞至「訂閱者」的命令數。
散發代理程式	SQL Server: Replication dist.	Dist:Delivered Trans/sec	每秒傳遞至「訂閱者」的交易數。
散發代理程式	SQL Server: Replication dist.	Dist:Delivery Latency	目前從交易傳送到分配者到交易在「訂閱者」套用所經過的時間 (毫秒)。

設定完成後如下圖

(3)執行update語法，以模擬大量資料更新

終於可以開始來執行資料更新了，以下語法是簡單的來更新[Order DetailsTemp]資料表內的2155筆資料的價格及數量，且連續執行40次

use [Northwind]
go
begin
   update [dbo].[Order DetailsTemp] set [UnitPrice]=[UnitPrice] + 10
   update [dbo].[Order DetailsTemp] set [Quantity]=[Quantity] +5 
end 
go 40

(4)執行update語法後的觀察

在複寫監視器中，可看到[未散發的命令]共有172,400個 (資料2,155筆 x 2個update語法 x 執行40次 = 172,400)，這些是還沒套用到訂閱者端的命令數，還在distribution database中等待處理

檢視Log Reader Agent的處理狀態，也確實傳遞了80個Transactions(含172,400個Commands)至distribution資料庫

在散發者端的Profiler中觀察[散發代理程式排程]讀取distribution資料庫的情形，至少耗費了137秒以上

在訂閱者端的Profiler中觀察[同步處理程式]的執行情形，它對訂閱者端(Subscriber)套用的exec sp_MSupd%語法超過17萬次，很可觀

若仔細觀察[效能監視器]會發現到，一開始Logreader:Delivered Trans/sec及Logreader:Delivered Cmds/sec的數值先同時急速攀升，沒多久Dist:Delivered Trans/sec及Dist:Delivered Cmds/sec的數值也會接著衝高，完全符合Log Reader Agent先擷取Transaction Log至distribution database，而Distribution Agent接著擷取distribution database並執行同步處理至訂閱者端的處理順序。

另外，觀察Dist:Delivery Latency計數器的數值變化，表示從交易傳送至「散發者」開始，到交易在「訂閱者」套用所經過的時間，一直在持續攀升，顯示套用交易至「訂閱者」已有塞車的徵兆。

 

<情境二>將update語法改寫成預存程序並套用至複寫機制

前面所設定好的SQL Server Profiler及效能監視器，等一下還會用到，所以先不要關掉視窗。在發行者端所設定的Profiler維持不變，但是在訂閱者端的Profiler設定，需要修改一下篩選條件，請將TextData欄位的[類似]項目，改填上 '%UpdteData%' ，"UpdteData"是我們等一下會建立的預存程序名稱，接著再手動清空所有SQL Server Profiler先前的收錄內容。

(1)在發行者端建立一支新預存程序：

USE [Northwind]
GO
create procedure UpdteData
as
begin
    declare @a smallint =0
    while @a<40
	   begin
               update [dbo].[Order DetailsTemp] set [UnitPrice]=[UnitPrice] + 10
               update [dbo].[Order DetailsTemp] set [Quantity]=[Quantity] +5 
               set @a=@a+1;
	   end;
end;

(2)修改複寫相關設定：

(a)將剛剛新建立的預存程序加入發行集中，並修改其[複寫]屬性由[僅預存程序定義]變成[預存程序的執行]，如此，當此預存程序被修改(Alter)或被執行(Exec)時，都會被套用至訂閱者端。
(b)在訂閱上按下滑鼠右鍵，點選[重新初始化]

並啟動快照集代理程式，此時，新加入預存程序將會被同步到訂閱者端(Subscriber)

(2)在發行者端執行預存程序

USE [Northwind]
GO
exec UpdteData

(3)執行預存程序後的觀察

觀察[檢視記錄讀取器代理程式的狀態]及[檢視同步處理的狀態]這兩個視窗，都只傳遞了1個交易(內含1個命令)，實在是精簡

在散發者端的Profiler中觀察[散發代理程式排程]讀取distribution資料庫的情形，其實並沒有像<情境一>的範例那樣，需要費時137秒來處理，所有指令幾乎都在數毫秒就處理完畢。下圖資料反白處即是散發代理程式正從distribution database中擷取該筆"執行UpdteData預存程序"的記錄。

有人可能會問，那怎麼確認該筆指令就是"執行UpdteData預存程序"的記錄呢? 首先，先將該筆指令複製到查詢視窗，sp_MSget_repl_commands這個預存程序被指定了幾個參數值，如下：
exec sp_MSget_repl_commands 11,0x0000021A00003558000300000000,0,10000000

--查看sp_MSget_repl_commands預存的內容，第二參數值是[交易序號]
exec sp_helptext 'sp_MSget_repl_commands'

--利用該交易序號，並透過sp_browsereplcmds預存程序，找出該交易的實際執行內容，可以看到[command]欄位的值顯示{call "dbo"."UpdteData" }，該交易確實是執行我們自建的預存程序。

exec distribution.dbo.sp_browsereplcmds
@xact_seqno_start = '0x0000021A00003558000300000000',
@xact_seqno_end = '0x0000021A00003558000300000000'

在訂閱者端的Profiler中觀察[同步處理程式]的執行情形，它對訂閱者端僅套用了一次 exec "dbo"."UpdteData"語法，所耗費的資源亦不高，也不像<情境一>範例需要逐筆執行超過17萬次的資料更新。

最後來看[效能監視器]，Logreader:Delivered Cmds/sec、Logreader:Delivered Trans/sec、Dist:Delivered Cmds/sec及Dist:Delivered Trans/sec的最大值都是1，因為所套用的交易(Tranactions)及命令(commands)都只有一個而已。

Logreader:Delivery Latency及Dist:Delivery Latency的最大值大概都在3秒上下，比較<情境一>範例確實改善不少。

[小結]：

當在採用交易式複寫時，若常有需批次更新大量資料的需求，適當地使用預存程序，也許可以幫忙緩解交易塞車的問題喔。

 

參考資料：

https://msdn.microsoft.com/zh-tw/library/ms151754.aspx

 

Jay Huang