Sql-server – Rebuild index in SQL Server does not reduce fragmentation

indexsql server

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.

Best 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

Related Question