Ускорение работы базы данных с помощью дефрагментация индексов

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

Для начала можно проверить фрагментацию

DECLARE @db_name varchar(50) = N'DbName',
@table_name varchar(250) = N'TableName'

SELECT IndStat.database_id,
IndStat.object_id,
QUOTENAME(s.name) + '.' + QUOTENAME(o.name) AS [object_name],
IndStat.index_id,
QUOTENAME(i.name) AS index_name,
IndStat.avg_fragmentation_in_percent,
IndStat.partition_number,
(SELECT count (*) FROM sys.partitions p
WHERE p.object_id = IndStat.object_id AND p.index_id = IndStat.index_id) AS partition_count
FROM sys.dm_db_index_physical_stats
(DB_ID(@db_name), OBJECT_ID(@table_name), NULL, NULL , 'LIMITED') AS IndStat
INNER JOIN sys.objects AS o ON (IndStat.object_id = o.object_id)
INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
INNER JOIN sys.indexes i ON (i.object_id = IndStat.object_id AND i.index_id = IndStat.index_id)
WHERE IndStat.avg_fragmentation_in_percent > 10 AND IndStat.index_id > 0

В db_name и/или table_name можно передать Null, тогда выведутся данные по всем базам и/или таблицам.

В выводе следует обратить внимание на столбец avg_fragmentation_in_percent, чем он выше, тем сильнее фрагментирован индекс, и тем больше будет выигрыш от его дефрагментации.

Для дефрагментации индексов можно использовать следующий код

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
    , @SQL NVARCHAR(MAX)

SELECT @SQL = (
    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 @SQL

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

Внимание! Не выполняйте код бездумно на боевой системе не опробовав его на тесте. Автор статьи не несёт ответственности за последствия.

Источники:
Оптимизируем базу на MSSQL: Определяем фрагментацию индексов
План обслуживания “на каждый день” – Часть 1: Автоматическая дефрагментация индексов