May 14, 2013 at 10:31 am
Looking to free up some disk space on a server, and have a question.
The DB itself, we're going to enable page level compression on the data and non-clustered indexes (testing shows that enabling compression on the table will also enable it on the clustered index) We're also going to shrink the data file down a few 10s of GB.
Now, I know that the shrink of the file will horrifically fragment the indexes, so we're going to also rebuild the indexes when the file shrink completes (and I'll be leaving room in the file for the data to grow without growing the file)
Looking at the T-SQL for enabling compression on an index, it looks like I can do the rebuild and compression of the non-clustered indexes in one step:
ALTER INDEX IX_INDEX_1
ON T1
REBUILD WITH ( DATA_COMPRESSION = PAGE ) ;
GO
(Yes, this is straight from the MSDN page on compression)
Of course, I also just realized I'll probably also need to rebuild the Clustered Index as well...
But, my question stands, will the Alter Index both compress and rebuild (removing / reducing fragmentation) the indexes?
Thanks,
Jason A.
May 14, 2013 at 2:33 pm
jasona.work (5/14/2013)
(testing shows that enabling compression on the table will also enable it on the clustered index)
The clustered index IS the table.
But, my question stands, will the Alter Index both compress and rebuild (removing / reducing fragmentation) the indexes?
Yes, but you'll need to compress before you shrink (or there'll be no free space), hence requiring a second rebuild afterwards.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 14, 2013 at 4:05 pm
Thanks Gail!
My plan is:
1. Compress the Clustered Index / Data
2. Shrink the file
3. Rebuild the Clustered Index
4. Compress and rebuild the non-clustered indexes
Once more, thank you!
Jason
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply