Heap Inserts and Replication

In my previous blog post I talked about how rows are inserted into a heap may lead to space in a page not being used. This is most likely to occur when single rows are inserted, and therefore it occurred to me that transactional replication may be one of the situations where this could lead to data file bloating. This is because transactional replication will convert DML statements on the publisher into singleton commands on the subscriber.

I wrote the following script to test this theory. When I inserted 4 rows into the published table in single statement. You can see from the output of DBCC IND that four rows are inserted into a single data page:




The DBCC PAGE output for this page is similar to the example in the previous post and can be found here.

If I check the subscriber DBCC IND shows two data pages for the replicated table.




The DBCC PAGE output for the subscriber table and shows three slots in use on one page and one slot used on the second page.

This could be a problem which leads to bloated data files on the subscriber, although I suspect it isn’t as common as some may think. A replicated table requires a Primary Key, in the majority of cases I think this is likely to be defined with a clustered index. When this is not the case it is probably because there is an alternate clustered index. This probably means that heaps are not often replicated.