Why after following Article Id: 11550 do I still see indexes with 99% fragmentation

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)
• CA Unified Infrastructure Management On-Premise (Nimsoft / UIM)
• CA Unified Infrastructure Management SaaS (Nimsoft / UIM)

Issue/Introduction

After following Article Id: 11550 in order to reduce fragmentation in the UIM database there are still many indexes that show average fragmentation as 99.9%

Why is this and should I be concerned?

Environment

• Release: Any

Resolution

When assessing index fragmentation it is important to take into account the page count for the table.

The page is the fundamental storage unit in SQL Server and each page is 8Kb. for a full description of pages please see the following Microsoft document 

Fragmentation effects disk I/O operations and where the table has less than 1000 pages it is generally accepted that fragmentation is of no concern as the data is so small will likley be held in memory so the index is not needed.

When an Index is rebuilt and it has a low page count, pages are allocated to the index from mixed extent, the pages are kind of scattered and thus show as being highly fragmented this is the default behavior of Database Engine, it is how it allocates pages.

The following Micosoft document describes this in great detail

SQL Server In depth: What can Cause Index to be Still Fragmented After Rebuild