LoginSignup
16

More than 5 years have passed since last update.

インデックス断片化の状況を表示する

Last updated at Posted at 2012-12-26

SQLServerでインデックスの断片化を確認するSQLです。
最終列にREBUILDするためのSQLも出力させていますが、テーブルロックさせずに実行したい場合は以下のようにONLINEオプションを指定する必要があります。

ALTER INDEX インデックス名 on テーブル名 REBUILD with ONLINE=ON;
FragmentationInfo.sql
with index_stat as (
    select *
    from sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorks'), NULL, NULL, NULL, NULL)
)
select
     C.schema_id 
    ,A.object_id table_id
    ,B.object_id index_id
    ,C.name schema_name
    ,A.name table_name
    ,B.name index_name
    ,D.avg_fragmentation_in_percent
    ,'ALTER INDEX [' + B.name + '] ON [' + C.name + '].[' + A.name + '] REBUILD;'
from sys.tables as A 
    inner join sys.indexes as B on (B.object_id = A.object_id)
    inner join sys.schemas as C on (C.schema_id = A.schema_id)
    inner join index_stat  as D on (D.object_id = B.object_id AND D.index_id = B.index_id)
where A.type='U'
order by D.avg_fragmentation_in_percent DESC;

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
16