SQL Server Read Committed Snapshot isolation with LOBs

2guxujil 于 2023-05-28 发布在其他

关注(0)|答案(2)|浏览(124)

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?

sql-server

来源：https://stackoverflow.com/questions/57811396/read-committed-snapshot-isolation-with-lobs

2条答案

按热度按时间

z4bn682m1#

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.

赞(0）回复(0）举报 2023-05-28

hts6caw32#

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/

赞(0）回复(0）举报 2023-05-28