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