question

rdavison avatar image
rdavison 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.
indexessql server 2008r2
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image
JohnM answered
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!
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
2 Likes 2 ·
jason_clark03 avatar image
jason_clark03 answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.