I just finished reading an excellent article on isolation levels here. Our company will soon start development on a rewrite and expansion of our current product. My desire is to have an OLTP database and a separate, more denormalized, reporting database. Assuming we're somewhat disciplined and most of our ad-hoc and reporting type queries actually go to the reporting database, does it sound appropriate that our OLTP database have a default isolation level of Read Committed (we won't need a more stringent isolation level for OLTP) and our reporting database be Snapshot Isolation (probably RCSI)?
My thinking is that if our OLTP database is actually a true OLTP database and not serving double-duty as a reporting DB, we won't need snapshot isolation, and the associated overhead it entails. But snapshot isolation would be desirable on the reporting database so that readers are not blocked by the constant flow of data coming in, and reading the last saved version of a row would be acceptable.
Best Answer
Just to add to the other answer.
SQL Server supports two different flavors of READ COMMITTED, legacy locking READ COMMITTED and READ COMMITTED SNAPSHOT. If you've ever built and supported a high-volume OLTP application on locking READ COMMITTED you know why RCSI was introduced (in addition to making it easy to port Oracle applications to SQL Server).
Locking READ COMMITTED is tricky to get concurrency and is prone to deadlocking, as readers block writers and writers block readers. Deadlocks are a kind of bug in your application, but they're famously hard to find in testing. So you have a chance to make a choice that reduces the number of hard-to-find-in-testing bugs. That's worth a lot. RCSI also increases the concurrency of your application, enabling you to scale up to use multiple cores more easily.
So, RCSI increases the scalability and reliability of your application, at the cost of a little extra bookkeeping for UPDATES and DELETES (and INSERTS if you have a trigger), and an extra 14 bytes per row.
RCSI is simpler to program to over all, the main thing is that you sometimes need to opt in to a locking read using the UPDLOCK table hint when you want to read a row and immediately update it and need to ensure that no other session does the same thing concurrently.