IndexOptimize: Heap (de)fragmentation

[blazdax]

Feature request - adding the ability to identify and "fix" heap fragmentation.

I know a heap is usually a bad idea and that heap maintenance is a pain, but there are some valid use cases for heaps.

[reharmsen]

This would be a very welcome addition

[EmilioBoucau]

This makes complete sense. There are some situations in which is needed to rebuild a heap (a new datafile was added, for example). If we could have a @RebuildHEAP option in the solution, it would be great !

[iampeterdaniels]

+1. I'm managing a vendor DB with 1400+ heap tables. An option to rebuild heaps would be quite welcome. :)

[EmilioBoucau]

Guys, as we wait for this new feature, I share the code I use in a job to accomplish this task. As it is, it only creates and shows the syntax needed. Uncomment the EXEC line to put it to work. I think you already have written something like this, but this can be helpful to someone.

---

--> BEGIN SCRIPT

DECLARE @Tablename SYSNAME;
DECLARE @Sintaxis VARCHAR( 500 )

DECLARE Cursor_Heaps CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR

    SELECT SCH.name + '.' + TBL.name
    FROM sys.tables AS TBL
    INNER JOIN sys.schemas AS SCH
    ON TBL.schema_id = SCH.schema_id
    INNER JOIN sys.indexes AS IDX
    ON TBL.object_id = IDX.object_id
    AND IDX.type = 0;

OPEN Cursor_Heaps;

FETCH NEXT FROM Cursor_Heaps INTO @Tablename;

WHILE @@fetch_status = 0
BEGIN
    
SET @Sintaxis = 'ALTER TABLE ' + @Tablename + ' REBUILD';
PRINT @Sintaxis
--EXEC( @Sintaxis )
FETCH NEXT FROM Cursor_Heaps INTO @Tablename;

END;

CLOSE Cursor_Heaps;

DEALLOCATE Cursor_Heaps;

--> END SCRIPT

[iampeterdaniels]

Thanks, @EmilioBoucau . I have also been working on a sproc to help rebuild heaps. It only rebuilds ones that have user scans in the last x # of days and non-zero forward fetch counts. My first public repo:
https://github.com/iampeterdaniels/sp_rebuild_heaps

[pnauta]

+1 on this request

[MarcusCulverTHL]

Brent Ozar has a valid suggestion here for an intermediary fix: https://www.brentozar.com/archive/2016/07/fix-forwarded-records/#:~:text=Ola%20Hallengren%E2%80%99s%20IndexOptimize%20does%20not%20rebuild%20heaps

[brockmank88]

See https://www.brentozar.com/archive/2016/07/fix-forwarded-records/ on advice to modify IndexOptimize for this situation.

I work with a lot of vendor applications. Getting vendor approval for clustered indexes meets a ton of resistance even after doing writeups with facts. Rebuilding heaps using an existing solution is the way to go.