Table occupies unexpected amount of space

[Gyana Panigrahi]

Hi Experts,

I am not able to find a reason why a table would take so much space in one environment and not in other environment with same/less number of rows.

I ran 

SELECT 

    t.NAME AS TableName,

    i.name as indexName,

    sum(p.rows) as RowCounts,

    sum(a.total_pages) as TotalPages, 

    sum(a.used_pages) as UsedPages, 

    sum(a.data_pages) as DataPages,

    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 

    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 

    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB

FROM 

    sys.tables t

INNER JOIN      

    sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN 

    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN 

    sys.allocation_units a ON p.partition_id = a.container_id

WHERE 

    t.NAME NOT LIKE 'dt%' AND

    i.OBJECT_ID > 255 AND   

    i.index_id <= 1

GROUP BY 

    t.NAME, i.object_id, i.index_id, i.name 

ORDER BY 

    TotalSpaceMB DESC

OUTPUT:

TableName RowCounts TotalPages UsedPages DataPages TotalSpaceMB UsedSpaceMB DataSpaceMB

TABLE_NM 22272978 93921677 90663156 90662503 733763 708305 708300

I see a huge number of pages were used in this environment as compared to other environment.

The table in question is kind of a staging table with deletes and inserts everyday for about 60% of its data.

Any pointers are appreciated!

Thanks in advance.

Gyana

[TheSQLGuru]

HEAP table with DELETEs and no table lock gets you empty pages still allocated to the heap. If this is ETL and no concurrent access you can probably add the table lock hit to the DELETE and all the empty pages will be deallocated. You can put in a dummy row and delete just that with the hint to clear out the space before the next ETL runs if you need it.

[Pete Dolan]

hi admin

you hope you doing well

thanks for contacting me

i really was a wonderfull day on your office

i hope we can talk together again

vanessa

[Gyana Panigrahi]

Thank you for the tip.

I rebuilt the table and that released the space. And I am planning on to use a clustered index on the table.

Thanks,

Gyana

--
You received this message because you are subscribed to the Google Groups "AtlantaMDF" group.
To unsubscribe from this group and stop receiving emails from it, send an email to atlantamdf+...@googlegroups.com.
To post to this group, send email to atlan...@googlegroups.com.
Visit this group at https://groups.google.com/group/atlantamdf.
For more options, visit https://groups.google.com/d/optout.

[Kevin Boles]

If it really is a scratch table, a clustered index could be a poor choice actually. Since you didn't have a clustered index for performance reasons, I would just put the table lock hint on your DELETE and be done with it, assuming no concurrent access issues.

Kevin G. Boles
SQL Server Consultant
SQL Server MVP 2007 - 2012
Indicium Resources, Inc.
@TheSQLGuru

You received this message because you are subscribed to a topic in the Google Groups "AtlantaMDF" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/atlantamdf/k3SObOiHy4Y/unsubscribe.
To unsubscribe from this group and all its topics, send an email to atlantamdf+...@googlegroups.com.