SQLServerで全DB・全テーブルの断片化と平均ページ密度の調査する時のクエリ。

断片化と平均ページ密度については↓のサイトでどうぞ。
https://blogs.msdn.microsoft.com/jpsql/2011/10/16/337/

SET NOCOUNT ON;

declare @CR_DATABASE_ID AS INT
declare @CR_DBNAME AS NVARCHAR(200)
declare @WKSTR AS VARCHAR(20)

--//DBとテーブルのリスト用
create table #WKTBLLIST
(
database_id int
,object_id int
,DB名 nvarchar(200)
,テーブル名 nvarchar(256)
)
--//結果保存用
create table #RESULT
(
database_id smallint
,object_id int
,table_object_id int
,index_id int
,DB名 nvarchar(256)
,テーブル名 nvarchar(256)
,[断片化率] float
,[平均ページ密度] float
,ページ数 bigint
,レコードカウント bigint
)

--//DB毎に繰り返し
declare CUR_1 cursor for
select top 1 database_id,name from sys.databases 
order by name

open CUR_1

FETCH NEXT FROM CUR_1
INTO    @CR_DATABASE_ID,@CR_DBNAME

while(@@FETCH_STATUS=0)
begin
	--//DB毎にテーブルの一覧を保存する
    set @WKSTR = cast(@CR_DATABASE_ID as varchar(10))
    exec(
    'use ' + @CR_DBNAME + ';' +
    'insert #WKTBLLIST '+
    'select '+ @WKSTR +',object_id,'''+@CR_DBNAME+''',name ' +
    'from sys.tables ' +
    );

    FETCH NEXT FROM CUR_1
    INTO    @CR_DATABASE_ID,@CR_DBNAME
end

Close CUR_1
DEALLOCATE CUR_1

declare @CR_OBJECT_ID AS INT
declare @CR_TABLENAME AS NVARCHAR(256)

--//上で保存したDB/テーブルのリストで繰り返し
declare CUR_1 cursor for
select database_id,object_id,DB名,テーブル名 from #WKTBLLIST
order by database_id,テーブル名

open CUR_1

FETCH NEXT FROM CUR_1
INTO    @CR_DATABASE_ID,@CR_OBJECT_ID,@CR_DBNAME,@CR_TABLENAME

while(@@FETCH_STATUS=0)
begin
	--//テーブル毎に断片化率を取得して結果テーブルに保存
    insert #RESULT
    select 
    database_id
    ,object_id
    ,@CR_OBJECT_ID
    ,index_id
    ,@CR_DBNAME as DB名
    ,@CR_TABLENAME as テーブル名
    ,avg_fragmentation_in_percent as [断片化率]
    ,avg_page_space_used_in_percent as [平均ページ密度]
    ,page_count as ページ数
    ,record_count as レコードカウント
    from sys.dm_db_index_physical_stats(@CR_DATABASE_ID,@CR_OBJECT_ID, NULL, NULL , 'DETAILED')

    FETCH NEXT FROM CUR_1
    INTO    @CR_DATABASE_ID,@CR_OBJECT_ID,@CR_DBNAME,@CR_TABLENAME
end

Close CUR_1
DEALLOCATE CUR_1

--//断片化してるインデックスを調査
select * from #RESULT
where 断片化率 > 30 --Microsoftの目安値
and ページ数 > 50 --ページ数が少ない奴は見てもしょうがないと思う

--//平均ページ密度が低下してるインデックスを調査
select * from #RESULT
where 平均ページ密度 < 60--60%はなんとなく
and ページ数 > 50 --ページ数が少ない奴は見てもしょうがないと思う

--//全部の結果
select * from #RESULT

SET NOCOUNT OFF;

あとは↓の感じで再構成か再構築する

--再構成はこっち
ALTER INDEX ALL ON [テーブル名] REORGANIZE WITH(LOB_COMPACTION=ON);
--再構築はこっち
ALTER INDEX ALL ON [テーブル名] REBUILD WITH(ONLINE=OFF);

再構成(REORGANIZE)はリーフレベル：一番下位のページしかやらないらしく、大本がそもそもイカれてる場合は意味無い。その場合は再構築(REBUILD)する。ただし、REBUILDは処理してるインデックス単位にロックするらしいので、実行してる最中はテーブルはさわっちゃだめ。

参考サイト

断片化について
https://blogs.msdn.microsoft.com/jpsql/2011/10/16/337/

インデックスの再構築 (rebuild) と再構成 (reorganize) の違い
https://blogs.msdn.microsoft.com/jpsql/2013/02/28/977/

Reorganize and Rebuild Indexes
https://msdn.microsoft.com/ja-jp/library/ms189858.aspx

ALTER INDEX (Transact-SQL)
https://msdn.microsoft.com/ja-jp/library/ms188388.aspx

sys.dm_db_index_physical_stats (Transact-SQL)
https://msdn.microsoft.com/ja-jp/library/ms188917.aspx