Перейти к содержимому

Как узнать фрагментацию индексов во всех таблицах в базе MSSQL

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('ax_molniya'), OBJECT_ID(NULL), 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

 

Similar Posts:

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *