SQL Serverのインデックスを再構成する

数年運用しているシステムの開発DB（SQL Server）の動作が怪しい部分があったため、DBを利用するミドルウェアの開発元に問合せたところインデックスの再構成を試してほしいとの依頼が。しかし、現時点でSQL Server Management Studioの「データベース」のツリーが表示されない状態（恐らくデータベースの1つが破損しているか管理情報が壊れている……）のため、SQL文で処理する必要があります。

ちょっと検索したところ、ASCIIのサイトで良さげなSQLが紹介されていたので利用させてもらいました。

ascii.jp

インデックスの断片化率などを一覧にし、さらに、パーセンテージに応じて「Reorganize」と「Rebuild」をまとめて実施するSQLが記載されていました。助かります。

1点注意点が。開発用サーバーのSQL ServerはStandard Editionであったため、Rebuildのオプション「WITH (ONLINE = ON)」が使えませんでした。そのため、下記の「-- インデックスと……」のコメントの次の行から、「WITH (ONLINE = ON)」を除外して実施しています。

DECLARE @Command VARCHAR(255)
DECLARE cur CURSOR LOCAL FOR
-- インデックスと断片化率の一覧を取得する
SELECT 'ALTER INDEX ' + '[' + IDX.name + ']' + ' ON [' + SCM.name + '].[' + OBJCT.name + '] REBUILD WITH (ONLINE = ON)' AS Command
FROM sys.dm_db_index_physical_stats (DB_ID(),null,null,null,null) AS IPS
LEFT OUTER JOIN  sys.objects AS OBJCT
	ON  IPS.object_id = OBJCT.object_id 
LEFT OUTER JOIN  sys.indexes AS IDX
	ON  IPS.object_id = IDX.object_id  AND IPS.index_id = IDX.index_id 
LEFT OUTER JOIN  sys.schemas AS SCM
	ON  OBJCT.schema_id = SCM.schema_id 
WHERE OBJCT.type = 'U'
AND   IDX.index_id > 0 

-- 断片化率が30%以上のものを抽出する
AND   IPS.avg_fragmentation_in_percent > 30
ORDER BY IPS.avg_fragmentation_in_percent DESC

OPEN cur

FETCH NEXT FROM cur INTO @Command
WHILE @@FETCH_STATUS = 0

BEGIN
  -- インデックスの再構築を実行する
  EXEC(@Command)
  FETCH NEXT FROM cur INTO @Command
END

CLOSE cur
DEALLOCATE cur