Оптимизация индексов mssql вариант 1
DECLARE @SQL NVARCHAR(MAX)
DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR
SELECT 'ALTER INDEX [' + i.name + N'] ON [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] ' +
CASE WHEN
1=1 --s.avg_fragmentation_in_percent > 30
THEN 'REBUILD WITH (SORT_IN_TEMPDB = ON)'
ELSE 'REORGANIZE'
END + ';'
+ '-- fragment=' + cast(s.avg_fragmentation_in_percent as nvarchar) + '%'
FROM (
SELECT
s.[object_id]
, s.index_id
, avg_fragmentation_in_percent = MAX(s.avg_fragmentation_in_percent)
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) s
WHERE s.page_count > 128 -- > 1 MB
AND s.index_id > 0 -- <> HEAP
AND s.avg_fragmentation_in_percent > 3
GROUP BY s.[object_id], s.index_id
) s
JOIN sys.indexes i WITH(NOLOCK) ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
JOIN sys.objects o WITH(NOLOCK) ON o.[object_id] = s.[object_id]
order by s.avg_fragmentation_in_percent desc
OPEN cur
FETCH NEXT FROM cur INTO @SQL
WHILE @@FETCH_STATUS = 0 BEGIN
--EXEC sys.sp_executesql @SQL
print @SQL
FETCH NEXT FROM cur INTO @SQL
END
CLOSE cur
DEALLOCATE cur