原文地址：https://www.mssqltips.com/sqlservertip/3598/troubleshooting-transactional-replication-latency-issues-in-sql-server/

問題

我安裝了幾個SQL Server 2012實例的集群，並且在複製延遲方面遇到了問題。該環境為發佈者分配了一個專門的SQL Server實例，一個實例具有發佈者資料庫，另一個實例具有訂閱者資料庫。據報道，大多數情況下複製中的延遲很高。我還註意到，在分發伺服器上有很多阻塞，並且CPU出現很大的峰值。

解決方案

解決延遲問題不是一個簡單的流程，您需要收集大量的數據，分析數據，依次進行更改，然後監視，以查看您是否已解決問題。這是一個連續的過程，直到您獲得可接受的延遲。

理解事務複製中的數據流

在我們開始之前，理解SQL Server事務複製的數據流程將會有所幫助。SQLServer的事務複製主要有三個組件：

發佈者（Publisher）　　- 需要複製數據的資料庫/伺服器
分發者（Distributor）　　- 存儲臨時複製數據的資料庫/伺服器
訂閱者（Subscriber）　　- 消費複製數據的目標資料庫/伺服器

通常，在高使用的OLTP系統中，每個組件都是一個專用的SQL Server，以支持高可用性。

圖1顯示了事務複製的體繫結構。

　　　Figure 1 - Replication Architecture (BOL: http://msdn.microsoft.com/en-us/library/ms151176.aspx)

監控SQLServer 事務複製

有必要實施延遲告警，以監控並提示延遲是否高於您定義的某個閾值。根據您的環境以及與用戶的業務服務水平協議(SLA)，它可能是5分鐘、10分鐘甚至幾秒鐘。在解決延遲問題時，這一點非常重要。延遲報告應包含有關總延遲、發佈者和分發者之間的延遲、分發者和訂閱者之間的延遲的信息，因此您可以確切地知道複製的哪個部分存在問題。

跟蹤令牌通常用於測量延遲。您可以使用複製監視器（RM）為每個發佈插入跟蹤令牌。或者，您也可以使用T-SQL命令。

有關跟蹤令牌的更多詳細信息，請參閱此BOL：http://technet.microsoft.com/en-us/library/ms151846(v=sql.105).aspx

如何獲取複製延遲

 以下是sp_replcounters在性能良好的環境中的輸出。

Table 1 - sp_replcounters輸出結果示例

• Database - 發佈資料庫
• Replicated transactions -日誌中等待傳遞到分發資料庫的事務數
• Replication rate trans/sec - 平均每秒向分發資料庫傳遞的事務數
• Replication latency - 事務在分發之前記錄在日誌中的平均時間(以秒為單位)
• Replbeginlsn - 日誌中當前截斷點的LSN (Log sequence number)
• Replendlsn - 等待發送到分發資料庫的下一個提交記錄的LSN

使用上述信息，您可以確定總體複製延遲有多好。“"replication transactions rate/sec”的值越高，複製的數據傳輸速度就越好。“複製延遲(秒)”列的數值也很低。

性能較差的複製系統的輸出示例如表2所示。

Table 1 - sp_replcounters輸出複製延遲較差的示例

在這種情況下，您可以看到延遲超過2.5小時(請參閱複製延遲列9232秒)。同時可以看到數據傳輸速率相當不錯(1612.123)。那麼問題出在哪裡呢?查看複製的事務，它超過1100萬個，這意味著有超過1100萬個命令等待傳遞到分發資料庫。換句話說，它們仍然在發佈者資料庫的事務日誌(T-Log)中。所以在這種特殊情況下，延遲主要發生在發佈者和分發者之間。如果您配置了延遲報告，它將顯示發佈者和分發者之間的高延遲值。

如果你看到像上面(表2)這樣奇怪的高數字，這可能是由於以下原因:

• 在發佈者資料庫中發生了大型事務
• 性能較慢的網路
• 性能較慢的存儲器

如果您在輸出中看到數百萬個等待命令，並且您認為這不是由於網路慢、存儲慢或發佈者的意外OLTP操作造成的，那麼問題可能是與發佈者資料庫的T-Log配置有關。

記住，複製是SQL Server中基於日誌的操作之一。因此，發佈者資料庫的t-log配置與複製的性能密切相關。名為Log Reader的程式掃描t-log以識別要複製的命令(參見圖1)。因此，在這種情況下，您需要註意t-log的大小，根據發佈者的事務數量、t-log的vlf數量和vlf的大小是否合適。對於複製，所有這些參數都很重要。就vlf的數量而言，確定t-log的“最佳點”是相當具有挑戰性的。下麵的鏈接可能會有所幫助。

• https://www.mssqltips.com/sqlservertip/1225/how-to-determine-sql-server-database-transaction-log-usage/
• http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/

SQLServer日誌讀取代理

日誌讀取器是一個在分發伺服器上執行的掃描發佈資料庫T-Log的可執行程式。有兩個線程執行這項工作：

讀取器線程 - 通過存儲過程sp_replcmds讀取T-Log。這掃描T-Log並識別要複製的命令，跳過不需要複製的命令。
寫入器線程 - 通過sp_MSadd_replcmds將讀取器線程識別的事務日誌寫入分發資料庫。
這兩個存儲過程都是在配置事務複製時創建的系統存儲過程。日誌讀取器代理配置文件有一些參數，您可以使用這些參數更改日誌讀取器的行為，從而可以更改複製行為。仔細查看日誌讀取器的參數值是排除複製問題（包括延遲）的重要部分。

Fore more details: BOL: http://msdn.microsoft.com/en-us/library/ms146878.aspx

如何查看日誌讀取代理配置文件

在SSMS中，連接到分發伺服器。右鍵單擊Replication，然後單擊Properties。(參閱圖2及圖3)

 Figure 2 - Get distributor properties

 Figure 3 - Distributor properties

單擊Figure 3所示的分發伺服器屬性視窗中的Profile Defaults。將顯示Agent Profiles視窗，如圖4所示。

 Figure 4 - Agent Profiles

“代理配置文件”視窗的右窗格包含所有複製代理配置文件。從列表中選擇日誌讀取器代理，您將看到日誌讀取器的配置文件。選中的是當前正在使用的，您可以單擊…來獲取Log Reader Agent Profile的配置值，如下麵的圖5所示。

Figure 5 - Profile Parameters

註意:當您更改日誌閱讀器屬性時，它們將不會生效，直到您重新啟動SQL Server代理。

日誌閱讀器代理配置文件的重要參數

在事務性複製系統的微調過程中，需要調整某些參數。

• continuous -指定代理是否嘗試連續輪詢複製的事務。如果指定，即使沒有掛起的事務，代理也會以輪詢間隔輪詢源的複製事務。
• historyverboselevel[0| 1| 2] -指定在日誌讀取器操作期間記錄的歷史數量。您可以通過選擇1來最小化歷史日誌記錄的性能影響。
• maxcmdsintran -指定日誌閱讀器向分佈資料庫寫入命令時，分組到事務中的最大語句數。使用此參數允許日誌讀取器代理和分發代理在訂閱伺服器上應用時，將發佈伺服器上的大事務(由許多命令組成)劃分為幾個較小的事務。指定此參數可以減少分發伺服器上的爭用，並減少發佈伺服器和訂閱伺服器之間的延遲。由於原始事務以較小的單元應用，因此訂閱伺服器可以在原始事務結束之前訪問大型邏輯發佈者事務的行，從而打破嚴格的事務原子性。預設值為0，它保留髮布伺服器的事務邊界。
•  pollinginterval—查詢複製事務日誌的頻率(以秒為單位)。預設值是5秒（譯者註：輪訓的頻率是5秒鐘，這意味著SQLServer的事務複製的最大延遲會超過5秒，因為日誌讀取器讀取到日誌之後要寫入distribute庫，再由日誌寫入器寫入分發資料庫，然後再推送（拉取）的方式寫入訂閱者資料庫，這個過程）。
• readbatchsize -每個處理周期從發佈資料庫的事務日誌中讀出的最大事務數，預設為500。代理將繼續分批讀取事務，直到從日誌中讀取所有事務。Oracle publisher不支持此參數。
• readbatchthreshold—分發代理髮送到訂閱伺服器之前要從事務日誌中讀取的複製命令的數量。預設值為0。如果未指定此參數，則日誌讀取代理將讀取到日誌的末尾或-ReadBatchSize(事務數)中指定的數量。

如何確定日誌閱讀器代理配置文件設置

您可以查詢分佈資料庫中的MSLogreader_history表，以查看日誌讀取器的統計信息。通過分析這些數據，您可以確定日誌讀取器的性能。你可以使用下麵的查詢;

USE distribution 
GO 

SELECT time, 
CAST(comments AS XML) AS comments, 
runstatus, 
duration, 
xact_seqno, 
delivered_transactions, 
delivered_commands, 
average_commands, 
delivery_time, 
delivery_rate, 
delivery_latency / ( 1000 * 60 ) AS delivery_latency_Min 
FROM mslogreader_history WITH (nolock) 
WHERE time > '2014-10-28 16:00:00.130' 
ORDER BY time DESC 

很難附加一個樣本輸出，因為輸出非常寬。然而，我想強調其中的一些欄位。

查看下麵Comments欄中的值。它包含有價值信息的xml片段。Comments列提供了關於日誌閱讀器執行情況的信息。下表顯示了複製環境中實際數據的六個不同樣例記錄。看看第2、3和6行。它通過狀態1、2和3消息顯示更多信息。

如果您看到很多類似於“在第4條中掃描了大約250萬條日誌記錄，其中有0條被標記為複製”的消息，這意味著日誌讀取器代理找到了0條要複製的記錄。這實際上意味著在發佈伺服器中進行的許多操作沒有被標記為複製。在這種情況下，增加-ReadBatchSize參數將是有益的。該參數的預設值是500，但是您可以將該值增加幾千來掃描更多的t-log記錄，因為大多數情況下您找不到太多需要複製的數據。

 下麵是這些status值的含義:

• status 1 -正常活動。沒什麼好擔心的
• status 2 -讀線程必須等待寫線程。有一些問題
• status 3 -寫線程必須等待讀線程。有一些問題

使用這些消息，您可以將日誌讀取器代理性能分析確定為讀取器或寫入器線程問題。您需要知道的另一個重要數據列是“xact_seqno”，它是最後處理的事務序列號。看看這個值，你會發現它經常變化。如果是這樣，則複製命令的處理速度很快。有時，您可能會在xact_seqno列中長時間看到相同的值，甚至可能持續幾個小時。這表明發佈者資料庫中發生了一個大型事務，導致了大型DML活動。您可以使用下麵的代碼片段識別事務的實際命令。

USE distribution 
go 
EXEC Sp_browsereplcmds 
@xact_seqno_start = '0x0008BF0F008A6D7F00AA', 
@xact_seqno_end = '0x0008BF0F008A6D7F00AA', 
@publisher_database_id = 10 

@publisher_database_id可能與發佈者伺服器的資料庫id不同。在執行上面的代碼之前，您需要首先知道這一點。使用下麵的代碼找到publisher_database_id。

USE distribution
GO

SELECT * FROM dbo.MSpublisher_databases

--或者

USE distribution 
go 
SELECT TOP 1 publisher_database_id 
FROM msrepl_commands 
WHERE xact_seqno = '0x0008BF0F008A6D7F00AA' 

註意:此發佈者database  id與發佈資料庫中的sys.databases中的database id不同。

請參考sp_browsereplcmd查詢的命令列，查看實際執行的命令。通過這種方式，您可以更好地瞭解在複製緩慢時正在發生的情況。

如果事務有數百萬個DML活動，則運行sp_browsereplcmd查詢需要時間。此外，您可以使用@article_id或@command_id或兩者過濾記錄，如下所示;

USE distribution 
go 
EXEC Sp_browsereplcmds 
@xact_seqno_start = '0x0008BF0F008A6D7F00AA', 
@xact_seqno_end = '0x0008BF0F008A6D7F00AA', 
@publisher_database_id = 10, 
@article_id = 1335, 
@command_id= '1000000' 

特定複製表有多大

分發資料庫有許多表來支持SQL Server複製。知道它們有多大是很重要的。至少是最重要的之一。這應該是您的故障排除工作的一部分。我通常使用下麵的查詢來查看事務複製中最中心的表的記錄計數。

USE distribution 
GO 
SELECT Getdate() AS CaptureTime, 
Object_name(t.object_id) AS TableName, 
st.row_count, 
s.NAME 
FROM sys.dm_db_partition_stats st WITH (nolock) 
INNER JOIN sys.tables t WITH (nolock) 
ON st.object_id = t.object_id 
INNER JOIN sys.schemas s WITH (nolock) 
ON t.schema_id = s.schema_id 
WHERE index_id < 2 
AND Object_name(t.object_id) 
IN ('MSsubscriptions', 
'MSdistribution_history', 
'MSrepl_commands', 
'MSrepl_transactions', 
) 
ORDER BY st.row_count DESC 

mssubscriptions　　為訂閱中的每個已發佈的文章包含一行

msdistribution_history　　包含與本地分發器相關聯的分發代理的歷史記錄行

msrepl_commands　　包含複製的命令行

msrepl_transactions　　為每個複製的事務包含一行

如果您看到較高的行數(可能超過100萬或200萬)，這意味著複製存在一些問題。這可能是以下原因之一:

1，清理作業(在分發伺服器中)未運行

2，向訂閱者發送命令要花很多時間

3，由於 clean-up job（正在執行），可能造成分發伺服器出現阻塞

（譯者註： 對於清理作業，該代理每隔 10 分鐘(預設值) 清除分發資料庫中的代理歷史記錄。根據分發的大小不同，可改變該代理的執行頻率。 刪除複製過程中publication和replication自身產生的歷史數據，預設刪除24小時之外的數據，參考如下截圖

）

使用下麵的查詢來確定當前在分發伺服器中正在發生什麼。(您可以在任何伺服器上使用這個查詢)

SELECT r.session_id, 
s.program_name, 
s.login_name, 
r.start_time, 
r.status, 
r.command, 
Object_name(sqltxt.objectid, sqltxt.dbid) AS ObjectName, 
Substring(sqltxt.text, ( r.statement_start_offset / 2 ) + 1, ( ( 
CASE r.statement_end_offset 
WHEN -1 THEN 
datalength(sqltxt.text) 
ELSE r.statement_end_offset 
END 
- r.statement_start_offset ) / 2 ) + 1) AS active_statement,
r.percent_complete, 
Db_name(r.database_id) AS DatabaseName, 
r.blocking_session_id, 
r.wait_time, 
r.wait_type, 
r.wait_resource, 
r.open_transaction_count, 
r.cpu_time,-- in milli sec 
r.reads, 
r.writes, 
r.logical_reads, 
r.row_count, 
r.prev_error, 
r.granted_query_memory, 
Cast(sqlplan.query_plan AS XML) AS QueryPlan, 
CASE r.transaction_isolation_level 
WHEN 0 THEN 'Unspecified' 
WHEN 1 THEN 'ReadUncomitted' 
WHEN 2 THEN 'ReadCommitted' 
WHEN 3 THEN 'Repeatable' 
WHEN 4 THEN 'Serializable' 
WHEN 5 THEN 'Snapshot' 
END AS Issolation_Level, 
r.sql_handle, 
r.plan_handle 
FROM sys.dm_exec_requests r WITH (nolock) 
INNER JOIN sys.dm_exec_sessions s WITH (nolock) 
ON r.session_id = s.session_id 
CROSS apply sys.Dm_exec_sql_text(r.sql_handle) sqltxt 
CROSS apply 
sys.Dm_exec_text_query_plan(r.plan_handle, r.statement_start_offset, r.statement_end_offset) sqlplan
WHERE r.status <> 'background' 
ORDER BY r.session_id 
go 

如果您看到LCK_M_S等待阻塞，這可能是由於清理工作。此作業（clean-up job）每10分鐘運行一次，它會清除已經複製的命令。安全的做法是停止並禁用該作業幾個小時以清除阻塞。

我經常註意到阻塞源頭程式是sp_MSsubscription_cleanup(這是來自sp_MSdistribution_cleanup的嵌套存儲過程調用，sp_MSdistribution_cleanup是“分發清理”作業)。您還可以註意到上述CXPACKET等待類型的存儲過程，它阻塞了以下語句。

UPDATE msdistribution_history 
SET runstatus = @runstatus, 
time = @current_time, 
duration = @duration, 
comments = @comments, 
xact_seqno = @xact_seqno, 
updateable_row = @this_row_updateable, 
error_id = CASE @error_id 
WHEN 0 THEN error_id 
ELSE @error_id 
END 
WHERE agent_id = @agent_id 
AND timestamp = @lastrow_timestamp 
AND ( runstatus = @runstatus 
OR ( @update_existing_row = 1 
AND runstatus IN ( @idle, @inprogress ) 
AND @runstatus IN ( @idle, @inprogress ) ) )

上面語句的等待類型是LCK_M_X，等待資源是MSdistribution_history表。這個表在阻塞源存儲過程中使用，並且它已經獲得了大多數行的共用鎖。我覺得MS需要對這段代碼進行一些優化。當我比較2008和2012版本的SQL Server之間的清理工作存儲過程時，我註意到它在2012版本中增加了一倍的代碼行。

同時，您也可能會註意到分發伺服器中的高CPU，這是由於上述阻塞器源造成的許多阻塞。除了停止和禁用clean-up job一段時間外，您真的無能為力。您也可以嘗試將發行版伺服器中的MAXDOP設置為1，以降低CPU使用率。

改善分發者和訂閱者之間的延遲

 再次感謝延遲報告。如果您確定複製延遲是在分發伺服器和訂閱伺服器之間，那麼值得考慮以下幾點。

Publishing Stored Procedure Execution

這在對發佈者執行大型批處理操作(例如:DELETE)的情況下特別有用。我曾見過這樣的情況，由於大規模的批量刪除，數百萬行受到影響，並且在它們發生的那一刻，它開始將命令傳遞給分發伺服器，然後是訂閱者。這會減慢複製速度，並且您會註意到延遲增加。使用此方法，可以在訂閱伺服器上執行相同的大型批處理操作，而不是通過分發伺服器傳遞單個命令。但是，在實現此解決方案之前，您需要花時間進行一些研究，並評估這對於您的環境的可行性。有很多因素需要你註意。

瞭解更多詳情，請訪問 http://msdn.microsoft.com/en-us/library/ms152754.aspx

Enable Multiple Streams for Subscriber

為訂閱者啟用多個流可以通過並行應用訂閱者更改來極大地提高聚合事務性複製吞吐量。在將其投入生產之前，您仍然需要考慮許多因素，並且需要做一些功課。

瞭解更多詳情，請訪問http://technet.microsoft.com/en-us/library/ms151762(v=sql.105).aspx

Maintain Indexes and Statistics in Distribution Database

分發資料庫在SSMS中屬於系統資料庫。然而，需要某種程度的DBA干預來保持分佈資料庫的良好狀態。分佈資料庫與普通用戶資料庫一樣具有表、索引和統計信息。我們知道，索引需要維護(重建/重組)，並且需要在用戶資料庫中運行更新統計信息，那麼為什麼不在分佈資料庫中進行相同的操作呢?清理存儲過程有自己的統計數據更新語句，以使統計數據保持最新，但不是所有統計數據都是最新的。將索引和統計更新作業部署到分佈資料庫，並安排它們在非高峰時間運行，就像在用戶資料庫中所做的那樣，這是完全可以的。按照MS的建議，我已經在生產環境中這樣做了。

Distribution Agent 性能

您可以查詢MSdistribution_history表來查看Distribution Agent的執行情況。

USE distribution 
go 
SELECT TOP 100 time, 
Cast(comments AS XML) AS comments, 
runstatus, 
duration, 
xact_seqno, 
delivered_commands, 
average_commands, 
current_delivery_rate, 
delivered_transactions, 
error_id, 
delivery_latency 
FROM msdistribution_history WITH (nolock) 
ORDER BY time DESC 

上述查詢的輸出類似於Log Reader歷史表的輸出。查看Comments列的值。如果您看到狀態為1的消息，則表示分發代理正常運行。使用xact_seqno可以識別複製的命令。如果您註意到xact_seqno的相同值持續了很長時間，這意味著它正在複製一個大事務。

Distribution Agent 配置文件

與日誌讀取器代理配置文件一樣，分發伺服器上也有分發代理配置文件。如果您從右窗格打開Agent Profiles視窗(參見圖4)，您可以選擇Distribution Agents來查看配置文件。您可以調整代理的參數值來更改複製行為。您可以在發佈級別執行此操作，也可以應用於所有發佈。它需要在分發伺服器中重新啟動SQL Server Agent才能生效。

以下是一些你可以考慮調整的參數:

• - commitbatchsize -在發出COMMIT語句之前要發送給訂閱者的事務數。預設值是100。
• - commitbatchthreshold—在發出COMMIT語句之前要向訂閱伺服器發出的複製命令的數量。預設值是1000。
• - historyverbosellevel[0 | 1 | 2 | 3] -指定在分發操作期間記錄的歷史數量。您可以通過選擇1來最小化歷史日誌記錄的性能影響。
• - maxdeliveredtransactions -在一次同步中應用於訂閱者的推或拉事務的最大數量。值為0表示最大事務數為無限大。訂閱伺服器可以使用其他值來縮短從發佈伺服器提取同步的持續時間。
• - pollinginterval—以秒為單位，查詢發佈資料庫中複製事務的頻率。預設值是5秒。
• -SubscriptionStreams[0 | 1 | 2 |…[64] -每個分發代理允許的連接數，以並行地向訂閱伺服器應用批量更改，同時保持使用單個線程時存在的許多事務特征。SQL Server Publisher支持的取值範圍是1 ~ 64。僅當發佈伺服器和分發伺服器運行在SQL Server 2005或更高版本時，才支持此參數。對於非sql Server訂閱用戶或點對點訂閱，該參數不支持，必須為0。

更多詳細信息:BOL: http://msdn.microsoft.com/en-us/library/ms147328.aspx

Next Steps

• 如果您仍然看到分發伺服器的高CPU、分發伺服器的持續阻塞、甚至在完成本文中建議的更改後延遲增加等問題，您可能需要進行更多的分析，並且可能需要向微軟提出申訴。
• 有關進一步分析，請參閱此鏈接。它有指令/腳本來收集更多的數據。
• 查看所有SQL Server複製技巧。