IndexOptimize: Heap (de)fragmentation about sql-server-maintenance-solution HOT 8 OPEN

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

from sql-server-maintenance-solution.

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

from sql-server-maintenance-solution.

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.

from sql-server-maintenance-solution.

This would be a very welcome addition

from sql-server-maintenance-solution.

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 !

from sql-server-maintenance-solution.

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

from sql-server-maintenance-solution.

+1 on this request

from sql-server-maintenance-solution.

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

from sql-server-maintenance-solution.