Results 1 to 4 of 4
  1. #1
    Join Date
    May 2002
    Location
    Timbaktu
    Posts
    185

    Unanswered: Trigger and MS-DTC

    I have 2 servers, SQL Server A and SQL Server B. SQL Server B has been configured as a Linked server under SERVER A and there is an Insert/Update/Delete trigger on a table in SQL Server A, which is suppose to write the same changes to SERVER B by using the I/U/D trigger. This system has been working fine for the last 3-4 months, but lately the response of the system has slowed down. After extensive research we found that whenever a full database backup runs on SERVERB, the trigger at Server A has to wait for a longer time than the rest of the day operation. I wonder why? Any Help?

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    more data and lack of indexes maybe? Are there any indexes on the target server? The slowdown in probably on UPDATE/DELETE, but may also be observed on INSERT if there is a clustered index on anything other than an IDENTITY or timestamp field. Send us the trigger, someone may see the light there

  3. #3
    Join Date
    May 2002
    Location
    Timbaktu
    Posts
    185
    Both the tables are identical and there are indexes also on both the tables. There is one Clustered index on the Non-ID column and 1 non-clustered index on the Identity column. This table has got 90% of inserts and rest of the operations are S/U/D.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    If new values of clustered index field are not in order, your INSERTs will be delayed by page splits. Rebuilding clustered index (which will force rebuilding of other indexes) with a smaller fillfactor may improve the performance.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •