The Curious Case of: the expanding table records

Question: (From a customer system I was examining this week…) My database has a very simple table with two bigint columns and no clustered index. When examining the database using sys.dm_db_index_physical_stats, I noticed that this heap has lots of forwarded records. How can that happen with such a simple table with fixed-width columns?

Answer: As I mentioned, this was a customer system I was looking at so I was able to dig in. The output from sys.dm_db_index_physical_stats showed that the record size varied from 23 bytes to 55 bytes and about half the records were forwarded.

As a bit of background, when a data record in a heap expands and there is not enough free space on the current page, it gets moved to a new page and a small ‘forwarding record’ is left in it’s place, pointing to the new location. I talk about this in more depth in my blog post Forwarding and forwarded records, and the back-pointer size.

For a table with fixed-width columns, normal DML operations should not cause the record to expand – except in the case where one of the snapshot isolation levels is being used. In that case even an update to a fixed-width column will result in a record version being copied into the version store in tempdb, and a 14-byte pointer added to the end of the updated table record. This extra 14 bytes could cause a record to become too large for it’s current location and require being forwarded as described above.

But the customer has never used snapshot isolation, so that was not the answer.

The next question I asked was whether the table had always had two bigint columns or whether it had started with one, and another was added later; or whether the columns had originally been ints and then change to bigints—both of which would have caused record expansion.

But again the customer said that neither was the case. They also said they had never added and removed columns on the table either.

At this point I asked to look directly at the database and examined some of the table pages using DBCC PAGE. Lo and behold , there were extra columns in some of the records—a single GUID column in some (making the records 39 bytes) and two GUID columns in others (making the records 55 bytes). Bingo!

But again the customer said they had not added those columns at any point.

Racking my brains for a bit, I came up with the answer: merge replication. It adds a hidden GUID to each table record that is changed.

And yes, the customer had added and removed merge replication several times as part of some testing and this caused the extra GUID columns in some table records but not others (depending on when the records were added).

Case solved!