SELECT
dbschemas.[name] as 'Схема',
dbtables.[name] as 'Таблица',
dbindexes.[name] as 'Индекс',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED') indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes dbindexes on dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent DESC;
5–30% фрагментации → ALTER INDEX … REORGANIZE
30% фрагментации → ALTER INDEX … REBUILD
DECLARE @TableName NVARCHAR(255);
DECLARE TableCursor CURSOR FOR
SELECT QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id;
OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Rebuilding indexes on ' + @TableName;
EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON)');
FETCH NEXT FROM TableCursor INTO @TableName;
END
CLOSE TableCursor;
DEALLOCATE TableCursor;