SQL Server 2008 R2 replication high delivery latency

Question

I am seeing an unusually high delivery latency between our distributor and subscribers and I do not understand why.

We have in this configuration 3 sql servers using transactional push replication to replicate data from one master server to two reporting servers.

We have 9 publications. The distribution agent for most publications are showing under 5ms, but one is show as 2000+ms to both subscribers.

The suspect publication has only 4 small articles (tables) that rarely, if ever, change. I've checked and each table has an primary key.

I've also checked the @status parameter for each article according to the MS KB: The distribution agent may experience high latency when you configure transactional replication with articles that are configured not to replicate changes as parameterized statements.

I'm tempted to start dropping articles to find out if one particular table is the culprit.

Does anyone have any suggestions as to what I can look at?

Answer 1

Below script will help you find out how many undistributed commands are there. Also, do you see any errors in sql server or windows logs ? Is it just slow or any failures reported ?

SELECT ( CASE 
           WHEN mdh.runstatus = '1' THEN 'Start - ' + CAST( 
                                         mdh.runstatus AS VARCHAR) 
           WHEN mdh.runstatus = '2' THEN 'Succeed - ' + CAST( 
                                         mdh.runstatus AS VARCHAR) 
           WHEN mdh.runstatus = '3' THEN 'InProgress - ' + CAST( 
                                         mdh.runstatus AS VARCHAR) 
           WHEN mdh.runstatus = '4' THEN 'Idle - ' + CAST(mdh.runstatus AS 
                                                          VARCHAR) 
           WHEN mdh.runstatus = '5' THEN 'Retry - ' + CAST( 
                                         mdh.runstatus AS VARCHAR) 
           WHEN mdh.runstatus = '6' THEN 'Fail - ' + CAST(mdh.runstatus AS 
                                                          VARCHAR) 
           ELSE CAST(mdh.runstatus AS VARCHAR) 
         END )                                   [Run Status], 
       mda.subscriber_db                         [Subscriber DB], 
       mda.publication                           [PUB Name], 
       RIGHT(LEFT(mda.name, Len(mda.name) - ( Len(mda.id) + 1 )), 
       Len(LEFT(mda.name, Len(mda.name) - ( Len(mda.id) + 1 ))) - ( 
       10 + Len(mda.publisher_db) + ( CASE 
                                          WHEN mda.publisher_db = 'ALL' THEN 1 
                                          ELSE Len(mda.publication) + 2 
                                        END ) )) [SUBSCRIBER], 
       CONVERT(VARCHAR(25), mdh.[time])          [LastSynchronized], 
       und.undelivcmdsindistdb                   [UndistCom], 
       mdh.comments                              [Comments], 
       'select * from distribution.dbo.msrepl_errors (nolock) where id = ' + 
       CAST(mdh.error_id AS VARCHAR(8))          [Query More Info], 
       mdh.xact_seqno                            [SEQ_NO], 
       ( CASE 
           WHEN mda.subscription_type = '0' THEN 'Push' 
           WHEN mda.subscription_type = '1' THEN 'Pull' 
           WHEN mda.subscription_type = '2' THEN 'Anonymous' 
           ELSE CAST(mda.subscription_type AS VARCHAR) 
         END )                                   [SUB Type], 
       mda.publisher_db + ' - ' + CAST(mda.publisher_database_id AS VARCHAR) 
                                                 [Publisher DB], 
       mda.name 
       [Pub - DB - Publication - SUB - AgentID] 
FROM   distribution.dbo.msdistribution_agents mda 
       LEFT JOIN distribution.dbo.msdistribution_history mdh 
         ON mdh.agent_id = mda.id 
       JOIN (SELECT s.agent_id, 
                    maxagentvalue.[time], 
                    SUM(CASE 
                          WHEN xact_seqno > maxagentvalue.maxseq THEN 1 
                          ELSE 0 
                        END) AS undelivcmdsindistdb 
             FROM   distribution.dbo.msrepl_commands t (nolock) 
                    JOIN distribution.dbo.mssubscriptions AS s (nolock) 
                      ON ( t.article_id = s.article_id 
                           AND t.publisher_database_id = s.publisher_database_id 
                         ) 
                    JOIN (SELECT hist.agent_id, 
                                 MAX(hist.[time]) AS [time], 
                                 h.maxseq 
                          FROM   distribution.dbo.msdistribution_history hist ( 
                                 nolock) 
                                 JOIN (SELECT agent_id, 
                                              Isnull(MAX(xact_seqno), 0x0) AS 
                                              maxseq 
                                       FROM 
                                 distribution.dbo.msdistribution_history ( 
                                 nolock) 
                                       GROUP  BY agent_id) AS h 
                                   ON ( hist.agent_id = h.agent_id 
                                        AND h.maxseq = hist.xact_seqno ) 
                          GROUP  BY hist.agent_id, 
                                    h.maxseq) AS maxagentvalue 
                      ON maxagentvalue.agent_id = s.agent_id 
             GROUP  BY s.agent_id, 
                       maxagentvalue.[time]) und 
         ON mda.id = und.agent_id 
            AND und.[time] = mdh.[time]