Найти самые фрагментированные таблицы в базе и исправить их

проверка фрагментированных таблиц

SELECT
    OBJECT_NAME(ips.object_id)       AS table_name,
    i.name                            AS index_name,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(
    DB_ID('ViatecUT'), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id 
                   AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 30
  AND ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC

Дефрагментувати таблиці в режимі онлайн. Доступно тільки для enterprise SQL

USE ViatecUT_Tro
GO

PRINT CONVERT(VARCHAR,GETDATE(),120) + ' _InfoRg8750...'
ALTER INDEX _InfoRg8750_ByDims8753_RR ON dbo._InfoRg8750
    REBUILD WITH (ONLINE=ON, SORT_IN_TEMPDB=ON, MAXDOP=4)

PRINT CONVERT(VARCHAR,GETDATE(),120) + ' _InfoRg9135 (1)...'
ALTER INDEX _InfoRg9135_ByDims9145_RTRRRL ON dbo._InfoRg9135
    REBUILD WITH (ONLINE=ON, SORT_IN_TEMPDB=ON, MAXDOP=4)

PRINT CONVERT(VARCHAR,GETDATE(),120) + ' _InfoRg9135 (2)...'
ALTER INDEX _InfoRg9135_ByDims_RRRRTL ON dbo._InfoRg9135
    REBUILD WITH (ONLINE=ON, SORT_IN_TEMPDB=ON, MAXDOP=4)

PRINT CONVERT(VARCHAR,GETDATE(),120) + ' Готово!'