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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: