SQL Server Performance

Transactional Replication & Bulk Updates

Discussion in 'Performance Tuning for SQL Server Replication' started by jovialcarl, May 25, 2004.

  1. jovialcarl New Member

    I have a 8 tables replicated with a pull subscription on SQL 2000 and this has been working fine.

    Unfortunately the system is rather open and last week the users of the system managed to cause an update to 100000 records (about half the table) at the publisher on a table with about 100 columns.

    These records then took a day to replicate (that wasn't helped by an On Update Trigger at the subscriber), during which time the subscriber table became progressively more locked up (using Key level locks) and so my web site that looks at the Subscriber database became unusable.

    Give that we need near instant replication of data & I can't guarantee the users wont do a bulk update again is there any way to help these records across faster.
  2. satya Moderator

  3. jovialcarl New Member

    Cheers for that,

    I'm having a look through those sites now, but I think I forgot to say before that there isn't a problem with the bulk update on the publisher.

    I'm not sure exactly how long the update took to run at the publisher, but I reckon it was only a minute.
    The Publisher database continues to work fine. The issue is that it calles the update proc on the subscriber 100000 times within one transaction, and each time the proc is run it takes a handfull of Key Level locks. Towards the end I had millions of locks (ran sp_lock).

    I wasn't sure if I could get it to perform the replication in some other way?
  4. satya Moderator

    Review the information listed on the first link to fine tune the replication.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page