Today I solved an issue for a customer that was a little bit unexpected, at least for me.
The scenario: a table with a few records is periodically updated: a few records are inserted (with a bulk insert) and a few records are deleted (the older ones). It’s a sort of circular buffer. That table hadn’t any indexes or primary key. I know, this is not a best practice, but since the table was very small, I didn’t realize the impact of the missing clustered index…
The problem: after a few months of this table has being in production, query performance joining this table degraded at an unacceptable level. I was astonished when I discovered that table had only a few rows… but something like 2 millions of pages was used by that table, resulting in a 16Gb of size for a bunch of records!. In this condition, a table scan needed a complete read of the table.
The solution: I created a non-unique clustered index on a field representing the date of the row.
The explanation: SQL Server doesn’t release a page of a heap table, and doesn’t use free space in previously used page (this was the very unexpected part) unless you use a table-level lock when you delete data. Details are explained in KB 913399.
Best practice: take care of any heap table you have in a production database if data are deleted and inserted. Standard maintenance plan doesn’t work well in this case, because a simple reindex operation doesn’t compact the heap table.