インデックス再構築に時間がかかったことについての調査と対策

Question

お世話になります。
表題の件につきましてご質問です。

レコード件数40000件ほど、インデックスサイズが10MBほどのテーブルがあり、日次でインデックスの再構築を行っています。
1年ほど運用していて今まで普段は10秒ほどで再構築が終わるのですが、
先日2時間もかかったことで参照に不都合が発生し、この原因の調査と対策を行う必要が出てきました。

確認したいのは以下の点です。
・インデックスの再構築する際に、普段は10秒ほどで終わる程度の容量のテーブルが、日によって2時間も時間がかかるようなことがあるのかどうか
（1日に100件程度のレコード増加を想定、急激にレコード件数が増える可能性はないものとする）
・ないのであれば考えられる原因は何か、事後でも調べられるものなのか
・対策として、再構築ではなく再構成を行うようにする想定で考えているが、再構成なら参照に影響はないのかどうか

よろしくお願いします。

Answer 1

tanifujiさん、こんにちは。フォーラムオペレーターのHarukaです。
MSDNフォーラムにご投稿くださいましてありがとうございます。

このシナリオで2時間かかるのは普通ではありません。通常、40000レコードの再構築に必要な時間はわずか数秒です。
この問題は、他のプロセスが再構築プロセスをブロックするなど、さまざまな理由で発生する可能性があります。
トレースを作成していないと、問題の発生後に原因を突き止めることができません。

次回このスクリプトを使用してインデックスを再構築することをお勧めします。
https：//ola.hallengren.com/では、それらが行っていることが記録されます。

どうぞよろしくお願いします。

---

MSDN/ TechNet Community Support Haruka

～参考になった投稿には「回答としてマーク」をご設定ください。なかった場合は「回答としてマークされていない」も設定できます。同じ問題で後から参照した方が、情報を見つけやすくなりますので、
ご協力くださいますようお願いいたします。また、MSDNサポートに賛辞や苦情がある場合は、[e-mail]までお気軽にお問い合わせください。～

Answer 2

参考になりました。

情報ありがとうございました。

Answer 3

tanifujiさん

・インデックスの再構築する際に、普段は10秒ほどで終わる程度の容量のテーブルが、日によって2時間も時間がかかるようなことがあるのかどうか
→既に回答にあるように、ブロッキングの可能性が高いかと思われます。

・ないのであれば考えられる原因は何か、事後でも調べられるものなのか
→こちらも既に回答にあるように、トレースをしかけるか、もしくは拡張イベントをしかけておく、といった方法があります。

もしくは、現在起きているブロッキング情報を取得できるクエリがあるため、それを定期実行してローカルのDBに保存する、といった方法でも原因調査は可能です。

https://qiita.com/maaaaaaaa/items/38fd95b142b07acf7700

こちらの記事の最後のブロッキング検出クエリの箇所を参考にしてみてください。

・対策として、再構築ではなく再構成を行うようにする想定で考えているが、再構成なら参照に影響はないのかどうか

→4万件程度のレコードであれば、日次での再構築はリッチすぎるような気もします。再構成でも参照のパフォーマンスに影響はほぼ無いのではと予想します。どちらかというと統計情報を1日1回更新しているか、といったほうを気にされた方がパフォーマンス面では良いと思われます（4万件程度なら、万一フルスキャンが走ってもほぼ問題ないような気もしますが、、）

Answer 4

とても分かりやすい回答と解説ありがとうございます！

Answer 5

(もし参考になった場合は、回答としてマークいただけるとありがたいです）

Answer 6

普段使わないもので失礼いたしました。