none
インデックスの再構築と再作成(再編成でなく) RRS feed

  • 質問

  • インデックスの再構築と再作成(再編成でなく)

    お世話になっております。

    インデックスの再構築を行うために
    断片化が30%以上なら再構築するバッチを作成し
    実行したんですが、
    (ALTER INDEX REBUILD)
    ①断片化が解消されるもの
    ②断片化が解消されないもの
    ③断片化が進んでしまうもの
    がありました。

    調べてみたら再構築にはインデックス領域が
    古いインデックスの約1.2倍程度必要になるとのことなので
    あらかじめ手動でデータファイルを広げておかないと
    上記の状態になってしまうのではと思います。

    しかし、
    空き領域がそれほど多くないため
    あまり広げたくないとのことで、
    試しにいくつかのインデックスを
    dropしてcreateしました(再作成)。
    結果は再構築より断片化が解消されました
    そもそも私の認識としては
    インデックスの再構築=再作成(drop~create)
    だったのですが、違うのでしょうか?

    もし再作成(drop~create)で問題ないのなら
    あとはクエリで統計情報を更新すれば良いのではと
    考えているんですが何か注意点はありますでしょうか?

    たびたびの質問になってしまい申し訳ありません。

    • 編集済み 2154mika 2014年7月29日 14:31
    2014年7月29日 14:30

すべての返信

  • インデックスの FILLFACTOR (もしくは REBUILD 時の FILLFACTOR)と、インデックスで使用しているページ数(8 ページ以下かどうか)、インデックス 1 行あたりのサイズによって、1~3 の事象全てが起こりえるので、起きている事象自体はおかしなところはありません。
    詳細な背景はここで私が書くよりも大変分かりやすいページがあるので、そちらを紹介しておきます。
    http://blogs.msdn.com/b/jpsql/archive/2011/10/17/10224501.aspx
    http://blogs.msdn.com/b/jpsql/archive/2013/02/05/do-s-amp-dont-s-8-rebuild-shrink.aspx
    http://blogs.msdn.com/b/jpsql/archive/2013/03/01/10397042.aspx

    >事前にデータファイルを広げておかないと
    ファイルの自動拡張が ON (デフォルト ON)になっていれば、そこまで気にするものではありません。事前に広げておかなくても処理は行われます。
    詳細が分からないのでなんとも言いにくいのですが、どちらかというとストレージを必要なだけ増設されたほうがよろしいのでは、という印象です。

    >再構築=再作成
    再構築を ONLINE で行うと新インデックス作成後に旧インデックスが削除され、一方で再作成では旧インデックスの削除後に新インデックス作成となりますので、ページの割り当て処理が変わりますね。
    他にも FILLFACTOR を指定していた場合は、再構築だと引き継がれますが、再作成だと指定しなければデフォルト値に変わります。
    論理的には同等であっても、物理的な処理は異なりますので、単純にイコールというわけではありません。

    >統計情報を更新
    統計情報はデータの分布などをサマリ情報を持ちますが、インデックスの再構築ではデータの分布が変わるわけでは無いので、インデックスの再構築とは関係ない話かと思います。
    より厳密には、インデックスの再構築の際に統計情報が再作成されているので、統計情報を更新してはいけません。
    詳細は以下が詳しいので、そちらを参照してください。
    http://blogs.msdn.com/b/jpsql/archive/2011/08/01/do-s-amp-dont-s-9-rebuild-update-statistics.aspx

    インデックスを再作成した場合は、(特にインデックスの削除時に)自動作成された統計情報がどういう扱いとなったかちょっと覚えていないので、こちらは実際に行ってみて統計情報の更新日時が変化しているかどうか確認してみてください。

    >再作成で問題ないのなら
    再作成中はインデックスが無い状態ですので、サービス提供時間やサービスレベルなど運用面の考慮は必要かと思います。
    あと、インデックスの再作成だとそのインデックスを使用しているプランキャッシュが全て破棄されると思われるので、CPU 負荷がキャッシュが溜まるまでのしばらくの間は高めになるかもしれません。

    ちなみに、上で紹介している Support Team の Blog は、日本語で正確で深い情報が記載されている希少な場所ですので、可能であれば一通り目を通されることをお勧めします。
    私もそちらで日々勉強しているような感じです。


    MCITP(Database Developer/Database Administrator)

    • 回答の候補に設定 星 睦美 2014年7月31日 5:06
    2014年7月29日 15:32
  • ところで、目的はインデックスの断片化解消なのですか? 遅いクエリーの改善だったと思うのですが、手段が目的化していませんか? 断片化解消によりクエリーのパフォーマンスが改善されているのであればいいのですが…今までの質問にそのような記述はなかったと思います。
    2014年7月29日 22:07

  • naginoさん

    いつもありがとうございます。

    確かに
    fragment_count
    8以下で起こっている現象でした。

    オプションとしては
    PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
    です。
    FILLFACTORも指定してしていないのでデフォルト値です。

    テスト環境である程度データファイルを拡張し実行すると
    すべての断片化が解消されたので原因かと思っていました。

    もっと勉強が必要ですね。
    申し訳ありませんでした。

    佐祐理さん

    いつもありがとうございます。

    >ところで、目的はインデックスの断片化解消なのですか? 遅いクエリーの改善だったと思うのですが、手段が目的化していませんか?
    > 断片化解消によりクエリーのパフォーマンスが改善されているのであればいいのですが…今までの質問にそのような記述はなかったと思います。

    クエリなんですが、断片化解消されているテスト環境と本番環境では違う為
    関係あるのではと考えています。
    正直、再現しない障害であることから、あまり重要視されておらず、
    『断片化解消すれば良いんじゃない??』
    で調査終了となってしまいました。

    エンドユーザ様もまた障害が起こったら報告しますと
    言ってくれているらしいので断片化解消して様子を見ることになっています。

    私自身もちょっとこんがらがってしまって
    手段が目的化してしまっているのは否めないです。。。

    申し訳ありません。


    2014年7月30日 13:51