SQL Server Replication and enabling read_committed_snapshot on the distributor

Question

Is there any documentation out there on enabling read_committed_snapshot isolation on the distribution database to reduce contention? I know it is mainly a lot of writes, but there appear to be a fair amount of reads doing some blocking as well. So I am curious if Microsoft has stated anything (since I have been searching and not found anything) or if maybe someone else has done it.

---

John M. Couch

Answer 1

There is some improvement with it on the subscriber database.

Note that if your msrepl_commands and msrepl_transaction tables are getting large your distribution cleanup agent may be unable to keep up. I find that if you schedule the log reader agent to stop at a point of low activity (ie 2 am) and then run the distribution agent to process all the back log of commands to be deleted you will get better performance.

Also you may want to disable immediate_sync, and minimize your  history and distribution retention times.

Use the missing index dmv to add indexes to the replication system tables.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 2

May be the below link can help to get some information about enabled read committed snapshot isolation on the distribution database.

http://msdn.microsoft.com/en-us/library/ms345124(v=sql.90).aspx