Geek City: How SQL Server Detects the Correct Versioned Rows

re: Geek City: How SQL Server Detects the Correct Versioned Rows

Michael Zilberstein — Thu, 03 Apr 2008 21:26:24 GMT

So this should work for indexes too? If query uses covering index, it may never come to data (or clustered index if the one exists) leaf level. So indexes should also keep additional 14 bytes. But for index row size which is usually small, additional 14 bytes per row can have pretty serious negative performance impact. Does it really work this way or some optimization exists that I can't think about?

re: Geek City: How SQL Server Detects the Correct Versioned Rows

Kalen Delaney — Fri, 04 Apr 2008 03:33:34 GMT

Hi Michael

Index rows do get versioned, so they need to keep the 14 bytes also.

I just ran a test on a table with a nonclustered index of type money, so it was 8 bytes, plus about 8 bytes of overhead in each row before snapshot isolation. I looked at an index page with DBCC IND and there were 426 rows on a page.

I then enabled the db for snapshot, and updated the money column for all rows in the table. When I looked at the index page again, there were now only 213 rows on the page, and each row was marked as containing VERSIONING_INFO. So yes, this can have a big impact on space usage, and thus on performance.

You didn't think you got snapshot isolation and non-blocking reads for free, did you? There are costs associated with snapshot isolation, in addition to this one.

~Kalen

re: Geek City: How SQL Server Detects the Correct Versioned Rows

Tom — Mon, 07 Apr 2008 02:09:45 GMT

Thank you very much for responding so fast, it helps!

re: Geek City: How SQL Server Detects the Correct Versioned Rows

Kalen Delaney — Mon, 07 Apr 2008 03:08:12 GMT

Oops, I need a correction.... I used DBCC IND to find a page number, and then DBCC PAGE to look at the page.