fragmented index not defragmenting after an index rebuild I have a weird behavior that I'm trying to explain to a client with SQL express 2014 (12.0.2269.0).  The table in question has 4.5 million rows and has over 60 thousand pages.  The average fragmentation for that index is 54% before and after the rebuild.  the only way I was able to fix the issue was to drop and recreate the clustered index.

Has anyone ever witnessed this behavior?
Can it be as simple as upgrading to SP2, although I didn’t find any direct correlation with the SP1 and SP2 release notes?

Any help would be greatly appreciated.

This is the query I use to look at the average fragmentation level:

SELECT t.name AS TableName, sch.name as SchemaName, i.name AS IndexName, s.avg_fragmentation_in_percent, ROW_NUMBER() OVER (PARTITION BY t.name order by t.name) as RowNum, TR.RowCnt
                FROM SYS.TABLES t
                INNER JOIN sys.schemas sch on t.schema_id=sch.schema_id
                JOIN SYS.INDEXES i ON t.object_id = i.object_id
                JOIN SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(),NULL,NULL,NULL,NULL) s ON t.object_id = s.object_id AND i.index_id = s.index_id
                LEFT JOIN (SELECT o.OBJECT_ID, ddps.row_count as RowCnt
                                FROM sys.indexes AS i
                                INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
                                INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
                                AND i.index_id = ddps.index_id
                                WHERE i.index_id &lt; 2
                                AND o.is_ms_shipped = 0) TR ON TR.object_id = t.object_id
                WHERE t.type  = 'U'
                AND s.avg_fragmentation_in_percent &gt; 5
                AND i.name is not null
                ORDER BY t.name, s.avg_fragmentation_in_percent
Yes I see that fragmentation all the time with GUID columns, due to the random inserts and page splits. In your case, this could be a result of a non-standard fill factor to account for GUID fields.

A clustering key should be on an ever-increasing, narrow data type, like INT or BIGINT, and the fill factor set as close to 100 (or 0) as possible.

That all said, rebuilding a clustered index due to fragmentation is a time-consuming process, and it generates a lot of transaction log, so it's generally more accepted to simply update the statistics fairly frequently.

You can worry about fragmentation less frequently, say once a month.