Question :
I have a heap table with around 700k rows. The rowsize is 34 bytes: (1x BIGINT, 2x SMALLINT, 2x BIT, 4X INT, 1x SMALLDATETIME). Why does Data Space Used say this table is taking up nearly 4000000KB (4GB) in Object Explorer Details. It is compressed with PAGE compression.
I know I must be missing something on this calculation because I have another heap table with 12.7 million rows that is only taking up 1.5 GB and it has a row size of 356 bytes. It is also compressed with PAGE compression.
EDIT:
I just did an SELECT * INTO to see what the size of this data would be in another table and it’s only 28MB…
Answer :
The solution was to create a clustered index on the table which immediately shrank the data size down to 13MB. I found this out thanks to the following post on StackOverflow: https://stackoverflow.com/questions/3336934/reduce-sql-server-table-fragmentation-without-adding-dropping-a-clustered-index
The point is, you have substantial fragmentation on the Heap. You keep calling it a “table”, but there is no such thing at the physical data storage or structure level; a table is a logical concept, rendered physically as:
- either the Heap plus all Nonclustered Indices plus Text/Image chains
- or the Clustered Index plus all Nonclustered Indices plus Text/Image chains.
Heaps get badly fragmented; the more interspersed (random) Insert/Deletes/Updates there are, the more fragmentation.
There is no way to clean up the Heap, as is. MS does not provide a facility (other vendors do).