March 2007 - Posts

Transactional Replication, Deferred Updates and Triggers.

 ( SQL Server 2000 )


I sometimes think that as part of my performance tuning and optimisation work I encounter every obscure feature of SQL Server there is.

I’ve created a customised transactional replication for a production database, which I’m not going to publish here, but one of the aspects of the database is a high level of referential integrity which is also applied at the subscribers.


My first interesting experience was the ( documented ) effect of  publishing a table where there is a clustered index which is not the primary key. Here’s the link to the Knowledge Base article

which provides a trace flag to be applied to the publisher , here’s the specific link for post sp1 trace flag  on SQL 2000


So hands up who knows what a deferred update is ? It’s a question I will sometimes ask DBA candidates in an interview and sad to say it’s often one that isn’t answered.


So if you have clustered indexes which are not Primary Keys then you have this issue, and you don’t have to be updating the column(s) that participate in the clustered index either, any update to the row presents a deferred update.


So I was at Reading listening to Kimberly and Paul and the subject of deferred updates introducing fragmentation was raised. Now I’ve always remembered that a trigger on a table forces a deferred update, so is the same behaviour reproduced in Transactional Replication ? Short answer YES !


So new scenario in Transactional Replication, triggers for update/delete/insert force a deferred update which will break replication as described in the KB. And yes the same trace flag resolves the problem which is fortunate.


Note that you cannot apply a range update in either scenario, the trace flag only resolves singleton updates.


So how did I check for deferred updates?  I used a log reader tool, actually I used a couple, but a quick plug for the Redgate product which confirmed the delete – insert .. See screen shot below.


When I get some free time I’ll check this through in SQL 2005.




Posted by GrumpyOldDBA with 2 comment(s)
Filed under: