2

I have a fairly large database that basically consists of one table, this table is paritioned by month and all partitions are compressed with page compression. After the initial load of the data there have been update operations (basically one column was set from NULL to an tinyint value). After that I noticed that in the disk usage report showed on the files that the space reserved did grow very large compared to the space used (81 GB vs. 24 GB). This obviously results in a huge waist of disk space. Compressing the partition and shrinking the file results in an awfully fragmented clustered index for that partition.

What would be best practice to reduce the disk space usage to the absolute minimum ?

1

Rebuild the index to write out all pages freshly in the optimal way.

If you want to absolutely minimize space usage, specify ONLINE = OFF. Online operations add a small amount of per-row space overhead.

MAXDOP = 1 can be good to reduce fragmentation. Not sure if that can help with space usage, though. I can't think of anything that a DOP of 1 might save.

If the target file of the rebuild already contains fragmented freespace the newly built index/partition can turn out to be fragmented immediately. Consider building into an empty target filegroup that is pre-sized to approximately the right size.

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.