Problem: Table size much larger than expected

Problem: 80GB table should only be around 10GB

Solution: Identified excessive fragmentation using dm_db_index_physical_stats. rebuilt the table reduced fragmentation and reduced the table size down to a more appropriate size. Fragmentation likely caused by frequent updates on a table. In this case the table was a heap so we performed an alter table.

On an indexed table we can use alter index – reorganize for a similar effect.

eg

select OBJECT_NAME(object_id),* FROM sys.dm_db_index_physical_stats(10,NULL,NULL,NULL,’LIMITED’) ORDER BY fragment_count DESC

ALTER INDEX ALL ON schema.tab;e REORGANIZE

ALTER TABLE schema.table REBUILD