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