I have a customer that is getting long blocking chains caused by reporting against OLTP data. I have successfully setup replication to report against a subset of the tables for another customer and this has worked fine. The only downside to this, besides
the replication performance impact, is that if many reports are running and updates are happening at the same time, the second report (and up) will be blocked. The blocking chain is the first report blocks the replication agent which blocks
the second report and so on. This is probably acceptable but I want to make sure it is the best option. Therefore, I would like to look into using either read committed snapshot isolation (RCSI) or the snapshot isolation (SI).
1) Are RCSI and SI mutually exclusive options? From my testing, I could turn on one without turning on the other. However, I read a blog that said you can only turn on SI once RCSI has been turned on for the database. Is this true? I am assuming the blog
2) If I only turn on SI and only ever use a "SET TRANSACTION ISOLATION LEVEL SNAPSHOT" statement in read only reports, the application (non-reports) should function the exact same correct? (Excluding the added tempdb space used). Obviously a huge dilemma
with setting any of these snapshot levels is trying to prevent the changes to how the applicati
View Complete Post