replication time just doubled (even with no table changes)

bgravesbgraves Posts: 19
In the past week I've notice that the replication time (when not timing out) has increased significantly. Our set of SQL tables replicated in under 11 minutes last week. Currently, it's taking 20-25 minutes on a regular basis. Is there a reason this is happening (even when no new rows are being added to the table)? This is very difficult to troubleshoot since the code is hidden in the redgate DLLs. Could upgrading the libraries help (currently we're using version 6...not version 8)? I'm afraid to upgrade them now since it sounds like the SQL commandTimout in the redgate libraries has been decreased to 10 minutes.

Comments

  • Hi, Brian has already helped out in your other thread regarding this message.
    I've just had a quick look in the code for the block executor and it looks like the commandtimeout is 6000 seconds, which would be 100 minutes? So I'm not sure that's entirely your problem.

    Either way, it doesn't explain why it's now slower than it was. Did it change suddenly, or has it got slower over time?

    Assuming nothing else has altered, has something happened to your network connection that's maybe slowing it down? Any other services added, or some other problem?
    Systems Software Engineer

    Redgate Software

  • The timeout varies: anywhere between 6704-7647 seconds. This issue occurred throughout the long weekend (was logged) and wasn't resolved until I manually stopped and restarted SQL server earlier this week. Is there a better way to recover from this timeout phase?

    When it doesn't time out, the replication time varies as well: between 730 seconds (122166 rows replicated) to 1822(984 rows replicated) seconds. Thankfully, today we're experiencing the lower part of this range (unlike earlier this week). The same set of tables is used in each case so there doesn't seem to be any rhyme or reason why the time varies. The only reason I can think of is the fact that since there was so many timeout issues over the weekend with the redgate replication, it caused subsequent extended time once SQL server was restarted. Would you agree?
  • So restarting SQL helped? Seems odd, I wouldn't particularly expect that to affect it.
    Is this a live production system? I can only think that varying loads on it are accounting for the differences.
    Systems Software Engineer

    Redgate Software

  • Restarting SQL was the only way I was able to stop getting the 2 hour timeouts trying to replicate the tables.

    Yes, this is a live production system. Today, we had a record breaking 35 minutes to replicate 21 tables. Something is definately wrong. :cry:
  • James BJames B Posts: 1,124 Silver 4
    Apologies for the delay in replying.

    Well, as a general rule the process will take "as long as it takes".
    You can speed it up by only including a subset of tables you're interested in (this will help the comparison phase) but for the actual sync it's basically executing an update/insert/delete script against the target server.
    This is subject to what else the server is doing at the time, and other network traffic and so on.

    I noticed you mentioned replication a lot in your messages. I'm not sure if this is exactly what you're using it for, but SQL Data Compare isn't really intended as a replacement for the full transactional replication offered within SQL Server itself. Whilst you can use it to move data from one db to another on a regular basis, it's only connecting to the server in the same way as any other client application and has to take its turn for resources like anything else.
    Systems Software Engineer

    Redgate Software

  • Sorry about the "replication" terminology. Basically, we're only updating changed table rows using a lastUpdate field. I'm just trying to find out why the excute time is slowing down so much now. It's not consistent but when it's slow, it's sloooooooow. It sounds like there is just too much of a load on SQL server and there's nothing I can do with the redgate libraries to speed this up, would you agree?
  • James BJames B Posts: 1,124 Silver 4
    Sorry I didn't reply sooner to this.

    Basically if the performance varies then there's unlikely to be much you can do to speed it up. If it had always been slow, then it could be worth looking in your code to see if improvements could be made (perhaps run our performance profiler against it) but if the only real variable is the time it happens to run at I don't think much can be done. Sorry.
    Systems Software Engineer

    Redgate Software

Sign In or Register to comment.