3

I have a couple transactional replication publications that make large changes (nearly 1 billion records) to a publication article. These transactions are captured by the log reader and stored in the distribution database for up to a day before we run the distribution agents to the two subscribers.

In conjunction with these large transactional replication publications, we also have several smaller publications that run daily and sometimes multiple times per day. What we are seeing is that while the log reader is reading the large transactions and storing them in the distribution database, the smaller, daily publications are being blocked until the log reader completes the write to the distribution database. Note: these publications use the same distribution database on a single distributor server.

For both scenarios above, the distribution agents are not scheduled but are run on-demand: an SSIS package calls several CMD distrib.exe while passing the parameters for each publication/subscription combination. There has not been an issue executing these agents.

Since these are two entirely different sets of data, although publishing from the same database, I am considering changing the transaction isolation level of the database to SNAPSHOT to avoid locks and allow concurrent processing of the transactions in the distribution database.

Does anybody have experience doing this? I've used SNAPSHOT isolation level for another database where we were having deadlocks while accessing the same table but different records and it worked out fine.

If changing the isolation level isn't the way to go, how can I reduce the impact of this large log reader write to the distribution database?

Improve this question
3
  • I would recommend hitting up the MSDN SQL Server forums and wait for Hillary Cotter to respond. He's exceptionally good at replication and will probably have good insight for you on this.
    – Ali Razeghi - AWS
    Jun 10, 2016 at 16:34
  • Thanks Ali! I'll post over there and see if anyone has any input.
    – benchwrmr22
    Jun 10, 2016 at 17:28
  • @benchwrmr22 refer to my answer here and let me know if it helps your situation or not. Also, look at the no of VLFs that contribute to T-Rep latency
    – Kin Shah
    Jun 14, 2016 at 18:18

2 Answers 2

Reset to default
1

With 1 billion changes, I would be worried about the size of TempDB when the row version is added to everything. Hilary Cotter responded to this same question here - https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2edbdc37-c2ed-4c84-ae72-16f89404172b/sql-server-replication-and-enabling-readcommittedsnapshot-on-the-distributor?forum=sqlreplication.

He is definitely the man you want to ask replication related questions to.

Improve this answer
1
  • +1. It was great that you found the question already answered!
    – Ali Razeghi - AWS
    Jun 14, 2016 at 18:11
0

If you have 1 billion changes, have you looked into replicate the execution of a stored proc?

Replicate execution of stored procedure will save yourself a lot of time and space and reduce the exact scenario you are hitting, where distribution db is filled with commands. If this is some kind of batch process, then marking the stored proc as replicated, it will log the stored proc call only, and replicate just the call to the subscriber.

Improve this answer
5
  • We wanted to avoid replicating stored procs as it would mean moving the data to the subscriber earlier on in the process and letting the machine do work there. Our idea was to have all the processing occur on one machine and simply replicate over the final product. This becomes problematic when the processing touches billions of records.
    – benchwrmr22
    Jul 5, 2016 at 13:34
  • the 1 billion changes are being replicated and duplicated on the subscribers anyways, correct?
    – Greg
    Jul 5, 2016 at 21:09
  • Yes, however, some of our processes load table into a pre-load table which is schema identical to our final table. We load some data into the pre-load table, do some work on it, then switch it into the final dbo table. Replication is problematic when millions (or billions) of records come through because the process has to replicate the loading of data into the pre-load table, replicate all of the work (set based updates, usually), and replicate the partition switch (successful so far).
    – benchwrmr22
    Jul 6, 2016 at 12:48
  • Also, the final part of the partition switch switches out the old data into a pre-delete table, which then has to be cleared after every run. Since transactional replication prohibits the use of TRUNCATE, we have to DELETE the records from the table which already takes a long time.
    – benchwrmr22
    Jul 6, 2016 at 12:50
  • I just realized I was getting off-track from the original question. We just haven't had a lot of success with SQL Replication.
    – benchwrmr22
    Jul 6, 2016 at 12:52

Your Answer

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

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