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?

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

2 Answers 2

Reset to default
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

Improve this answer
1
  • 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, 2016 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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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