HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Dynamically re-index all indexes in all databases on server

Submitted by: @import:stackexchange-codereview··
0
Viewed 0 times
sqlsql-servercodereviewstackoverflow
dynamicallydatabasesallindexesserverindex

Problem

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

Solution

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

Code Snippets

[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
[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)
        )
    )
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)) +

Context

StackExchange Code Review Q#158061, answer score: 3

Revisions (0)

No revisions yet.