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?
Best Answer
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:
Next I would consider adding a clustered index.