Post reply

Bulk Insert performance

  • October 5, 2007 at 11:18 am

    #69062

    Hi ,

    I have scenario where we have huge tables Transactional replication and now requirement is to re-initialize the subscription , what are the things we need to take care to optimize the bulk insert performance through dist agent after re-initialize , like dropping of indexes from destination tables or any other things?

    All 3 are on different servers

    Publisher sql2000

    distributor sql 2005

    subscriber sql2000

    Thanks

  • October 5, 2007 at 2:15 pm

    #739713

    sqlquery (10/5/2007)

    Hi ,

    I have scenario where we have huge tables Transactional replication and now requirement is to re-initialize the subscription , what are the things we need to take care to optimize the bulk insert performance through dist agent after re-initialize , like dropping of indexes from destination tables or any other things?

    All 3 are on different servers

    Publisher sql2000

    distributor sql 2005

    subscriber sql2000

    Thanks

    The re-initialization process by default drops/creates schema an indexes. If you have that changed then you must manually do that too.

    What you should do though before applying the snapshot is:

    1. Make sure your subscriber is in BULK_LOGGED mode

    2. Set MaxBCPthread of the distribution agent to a value >1 ( I use normally 2)

    3. Set BcpBatchSize equal to MAXINT

    Then when you are done set recovery mode back to what it was on the subscriber.

    Cheers,


    * Noel

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply