none
Very slow replication

    General discussion

  • I have a replication with pull subscription. over the wan.

    At times it is ok (Not briliant) but sometimes it gets realy slow.

    For example today, we reached 2 commands per minute!!!!

    at the same time we expirianced this, I copied from the same location a 13MB file and got 120-170kb/sec speed.

    So I believe the IT guy saying the line is fine. I checked for locks - none. The waits are on network and they are short ones( a few seconds at the most).

    The trace shows nothing during the wait (only at the end everything gushes through)

    here is the agent log:

    Date  10/06/2013 22:29:01
    Log  Job History (HK-DB-LSD_HK-LSD_HK_PUB-ILREP01-LSD_HK_SUB-1AA4CA8A-B1AA-4D3D-8EC6-760F707898E6)

    Step ID  1
    Server  ILREP01
    Job Name  HK-DB-LSD_HK-LSD_HK_PUB-ILREP01-LSD_HK_SUB-1AA4CA8A-B1AA-4D3D-8EC6-760F707898E6
    Step Name  Run agent.
    Duration  00:26:34
    Sql Severity  0
    Sql Message ID  0
    Operator Emailed  
    Operator Net sent  
    Operator Paged  
    Retries Attempted  0

    Message
    06-10-2013 22:55:34

    Total Run Time (ms) : 1592521  Total Work Time  : 1580571
    Total Num Trans     : 50  Num Trans/Sec    : 0.03
    Total Num Cmds      : 50  Num Cmds/Sec     : 0.03
    Total Idle Time     : 0

    Writer Thread Stats
      Total Number of Retries   : 0
      Time Spent on Exec        : 46
      Time Spent on Commits (ms): 0  Commits/Sec         : 0.00
      Time to Apply Cmds (ms)   : 62  Cmds/Sec            : 806.45
      Time Cmd Queue Empty (ms) : 1581352  Empty Q Waits > 10ms: 3
      Total Time Request Blk(ms): 1581352
      P2P Work Time (ms)        : 0  P2P Cmds Skipped    : 0

    Reader Thread Stats
      Calls to Retrieve Cmds    : 1
      Time to Retrieve Cmds (ms): 1580571  Cmds/Sec            : 0.03
      Time Cmd Queue Full (ms)  : 0  Full Q Waits > 10ms : 0
    *******************************************************************************

    2013-06-10 19:55:34.432 A total of 50 transaction(s) with 50 command(s) were delivered.


    • Edited by oky111 Monday, June 10, 2013 8:01 PM
    Monday, June 10, 2013 7:59 PM

All replies

  • Is this P2P?

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Monday, June 10, 2013 10:49 PM
  • Can you run

    select wait_time,wait_type,last_wait_type,wait_resource  fromsys.dm_exec_requestswheresession_id=

    on the subscriber, filtering on the distributor process id ? What do you see ?

    Cheers

             Marco


    Marco Carozzi

    Monday, June 10, 2013 11:49 PM
  • Hilary - Yes


    • Edited by oky111 Monday, June 17, 2013 10:27 AM
    Tuesday, June 11, 2013 6:26 AM
  • I will try.

    you say to run it on the subscriber. I understand that if an SQL query runs from a SQL server it can be traced from the client server.

    I will try this when I get another hangup.

    at the moment replication is too fast to diagnose.

    I add here a graph to show that.

    Tuesday, June 11, 2013 7:51 AM
  • Can you run a dbcc loginfo and get an idea of the number of vlfs you have? The fewer the better, several hundred is ok, several thousand is not.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Tuesday, June 11, 2013 11:41 AM
  • Marco -

    So I run this on the publisher.

    I got many quick waits on network.

    this should be ok:

    wait_time wait_type last_wait_type wait_resource logical_reads
    93 ASYNC_NETWORK_IO ASYNC_NETWORK_IO  69119

    I had 1 big transaction 150,000 commands that passed in the rate of 27 cmd/sec. that is fine,too.

    but then there were many small single command transactions.

    They gave the rate of 0.22 cmd/sec.

    I run from the subscriber:

    SELECT

    TOP 1000 * FROM dbo.MSrepl_commands AS msc

    it takes 12 seconds.

    There are 42229 commands in there.

    I now noticed the logical reads. for each distribution agent run, it counts up to ~80,000 and then the agent finishes 50 command run.

    profiling the dustributor database brings the following (18 sec example):

    exec sp_MSget_subscription_guid 58
    go
    exec sp_MShelp_distribution_agentid 0,N'LSD_NY',N'LSD_NY_PUB',9,N'LSD_NY_SUB',1
    go
    exec sp_MSget_repl_commands 58,0x006C476E00000FB6000600000000,0,9000000
    go
    exec sp_MSget_subscription_guid 58
    go
    exec sp_MShelp_distribution_agentid 0,N'LSD_NY',N'LSD_NY_PUB',9,N'LSD_NY_SUB',1
    go
    exec sp_MSget_repl_commands 58,0x006C476E00000FB6000600000000,0,9000000
    go
    exec sp_MSget_subscription_guid 58
    go
    exec sp_MShelp_distribution_agentid 0,N'LSD_NY',N'LSD_NY_PUB',9,N'LSD_NY_SUB',1
    go
    exec sp_MSget_repl_commands 58,0x006C476E00000FB6000600000000,0,9000000
    go
    exec sp_MSget_subscription_guid 58
    go
    exec sp_MShelp_distribution_agentid 0,N'LSD_NY',N'LSD_NY_PUB',9,N'LSD_NY_SUB',1
    go
    exec sp_MSget_repl_commands 58,0x006C476E00000FB6000600000000,0,9000000
    go

    What say you?


    • Edited by oky111 Monday, June 17, 2013 10:28 AM
    Sunday, June 16, 2013 7:40 PM
  • Hilary -

    155 vlfs on published db

    199 on distributor db

    I don't feel this is the issue.

    but will try to reorgenize.


    • Edited by oky111 Monday, June 17, 2013 10:27 AM
    Sunday, June 16, 2013 8:10 PM