SQLServerでテーブルインデックスの断片化対策

use {DATABASE_NAME}
SELECT DB_NAME(s.database_id), OBJECT_NAME(s.object_id), s.index_type_desc, i.name, s.fragment_count, s.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID('{DATABASE_NAME}'), NULL, NULL, NULL, 'LIMITED') s
INNER JOIN sys.indexes i ON i.object_id = s.object_id
  and i.index_id = s.index_id
ORDER BY s.avg_fragmentation_in_percent DESC

上記のコマンドで断片化しているテーブルに対して下記のSQLを発行

ALTER TABLE {TABLE_NAME} REBUILD PARTITION = ALL

ALTER INDEX all ON {TABLE_NAME} REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF)