【SQLServer】インデックスの断片化の一覧を出すSQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
--インデックスの断片化 SET NOCOUNT ON SELECT 'ALTER INDEX ' + '[' + C.name + ']' + ' ON [' + D.name + '].[' + B.name + '] REBUILD' AS 'rebuild command' ,D.name AS schemaname ,B.name AS table_name ,C.name AS index_name ,A.avg_fragmentation_in_percent ,A.page_count FROM sys.dm_db_index_physical_stats (DB_ID(),null,null,null,null) AS A LEFT OUTER JOIN sys.objects AS B ON A.object_id = B.object_id LEFT OUTER JOIN sys.indexes AS C ON A.object_id = C.object_id AND A.index_id = C.index_id LEFT OUTER JOIN sys.schemas AS D ON B.schema_id = D.schema_id WHERE B.type = 'U' AND C.index_id > 0 --断片化率が30%以上のものを抽出する AND A.avg_fragmentation_in_percent > 30 ORDER BY A.avg_fragmentation_in_percent DESC; |
