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

    total_pages = 964611
    used_pages = 964395
    data_pages = 964392

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

  • Why are there no indexes? – Aaron Bertrand Oct 2 '12 at 21:52
  • For the same reason the columns are all 50 or 255 wide--because it defaulted that way and no one knew better. I'm out to fix it, but I want to make sure I understand it as much as possible. We have hundreds of similar tables; this is the only one I've noticed this bloat on, but I'd be surprised if it's the only one. – SQLFox Oct 2 '12 at 22:05
  • Is there anything different about the process that utilizes this staging table compared to the others that don't balloon to these proportions? – Aaron Bertrand Oct 3 '12 at 0:05
  • The package for this table uses a DELETE when most of them use TRUNCATE, but it's not the only one. Other than that, it's just an ODBC destination in a very simple data flow. – SQLFox Oct 3 '12 at 13:41

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.

  • Is that syntax new to SQL Server 2008? I've never seen it! – Max Vernon Oct 2 '12 at 22:26
  • I rebuilt the table and it's now less than 14 MB. Y'all were leading me to it, but I think looking up DELETE WITH (TABLOCK) made it sink in why the pages weren't being deallocated. The simplest explanation seems to be that the table was once loaded with way too much data, and those pages have been allocated ever since. I notice now that avg_page_space_used_in_percent has jumped greatly, so that may help me find other offenders. – SQLFox Oct 3 '12 at 13:47

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,
    coalesce(cx.name, c.name) as column_name,
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;
  • Interesting info, but in this case, this query returns the expected layout – SQLFox Oct 3 '12 at 13:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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