Read Commit Snapshot Isolation (RCSI) is a feature in SQL Server that can help alleviate blocking in an application.
From an application point of view, there are pro’s and con’s. They are well enumerated in this post here:
http://sqlperformance.com/2014/05/t-sql-queries/read-committed-snapshot-isolation
With the applications I work with a lot (PeopleSoft, Siebel, & JD Edwards), it’s encourages to use this feature:
So with so much going for it, what’s the downside?
SQL Server uses tempdb for row versioning. It keeps the BEFORE copy/version of the data in tempdb. As such, tempdb gets hammered a lot more than a database without RCSI. This is discussed here:
https://www.simple-talk.com/sql/t-sql-programming/row-versioning-concurrency-in-sql-server/
Since tempdb is getting a lot more IO, you need more IOPS from your disk subsystem to support this new workload. There are only two ways to get this… use SSD or buy a lot more physical drives to handle the workload. I think about it like a car engine. Each cylinder does “x” many units of work. The more cylinders in and engine, the more work the engine can handle. Keep in mind your RAID levels, too. RAID 5 puts a 42% penalty on all writes. So NEVER use RAID 5 for tempdb as it’s 80% writing all the time.
Here’s what happened to a customer who is running a 300GB PeopleSoft database and turned on RCSI without re-sizing tempdb… write latency at 15 seconds… yes… seconds!
vReadLatency vWriteLatency DB FileName ------------ ----------------- ------ ---------------- 14 15032 tempdb T:\tempdb3.ndf 14 15032 tempdb T:\tempdb4.ndf 13 15030 tempdb T:\tempdb5.ndf 20 15025 tempdb T:\tempdb.mdf 14 15028 tempdb T:\tempdb7.ndf 17 15027 tempdb T:\tempdb8.ndf 13 15024 tempdb T:\tempdb6.ndf 19 12997 tempdb T:\tempdb2.ndf
There are many blog postings on sizing for RCSI. Just Google: “sizing tempdb for rcsi”
The easiest, cleanest, and often the cheapest solution is to just use local attached SSD for tempdb. Since tempdb is rebuilt every time SQL Server is cycled, you don’t need to RAID it, etc…
First choice for SSD would be something connected to the PCI bus. These are always faster. Second choice is something connected to the SATA/SAS bus… you will bottleneck on the bus… but it’s faster than than physical disk and a lot cheaper than SAN disk drives.
Script to see latency (Courtesy Jimmy May):
SELECT --virtual file latency vReadLatency = CASE WHEN num_of_reads = 0 THEN 0 ELSE (io_stall_read_ms/num_of_reads) END , vWriteLatency = CASE WHEN num_of_writes = 0 THEN 0 ELSE (io_stall_write_ms/num_of_writes) END , vLatency = CASE WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0 ELSE (io_stall/(num_of_reads + num_of_writes)) END ----avg bytes per IOP --, BytesperRead --= CASE WHEN num_of_reads = 0 --THEN 0 ELSE (num_of_bytes_read/num_of_reads) END --, BytesperWrite --= CASE WHEN num_of_writes = 0 --THEN 0 ELSE (num_of_bytes_written/num_of_writes) END --, BytesperTransfer --= CASE WHEN (num_of_reads = 0 AND num_of_writes = 0) --THEN 0 ELSE ((num_of_bytes_read+num_of_bytes_written)/(num_of_reads + num_of_writes)) END --, LEFT(mf.physical_name,2) as Drive , substring(DB_NAME(vfs.database_id), 1, 10) as DB --, mf.name AS FileName --, vfs.* , substring(mf.physical_name, 1, 15) FROM sys.dm_io_virtual_file_stats(NULL,NULL) as vfs JOIN sys.master_files as mf ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id --WHERE mf.type_desc = ‘LOG’ — log files --WHERE DB_NAME(vfs.database_id) IN (‘tpcc’,’tpcc2′) ORDER BY vLatency DESC
Mission Critical Computing 