SQL SERVER – Rebuilding Index with Compression

To rebuild an Index with Compression, I target its own settings and partitions. Customer ROW and PAGE results need separate evaluation.

SELECT i.name,p.partition_number,p.data_compression_desc
FROM sys.indexes i JOIN sys.partitions p ON p.object_id=i.object_id AND p.index_id=i.index_id
WHERE i.object_id=OBJECT_ID(N'Production.TransactionHistory')
ORDER BY i.index_id,p.partition_number;
-- Original table/heap or clustered-data rebuild shapes:
-- ALTER TABLE Production.TransactionHistory REBUILD WITH (DATA_COMPRESSION=ROW);
-- ALTER TABLE Production.TransactionHistory REBUILD WITH (DATA_COMPRESSION=PAGE);
-- An individually selected nonclustered index has its own setting:
-- ALTER INDEX [YourIndex] ON Production.TransactionHistory REBUILD
--     WITH (DATA_COMPRESSION=PAGE);

My old ALTER TABLE … REBUILD examples targeted table data. They did not set every nonclustered index compression option. Use ALTER INDEX for the intended named index. Inspect relevant partitions first.

ROW and PAGE can save space and reads while costing CPU. Eligibility depends on engine, edition and object. Rebuilds consume resources and log space. Plan locks and supported online behavior.

Estimate savings and compare representative reads, writes, CPU and duration on a copy. Preserve earlier settings and rollback definitions. Retaining the current configuration is valid when measurements don’t support change.

Reference: Index rebuild and independent compression settings.

Related reading

• Comprehensive Database Performance Health Check
• SQL in Sixty Seconds series
• MAX Columns Ever Existed in Table – SQL in Sixty Seconds #182
• Tuning Query Cost 100% – SQL in Sixty Seconds #181
• Queries Using Specific Index – SQL in Sixty Seconds #180
• Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
• One Scan for 3 Count Sum – SQL in Sixty Seconds #178
• SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
• COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176

A table rebuild is not automatic compression of every nonclustered index, it is work on the targeted storage structure.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.