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:

http://docs.oracle.com/cd/E38689_01/pt853pbr0/eng/pt/tadm/concept_MicrosoftSQLServerFeatureConsiderations-0779f1.html

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