0

I have a table in an SQL Server 2017 DB used by a lot of long running transactions that originate from multiple threads. This causes deadlocking several times a day so I am considering implementing read committed snapshot isolation. The trick is that this table has 3 VARBINARY(MAX) columns and each of them contains data between 10-1000MB (with the mean around 20 MB) beside several int and bit columns.

Now the questions:

Q1: Will SQL Server copy the entire row (including the VARBINARY(MAX) columns) into the TEMPDB?

Q2: If so, would the performance benefit from moving the VARBINARY(MAX) columns into a separate table with a 1:1 relationship to the original table?

Daniel
  • 1,391
  • 2
  • 19
  • 40

2 Answers2

1

Sql Server has to present you with consistent view on your data (e.g. T2 sees your row, including LOB, as it were before T1 started mutating transaction). Which means -- yes, it has no choice but to copy LOB with the rest of the row data. Which makes me think that yes, performance may benefit from having separate table with LOBs.

As usual, I would recommend doing simple experiment that will measure performance with both configurations. Please post your results here.

Konstantin Surkov
  • 248
  • 1
  • 3
  • 9
  • However, before changing transaction isolation level, which has many other consequences you may not know about upfront, I would recommend to change the code leading to deadlocks to a code that doesn't. – Konstantin Surkov Sep 05 '19 at 19:12
  • Thanks, but I cannot see, how we could do that. We have some long lasting (2-5 min) insert transactions and a lot of selects in the meantime. We cannot wait for the insert to complete before allowing users to obtain data. Selects will always block inserts and vice versa at some point. – Daniel Sep 05 '19 at 20:01
  • Blocking and deadlocks are two very different things. What you say about using snapshot isolation to avoid blocking makes perfect sense. Deadlocks, however, almost always mean "bad code, needs fix". https://stackoverflow.com/questions/34512/what-is-a-deadlock – Konstantin Surkov Sep 06 '19 at 18:30
0

Seams like SQL Server does not copy LOB columns to tempdb on update that does not change those LOB columns. See https://littlekendra.com/2018/05/28/row-width-impact-on-version-store-usage-under-snapshot-isolation/

Pavel Kulakov
  • 1