Kendra Little shows us the impact that row width has on snapshot isolation:
So I went to work to demonstrate row width impact on the version store — when only a tiny bit column is changed in the row.
Here’s how I did the test:
- I created two tables, dbo.Narrow and dbo.Wide. They each each have a bit column named bitsy, along with some other columns.
- I inserted one row in each table, but I put a lot more data into the row in dbo.Wide.
- I allowed snapshot isolation on the database
- I began a transaction in another session under snapshot isolation and left the transaction open (so version store cleanup wouldn’t kick in while I looked around)
- I updated the bit column named bitsy for the single row in each table, thereby generating a row-version in tempdb for each table
The code I ran to test this is here, if you’d like to play around with it.
Read on for the results.