Index rebuild - Not reducing fragmentation

Question

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?

Answer 1

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

Answer 2

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.