Heap size too large for data present

Question

A SQL Server 2008 R2 Standard Edition database has a staging table with no indexes, eight varchar(50) and one varchar(255) columns, and 162,676 rows. I'd expect that this table shouldn't be much over 100 MB in size, however it actually exceeds 7.5 GB.

What can cause this? I suspect that I can make the problem go away by adding a clustered index or recreating the table completely, but I want to understand what's going on that makes one staging table inflate to such ridiculous proportions. Here's what I've gathered as pertinent data so far:

sys.dm_db_index_physical_stats (DETAILED)
    fragment_count = 1056
    page_count = 964392
    forwarded_record_count = 0

sp_spaceused @objname = 'db.schema.table',@updateusage = 'TRUE'
    reserved = 7716888 KB
    data = 7715136 KB
    index_size = 24 KB
    unused = 1728 KB

sys.allocation_units
    total_pages = 964611
    used_pages = 964395
    data_pages = 964392

Can anyone tell me why this table is occupying so much space?

Answer 1

Chances are that a lot of data has been updated or deleted, or you have adjusted the size of some of your columns, or you have truncated / deleted the table and re-loaded many times. Some of these operations can lead to un-reclaimed space in some scenarios. I would try this first:

ALTER TABLE schema.table REBUILD;

Next I would consider adding a clustered index.

Answer 2

Chances are the physical table layout is vastly different from the logical layout. This is specially true with staging tables. See SQL Server table columns under the hood for more details, including a query that will show the physical layout:

select p.index_id, p.partition_number,
    pc.leaf_null_bit,
    coalesce(cx.name, c.name) as column_name,
    pc.partition_column_id,
    pc.max_inrow_length,
    pc.max_length,
    pc.key_ordinal,
    pc.leaf_offset,
    pc.is_nullable,
    pc.is_dropped,
    pc.is_uniqueifier,
    pc.is_sparse,
    pc.is_anti_matter
from sys.system_internals_partitions p
join sys.system_internals_partition_columns pc
    on p.partition_id = pc.partition_id
left join sys.index_columns ic
    on p.object_id = ic.object_id
    and ic.index_id = p.index_id
    and ic.index_column_id = pc.partition_column_id
left join sys.columns c
    on p.object_id = c.object_id
    and ic.column_id = c.column_id
left join sys.columns cx
    on p.object_id = cx.object_id
    and p.index_id in (0,1)
    and pc.partition_column_id = cx.column_id
where p.object_id = object_id('...')
order by index_id, partition_number;