asked
How to defragment an index file using sql 2008R2
I have an index file that needs to be defragmented but am not sure about how to do this. We are using SQL Server 2008 R2.
Do you want to rebuild or reorganize the index? Usually the rule of thumb (your mileage may vary) is that if the index is less than 30% fragmented you reorganize if it's greater than 30% you rebuild the index. A basic rebuild command would look something like this: ALTER INDEX [indexName] ON [SchemaName].[TableName] REBUILD; There are a number of options that you can do when rebuilding/reorganizing so I'd probably suggest reading up on the reference link below Reference:
https://msdn.microsoft.com/en-us/library/ms188388(v=sql.105).aspx Hope that helps!
In addition, if the index in question is a clustered index, its rebuild will cause all data to move, in which case I would also consider rebuilding all nonclustered indexes afterwards. Generally speaking, if the rule to make sure that the clustered index' columns values are ever-increasing and never changing is used, the clustered index will not get fragmented over time. However, if the rule is ignored, such as when the table is clustered in the evil manner based on the uniqueidentifier column with newid() default, which always causes high levels of fragmentation, defragmenting the nonclustered indexes afterwards should be considered.
If avg_fragmentation_in_percent > 5% and < 30%, then use ALTER INDEX REORGANIZE: This statement is replacement for DBCC INDEXDEFRAG to reorder the leaf level pages of the index in a logical orde
21 People are following this question.
