arkuznetsov · October 12, 2018 11:58
diff --git a/MS_SQL_index_maintenance.sql b/MS_SQL_index_maintenance.sql
 --USE ...

 DECLARE
      @PageCount INT = 128
    , @RebuildPercent INT = 30
    , @ReorganizePercent INT = 10
    , @WithRebuild BIT = 1
    , @IsOnlineRebuild BIT = 1
    , @IsVersion2012Plus BIT =
        CASE WHEN CAST(SERVERPROPERTY('productversion') AS CHAR(2)) NOT IN ('8.', '9.', '10')
            THEN 1
            ELSE 0
        END
    , @IsEntEdition BIT =
        CASE WHEN SERVERPROPERTY('EngineEdition') = 3
            THEN 1
            ELSE 0
        END
    , @SQL NVARCHAR(MAX)

 SELECT @SQL = (
    SELECT
 '
 ALTER INDEX ' + QUOTENAME(idx.name) + ' ON ' + QUOTENAME(schem.name) + '.' + QUOTENAME(obj.name) + ' ' +
        CASE WHEN stat.avg_fragmentation_in_percent >= @RebuildPercent AND @WithRebuild = 1
            THEN 'REBUILD'
            ELSE 'REORGANIZE'
        END + ' PARTITION = ' +
        CASE WHEN ds.[type] != 'PS'
            THEN 'ALL'
            ELSE CAST(stat.partition_number AS NVARCHAR(10))
        END + ' WITH (' + 
        CASE WHEN stat.avg_fragmentation_in_percent >= @RebuildPercent AND @WithRebuild = 1
            THEN 'SORT_IN_TEMPDB = ON' + 
                CASE WHEN @IsEntEdition = 1
                        AND @IsOnlineRebuild = 1 
                        AND ISNULL(lob.is_lob_legacy, 0) = 0
                        AND (
                                ISNULL(lob.is_lob, 0) = 0
                            OR
                                (lob.is_lob = 1 AND @IsVersion2012Plus = 1)
                        )
                    THEN ', ONLINE = ON'
                    ELSE ''
                END
            ELSE 'LOB_COMPACTION = ON'
        END + ')'
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) stat
    JOIN sys.indexes idx ON idx.[object_id] = stat.[object_id] AND idx.index_id = stat.index_id
    LEFT JOIN (
        SELECT
              col.[object_id]
            , index_id = ISNULL(idx.index_id, 1)
            , is_lob_legacy = MAX(CASE WHEN col.system_type_id IN (34, 35, 99) THEN 1 END)
            , is_lob = MAX(CASE WHEN col.max_length = -1 THEN 1 END)
        FROM sys.columns col
        LEFT JOIN sys.index_columns idx ON col.[object_id] = idx.[object_id]
            AND col.column_id = idx.column_id AND idx.index_id > 0
        WHERE col.system_type_id IN (34, 35, 99)
            OR col.max_length = -1
        GROUP BY col.[object_id], idx.index_id
    ) lob ON lob.[object_id] = idx.[object_id] AND lob.index_id = idx.index_id
    JOIN sys.objects obj ON obj.[object_id] = idx.[object_id]
    JOIN sys.schemas schem ON obj.[schema_id] = schem.[schema_id]
    JOIN sys.data_spaces ds ON idx.data_space_id = ds.data_space_id
    WHERE idx.[type] IN (1, 2)
        AND idx.is_disabled = 0
        AND idx.is_hypothetical = 0
        AND stat.index_level = 0
        AND stat.page_count > @PageCount
        AND stat.alloc_unit_type_desc = 'IN_ROW_DATA'
        AND obj.[type] IN ('U', 'V')
        AND stat.avg_fragmentation_in_percent > @ReorganizePercent
    FOR XML PATH(''), TYPE
 ).value('.', 'NVARCHAR(MAX)')

 PRINT @SQL
 EXEC sys.sp_executesql @SQL
	--USE ...

	DECLARE
	@PageCount INT = 128
	, @RebuildPercent INT = 30
	, @ReorganizePercent INT = 10
	, @WithRebuild BIT = 1
	, @IsOnlineRebuild BIT = 1
	, @IsVersion2012Plus BIT =
	CASE WHEN CAST(SERVERPROPERTY('productversion') AS CHAR(2)) NOT IN ('8.', '9.', '10')
	THEN 1
	ELSE 0
	END
	, @IsEntEdition BIT =
	CASE WHEN SERVERPROPERTY('EngineEdition') = 3
	THEN 1
	ELSE 0
	END
	, @SQL NVARCHAR(MAX)

	SELECT @SQL = (
	SELECT
	'
	ALTER INDEX ' + QUOTENAME(idx.name) + ' ON ' + QUOTENAME(schem.name) + '.' + QUOTENAME(obj.name) + ' ' +
	CASE WHEN stat.avg_fragmentation_in_percent >= @RebuildPercent AND @WithRebuild = 1
	THEN 'REBUILD'
	ELSE 'REORGANIZE'
	END + ' PARTITION = ' +
	CASE WHEN ds.[type] != 'PS'
	THEN 'ALL'
	ELSE CAST(stat.partition_number AS NVARCHAR(10))
	END + ' WITH (' +
	CASE WHEN stat.avg_fragmentation_in_percent >= @RebuildPercent AND @WithRebuild = 1
	THEN 'SORT_IN_TEMPDB = ON' +
	CASE WHEN @IsEntEdition = 1
	AND @IsOnlineRebuild = 1
	AND ISNULL(lob.is_lob_legacy, 0) = 0
	AND (
	ISNULL(lob.is_lob, 0) = 0
	OR
	(lob.is_lob = 1 AND @IsVersion2012Plus = 1)
	)
	THEN ', ONLINE = ON'
	ELSE ''
	END
	ELSE 'LOB_COMPACTION = ON'
	END + ')'
	FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) stat
	JOIN sys.indexes idx ON idx.[object_id] = stat.[object_id] AND idx.index_id = stat.index_id
	LEFT JOIN (
	SELECT
	col.[object_id]
	, index_id = ISNULL(idx.index_id, 1)
	, is_lob_legacy = MAX(CASE WHEN col.system_type_id IN (34, 35, 99) THEN 1 END)
	, is_lob = MAX(CASE WHEN col.max_length = -1 THEN 1 END)
	FROM sys.columns col
	LEFT JOIN sys.index_columns idx ON col.[object_id] = idx.[object_id]
	AND col.column_id = idx.column_id AND idx.index_id > 0
	WHERE col.system_type_id IN (34, 35, 99)
	OR col.max_length = -1
	GROUP BY col.[object_id], idx.index_id
	) lob ON lob.[object_id] = idx.[object_id] AND lob.index_id = idx.index_id
	JOIN sys.objects obj ON obj.[object_id] = idx.[object_id]
	JOIN sys.schemas schem ON obj.[schema_id] = schem.[schema_id]
	JOIN sys.data_spaces ds ON idx.data_space_id = ds.data_space_id
	WHERE idx.[type] IN (1, 2)
	AND idx.is_disabled = 0
	AND idx.is_hypothetical = 0
	AND stat.index_level = 0
	AND stat.page_count > @PageCount
	AND stat.alloc_unit_type_desc = 'IN_ROW_DATA'
	AND obj.[type] IN ('U', 'V')
	AND stat.avg_fragmentation_in_percent > @ReorganizePercent
	FOR XML PATH(''), TYPE
	).value('.', 'NVARCHAR(MAX)')

	PRINT @SQL
	EXEC sys.sp_executesql @SQL