Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a VLDB that is being replicated using transnational replication. The performance has been terrible, and the process frequently times out.

After much investigation, including Service Packs and hotfixes, page file increase, TempDB tweaks, RAM increases, SAN tests, even a new node in the cluster(!) we have finally found that the issue is being caused by a Database Snapshot on the subscriber.

I have seen a similar issue with index rebuilds, where the Ghost clean-up task was blocking the Copy-On-Write thread, and the resolution was to disable the Ghost clean-up task.

Unfortunately, in this case, disabling the ghost clean-up task has not resolved the issue, and replication continues to get Query Time-outs, usually during delete operations. Increasing the query timeout period simply increases the amount of time before it times-out!

Has anybody come across this, or have any ideas as to how to resolve the issue? Removing the Database Snapshot, even during the period of replication is not possible, as it is supporting user reports.

share|improve this question
    
when you say replication times out usually during delete operations are you replicating Deletes to subscriber/s or is it when you are generating a Snapshot to be applied to subscriber/s (not the database snapshot, the snapshot that replication generates when you reinitialize or add new articles, etc) ? –  Kin Jun 26 '13 at 14:24
1  
@Kin - it is when deletes are being transitionally replicated, not during the snapshot initialization. However, snapshot erplication is 8x faster without the snapshot –  Pete Carter Jun 27 '13 at 22:03
    
Do you have a requirement to propagate Delete's to the subscribers ? Have you thought of Using Custom Stored Procedures for Inserts, Updates, and Deletes at Subscribers [msdn.microsoft.com/en-us/library/aa237133(v=sql.80).aspx] –  Kin Jun 27 '13 at 22:21
    
@kin - Unfortunately this is not "politically" possible. Also, I an not convinced that it would help, as the issue is not with getting the data over the wire, it is actually the high volume IO vs the database snapshot. –  Pete Carter Jun 28 '13 at 8:02
1  
Have you looked into wait stats analysis ? This is very unusaual to me. Possibly not a replication issue, but something else is the root cause. –  Kin Jun 28 '13 at 12:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.