After one rebuilds their SQL Server indexes using page compression (ALTER INDEX IX1 REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)
), do subsequent rebuilds (as done by some maintenance scripts past a certain fragmentation threshold) need to specify data compression again? Would the indexes otherwise be effectively decompressed?
SQL Server Index Compression – Do Compressed Indexes Remain Compressed on Rebuild?
compressiondata-pagesindexsql server
Related Question
- Sql-server – Database Indexing – Maintenance Jobs
- Sql-server – index rebuild/reorganize frequency
- Sql-server – SQL Server Compression Estimate Is Significantly Lower When Estimating Current Compression Type
- Sql-server – SQL Alter Index within a Script
- SQL Server – How to Alter Index Within a Script
- SQL Server Index Rebuild vs Drop/Create – Functional Differences
- DB2 Alter Column Type – Understanding RBDP vs. ‘Needs a Rebuild’ States
Best Answer
Indexes remain compressed when rebuilding / reorganizing them.
Create table and compressed index
Check Compression
Result
Rebuild the index
Check Compression
Result
Disabling them and then rebuilding has a different result, since disabling removes the index, while keeping the index definition.
Result
Compression was lost, compression definition would also be lost when dropping and creating the index via SSMS without adapting the Index create script.
Why?
Because the data_compression option is not retained when scripting out the Index create statement.
however, if we disable the index , rebuild with compression and then rebuild again:
Result
Testing a rebuild with Ola hallengren's maintenance solution
The parameters are modified for testing purposes.
Add some data to get to one page, as it is needed for the MinNumberOfPages parameter.
Execute the index optimize proc to print out the statement.
Result:
Executing the generated command
Compression is retained
Testing a rebuild with a maintenance plan (I would strongly argue for ola's solution)
Rebuild indexes
Choose the test table
Add some test fragmentation levels.
Insert some values to get the fragmentation going
Check the fragmentation percentage
Result
Run the plan
The interesting part here, when looking at the plan report, is that the
DATA_COMPRESSION = PAGE
option is added to the generatedREBUILD
command!Fragmentation:
Compression: