replication settings for a table that has high insert rate

We have set up a push publication for a single large article which has high insert rate.  We are in SQL 2008 enterprise.  This table has over billion rows and has an insert rate of 6,000 to 9,000 per min during peak hours.   We also have other publications on the same publishing database (the same one the big table resides) to the same subscriber.  Sometimes we encounter latency, double digits in mins.  We have a dedicated distrubtor server.  Are there some settings in replication or better methodology to replicate such a large table with high insert rate?  Can SQL transaction replication handle such a large and high insert article by design?  Would sQL 2012 be better?

June 14th, 2013 1:11am

In some cases transactional replication can deliver 10,000 transactions/sec so 9,000/min (150/sec) is not problem. 
Some recommendations:

  • reduce the number of indexes on the table at the subscriber side for the possible minimum (the best is only a "slim" clustered index or heap)
  • set the article to use stored procedure for delivering INSERT operations
  • delete the constaints on subscriber (foreign keys, check constraints, defaults, null and identity property) or re-create the table
  • try to prevent locking on subscriber when querying the table (use nolock table hint or read uncommitted isolation level)
  • use limited paralellism when querying the table on subscriber (use maxdop query option with a 50% number of CPUs)
  • use exact gowth size on datafile on the subscriber (not percentage) with a higher value (around 100 Mb)
  • use SIMPLE recovery model on subscriber
Free Windows Admin Tool Kit Click here and download it now
July 1st, 2013 7:36pm

I have hit 1,000 transaction per second with minimal latency. It all depends on what the link between the publisher and the subscriber is like and other replication activity.

Separate this table in its own publication and use the independent agent option.

Then look at what the latency is like on this publication and determine if the max latency is in the log reader agent or the distribution agent. The log reader agent should never be the problem and its latency should always be below 1 s. The distribution agent is normally the bottleneck.

July 2nd, 2013 1:38pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics