Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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...

share|improve this question
    
Where are you reading the space used from? Something like sp_spaceused that separates the data/index/unallocated pages? If not have you deleted a lot of data from that table at any point? –  David Spillett Jun 13 at 15:00
    
How many pages does the table have? select sum(page_count) from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.YourTableName'), default, default, 'detailed'); (substitute dbo.YourTableName with the appropriate table name) –  Thomas Stringer Jun 13 at 15:01
    
David Spillett: I'm using Object Explorer 'Data Space Used' column, though I also have a query that looks at the sys.allocation_units table and it's reporting the same sizes. –  Brad Jun 13 at 15:05
    
Thomas Stringer: 492145 pages vs only 3563 on the table I did a SELECT * INTO of this data. –  Brad Jun 13 at 15:07
    
Did you delete columns? LOB columns maybe? Rebuild the table and the space is reclaimed. –  usr Jun 13 at 15:07

1 Answer 1

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: http://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).

share|improve this answer
    
Heaps don't get logical fragmentation. You were probably hitting the issue here. I suppose that might be regarded as internal fragmentation. I have no idea what random inserts in a heap are supposed to be. They always just go where there is space. –  Martin Smith Jun 15 at 14:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.