Со временем и накоплением данных в базе может накапливаться фрагментация индексов, и это может сильно сказаться на скорости работы базы данных. В этой статье рассмотрено как выявить наличие проблемы и как её устранить.
Для начала можно проверить фрагментацию
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: Автоматическая дефрагментация индексов