4

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

Improve this question
9
  • 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, 2014 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, 2014 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, 2014 at 15:05
  • Thomas Stringer: 492145 pages vs only 3563 on the table I did a SELECT * INTO of this data.
    – Brad
    Jun 13, 2014 at 15:07
  • Did you delete columns? LOB columns maybe? Rebuild the table and the space is reclaimed.
    – usr
    Jun 13, 2014 at 15:07

1 Answer 1

Reset to default
3

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

Improve this answer
1
  • 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, 2014 at 14:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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