The Situation:

I have a write-heavy, read-light ETL workload with the basic process of:

1. get chunk of raw data (generally 1000-5000 records) from an import landing table.
2. pass the chunk of data from step 1 to the requesting windows service for some heavy duty calculations & processing. Each record ingested in a chunk may result in 100+ downstream records in other tables.
3. Windows service calls a save SP which has 14 MemOpt table variable parameters containing the processed "chunk" of data (three of these table variables have upwards of 50k rows or more).

• there is very little read ops going on in step 3. There minimal read contention between step 1 & 3.
• I've virtually eliminated deadlocks during step 3, although there is significant blocking during 3 with insert/update ops.

Question:

Would Snapshot or RCSI isolation help when performing the insert/update ops in Step 3?

The Situation:

I have a write-heavy, read-light ETL workload with the basic process of:

1. get chunk of raw data (generally 1000-5000 records) from an import landing table.
2. pass the chunk of data from step 1 to the requesting windows service for some heavy duty calculations & processing. Each record ingested in a chunk may result in 100+ downstream records in other tables.
3. Windows service calls a save SP which has 14 MemOpt table variable parameters containing the processed "chunk" of data (three of these table variables have upwards of 50k rows or more).

• The work is insert heavy and there is very little read ops going on in step 3, however, the "chunk" may contain data to be deleted and/or updated. There is minimal read contention between step 1 & 3.
• I've virtually eliminated deadlocks during step 3, although there is significant blocking during 3 with insert/update ops.

Question:

Would Snapshot or RCSI isolation help when performing the insert/update ops in Step 3? What are the risks of introducing update conflicts?