全データベースの全テーブルのインデックスの再構築をする

バッチファイル SQL

実はいまものすごくハマっている(と言ってものめり込んでいるという意味ではなくてトラブル真っ只中という意味の方です...)ことがありまして、それがプログラムの処理がものすごく遅くなる現象です。気付いたら遅延どころかタイムアウトも出ちゃう有様でして、いったいどうやって対処しようかと悩んでいるところですが、そんなわたしに「DBのデータが断片化してるんじゃね？」というとてもありがたいヒントをくれた方がおりまして、じゃあためしにデータベースのすべてのテーブルのインデックスを再構築してみようということになりました。


テストや実環境で使用しているデータベースサーバーはSQLServer2005 Standard Editionです。
未確認ですが、たぶんSQLServer2008でも大丈夫のはずです。ただし2000の場合はシステムテーブルの表記(sys.databases → sysdatabasesなど)を一部書き換える必要がありますのでご注意ください。

＃ 詳しくは「続きを読む」を選択してください



まずは本当に断片化しているかどうかを確認したいのですが、これを実現する方法について調べてみました。すると、DBCC SHOWCONTIGというステートメントを実行すると接続中のデータベースの全テーブルの断片化状況をレポートしてくれることが分かりました。
というわけで、これをすべてのデータベースへ接続して実行すればよいわけでして、これをTransactSQLだけでやろうという場合には、sys.databasesからデータベース名を取得してカーソルに入れて、あとはひとつずつ処理すればやりたいことは出来そうです。


とは言え、さすがにこれはちょっといけてないなというのもそのとおりでして、もっと便利な方法がないか調べてみました。
すると非常に便利な隠しプロシージャ(アンドキュメンテッドプロシージャと呼ばれていることが多いようですが)があることを知りました。


sp_MSforeachdbとsp_MSforeachtableがそのプロシージャです。


sp_MSforeachdbは現在接続しているインスタンス内のすべてのデータベースを列挙して処理をするためのプロシージャです。
また、sp_MSforeachtableは現在接続しているデータベースの各ユーザーテーブルを列挙して処理をするためのプロシージャです。


どういう動作をするのか、簡単なテストクエリーを書いてテストしてみました。


まずはsp_MSforeachdbから。


[クエリー]

sp_MSforeachdb @command1="SELECT '?'"

[結果]


すべてのデータベース名が表示されます。SELECT '?'がこのデータベース名の表示を行っている部分なのですが、見てのとおり、?がデータベース名に置き換わっています。つまり、このプロシージャを使うことで、?でそれぞれのデータベース名を動的に取得しながら処理を行うことが可能となるのです。


動かした際の感覚としては、カーソルを使う場合よりも高速な気がします。
カーソルを使った場合と処理時間を実測して比較しようかと考えたのですが、面倒なのでひとまず置いておくことにします。



続いてsp_MSforeachtableについて。


[クエリー]

sp_MSforeachtable @command1="SELECT '?'"

[結果]


接続中のデータベース内にあるすべてのユーザーテーブルの名前が表示されます。SELECT '?'でテーブル名の表示が出来るようです。sp_MSforeachdbでは?がデータベース名に置き換わっていましたが、sp_MSforeachtableでは?がテーブル名に置き換わっています。

よって、このプロシージャを使えば、?でテーブル名を動的に取得しながら処理を行うことが可能となることがわかりました。


ちなみに@command1は128文字以上は入らないようでして、そう考えるとあまり無茶な処理は出来ません。


とりあえず便利なプロシージャがあるということがわかりましたが、これを使って断片化状況を調査するクエリを作成してみます。


[クエリー]

sp_MSforeachtable @command1="dbcc showcontig"


[実行結果]

DBCC SHOWCONTIG により、'spt_fallback_db' テーブルがスキャンされています...
テーブル: 'spt_fallback_db' (117575457)、インデックス ID: 0、データベース ID: 1
TABLE レベルのスキャンが実行されました。

  • スキャンされたページ数.....................: 0
  • スキャンされたエクステント数...............: 0
  • エクステントの切り替え回数.................: 0
  • エクステントごとの平均ページ数.............: 0.0
  • スキャン密度 [最善値:実際値]...............: 100.00% [0:0]
  • エクステント スキャンの断片化 .............: 0.00%
  • ページごとの平均空きバイト数...............: 0.0
  • 平均ページ密度 (全体) .....................: 0.00%

DBCC SHOWCONTIG により、'spt_fallback_dev' テーブルがスキャンされています...
テーブル: 'spt_fallback_dev' (133575514)、インデックス ID: 0、データベース ID: 1
TABLE レベルのスキャンが実行されました。

  • スキャンされたページ数.....................: 0
  • スキャンされたエクステント数...............: 0
  • エクステントの切り替え回数.................: 0
  • エクステントごとの平均ページ数.............: 0.0
  • スキャン密度 [最善値:実際値]...............: 100.00% [0:0]
  • エクステント スキャンの断片化 .............: 0.00%
  • ページごとの平均空きバイト数...............: 0.0
  • 平均ページ密度 (全体) .....................: 0.00%


こうやって実際にどのくらい断片化されているのかを確認すれば、デフラグすべきかどうかが分かります。



さて。では本題。
実際にインデックスの再構築をする方法について調べてみると、方法がいくつか見つかりました。

    1. DBCC DBREINDEX を使う
    2. DBCC INDEXDEFRAG を使う
    3. ALTER INDEX を使う


わざわざ別のステートメントにしているということは違いがあると言うことですからそれぞれについて調べてみたいのですが、このうち、ALTER INDEXについては今回は調査を保留します。理由としては再構築する対象として、テーブルではなくインデックスを直接指定する必要があるようでして、そうだとするとテーブル名から対象となるインデックス名を取得する処理を行う必要が出てくるために処理が煩雑になってしまうのです。
# sp_MSforeachindexというのを使えば出来そうですが、それはさすがに...


と言うわけで、今回はDBCCを使った処理について調べてみます。

1. DBCC DBREINDEX

MSDNへのリンク


DBCC DBREINDEXは指定されたテーブル内のインデックスを再構築してくれるステートメントです。


このステートメントが優れている点はテーブル名だけを指定することでそのテーブルに付いているすべてのインデックスを自動で再構築してくれるためです。つまりインデックス名を個別に指定する必要はありません。


またこのステートメントがひとつのトランザクションになりますがそれはとても使い勝手がよいです。個別にインデックスを再作成する場合は、意識的に"インデックスの削除"と"インデックスの作成"をひとつのトランザクションとしてまとめる必要がありますが、それを使う側が意識する必要がないのです。


そしてもう一点このステートメントのメリットとしては、一つ一つを再作成するよりも最適化されるそうです(上記MSDNでMicrosoftが自己申告しているのを信じれば..ですが)。


逆にデメリットとしては実行中はロックがかけられてしまうという点です。
非クラスタ化インデックスの場合は共有ロックがかけられるため、更新処理が一切出来なくなってSELECT処理のみ可能となります。
さらにクラスタ化インデックスの場合は排他ロックまでかけられてしまうので照会を含む一切のアクセスが出来なくなります。
そのため、オフラインで再構築処理(実行中の業務利用は不可)を行う必要があります。


加えて対象テーブルを指定する場合、テーブル名を所有者やデータベース名で修飾することは出来ないために該当のデータベースへ
直接接続して実施する必要があります。これは処理を行う上で使いにくいため、デメリットとして挙げておきます。


そして一番残念なのは、DBREINDEXは2008の次のバージョンで削除される予定だということです。
とても有効なステートメントなのですが、互換性を考えると選択肢としては難しいですね...。

2. DBCC INDEXDEFRAG

MSDNへのリンク


DBCC INDEXDEFRAGはDBREINDEX同様、指定されたテーブルまたはビューのインデックスの断片化を解消してくれます。再構築ではなく、あくまで解消だという点が強調されていました。


DEREINDEXと比較して、このステートメントのメリットとして挙げられるのはオンライン処理が可能であるという点です。
つまりロックがかけられて処理が出来なくなるということはないということですが、これは非常に大きいです。


また、デフラグ対象のテーブルのあるデータベースもステートメントで指定できるため、プログラムから呼ぶ際には比較的使いやすいという点もメリットとして挙げられます。加えて処理の進捗も定期的に出力してくれるそうでしてこれは非常に配慮あるプロシージャであると感じました。


逆にデメリットとしては、断片化が進んでいる場合には処理には処理に時間がかかるという点です。また再構築に比べて単にページを並び替えるだけなので効果が薄いという情報もありました。口先だけはいいことをいうけど中身の伴わない優男みたいなプロシージャです。


DBREINDEXを使うのか、それともINDEXDEFRAGを使うのかと悩んだのですが、大して違いはありませんのでとりあえずどっちも使えるようにどちらも作成しました。と言っても数行書き換えるだけなのですが...。



[DBREINDEX用のクエリファイル]

-- ファイル名： QueryCreate.sql

DECLARE @dbname varchar(50)
DECLARE @dbid   varchar(10)


-- データベース名一覧取得のカーソル
DECLARE dbname_cs CURSOR FOR
 SELECT name, Convert(varchar(10),database_id) FROM sys.databases WHERE name not in ('master', 'msdb', 'tempdb', 'model') ORDER BY 1

OPEN dbname_cs

-- データを取得
FETCH NEXT FROM dbname_cs INTO @dbname, @dbid
WHILE @@FETCH_STATUS = 0 BEGIN
  print 'use ' + @dbname
  print 'print ''-----------------------------------------------------'''
  print 'print ''○ データベース名 ＝ ' + @dbname + ''''
  print 'print ''-----------------------------------------------------'''
  print 'declare @tblName' + @dbid + ' varchar(20)'
  print 'declare tblcs' + @dbid + ' cursor for select name from sys.tables'
  print 'OPEN tblcs' + @dbid
  print 'fetch next from tblcs' + @dbid + ' into @tblName' + @dbid
  print 'while @@FETCH_STATUS = 0 begin'
  print '  if exists (select ''x'' from sys.objects where name = @tblName' + @dbid+ ') begin'
  print '    print @tblName' + @dbid
  print '    exec (''dbcc dbreindex(''' + @tblName + ''')'')'
  print '  end'
  print '  SELECT GetDate() as [時刻]'
  print '  fetch next from tblcs' + @dbid + ' into @tblName' + @dbid
  print 'end'
  print 'close tblcs' + @dbid
  print 'deallocate tblcs' + @dbid
  print ''

  -- データを取得
  FETCH NEXT FROM dbname_cs INTO @dbname, @dbid
END

CLOSE dbname_cs
DEALLOCATE dbname_cs


[INDEXDEFRAG用のクエリファイル]

-- ファイル名： QueryCreate.sql

DECLARE @dbname varchar(50)
DECLARE @dbid   varchar(10)


-- データベース名一覧取得のカーソル
DECLARE dbname_cs CURSOR FOR
 SELECT name, Convert(varchar(10),database_id) FROM sys.databases WHERE name not in ('master', 'msdb', 'tempdb', 'model') ORDER BY 1

OPEN dbname_cs

-- データを取得
FETCH NEXT FROM dbname_cs INTO @dbname, @dbid
WHILE @@FETCH_STATUS = 0 BEGIN
  print 'use ' + @dbname
  print 'print ''-----------------------------------------------------'''
  print 'print ''○ データベース名 ＝ ' + @dbname + ''''
  print 'print ''-----------------------------------------------------'''
  print 'declare @tblName' + @dbid + ' varchar(20)'
  print 'declare tblcs' + @dbid + ' cursor for select name from sys.tables'
  print 'OPEN tblcs' + @dbid
  print 'fetch next from tblcs' + @dbid + ' into @tblName' + @dbid
  print 'while @@FETCH_STATUS = 0 begin'
  print '  if exists (select ''x'' from sys.objects where name = @tblName' + @dbid+ ') begin'
  print '    print @tblName' + @dbid
  print '    exec (''dbcc INDEXDEFRAG(' + @dbname + ','' + @tblName' + @dbid + ' + '')'' )'
  print '  end'
  print '  SELECT GetDate() as [時刻]'
  print '  fetch next from tblcs' + @dbid + ' into @tblName' + @dbid
  print 'end'
  print 'close tblcs' + @dbid
  print 'deallocate tblcs' + @dbid
  print ''

  -- データを取得
  FETCH NEXT FROM dbname_cs INTO @dbname, @dbid
END

CLOSE dbname_cs
DEALLOCATE dbname_cs


あれ？と思った方もいると思いますが、実はこのクエリーを実行してもデフラグはされません。このクエリーを実行するとそのサーバー環境でデフラグをするためにクエリーが生成されます。
直接実行すればいいのに、なぜこんなまどろっこしいことをするのかと言うと、理由が二つあります。


ひとつ目の理由はクエリーファイルを残しておくことで、最終的にどういう処理をしたのかを明確に残しておくことが出来るということです。
どんな処理でもそうですが、実行した場合にはそのログを残すべきです。が、どのくらい詳細なログを残せばいいのかというのはいつも頭を悩ませるところです。その点、実施した処理そのものの命令文を残しておくというのはログという観点からもとてもよいことだと言えます。


ふたつ目の理由はデバッグがしやすいということです。動的にSQLを生成して実行する場合、どういうクエリーが実行されているのかを見るためには実行前にそれを表示する必要があります。わざわざ表示と実行を切り替えるくらいだったら、毎回クエリーを出力しておいてそれを実行した方がよいではないかとうわけです。
加えて、処理の一部をちょっとだけ変えたい場合にはその作成されたクエリーファイルを直接編集再利用することが出来るのも大きなメリットといえます。


あとは最近あまり聞かないのですが、まったく同じクエリーでも動的クエリーと静的クエリーでは生成される実行プランが異なるということがあったと記憶しています(execが主流だった前のことですが)。sp_execsql経由であれば問題ないはずですが、動的クエリーよりは静的の方が実行プランに与える影響は少ないんじゃないかという心理的な部分も影響しています。
まあ、これは古い意見だと思います。


最後にこのクエリーを呼び出すバッチファイルを作れば完成です。
実際に使う場合には接続ユーザー名やパスワードを適宜設定してから実行してください。

@ECHO OFF

:: ログファイルの絶対パス
SET LOGFILE="%~dp0%COMPUTERNAME%_DbRebuild.log"

:: 使用するクエリファイルの絶対パス
SET QUERYFILE="%~dp0QueryCreate.sql"

:: 自動生成するクエリファイルの絶対パス
SET TMPQUERYFILE="%TEMP%\tmp_%COMPUTERNAME%.sql"

:: 接続ユーザー名
SET CONNUSER=sa

:: 接続ユーザーパスワード
SET CONNPW=xxxxxxxxx

:: ログファイルの削除
IF EXIST %LOGFILE% (
  DEL %LOGFILE%
)

:: 一時クエリファイルの削除
IF EXIST %TMPQUERYFILE% (
  DEL %TMPQUERYFILE%
)

:: クエリの生成処理
OSQL -U%CONNUSER% -P%CONNPW% -dmaster -i%QUERYFILE% -n -w2000 > %LOGFILE%
IF %ERRORLEVEL%.==0. (
  COPY %LOGFILE% %TMPQUERYFILE% /Y > NUL

  ECHO ------------------------------------------------- > %LOGFILE%
  ECHO ○ データベース再構築処理 [%DATE% - %TIME%]       >> %LOGFILE%
  ECHO 　作業者：%USERNAME%＠%USERDOMAIN%                >> %LOGFILE%
  ECHO ------------------------------------------------- >> %LOGFILE%
  ECHO. >> %LOGFILE%
  OSQL -U%CONNUSER% -P%CONNPW% -dmaster -i%TMPQUERYFILE% -n -w2000 >> %LOGFILE%

  NOTEPAD %LOGFILE%
  GOTO FINISH
)

ECHO 処理は失敗しました[エラーコード＝%ERRORLEVEL%]
PAUSE > NUL

:FINISH


データ量にもよりますが、結構処理には時間がかかりそうです。
ものすごく時間がかかる場合にはINDEXDEFRAGを使い、そうでない場合にはDBREINDEXを使う方がよいのかも知れません。