3

First of all, I'm not a DBA, but I'd like to get advice how to deal with the following issue.

We have few database servers based on SQL Server 2008 R2 SP1. They have been organized as shown below, so server A is the source of data to servers B, C and D. There is one-way peer-to-peer replication between servers. Network is 1Gbps.

  __ A __
 /   |   \
 B   C    D

We have some denormalized table in our database with about ~700k rows. Table is about of 1Gb in size (and 5.5Gb of indexes) This table constantly updates - so all content in this table is renewed in ~2 hours (i.e. old rows are deleted and new are inserted). This is done in transactions of 5k records. All works fine on server A, but replication of data from A to B, C and D slows down and delay could be about one hour between these servers.

Our DBAs say that it happens because of big delete transactions. They say that SQL Server executes delete operations on subscriber sequentially on each row, one by one. Is there a way to change this behavior if it does it so?

Actually, I'm not a big fan of replication, I would prefer to use some kind of messaging and publish-subscribe pattern to synchronize data between servers, but replication is the main way to transfer data in a company that I work for now. So it will be kinda hard to change these things.

Any thoughts how to improve performance of replication?

Improve this question
3
  • Are there any triggers on the table? Any cascaded deletes?
    – SqlACID
    Commented Sep 9, 2011 at 14:26
  • No, there is no triggers or cascades. It's just a plain denormalized table.
    – xelibrion
    Commented Sep 9, 2011 at 19:55
  • Unless you re-write the stored procedures done for the deletion of rows then they are deleted line by line (there are reasons for this) - It may be worth changing the batch size of your deletes see if that changes the performance, do it in 2k batches with a second wait between
    – Ste Bov
    Commented Oct 16, 2015 at 7:58

1 Answer 1

Reset to default
4

First there's no such thing as one way peer to peer (that I know of). Are you sure you aren't using transactional replication?

Are the deletes done via stored procedures? If so you can replicate the stored procedures as well as the tables (all in the same publication, this will require a reinit of the subscribers). This way the stored procedure call will be replicated instead of the actual row by row delete.

With only 700k rows in the table, even if you blew them all away and reloaded them the replication shouldn't get that far behind unless you have a really slow link between the servers (which you didn't specify).

Improve this answer
4
  • Regarding replication type - our DBAs told so. If I got it right, SQL Server Enterprise Edition provides peer-to-peer replication on top of transactional replication. So in my case, I think, it is really transactional replication. Deletes are done using ORM (NHibernate), so there is no way to replicate procedure call.
    – xelibrion
    Commented Sep 9, 2011 at 13:35
  • As the deletes are done using the ORM there's nothing you can do to batch them up. You are stuck with replication moving the deletes one row at a time.
    – mrdenny
    Commented Sep 9, 2011 at 20:27
  • Okay, we'll try to do it using stored procedure. By the way, can be call of sp_executesql replicated? Or it's just for custom stored procedures?
    – xelibrion
    Commented Sep 10, 2011 at 5:25
  • No the calls to sp_executesql can't be replicated as that's an object in another database. Only calls to user procedures within the user database can be replicated.
    – mrdenny
    Commented Sep 10, 2011 at 5:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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