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 < 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 > 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.

  • I don't have any GUID fields but I do have a fill factor of 90%. The clustering key is on 5 combined fields (datetime, int and 3 varchars). I follow Microsoft's recommendation, if between 5 and 30% reorg and over 30% rebuild. I also added to my routine the update of statistics when reorganizing because I noticed a performance hit otherwise. – JohnG Aug 1 '16 at 19:14
  • 1
    That 5% - 30% recommendation was a guess. You don't have to follow it to the letter. If performance is not affected, don't rebuild. Just update the statistics. Reorganizing does not update statistics, only a rebuild does. – Randolph West Aug 1 '16 at 20:33

Not the answer you're looking for? Browse other questions tagged or ask your own question.