Hcfyk

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:

1. To create clustered index on table and drop it - This would clear all the fragmentation and also rebuild all non-clustered index however it would be time and I/O consuming.


2. Rebuilding the heap - This would also clear the fragmentation and rebuild all non-clustered index part of table rebuild.

I can't go for option 1 because I am not aware of columns where clustered index can be created and also this could take longer than option 2.

I am looking for possibility of implementing option 1 in the scripts by Ola Hallengren or alternative method for handling this. Also to add, I would like to rebuild my heaps only when the size of heap is more than 10,000 pages and fragmentation level is more than 80.

I am using Microsoft SQL Server 2014 SP3 Enterprise Edition.

As a DBA - I don't prefer to have heaps in my database however since it is vendor supported application and since they have already defined primary key as unique index and these keys are foreign keys, its very difficult to change them to clustered due to references as well as likeliness of down time.

EDIT: I went through the link provided by Mr. Erik Darling and I can confirm that I have number of heaps with forwarded records or deletes across the databases. Now, I am back to point from where I had started i.e. with those two options. As I mentioned earlier, creating clustered index is very very difficult in my scenario and will require at least months(being optimistic) with likeliness of downtime considering complex foreign key structure. Need advise on rebuilding the heaps and possible side effect.

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