原文: Replication的犄角旮旯（四）--關于事務復制的監控

?

?

《Replication的犄角旮旯》系列導讀

Replication的犄角旮旯（一）--變更訂閱端表名的應用場景

Replication的犄角旮旯（二）--尋找訂閱端丟失的記錄

Replication的犄角旮旯（三）--聊聊@bitmap

Replication的犄角旮旯（四）--關于事務復制的監控

Replication的犄角旮旯（五）--關于復制identity列

Replication的犄角旮旯（六）-- 一個DDL引發的血案(上)（如何近似估算DDL操作進度）

Replication的犄角旮旯（七）-- 一個DDL引發的血案(下)（聊聊logreader的延遲）

Replication的犄角旮旯（八）-- 訂閱與發布異構的問題

Replication的犄角旮旯（九）-- sp_setsubscriptionxactseqno，賦予訂閱活力的工具

---------------------------------------華麗麗的分割線--------------------------------------------

?

最近經常被群里的朋友問到如何監控復制狀態這樣的問題；總結一下我自己的經驗吧，僅供參考；

?

關于事務復制，一般監控的內容無外乎代理的狀態（重試、失敗）、復制延遲兩類，而復制延遲又分為兩個階段（發布到分發、分發到訂閱）

?

檢測復制代理狀態

MSdistribution_agents? --其中每個在本地分發服務器上運行的分發代理對應一行。此表存儲在分發數據庫中。

http://msdn.microsoft.com/zh-cn/library/ms174399%28v=sql.120%29.aspx

?

MSdistribution_history? --包含與本地分發服務器關聯的分發代理的歷史記錄行。 此表存儲在分發數據庫中。

http://msdn.microsoft.com/zh-cn/library/ms179878%28v=sql.120%29.aspx

?

根據這兩個系統表，可以查出近期分發代理的狀態；

MSdistribution_agents中的id列與MSdistribution_history中的agent_id關聯

MSdistribution_history中的runstatus列表示運行狀態

運行狀態：

1 = 啟動。

2 = 成功。

3 = 正在進行。

4 = 空閑。

5 = 重試。

6 = 失敗。

?

如果對MSdistribution_history表的time列取最近N分鐘的記錄，與MSdistribution_agents 做right join，則可以看出近N分鐘內，是否存在不活動的分發代理；

?

檢測復制延遲

sp_replmonitorhelpsubscription --返回發布服務器上屬于一個或多個發布的訂閱的當前狀態信息，并為每個返回的訂閱返回一行。 在分發服務器上對分發數據庫執行此存儲過程，用于監視復制。

http://msdn.microsoft.com/zh-cn/library/ms188073%28v=sql.120%29.aspx

?

用法如下：

EXEC distribution.dbo.sp_replmonitorhelpsubscription NULL,NULL,NULL,0,0,0,NULL,0

其中latency表示在事務發布中，由日志讀取器代理或分發代理傳播的數據更改的最長滯后時間（秒）

盡管這個值并不能明確的表示具體是哪個階段發生的延遲（發布到分發、分發到訂閱）

?

關于復制延遲進一步的判斷

sp_replcounters??? --為每個發布數據庫返回有關滯后時間、吞吐量和事務計數的復制統計信息。 此存儲過程在發布服務器的任何數據庫中執行。

http://msdn.microsoft.com/zh-cn/library/ms190486%28v=sql.120%29.aspx

其中 Replicated transactions 列表示 日志中等待傳送到分發數據庫的事務數；也就是logreader等待從日志中讀取的事務數。如果這個值持續增長，說明logreader正處于繁忙狀態。首要檢查一下VLF是否過多，或者是否寫入量較大；

具體的處理辦法，可以參考一下高桑的《 Replication--復制延遲的診斷和解決 》

?

msrepl_commands? -- 包含復制的命令行數。 該表存儲在分發數據庫中。

http://msdn.microsoft.com/zh-cn/library/ms178611.aspx

這個表是已經從發布庫日志中讀取到信息，轉換為復制命令存儲到此表中，每個命令對應一條記錄；

如果這個表的記錄數過大（前提是publication中immediate_sync為false，且剛剛執行過分發清除代理時），則表明當前有較多的復制命令未完成分發，說明分發代理繁忙。需要檢查一下訂閱端是否存在鎖、或者較多的索引，導致分發代理效率低下；

?

關于publication中immediate_sync屬性

在默認情況下，immediate_sync是關閉的，這個屬性可以在創建publication時指定，也可以在創建完畢后修改。 如果immediate_sync為true, snapshot 文件和replicated transaction將一直保留到data retention.然后才會被刪除。這會導致distribution 數據庫增長，復制性能下降。 所以推薦設置為false. 需要注意的時，如果一個數據庫有多個publication，只要其中有一個publication的immediate_sync為true,將會導致 這個數據庫的所有publication的replicated transaction的保留期都延長至data retention.

http://blogs.msdn.com/b/sqlreplication/archive/2013/08/19/transactional-replication-immediate-sync.aspx

?

或者更準確一些，使用sp_replmonitorsubscriptionpendingcmds

sp_replmonitorsubscriptionpendingcmds? -- 返回有關對事務發布的訂閱的等待命令數以及處理這些命令的粗略估計時間的信息。 此存儲過程針對每個返回的訂閱返回一行。 在分發服務器的分發數據庫上執行此存儲過程，用于監視復制。

使用方法：

    sp_replmonitorsubscriptionpendingcmds [ @publisher = ] 'publisher'?

        , [ @publisher_db = ] 'publisher_db'?

        , [ @publication = ] 'publication'?

        , [ @subscriber = ] 'subscriber'?

        , [ @subscriber_db = ] 'subscriber_db'?

        , [ @subscription_type = ] subscription_type
    

    

    
      
        結果集
      
    
  

---

?

?

剛剛又被朋友問到，發生延遲的時候，是否能定位到是哪些表有頻繁的事務；

下面這個腳本是檢索當前msrepl_commands中命令涉及表的分布情況，基本可以定位到引起延遲的對象；

如果需要檢索最近N分鐘的情況，按照b.entry_time在CTE中取最近的N分鐘即可；

--當前msrepl_commands表中命令涉及表的分布情況
WITH cte AS(SELECT? a.xact_seqno,b.entry_time,
REPLACE(CONVERT(NVARCHAR(1024),SUBSTRING(a.command,17,1024)),'[dbo].[sp_MS','') commands

FROM dbo.MSrepl_commands a(NOLOCK)
? JOIN MSrepl_transactions b(NOLOCK) ON a.xact_seqno=b.xact_seqno

)
SELECT SUBSTRING(commands,9,CHARINDEX(']',commands)-9),COUNT(1) FROM cte WHERE CHARINDEX(']',commands)>9
GROUP BY SUBSTRING(commands,9,CHARINDEX(']',commands)-9)
ORDER BY COUNT(1) DESC

?

歡迎拍磚；

Replication的犄角旮旯（四）--關于事務復制的監控