Автоматизация дефрагментации индексов в базе данных MS SQL Server

Из статьи не понятно, что мы говорим о часных случаях. Всегда есть особые решения. У меня опыта небыло на свербольших системах, но базы в пол террабайта и таблицы за 100 миллионов записей прекрасно живут с универсальными решениями, если не запускать.

Автоматизация дефрагментации индексов в базе данных MS SQL Server

Я лишь говорю о том, что сама процедура не русурсозатратна, если же вы и при обычной нагрузке находитесь на пороге производительности, то возможно выход, но это уже больше на агонию похоже

Автоматизация дефрагментации индексов в базе данных MS SQL Server

Склонен к другому источнику повреждения данных, ни разу не встречал проблем с этим при перестроении, даже очень больших индексов и высокой нагрузке

Автоматизация дефрагментации индексов в базе данных MS SQL Server

Я замерял для себя, цифр уже не приведу, но если мы находимся в предедах рекомендации для массива по дисковым очередям, то сколь либо существенного снижения производительности общей системы не происходит

Автоматизация дефрагментации индексов в базе данных MS SQL Server

Я сторонник универсальнвх решений и перестройка тоже идёт в режиме online, запускайте мой скрипт раз в 5 минут и получите тоже самое. Только без вьюшек, хранимых процедур и т.д. При своременных мощностях можно пренебречь сверхтюнингом в угоду простоты и универсальности.

Автоматизация дефрагментации индексов в базе данных MS SQL Server

Ну в данном случае не по всем, а по тем, что рекомендует Microsoft. Если необходимости нет, то и индекс не будет перестроен или дефрагментирован в зависимости от процентра фрагментации. Очень универсальное решение позволяющее забыть на 90% систем про индексы

Автоматизация дефрагментации индексов в базе данных MS SQL Server

По всем БД

set quoted_identifier on;

DECLARE @SQL VARCHAR(MAX) 
DECLARE @DB sysname 
 
DECLARE CURSDB CURSOR FORWARD_ONLY STATIC FOR 
   SELECT [name] 
   FROM master..sysdatabases 
   WHERE [name] NOT IN ('model', 'tempdb')
   ORDER BY [name] 
 
OPEN CURSDB 
FETCH NEXT FROM CURSDB INTO @DB  
WHILE @@FETCH_STATUS = 0  
   BEGIN 

----------------

       SELECT @SQL = CHAR(13) + '-- UPDATE FOR ' +  @DB +  CHAR(13) + 'USE [' + @DB +']'
       PRINT @SQL 
	   EXEC (@SQL)


DECLARE
      @PageCount INT = 128
    , @RebuildPercent INT = 30
    , @ReorganizePercent INT = 10
    , @IsOnlineRebuild BIT = 0
    , @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('EditionID') IN (1804890536, -2117995310)
            THEN 1
            ELSE 0
        END
    , @SQL1 NVARCHAR(MAX)

SELECT @SQL1 = (
    SELECT
'
ALTER INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(s2.name) + '.' + QUOTENAME(o.name) + ' ' +
        CASE WHEN s.avg_fragmentation_in_percent >= @RebuildPercent
            THEN 'REBUILD'
            ELSE 'REORGANIZE'
        END + ' PARTITION = ' +
        CASE WHEN ds.[type] != 'PS'
            THEN 'ALL'
            ELSE CAST(s.partition_number AS NVARCHAR(10))
        END + ' WITH (' + 
        CASE WHEN s.avg_fragmentation_in_percent >= @RebuildPercent
            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) s
    JOIN sys.indexes i ON i.[object_id] = s.[object_id] AND i.index_id = s.index_id
    LEFT JOIN (
        SELECT
              c.[object_id]
            , index_id = ISNULL(i.index_id, 1)
            , is_lob_legacy = MAX(CASE WHEN c.system_type_id IN (34, 35, 99) THEN 1 END)
            , is_lob = MAX(CASE WHEN c.max_length = -1 THEN 1 END)
        FROM sys.columns c
        LEFT JOIN sys.index_columns i ON c.[object_id] = i.[object_id]
            AND c.column_id = i.column_id AND i.index_id > 0
        WHERE c.system_type_id IN (34, 35, 99)
            OR c.max_length = -1
        GROUP BY c.[object_id], i.index_id
    ) lob ON lob.[object_id] = i.[object_id] AND lob.index_id = i.index_id
    JOIN sys.objects o ON o.[object_id] = i.[object_id]
    JOIN sys.schemas s2 ON o.[schema_id] = s2.[schema_id]
    JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
    WHERE i.[type] IN (1, 2)
        AND i.is_disabled = 0
        AND i.is_hypothetical = 0
        AND s.index_level = 0
        AND s.page_count > @PageCount
        AND s.alloc_unit_type_desc = 'IN_ROW_DATA'
        AND o.[type] IN ('U', 'V')
        AND s.avg_fragmentation_in_percent > @ReorganizePercent
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')

PRINT @SQL1
EXEC (@SQL1)

---------

       FETCH NEXT FROM CURSDB INTO @DB 
   END  
 
CLOSE CURSDB 
DEALLOCATE CURSDB