Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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. Ive checked and each table has an primary key.

ive 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

Im tempted to start droping articles to find out if one particular table is the culprit.

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

share|improve this question
1  
Did this just start happening recently, or has it been this way all along? And can you confirm there actually is a performance issue with late deliveries on the subscriber side? –  Jon Seigel Mar 15 '13 at 16:07
add comment

1 Answer

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] 
share|improve this answer
    
so i ran the script and the database/publication in question has 0 undistributed commands. Im using spotlight on sql server to monitor performance and it indicates that "the distribution agent '...' has a latency of 2148.9 seconds. could this just be a false alarm? –  Chuck Herrington Mar 15 '13 at 19:18
    
If it has 0 undistributed commands, then there is no data to be replicated. Did you check by launching Replication Monitor to see if you are seeing same latency ? Also this will help you with TSQL Code sqlfool.com/2008/11/checking-replication-latency-with-t-sql –  Kin Mar 15 '13 at 19:30
    
sql server replication monitor does quite frequently report that the perfomance is critical with latencies of over ... waiting for it to happen again ... 00:02:17 on one or both subscriptions (this occurance was just on one). This database is mainly a reference database and is rarely written to (maybe once a week) so it doesnt surprise me that there are no transactions to be replicated. Why though would there be high latency? I did do a tracer token and from pub to dist the latency is 00:00:04, from dist to sub it is 00:00:03. –  Chuck Herrington Mar 18 '13 at 12:52
1  
What is your replication setting ? Is it running continuously or every 5 mins, etc ? Just another thought ...If this database is rarely written (once a week) why are you using T-Rep ? You do have an option of snapshot replication. Also, the tracer token latency of 4 sec is not that high which needs a concern ... Did you check your N/W settings or anything has changed on your server that would affect this all of a sudden ? –  Kin Mar 18 '13 at 14:08
1  
Thanks ! Unless there is a business need, there is no need for Replication to run continuously. Your case makes sense to schedule it to run every 5 mins or 15 - 30 mins. Did you check the n/w if packets are being dropped using tracert or ping ... and probably with your n/w team to see if there is any change in the n/w ? –  Kin Mar 19 '13 at 20:18
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.