So-net無料ブログ作成

SQL Server 2005 の インデックス再構築 [DBMS]

せっかくいろいろ調べたので載っけておこうと思います。
SQL Server 2005 での インデックス再構築です。
再編成はREORGANIZE 、再構築は REBUILD で、再構築の方です。
DBで性能問題というと、統計情報の更新→インデックス再編成→インデックス再構築→テーブル再編成 となります。
最近のDBMSではテーブルの再編成まではリスクが大きいのか勧めてるものは無くなっていて、行ってもインデックスの再編成又は再構築を行えば十分な性能が戻ると言われてます。
…で、たまにあります。全てのインデックスを再構築しろと…

↓はインデックス操作 ALTER INDEX の構文です。
SQLリファレンス.jpg
テーブルの全てのインデックスを再構築するには下のSQL文になります。
alter index all on テーブル rebuild
が、いちいちテーブルを指定しないといけません。
テーブルの一覧取得と組み合わせないとなりません。
いろいろと考えた結果、以下の様になりました。
これをコマンドプロンプトから実行します。for の行は長いので折れ曲がって表示されてます。
実行コマンド.jpg
DB名(HS2)とサーバ名(JANUS)は適時変更願います。表示の関係で行が分かりにくいので > を行頭に入れてます。
----------------------------------------------------------------------------
> sqlcmd -E -d HS2 -S JANUS -Q "exec sp_tables" -o tables.txt -h -1
> if exist rebuild.sql del rebuild.sql
> for /F "eol=( tokens=2,3" %%i in (tables.txt) do @if not %%i == sys @if not %%i == INFORMATION_SCHEMA @echo alter index all on [%%i].[%%j] rebuild with (online=OFF) >> rebuild.sql
> sqlcmd -E -d HS2 -S JANUS -i rebuild.sql
----------------------------------------------------------------------------
・1行目
テーブルの一覧取得にはいろいろと方法があるのですが、ここでは sp_tables を使いました。
sqlcmd コマンドを使用して、SQL Server へログオンして -Q オプションで指定したSQLを発行します。
実行結果を中間ファイル tables.txt に出力します。
-h -1 は検索結果の項目名の出力しないオプションです。後で加工する時にじゃまなので指定してます。
tables.txt にはDB名,オブジェクトオーナー,テーブル名などが出力されます。詳しくは sp_tables を調べて下さい。

・2行目
再構築で使用する中間ファイルの前回実行したものがあれば削除します。

・3行目
テーブル一覧を基にインデックス再構築のSQLを作ってます。
for で1行づつ、オブジェクトオーナー(%%i)とテーブル名(%%j)を取り出し、最後の echo 文で alter index 文に混ぜて中間ファイル rebuild.sql に追記してます。
入力ファイルの sp_tables の結果は、最終行に 検索件数 がカッコ付きで出力されるので "eol=(" のところで除外してます。
途中、オーナーが sys と INFORMATION_SCHEMA のテーブルを if 文で除外してます。
テーブル毎に以下の行が rebuild.sql に追加されます。
alter index all on [スキーマ].[テーブル名] rebuild with (online=OFF)
再構築はオンライン実行が可能ですが、一部のテーブルについては出来ないものもあるので OFF にしてます。

・4行目
3行目で作ったSQLでインデックスの再構築を実行します。

再構築にかかる時間は、テーブルの数やデータの量,HDDのIO性能,CPUの性能によって変わります。
環境によってはうん時間かかる事もあると思いますので注意願います。

で、これだと途中で止めた時にどーなるか判らないのでちょっと改造します。
・その①
alter index を一行一行実行する様に go を各行に入れます。

alter index all on [スキーマ].[テーブル名] rebuild with (online=OFF)
go

3行目の最後に && @echo go >> rebuild.sql を追加します。

・その②
再構築実行時にどの行を行っているか、出力します。
sqlcmd のオプションに -e を追加します。

で、結局以下の様になります。
DB名(HS2)とサーバ名(JANUS)は適時変更願います。表示の関係で行が分かりにくいので > を行頭に入れてます。
----------------------------------------------------------------------------
> sqlcmd -E -d HS2 -S JANUS -Q "exec sp_tables" -o tables.txt -h -1
> if exist rebuild.sql del rebuild.sql
> for /F "eol=( tokens=2,3" %%i in (tables.txt) do @if not %%i == sys @if not %%i == INFORMATION_SCHEMA @echo alter index all on [%%i].[%%j] rebuild with (online=OFF) >> rebuild.sql && @echo go >> rebuild.sql
> sqlcmd -E -d HS2 -S JANUS -i rebuild.sql -e
----------------------------------------------------------------------------

ちなみに、本来はインデックスのフラグメントをチェックして、そのインデックスを再構成/再構築します。
↓はBooksOnlineに載っていた調査方法です。テーブルを指定しないといけませんけどね。
フラグメントチェック.jpg
----------------------------------------------------------------------------
use HS2
SELECT a.object_id, a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
(DB_ID(), OBJECT_ID(N'[hs2].[VRSD]'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO
----------------------------------------------------------------------------
ちなみに再構築直後なのでフラグメントは 0% になってます。
nice!(0)  コメント(1)  トラックバック(0) 
共通テーマ:パソコン・インターネット

nice! 0

コメント 1

G_Collection

参考にさせて頂きます。有り難うございます・
by G_Collection (2015-10-26 11:09) 

コメントを書く

お名前:
URL:
コメント:
画像認証:
下の画像に表示されている文字を入力してください。

トラックバック 0