Dynamically re-index all indexes in all databases on server

Question

The intent of this SQL Server query is to:

Dynamically work rebuild/reorganize all indexes in all tables in all databases on a server.
Rebuild or reorganize depending on the specified fragmentation threshold values.
Attempt to perform an online rebuild. If an online rebuild can not be performed due to column types, do an offline rebuild if allowed, otherwise just do a reorganize.
Excludes system databases like master, msdb, tempdb, model, and distribution

I'm interested in a code review for correctness in logic.

--Reorganize and/or rebuild in all tables on all databases based on fragmentation thresholds
DECLARE @iReorganizeMinThreshold INT = NULL; --Fragmentation amount between this and the rebuild threshold will cause a reorganize (NULL = never)
DECLARE @iRebuildMinThreshold INT = NULL; --Fragmentation amount above this value will cause a rebuild (-1 = always rebuild, NULL = never)
DECLARE @bAllowOfflineRebuild BIT = 0; --If true, indexes will be rebuilt even if they can't be rebuilt with online option.  If false they will be reogranized instead
--If you’re running SQL Server 2012 SP1 – SP2, parallel online index rebuilds can cause corruption.
--https://sqlperformance.com/2014/06/sql-indexes/hotfix-sql-2012-rebuilds
--https://support.microsoft.com/en-ca/help/2969896/fix-data-corruption-occurs-in-clustered-index-when-you-run-online-index-rebuild-in-sql-server-2012-or-sql-server-2014
PRINT 'Rebuilding/Reorganizing all indexes on every table in every database...'

DECLARE @tblIndexes AS TABLE
(
    DatabaseName VARCHAR(255),
    SchemaName VARCHAR(255),
    TableName VARCHAR(255),
    IndexName VARCHAR(255),
    AvgPageFragmentation INT,
    CanRebuildOnline BIT
)

PRINT 'Gethering indexes from all databases..'
INSERT INTO @tblIndexes  
EXEC sp_MSforeachdb
'USE [?]
SELECT 
        [Database] = DB_NAME(dps.DATABASE_ID), 
        [Schema] = OBJECT_SCHEMA_NAME(dps.OBJECT_ID),
        [Table] = OBJECT_NAME(dps.OBJECT_ID),  
        [Index] = si.NAME,
        [AvgPageFragmentation] = dps.AVG_FRAGMENTATION_IN_PERCENT,
        [CanRebuildOnline] =
        CASE
            WHEN si.type_desc = ''CLUSTERED'' THEN
                CASE WHEN NOT EXISTS 
                (
                    SELECT 
                        *
                    FROM 
                        sys.[tables] AS tbl
                    JOIN 
                        sys.[all_columns] col ON tbl.[object_id] = col.[object_id]  
                    WHERE 
                        tbl.[object_id] = si.[object_id] AND 
                        (
                            (col.system_type_id IN (34,35,99,241)) OR 
                            (col.system_type_id IN (167,231,165) AND max_length = -1)
                        ) 
                ) THEN 1 ELSE 0 END
            WHEN si.type_desc <> ''CLUSTERED'' THEN 
                CASE WHEN NOT EXISTS
                (
                    SELECT 
                        * 
                    FROM 
                        sys.[tables] AS tbl
                    JOIN 
                        sys.[indexes] idx ON tbl.[object_id] = idx.[object_id]
                    JOIN 
                        sys.[index_columns] ic ON idx.[object_id] = ic.[object_id] AND 
                                                    idx.[index_id] = ic.[index_id]
                    JOIN 
                        sys.[all_columns] col ON tbl.[object_id] = col.[object_id] AND 
                                                ic.[column_id] = col.[column_id]
                    WHERE 
                        tbl.[object_id] = si.[object_id] AND 
                        si.[object_id] = idx.[object_id] AND 
                        si.index_id = idx.index_id AND 
                        (
                            (col.system_type_id IN (34,35,99,241)) OR 
                            (col.system_type_id IN (167,231,165) AND max_length = -1)
                        ) 
                ) THEN 1 ELSE 0 END
            ELSE NULL
        END
FROM 
    sys.indexes si
JOIN 
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) dps ON dps.object_id = si.object_id AND dps.index_id = si.index_id
JOIN
    sys.tables t ON t.object_id = dps.object_id
WHERE
    t.type = ''U'' AND
    dps.alloc_unit_type_desc = ''IN_ROW_DATA'' AND
    dps.index_type_desc <> ''HEAP'' AND  
    DB_NAME(dps.DATABASE_ID) NOT IN (''master'',''msdb'',''tempdb'',''model'',''distribution'')
ORDER BY
    [Database],
    [Schema],
    [Table];'

SELECT * FROM @tblIndexes;

DECLARE @sDatabase VARCHAR(255);
DECLARE @sSchema VARCHAR(255);
DECLARE @sTable VARCHAR(255);
DECLARE @sIndex VARCHAR(255);
DECLARE @iFragmentation INT;
DECLARE @sAlter varchar(200);
DECLARE @bCanRebuildOnline BIT;

DECLARE curIndexes CURSOR FOR
SELECT 
        [DatabaseName], 
        [SchemaName],
        [TableName],  
        [IndexName],
        [AvgPageFragmentation],
        [CanRebuildOnline]
FROM
    @tblIndexes;

--Iterate through each index, rebuild or reorganize or leave alone based on the amount of fragmentation
OPEN curIndexes
FETCH NEXT FROM curIndexes INTO
    @sDatabase,
    @sSchema,
    @sTable,
    @sIndex,
    @iFragmentation,
    @bCanRebuildOnline;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @iReorganizeMinThreshold IS NOT NULL AND @iFragmentation BETWEEN @iReorganizeMinThreshold and @iRebuildMinThreshold 
    BEGIN       
        PRINT CONVERT(VARCHAR, GETDATE(), 121) + ' - Reorganizing index [' + @sIndex + '] ON Table [' + @sDatabase + '].[' + @sSchema + '].[' + @sTable +'], fragmentation is at ' + CAST(@iFragmentation AS VARCHAR(15)) + '%...';
        SET @sAlter = 'ALTER INDEX [' + @sIndex + '] ON [' + @sDatabase + '].[' + @sSchema + '].[' + @sTable + '] REORGANIZE;';
    END
    ELSE IF @iRebuildMinThreshold IS NOT NULL AND @iFragmentation > @iRebuildMinThreshold 
    BEGIN 
        --An online rebuild operation cannot be performed on some indexes lif the index contains columns of data type text, ntext, image or FILESTREAM. For a non-clustered index, the column could be an include column of the index. For a clustered index, the column could be any column of the table. If DROP_EXISTING is used, the column could be part of a new or old index. The operation must be performed offline.

        IF @bCanRebuildOnline = 1
        BEGIN
            PRINT CONVERT(VARCHAR, GETDATE(), 121) + ' - Rebuilding index (online) [' + @sIndex + '] ON Table [' + @sDatabase + '].[' + @sSchema + '].[' + @sTable +'], fragmentation is at ' + CAST(@iFragmentation AS VARCHAR(15)) + '%...';

            SET @sAlter = 'ALTER INDEX [' + @sIndex + '] ON [' + @sDatabase + '].[' + @sSchema + '].[' +  @sTable + '] REBUILD WITH (ONLINE=ON, MAXDOP=1);'; 
        END
        ELSE
        BEGIN
            IF @bAllowOfflineRebuild = 1
            BEGIN
                PRINT CONVERT(VARCHAR, GETDATE(), 121) + ' - Rebuilding index (offline) [' + @sIndex + '] ON Table [' + @sDatabase + '].[' + @sSchema + '].[' + @sTable +'], fragmentation is at ' + CAST(@iFragmentation AS VARCHAR(15)) + '%...';
                SET @sAlter = 'ALTER INDEX [' + @sIndex + '] ON [' + @sDatabase + '].[' + @sSchema + '].[' +  @sTable + '] REBUILD;'; 
            END
            ELSE
            BEGIN
                PRINT CONVERT(VARCHAR, GETDATE(), 121) + ' - Reorganizing index (online rebuild not possible, offline not allowed) [' + @sIndex + '] ON Table [' + @sDatabase + '].[' + @sSchema + '].[' + @sTable +'], fragmentation is at ' + CAST(@iFragmentation AS VARCHAR(15)) + '%...';
                SET @sAlter = 'ALTER INDEX [' + @sIndex + '] ON [' + @sDatabase + '].[' + @sSchema + '].[' + @sTable + '] REORGANIZE;';
            END
        END
    END
    ELSE 
    BEGIN
        PRINT CONVERT(VARCHAR, GETDATE(), 121) + ' - Skipping INDEX  [' + @sIndex + '] ON Table [' + @sDatabase + '].[' + @sSchema + '].[' + @sTable +'], fragmentation is at ' + CAST(@iFragmentation AS VARCHAR(15)) + '%.';
    END

    --Force refresh of print output
    --RAISERROR (N'', -- Message text
 --          10, -- Severity
 --          1) -- State 
 --     WITH NOWAIT
    EXEC(@sAlter);

    FETCH NEXT FROM curIndexes INTO
        @sDatabase,
        @sSchema,
        @sTable,
        @sIndex,
        @iFragmentation,
        @bCanRebuildOnline;
END;
CLOSE curIndexes;
DEALLOCATE curIndexes;

I'll try and get a proper answer tomorrow, but avoiding the cursor and replacing it with set logic would be ideal. If it looping can't be avoided perhaps a while loop, or a loop using a goto which perferms better in some cases, would be more applicable. — PenutReaper, Apr 9, 2017 at 3:31
Does this code function in your testing? I'm experiencing some issues getting it to run as written on SQL Server 2016 — PenutReaper, Apr 9, 2017 at 20:51
@PenutReaper Yes, it does function on our SQL 2012 servers. What errors are you seeing? — Jeremy, Apr 17, 2017 at 21:14
It's because the query used in the sp_MSforeachDB proc is too long. Must have been an alteration to it in 2016. That's what you get for using undocumented procs I guess. — PenutReaper, Apr 18, 2017 at 11:31
If you don't want to reinvent the wheel: I've always used these scripts: ola.hallengren.com/…. — dfhwze, Sep 18, 2019 at 4:17

PenutReaper · Accepted Answer · 2017-04-09 21:25:34Z

Okay, so removing the cursor seemed to improve the performance (and I would also argue improves readability. Basically, I added another column to the temp table you already create to track which record we are currently operating on.

I also altered this section of your query:

 [CanRebuildOnline] =
    CASE
        WHEN si.type_desc = ''CLUSTERED'' THEN
            CASE WHEN NOT EXISTS 
            (
                SELECT 
                    *
                FROM 
                    sys.[tables] AS tbl
                JOIN 
                    sys.[all_columns] col ON tbl.[object_id] = col.[object_id]  
                WHERE 
                    tbl.[object_id] = si.[object_id] AND 
                    (
                        (col.system_type_id IN (34,35,99,241)) OR 
                        (col.system_type_id IN (167,231,165) AND max_length = -1)
                    ) 
            ) THEN 1 ELSE 0 END
        WHEN si.type_desc <> ''CLUSTERED'' THEN 
            CASE WHEN NOT EXISTS
            (
                SELECT 
                    * 
                FROM 
                    sys.[tables] AS tbl
                JOIN 
                    sys.[indexes] idx ON tbl.[object_id] = idx.[object_id]
                JOIN 
                    sys.[index_columns] ic ON idx.[object_id] = ic.[object_id] AND 
                                                idx.[index_id] = ic.[index_id]
                JOIN 
                    sys.[all_columns] col ON tbl.[object_id] = col.[object_id] AND 
                                            ic.[column_id] = col.[column_id]
                WHERE 
                        tbl.[object_id] = si.[object_id] AND 
                        si.[object_id] = idx.[object_id] AND 
                        si.index_id = idx.index_id AND 
                        (
                            (col.system_type_id IN (34,35,99,241)) OR 
                            (col.system_type_id IN (167,231,165) AND max_length = -1)
                        ) 
                ) THEN 1 ELSE 0 END
            ELSE NULL
        END

into this:

 [CanRebuildOnline] =
    (
        SELECT
            CASE 
                WHEN idx.[type_desc] = ''CLUSTERED'' THEN 0
                WHEN idx.[type_desc] <> ''CLUSTERED'' THEN 0
                ELSE 1
            END
        FROM sys.[tables] AS tbl
        INNER JOIN sys.[indexes] idx
            ON tbl.[object_id] = idx.[object_id]
        INNER JOIN sys.[index_columns] ic
            ON idx.[object_id] = ic.[object_id]
            AND idx.[index_id] = ic.[index_id]
        INNER JOIN sys.[all_columns] col 
            ON tbl.[object_id] = col.[object_id]
            AND ic.[column_id] = col.[column_id]
        WHERE tbl.[object_id] = si.[object_id] 
        AND
        (
            (col.system_type_id IN (34,35,99,241)) OR 
            (col.system_type_id IN (167,231,165) AND max_length = -1)
        )
    )

due to the fact that the dynamic query was too long and caused the query to not run in SQL Server 2016.

Here is the full query I came up with:

DECLARE
    @iReorganizeMinThreshold INT = NULL, 
    @iRebuildMinThreshold INT = NULL, 
    @bAllowOfflineRebuild BIT = 0,
    @cur_i INT = 1;

PRINT 'Rebuilding/Reorganizing all indexes on every table in every database...'

DECLARE @tblIndexes AS TABLE
(
    ID INT IDENTITY,
    DatabaseName VARCHAR(255),
    SchemaName VARCHAR(255),
    TableName VARCHAR(255),
    IndexName VARCHAR(255),
    AvgPageFragmentation INT,
    CanRebuildOnline BIT
)

PRINT 'Gethering indexes from all databases..'
INSERT INTO @tblIndexes  
EXEC sp_MSforeachdb 'USE [?]
SELECT 
        [Database] = DB_NAME(dps.DATABASE_ID), 
        [Schema] = OBJECT_SCHEMA_NAME(dps.OBJECT_ID),
        [Table] = OBJECT_NAME(dps.OBJECT_ID),  
        [Index] = si.NAME,
        [AvgPageFragmentation] = dps.AVG_FRAGMENTATION_IN_PERCENT,
        [CanRebuildOnline] =
        CASE WHEN (
            SELECT tbl.[object_id]
            FROM sys.[tables] AS tbl
            INNER JOIN sys.[indexes] idx
                ON tbl.[object_id] = idx.[object_id]
            INNER JOIN sys.[index_columns] ic
                ON idx.[object_id] = ic.[object_id]
                AND idx.[index_id] = ic.[index_id]
            INNER JOIN sys.[all_columns] col 
                ON tbl.[object_id] = col.[object_id]
                AND ic.[column_id] = col.[column_id]
            WHERE tbl.[object_id] = si.[object_id] 
            AND
            (
                (col.system_type_id IN (34,35,99,241)) OR 
                (col.system_type_id IN (167,231,165) AND max_length = -1)
            )
        ) IS NOT NULL THEN 0 ELSE 1 END
FROM sys.indexes si
INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) dps
    ON dps.object_id = si.object_id AND dps.index_id = si.index_id
INNER JOIN sys.tables t
    ON t.object_id = dps.object_id
WHERE t.type = ''U''
AND dps.alloc_unit_type_desc = ''IN_ROW_DATA''
AND dps.index_type_desc <> ''HEAP''
AND DB_NAME(dps.DATABASE_ID) NOT IN (''master'',''msdb'',''tempdb'',''model'',''distribution'')
ORDER BY
    [Database],
    [Schema],
    [Table];'

SELECT * FROM @tblIndexes;

DECLARE
    @sDatabase VARCHAR(255),
    @sSchema VARCHAR(255),
    @sTable VARCHAR(255),
    @sIndex VARCHAR(255),
    @iFragmentation INT,
    @sAlter varchar(200),
    @bCanRebuildOnline BIT;

--Iterate through each index, rebuild or reorganize or leave alone based on the amount of fragmentation
_loop:

SELECT
    @sDatabase = DatabaseName,
    @sSchema = SchemaName,
    @sTable = TableName,
    @sIndex = IndexName,
    @iFragmentation = AvgPageFragmentation,
    @bCanRebuildOnline = CanRebuildOnline
FROM @tblIndexes
WHERE ID = @cur_i

IF @iRebuildMinThreshold IS NOT NULL AND @iFragmentation > @iRebuildMinThreshold 
BEGIN 
    IF @bCanRebuildOnline = 1
    BEGIN
        PRINT CONVERT(VARCHAR, GETDATE(), 121) + ' - Rebuilding index (online) [' + @sIndex + '] ON Table [' + @sDatabase + '].[' + @sSchema + '].[' + @sTable +'], fragmentation is at ' + CAST(@iFragmentation AS VARCHAR(15)) + '%...';
        SET @sAlter = 'ALTER INDEX [' + @sIndex + '] ON [' + @sDatabase + '].[' + @sSchema + '].[' +  @sTable + '] REBUILD WITH (ONLINE=ON, MAXDOP=1);'; 
    END
    ELSE IF @bAllowOfflineRebuild = 1
    BEGIN
        PRINT CONVERT(VARCHAR, GETDATE(), 121) + ' - Rebuilding index (offline) [' + @sIndex + '] ON Table [' + @sDatabase + '].[' + @sSchema + '].[' + @sTable +'], fragmentation is at ' + CAST(@iFragmentation AS VARCHAR(15)) + '%...';
        SET @sAlter = 'ALTER INDEX [' + @sIndex + '] ON [' + @sDatabase + '].[' + @sSchema + '].[' +  @sTable + '] REBUILD;'; 
    END        
    ELSE
    BEGIN
        PRINT CONVERT(VARCHAR, GETDATE(), 121) + ' - Reorganizing index (online rebuild not possible, offline not allowed) [' + @sIndex + '] ON Table [' + @sDatabase + '].[' + @sSchema + '].[' + @sTable +'], fragmentation is at ' + CAST(@iFragmentation AS VARCHAR(15)) + '%...';
        SET @sAlter = 'ALTER INDEX [' + @sIndex + '] ON [' + @sDatabase + '].[' + @sSchema + '].[' + @sTable + '] REORGANIZE;';
    END
END
ELSE 
BEGIN
    PRINT CONVERT(VARCHAR, GETDATE(), 121) + ' - Skipping INDEX  [' + @sIndex + '] ON Table [' + @sDatabase + '].[' + @sSchema + '].[' + @sTable +'], fragmentation is at ' + CAST(@iFragmentation AS VARCHAR(15)) + '%.';
END

EXEC(@sAlter);

SELECT @cur_i += 1;

IF @cur_i <= (SELECT MAX(ID) FROM @tblIndexes) GOTO _loop

\$\begingroup\$ I'll have to give this a try. \$\endgroup\$
– Jeremy
Apr 17, 2017 at 21:16 — Jeremy, Apr 17, 2017 at 21:16

d4nix · Accepted Answer · 2019-09-17 23:07:27Z

great script, it helped me a lot. Thanks.

But I've made little modification in [CanRebuildOnline] part. I'm not sure about other versions of SQL, but on SQL 2012 r2 SP4 if any column have set the bad data type, then is not possible to do online rebuild. I've commented useless tables and add the TOP 1 to avoid errors when some table have more than one bad column. =)

[CanRebuildOnline] =
    CASE WHEN (
        SELECT TOP 1 tbl.[object_id]
        FROM sys.[tables] AS tbl
        --INNER JOIN sys.[indexes] idx
        --    ON tbl.[object_id] = idx.[object_id]
        --INNER JOIN sys.[index_columns] ic
        --    ON idx.[object_id] = ic.[object_id]
        --    AND idx.[index_id] = ic.[index_id]
        INNER JOIN sys.[all_columns] col 
            ON tbl.[object_id] = col.[object_id]
        --    AND ic.[column_id] = col.[column_id]
        WHERE tbl.[object_id] = si.[object_id] 
        AND
        (
            (col.system_type_id IN (34,35,99,241)) OR 
            (col.system_type_id IN (167,231,165) AND max_length = -1)
        )
    ) IS NOT NULL THEN 0 ELSE 1 END

Stack Exchange Network

Dynamically re-index all indexes in all databases on server

2 Answers 2

Your Answer

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

Hot Network Questions

Dynamically re-index all indexes in all databases on server

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

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

Related

Hot Network Questions

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