none
replication settings for a table that has high insert rate

    Question

  • 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?

    OD


    Ocean Deep

    Thursday, June 13, 2013 10:11 PM

All replies

  • 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

    m@te


    • Edited by farkas.mate Monday, July 01, 2013 4:37 PM 9000/sec -> 9000/min
    • Proposed as answer by farkas.mate Monday, July 01, 2013 4:43 PM
    Monday, July 01, 2013 4:36 PM
  • 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.


    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, July 02, 2013 5:36 PM
    Moderator