Fragmentation Level for Heaps

I am currently using scripts provided by Mr. Ola Hallengren for executing maintenance job and of-late I have been noticing that there are many tables (heaps) fragmentation level is alarmingly high and needs to be looked and taken action upon. I checked FAQ at the site and seems his script doesn't support rebuilding heaps. I used below query to find the fragmentation level:

SELECT dbschemas.[name] as 'Schema', 
dbtables.[name] as 'Table', 
dbindexes.[name] as 'Index',
indexstats.alloc_unit_type_desc,
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID() and dbindexes.name is null
ORDER BY page_count desc, indexstats.avg_fragmentation_in_percent desc

My application is supported by vendor and I have been communicating with them to change these heaps to tables and create clustered index however it hasn't yielded any meaningful result yet since they have defined primary key as unique non-clustered index and it is also part of foreign key, so needs to change at many level before doing any change. First of all, it took many days for me to explain the difference between clustered index and primary key with unique index.

I also went through the tweaks suggested by Mr. Brent Ozar for changing the defaults at script provided by Mr. Ola Hallengren for index optimize in order to make it more efficient however I didn't find any details of heap rebuilding.

As per my understanding heap's fragmentation can be handled in two ways as described here:

• To create clustered index on table and drop it - This would clear all the fragmentation and also rebuild a

Heaps have a few special challenges that you can't experience with clustered indexes:

• Forwarded Records

• Captive Pages

I'd suggest running sp_BlitzIndex against your database to find out if either of these things is happening with your Heaps. If not, then leave them alone. If they are, you may need to consider rebuilding them.

At this time, you can't reorganize a Heap table, and rebuilding a Heap table will also rebuild any nonclustered indexes on it. It may be cheaper to drop them, rebuild the Heap table, and then recreate the nonclustered indexes afterwards.

You can read more about this stuff here:

• sp_BlitzIndex Self Loathing Indexes

• How To Fix Forwarded Records

• Mysterious Forwarded Records

• Forwarded Fetches and Bookmark Lookups

StackExchange Database Administrators Q#232357, answer score: 11