RCSI Questions

• Mike Scalise

  SSCrazy Eights

  Points: 8145

  More actions

  January 30, 2020 at 10:33 pm

  #3721805

  Hi All,

  I have a few questions regarding RCSI that I'm hoping someone can answer (or at least confirm that my understanding is correct).

  1. When RCSI is enabled at the database level, and a query that would take an exclusive lock on a record is run (i.e., INSERT, UPDATE, DELETE), SQL Server copies that record into tempdb so that if any SELECTs try to read that record or table, they get their data from the tempdb copy. I know there's more to it and it gets deeper etc., but I just want to make sure that it's the query that needs an exclusive lock that's the reason SQL Server saves the original version of the row and not anything related to any of the SELECTs needing to read that data. Is that correct, or do SELECTs also make a copy of the row they're about to read and read that row from tempdb too?
  2. Now, I recently read a thread that suggests RCSI can be used on a per query basis if it's turned off at the database level:

  "just at the beginning of your query put:

  set transaction isolation level snapshot

  go

  select .... bla bla bla...

  SQL will pull the latest value form tempdb if the rows is exclusively locked in the queries table."

  https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c9cf9b1d-9904-4c65-b3e1-132e5092e7a4/rcsi-for-a-single-query?forum=transactsql

  Now I'm not sure of the answer to #1, but let's say that RCSI is not enabled in the database, would I need to use "set transaction isolation level snapshot" before both SELECT statements and action queries (UPDATE, INSERT, DELETE). In his example, it's before a SELECT statement and it seems that command instructs SQL Server to specifically look in tempdb for the row, whereas I thought RCSI was less about instructing SQL where to look for a record and more about whether or not to make a copy of the record--in which case I would expect to only need to use "set transaction isolation level snapshot" before any action queries...but maybe it's kind of required in both scenarios to always ensure neither query blocks the other?

  Does it say to the updates, "hey, make a copy of this record in tempdb" and to the selects say, "hey, I don't know if there are any copies of the record(s) you want in tempdb, but check there first"?

  Does my explanation make sense? I'm just trying to get a definitive answer on how this works on a per query basis across the different types of queries (and whether it's useless to put it before some of those queries), specifically when RCSI is not enabled at the database level.

  Thanks in advance for helping to clear this up for me.

  Mike

  • This topic was modified 4 years, 9 months ago by  Mike Scalise.

  Mike Scalise, PMP
  https://www.michaelscalise.com

• Grant Fritchey

  SSC Guru

  Points: 398882

  More actions

  January 31, 2020 at 10:24 am

  #3721947

  It's not per query, it's per connection. You can define the connection to be RCSI. Then, all queries run by the connection follow the rules as you outlined accurately. However, I wouldn't recommend it. It means extra coding. I'd just go for the RCSI as the default setting and run from there.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• Mike Scalise

  SSCrazy Eights

  Points: 8145

  More actions

  January 31, 2020 at 3:01 pm

  #3722032

  Thank you, Grant! That actually clears everything up. I wasn't planning on using RCSI at a connection level, but rather just trying to understand the different ways in which it could be used.

  Thanks again,

  Mike

  Mike Scalise, PMP
  https://www.michaelscalise.com