August 21, 2025 at 1:20 pm
We upgraded an application and are seeing performance issues. The vendor suggests enabling READ_COMMITTED_SNAPSHOT (RCSI) to “improve performance.”
Symptoms: performance degrades in the afternoon when there’s more reporting/querying against the production database. Reports are data heavy. We don’t yet have a separate reporting server, but that’s likely coming.
Questions:
What we’ve tried: in our test environment, with a lighter workload, turning RCSI on made the app feel slower; turning it off returned performance to normal. We understand RCSI can reduce reader/writer blocking, but it also adds tempdb version-store overhead. We’re trying to decide whether to keep RCSI off for now, move tempdb to faster dedicated storage, and focus on a reporting replica—or enable RCSI after moving/tuning tempdb.
Any insight on RCSI’s real world trade offs for this scenario, and whether tempdb isolation alone is a worthwhile first step, would be appreciated.
August 21, 2025 at 8:30 pm
My understanding is that with write heavy databases, RCSI can help as the read operations are no longer blocked by the writes. That being said, you are going to see more disk usage in tempdb due to the snapshots. BUT like all things SQL Server - it really depends. It depends on the reason for the slowness in the app. That is where you should start your investigation, not changing server-side settings.
Are you having problems with the reports due to blocking or due to missing indexes or poorly written queries (cursors for example)? And what are your bottlenecks? Are you seeing the Disk I/O as a bottleneck? If yes, then faster disk or having dedicated disk may improve performance. I say "MAY" because if you are in an environment with a SAN for your storage (likely), having a separate logical disk doesn't mean you have a separate physical disk.
Knowing what will make a difference really depends on your environment. If disk I/O isn't a bottleneck, moving tempdb to it's own physical disk may not make any difference. If blocking isn't a problem, RCSI likely won't help performance.
What I recommend is to determine the cause of the slowness. If an app upgrade happened and you made no changes to the DB, the problem is likely more related to the app than the database BUT may have database issues too if the company who made the app did a poor job on the database side (such as missing indexes). The slowness in the app MAY even be related to the machine running the app. Might not hurt to check what's going on in the computer/server when performance gets slow. I've seen times where the database is blamed for slowness and it's caused by the application paging to disk for example. Before making ANY database changes, find the bottleneck so you can address the problem. If your car won't start, you don't throw out the car and buy a new one - you check things out (battery, oil, gas, etc) or take it to a mechanic (depending on your skills) and diagnose the cause of the problem before you do anything drastic.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 22, 2025 at 2:56 pm
You’re right that “it depends,” so we started with measurement. In our afternoon windows the sessions that line up with user complaints are Cognos runs from our reporting servers showing ASYNC_NETWORK_IO, which means SQL has rows ready and is waiting for the client to read them. That’s not a server CPU or disk stall. At the same time, storage latency on both the main database and tempdb has been sub-millisecond when we sampled, so we’re not seeing a steady disk I/O bottleneck. When we tested RCSI, some users felt faster with it off, which makes sense for our write-heavy OLTP screens because row versioning adds tempdb write pressure and long reads can hold versions alive. RCSI still has value for reducing reader-writer blocking during report bursts, but it will not fix slow client fetch/render or a report that returns too many rows. We also saw recurring heavy query patterns in the PM snapshots pointing to a few tables that likely need targeted indexing; that’s an application/query design so I'll have to bring that up with the vendor.
The plan is to keep using evidence to decide changes. We’re coordinating with reporting to identify the exact reports/users at those timestamps, add the missing or reporting-friendly indexes so the vendor can fix them, and stand up a proper reporting copy (transactional replication if funds allow) so Cognos runs do not compete with OLTP. We’ll move tempdb to its own physical RAID set so bursts have their own queue path, which helps whether RCSI is on or off. We’ll continue to monitor peak-hour waits, blocking chains, and per-file latency to validate each change. This keeps us focused on the real bottlenecks first, and uses RCSI and faster tempdb only where they demonstrably improve the afternoon experience.
Thanks for the response.
August 29, 2025 at 6:28 am
You need to test the impact using a non-production system. However I have found RCSI to mostly be a useful thing, to the extent that I made it standard for just about all databases.
One key thing to remember is that Oracle has its equivalent of RCSI enabled by default, and you have to do quite a bit to disable it. There are a vast amount of databases running on Oracle, and for them RCSI works fine.
The underlying mechanism used by Oracle for its RCSI implementation is not that much different to how SQL Server works. Oracle has a special store for its rollback segments while SQL Server keeps its equivalent in tempdb.
This means that turning on RCSI for SQL Server needs attention given to tempdb placement and sizing. Get this right and RCSI can be a useful option. There are overheads in maintaining read consistency, but there are overhead reductions as many locks no longer need to be taken. This leads to throughput improvements as readers and writers no longer block each other.
It also eliminates any justification for the (NOLOCK) hint, which is often proposed out of ignorance and with no understanding of the real risk of including ghost records within the query data.
However not all databases are suitable for RCSI. If you have an application that uses multi-threaded services to get and update data, then with RCSI enabled each service will operate independently and get its own set of data. With RCSI off the services can interact more with each other.
A good/bad (depends on your POV) of this is Reporting Services and PowerBI. If you want a report that shows 'Page x of y' this only works with RCSI turned off for the ReportServer DBs.
So, RCSI should not be feared and can be mostly beneficial (as in earth described as mostly harmless in HHGTTG). But it does need some testing and you do need to look at where tempdb is located.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply