patternsqlMinor
Rebuilding an giant index in SQL Server
Viewed 0 times
sqlgiantserverindexrebuilding
Problem
I am facing a problem with an rather big (Non-Clustered) index ~6 T on one of my tables in my SQL Server database. According to the "Disk Usage by Partition" report only around ~2 T are actually used. As we are constantly inserting into the database it is unsurprising that the data gets pretty fragmented. So the next step should be to rebuild the index.
Here my problem is though that the disk is rather full with only around 400 GB of free space. When I try to rebuild this index it task quite some time and fails then. (I missed writing down the exact error message but it basically sad that it failed due to missing space). Also the complete disk was full afterwards.
Is there a way to still rebuild this index?
Is it may be the solution is to delete the index and just recreate it? (I guess this should work but as I inherited the Database with many of its configuration I don´t want to break anything). Or does this have any consequences I am missing?
Here my problem is though that the disk is rather full with only around 400 GB of free space. When I try to rebuild this index it task quite some time and fails then. (I missed writing down the exact error message but it basically sad that it failed due to missing space). Also the complete disk was full afterwards.
Is there a way to still rebuild this index?
Is it may be the solution is to delete the index and just recreate it? (I guess this should work but as I inherited the Database with many of its configuration I don´t want to break anything). Or does this have any consequences I am missing?
Solution
If the table (or rather: index) is partitioned, then you could consider rebuilding at partition level.
Also, since this is a non-clustered index, you could drop and then create the index. ALTER INDEX REBUILD will create a new index, and then drop the old one. By droppring the index first, you don't need double space (old and new).
Based on your insert pattern, rebuild might not buy you a lot, though. If you insert "all over the place", then you will get splits after your rebuild and after a while you are back to where you started.
Also, since this is a non-clustered index, you could drop and then create the index. ALTER INDEX REBUILD will create a new index, and then drop the old one. By droppring the index first, you don't need double space (old and new).
Based on your insert pattern, rebuild might not buy you a lot, though. If you insert "all over the place", then you will get splits after your rebuild and after a while you are back to where you started.
Context
StackExchange Database Administrators Q#307620, answer score: 3
Revisions (0)
No revisions yet.