Q: We’ve recently started to use snapshot isolation as a way to avoid blocking problems during our daily updates. This is working well but we’ve found that one table with a clustered index is becoming fragmented. The updates are only affecting fixed-width columns, so why is there fragmentation happening?
A: This is a tactic that we’ve used with a client to allow data loading into a database while concurrent queries are running. By enabling snapshot isolation just before the data loading commences, and switching it off again after the data loading finishes, the locks held by the data load do not block the concurrent queries.
The most well-known side-effect of snapshot isolation is the extra load placed on tempdb. Snapshot isolation works by creating a chain of one or more prior versions of the record being changed. Each version of the record has an associated timestamp indicating the time at which that version was created, plus a pointer to the location of the previous version of the record in the version store, which is located in tempdb.
In a nutshell, whenever a record is changed, the previous version is copied into the version store. When a concurrent query needs to read a record, the storage engine traverses the record’s version chain to find the one that existed at the time the query started. This write and read activity accounts for the extra load on tempdb.
The little-known side-effect of snapshot isolation is the possibility of index fragmentation where you wouldn’t expect it – as you’ve explained you’re seeing.
Whenever a record is updated and a version is created, the new record must have a 14-byte tag appended to it. The tag holds the versioning timestamp plus the pointer to the previous version of the record.
When this operation occurs, the record obviously becomes 14 bytes longer, even if the only updated columns in the record are fixed-width (such as an integer or float column). For records stored in an index, if the page where the record is stored does not have enough free space to accommodate the extra 14 bytes, space must be created – just as if a variable-length column was being updated to be longer.
The free space is created by performing a page split operation – which moves a number of records to a newly-allocated page. This creates fragmentation because the newly allocated page is almost never physically contiguous to the original page. Apart from creating fragmentation, page splits are very costly as all parts of the operation must be fully logged (see my blog post How expensive are page splits in terms of transaction log?).
You can alleviate the likelihood of page splits and fragmentation by using a non-zero fillfactor (the default is to have each page 100% filled) to provision free space during index maintenance to allow for the extra 14-bytes for each versioned record during your update activity.
