1

I have a table with nearly 18 million records and it has nearly 14 indexes including a clustered index.

One of the non-clustered index has a fragmentation level of 97%, so when I tried to rebuild the index - fragmentation level goes down to 96% or 94%. But, it is not reducing any further. Few details about the index,

Key columns: 2 (Includes clustered index column)

  • Col 1 - nvarchar(255) – Clustered index column
  • Col 2 - nchar(16)

Included columns: 2

  • Col 1 - smallint
  • Col 2 - smallint

Pages: 143539
Index size: 1.3 GB
Fill factor: 90
Row locks: True
Page locks: False
Pad index: False

Any ideas why fragmentation level is not reducing?

  • Show us the table definition and index definition. You can change the column names if you want to. – Randolph West Oct 14 '16 at 14:23
  • @RandolphWest - Table has 72 columns, so I just updated the above post with columns used in the index – Naveen Oct 14 '16 at 15:02
  • Try rebuilding with maxdop 1. – Kin Shah Oct 15 '16 at 1:33
  • What command did you use to rebuild the index? What is the index definition? – Randolph West Oct 15 '16 at 5:44
  • @kin Thanks kin. It did worked for me after using maxdop 1. – Naveen Oct 21 '16 at 12:56
0

This might be due to MAXDOP setting on your server instance. It has been proven that Online Index Rebuild – Can Cause Increased Fragmentation when it is allowed to run with MAX DOP > 1 and ALLOW_PAGE_LOCKS = OFF directives.

If you are seeing this behavior, then its better to use (MAXDOP 1) (serial index rebuild) at query level when rebuilding index.

e.g.

Alter Index all On test2 Rebuild With (maxdop = 1)

Refer to : How It Works: MAX DOP Level and Parallel Index Builds

  • After your earlier comment (on Oct 15), I did referred to exactly those two links which you mentioned here. It did worked. Thank you again. – Naveen Oct 26 '16 at 11:58
0

The issue could be not enough contiguous free space inside the database to arrange the index consecutively. A work around to a very large index that won't defragment, one solution is to pre-size a new database and move all your objects to that DB, then recreate your indexes there as this will surely do the trick.

  • Thank you, but copying the database isn't viable for me at this moment. – Naveen Oct 14 '16 at 15:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.