SQL Server Transactional replication distribution to subscription synchronization slow

Original: SQL Server transactional replication distribution to subscription synchronization slow

Recently found that there is a release frequently problems, every few days the error is not synchronized, prompting the request to initialize. After you re-adjust the synchronization, the copy is still slow! 5 million or 6 million orders are not distributed every day! To resolve slow issues, you need to understand what is going on from the publication database to the subscription database before you know which step is slow to synchronize.

This is a diagram that I made a long time ago, mainly describing what operations are used for publishing to distribution, distribution, and replication, such as:

Publish to Distribution:

In a publication, replication uses the Log reader read (SP_REPLCMDS) to fetch the transaction logs in the publication database, and the log reader is read in transactional order, so there can only be one per database (if the CDC also uses the same log reader).

Preferred, in the transaction log, exactly how much is needed to replicate? Use the following command to determine how many commands in the transaction log are flagged for replication.

Use <publisher_database>goselect count (*) From:: Fn_dblog (NULL, NULL) WHERE description= ' REPLICATE ' GO

If there are many commands in the transaction log that are marked as duplicated, the common scenarios are:

1. There is a larger transaction that has not yet been read;

2. There is a problem with the log reader;

Normally, the Log Reader scan log is still relatively fast, there is no problem, there will not be more log to read. You can use performance counters to monitor Log reader reads:

"\sqlserver:replication logreader\logreader:delivered cmds/sec"

"\sqlserver:replication logreader\logreader:delivered trans/sec"

After the date reader reads the transaction log, it writes through the stored procedure sp_msadd_commands to the distribution database distribution at the Distributor. But is it normal to write? From the above counter tracking situation, you can estimate how many commands are read per minute. The transactions and commands that need to be distributed, the system stored procedures are written to the tables msrepl_transactions and msrepl_commandson the distribution library, both of which record the commands that need to be distributed ( Note: The commands may have been distributed to the subscription, but not cleared . Correlate the two tables in minutes to determine how many commands are written to the distribution library per minute.

SELECT    DATEPART (mm, [entry_time]) ' Month ',    DATEPART (DD, [entry_time]) ' Day ',    DATEPART (hh, [Entry_time]) ' Hour ', DATEPART (MI, [entry_time]) ' Minute ',    count (c.[xact_seqno]) ' Count of commands ' from [dbo].[ MSrepl_transactions] (NOLOCK) Tinner JOIN [dbo]. [MSrepl_commands] (NOLOCK) C on    t.[xact_seqno] = c.[xact_seqno] and T.publisher_database_id=c.publisher_database_idwhere [entry_time] >= ' 2017-05-04 12:00:00 ' GROUP by    DATEPART (MM, [entry_time]),            DATEPART (DD, [entry_time]),            DATEPART (hh, [ Entry_time]), DATEPART (MI, [entry_time]) Order by 1, 2, 3,4go

In publishing to a distribution, the read and write of a transaction command can be compared to determine which segment of the read or write is in question.

Distribute to Subscription:

Distribution to the subscription, preferred to determine how many commands need to be distributed, if the direct summation of msrepl_transactions and msrepl_commands is inaccurate, because some have already been distributed. You can turn on Replication Monitor to view commands that are not distributed in a publication, and if you use a script, there are two methods:

/************************ method One ****************************/exec distribution.sys.sp_ Replmonitorsubscriptionpendingcmds @publisher = n ' publisher name ', @publisher_db = n ' publication database name ', @publication = n ' publication name ', @subscri ber = N ' subscriber name ', @subscriber_db = n ' subscription database name ', @subscription_type =1go/************************ method two ******************* /--number of transactions not distributed with Maxxact (ServerName, Publisherdbid, xactseqno) as (Select s.name, da.publisher_database_id, Max    (h.xact_seqno) From Distribution.dbo.MSdistribution_history H with (nolock) Inner Join distribution.dbo.MSdistribution_agents DA with ( NOLOCK) on da.id = h.agent_id Inner Join master.sys.servers S with (nolock) on s.server_id = da.subscriber_id Group B Y s.name, da.publisher_database_id) Select MX. ServerName, MX. Publisherdbid, COUNT (*) as Transactionsnotreplicatedfrom distribution.dbo.msrepl_transactions T with (NOLOCK) right Join maxxact mx on MX. Xactseqno < t.xact_seqno and MX. Publisherdbid = T.publisher_database_idgroup by MX. ServerName, MX. PubLisherdbid; go--number of commands not distributed with Maxxact (ServerName, Publisherdbid, xactseqno) as (Select s.name, da.publisher_database_id, Max (h.xact_  seqno) from Distribution.dbo.MSdistribution_history H with (nolock) Inner Join distribution.dbo.MSdistribution_agents    DA with (nolock) on da.id = h.agent_id Inner Join master.sys.servers S with (nolock) on s.server_id = da.subscriber_id Group by S.name, da.publisher_database_id) Select MX. ServerName, MX. Publisherdbid, COUNT (*) as Commandsnotreplicatedfrom distribution.dbo.MSrepl_commands C with (nolock) right Join maxxact MX on MX. Xactseqno < c.xact_seqno and MX. Publisherdbid = C.publisher_database_idgroup by MX. ServerName, MX. Publisherdbid; GO

When overall monitoring is distributed to the subscription, you can use counter tracking:

"\sqlserver:replication dist\dist:delivered cmds/sec"
"\sqlserver:replication dist\dist:delivered trans/sec"

For more information, you can restart the distribution job by adding the following parameters to the Distribution Agent.

-output [Output_path_and_file_name]
-outputverboselevel [0|1|2]]

Next regression topic: SQL Server Transactional replication distribution to subscription synchronization slow

Orders that are not currently distributed have been blocked Libedovan, and distributed almost two minutes before distribution. See what commands are currently executing in the distribution database, often seen in executing stored procedure sp_msget_repl_commands , from msrepl_transactions and Msrepl_ Commands Read the commands that are not distributed and apply them to the subscription. But the read has a wait type async_network_io,async_network_io means the data has been read, but the customer has not completely taken the data. As is normally understood, these wait types are likely to be network problems.

To determine if it is a network problem, I used the following methods:

1. From other non-clogging releases, insert the tracker. Verify that the entire process network is fine.

2. Ping the network to see how much time. Basic 1ms inside, normal!

3. Create a shared folder in the subscription, access the share at the Distributor, copy a large file in the past. Copy speed 66mb/s, no problem!

Since there is no problem with the network, it is necessary to determine whether the distribution to the subscription "write" there is a problem? To the subscriber to see what commands are currently executing, and to find that the total number of commands has been executed!

From the cache, review the execution and discover that the stored procedure takes a lot of time!

SELECT TOP  st.text, Qp.query_plan,    (Qs.total_logical_reads/qs.execution_count) as Avg_logical_reads,    (Qs.total_logical_writes/qs.execution_count) as Avg_logical_writes,    (Qs.total_physical_reads/qs.execution_count) as Avg_phys_reads,  qs.*from sys.dm_exec_query_stats as QsCROSS Apply Sys.dm_exec_sql_text (qs.sql_handle) as Stcross apply Sys.dm_exec_query_plan (qs.plan_handle) as QpWHERE St.text Like '%sp_msupd_dbomds_adrelation% ' ORDER by Qs.total_worker_time Descgo

Click on the above query out of the execution plan, found that the application of the table is a table scan!!!

Find the table mds_adrelation , found that there is no primary key (or index)!!

Table Publishing subscriptions must have a primary key, the subscription will also retain the primary key, the data synchronization update is the primary key to do the conditions to update, and the table primary key is gone, the update will not be indexed!! I don't know who deleted it!

The script that creates the primary key is exported from the publication to the subscription execution, because synchronization is in progress and is created online using (online=on) when creating the primary key. Once created, the distribution instantly accelerates (for example) and the traffic in the subscription increases instantly!! The copy is finally normal!!

SQL Server Transactional replication distribution to subscription synchronization slow