Rebuild index in SQL Server does not reduce fragmentation

Posted on

Question :

I am trying to reduce the fragmentation of my indexes.

First of all I rebuild all indexes with fragmentation higher than 30%, but the most of my indexes still with high fragmentation or does not even change 1%.

Looking deeply, when I reduce the fill factor on the rebuild statement then reduces the fragmentation too.

Now, what is the best option?

A higher fragmentation with a high fill factor or reduce the fill factor and then gets lower fragmentation?

Thanks for now.

Answer :

From this connect item Index Rebuild Doesn’t Affect Fragmentation

For small tables, usually performance impact on fragmentation is
undectable. The first 8 page allocation would be from mixed extents
and mixed extents could be anywhere in database files. Rebuilding
indexes would not change this nature.

If you have a small table, those mixed pages weight a lot during
fragmentation calculation; therefore, rebuilding index may not reduce
fragmentation. (As matter of fact, I could easily construct a case
that fragmentation increases after rebuild.) Those fragmentation would
not be a pain for your query performance; so basically you can ingore
them.

When page counts of an index reaches to certain big size (for example,
1000 pages), then fragmentation may start to impact performance.
Rebuilding index should reduce fragmentation.

Therefore probable reason rebuilding does not lower the fragmentation value due to fact that after rebuild the pages which are allocated to index is from Mixed extent.

Also, @Shanky has written a great article on same with in-depth analysis showing this behavior with a test scenario in What can Cause Index to be Still Fragmented After Rebuild

Here is a quick example:

USE [AdventureWorks]
GO

ALTERINDEX [PK_Product_ProductID] ON [Production].[Product]
REBUILDPARTITION=ALLWITH (FILLFACTOR= 90)
GO

The best value for the fill factor

  1. Static tables – set fill factor at 100 (or default server fill factor)

  2. Tables updated less often – set fill factor at 95.

  3. Frequently updated tables – set fill factor between 70 and 90.

  4. Tables with clustered index on identity column – set fill factor at 100.

If the fill factor is 100, I usually say it needs to be lowered for the frequently updated tables. If fill factor is lower than 100, I usually say, it needs to be set to 100 for identity columns and master tables.

Leave a Reply

Your email address will not be published. Required fields are marked *