动态地重新索引服务器上所有数据库中的所有索引

Question

此Server查询的目的是：

1. 动态地重新构建/重新组织服务器上所有数据库中所有表中的所有索引。
2. 根据指定的分段阈值重新生成或重新组织。
3. 尝试执行联机重建。如果由于列类型而无法执行联机重建，请在允许的情况下进行脱机重建，否则只需重新组织即可。
4. 不包括系统数据库，如主数据库、msdb、tempdb、模型和分布。

我对逻辑正确性的代码评审感兴趣。

--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;

Answer 1

好的，所以移除光标似乎提高了性能(我也认为这提高了可读性。基本上，我将另一列添加到您已经创建的temp表中，以跟踪我们当前操作的记录。

我还修改了查询的这一部分：

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

由于动态查询太长，导致查询在Server 2016中不运行。

下面是我提出的完整查询：

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

Answer 2

好剧本，对我有很大帮助。谢谢。

但我对CanRebuildOnline部分做了一点修改。我不确定其他版本的SQL，但在SQL 2012 r2 SP4上，如果有列设置了错误的数据类型，则无法进行联机重建。我对无用的表进行了注释，并添加了前1，以避免某些表有多个坏列时的错误。=)

[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