原文: Replication的犄角旮旯（四）--關(guān)于事務(wù)復(fù)制的監(jiān)控

?

?

《Replication的犄角旮旯》系列導(dǎo)讀

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

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

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

Replication的犄角旮旯（四）--關(guān)于事務(wù)復(fù)制的監(jiān)控

Replication的犄角旮旯（五）--關(guān)于復(fù)制identity列

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

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

Replication的犄角旮旯（八）-- 訂閱與發(fā)布異構(gòu)的問題

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

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

?

最近經(jīng)常被群里的朋友問到如何監(jiān)控復(fù)制狀態(tài)這樣的問題；總結(jié)一下我自己的經(jīng)驗吧，僅供參考；

?

關(guān)于事務(wù)復(fù)制，一般監(jiān)控的內(nèi)容無外乎代理的狀態(tài)（重試、失敗）、復(fù)制延遲兩類，而復(fù)制延遲又分為兩個階段（發(fā)布到分發(fā)、分發(fā)到訂閱）

?

檢測復(fù)制代理狀態(tài)

MSdistribution_agents? --其中每個在本地分發(fā)服務(wù)器上運行的分發(fā)代理對應(yīng)一行。此表存儲在分發(fā)數(shù)據(jù)庫中。

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

?

MSdistribution_history? --包含與本地分發(fā)服務(wù)器關(guān)聯(lián)的分發(fā)代理的歷史記錄行。 此表存儲在分發(fā)數(shù)據(jù)庫中。

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

?

根據(jù)這兩個系統(tǒng)表，可以查出近期分發(fā)代理的狀態(tài)；

MSdistribution_agents中的id列與MSdistribution_history中的agent_id關(guān)聯(lián)

MSdistribution_history中的runstatus列表示運行狀態(tài)

運行狀態(tài)：

1 = 啟動。

2 = 成功。

3 = 正在進(jìn)行。

4 = 空閑。

5 = 重試。

6 = 失敗。

?

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

?

檢測復(fù)制延遲

sp_replmonitorhelpsubscription --返回發(fā)布服務(wù)器上屬于一個或多個發(fā)布的訂閱的當(dāng)前狀態(tài)信息，并為每個返回的訂閱返回一行。 在分發(fā)服務(wù)器上對分發(fā)數(shù)據(jù)庫執(zhí)行此存儲過程，用于監(jiān)視復(fù)制。

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

其中l(wèi)atency表示在事務(wù)發(fā)布中，由日志讀取器代理或分發(fā)代理傳播的數(shù)據(jù)更改的最長滯后時間（秒）

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

?

關(guān)于復(fù)制延遲進(jìn)一步的判斷

sp_replcounters??? --為每個發(fā)布數(shù)據(jù)庫返回有關(guān)滯后時間、吞吐量和事務(wù)計數(shù)的復(fù)制統(tǒng)計信息。 此存儲過程在發(fā)布服務(wù)器的任何數(shù)據(jù)庫中執(zhí)行。

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

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

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

?

msrepl_commands? -- 包含復(fù)制的命令行數(shù)。 該表存儲在分發(fā)數(shù)據(jù)庫中。

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

這個表是已經(jīng)從發(fā)布庫日志中讀取到信息，轉(zhuǎn)換為復(fù)制命令存儲到此表中，每個命令對應(yīng)一條記錄；

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

?

關(guān)于publication中immediate_sync屬性

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

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

?

或者更準(zhǔn)確一些，使用sp_replmonitorsubscriptionpendingcmds

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

使用方法：

    sp_replmonitorsubscriptionpendingcmds [ @publisher = ] 'publisher'?

        , [ @publisher_db = ] 'publisher_db'?

        , [ @publication = ] 'publication'?

        , [ @subscriber = ] 'subscriber'?

        , [ @subscriber_db = ] 'subscriber_db'?

        , [ @subscription_type = ] subscription_type
    

    

    
      
        結(jié)果集
      
    
  

---

?

?

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

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

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

--當(dāng)前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的犄角旮旯（四）--關(guān)于事務(wù)復(fù)制的監(jiān)控