indexの断片化によるクエリ遅延について

Question

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

インデックスの断片化により、
クエリが遅くなった場合、
必ず遅くなりますでしょうか？

現在開発しているアプリケーション 
はVBからADOで
sqlserver2008 Standard Edition
に接続されています。

そのアプリケーションが本番環境で稼働している時に
テストでは2〜3分程度の処理されるクエリ（SELECT～UPDATE文）が
2時間たっても終了しない事態に陥りました。
利用状況モニタで確認した所、【論理読み込み数/秒】が推移していたので
読み込んでいることは確認したんですが要望があり、
強制終了してしまいました。
このクエリ（SELECT～UPDATE文）は10万件程度を処理するものになります。

3度アプリケーションを立ち上げ処理を行ったら
（2度目も数時間たっても終了しなかった）
2～3分程度の処理で正常にクエリが帰ってきます。

調査を行ったところ、
インデックスの断片化が進んでおり、
（該当テーブルで98％以上）

何故そこまで断片化が進んでいたかというと
処理を行う前にインデックスの再構築と
ファイルの圧縮を行ったことが原因でした。
またその時にサーバの再起動も行っています。

その説明を上の方に話したところ
『でも、インデックスの断片化が原因なら毎回クエリが遅くなるのでは？』
というご指摘をいただき、
（私自身も確かにそうだな…という思いでして）
色々調べたんですが分かりません。

インデックスの断片化が原因のクエリ遅延の場合
毎回遅いままなんでしょうか？
それとも何らかのタイミングで
実行時間に差がでたりするのでしょうか？

よろしくお願い致します。

Answer 1

それとも何らかのタイミングで実行時間に差がでたりするのでしょうか？

先のスレッドでnaginoさんが回答されているように実行プランに依ります。早いときと遅いときのプランを比較するしかわかりません。

実行毎に統計情報などを元にクエリの実行プランが決定されるため、同じクエリであっても断片化の進んだインデックスを使う場合と使わない場合があり、実行するまでわかりません。このように動作速度が不安定になることを好まないのであれば各種クエリヒントを指定することで動的に決定されるプランをある程度誘導するか、最終的にはUSE PLANヒントで具体的なプランを指定して毎回確実に同じ動作をさせることになります。

# ところで「ご指摘をいただき」という敬語はどうかと…フォーラムという公の場で質問者さんの身内である「上の方」を尊敬してどうするのかと（－－

Answer 2

2回目までの処理でメモリにデータがキャッシュされているのかもしれませんし、
2回目までに実施したUpdate処理で統計情報が自動更新された結果、実行プランが変わっているのかもしれません。

どちらにしても佐祐理さんが書いている通り統計情報を見ればすぐわかると思いますよ

Answer 3

佐祐理、Kozuka Daisuke 
ご回答ありがとうございます。

＞# ところで「ご指摘をいただき」という敬語はどうかと…フォーラムという公の場で
＞質問者さんの身内である「上の方」を尊敬してどうするのかと（－－

すいません。お客様の上の方でした。
ここでの質問が慣れていなく、読み直してみると
確かに変ですね。申し訳ありませんでした。

統計情報なんですが以下のクエリで確認しました。
（後出しですいません。）
『
SELECT 
    so.name 
    , ss.name 
    , ss.auto_created 
    , ss.user_created 
    , ss.no_recompute 
    , STATS_DATE(ss.object_id, ss.stats_id) 
FROM 
    sys.objects AS so 
    LEFT JOIN 
        sys.stats AS ss 
    ON 
        ss.object_id = so.object_id 
WHERE 
    type = ‘U’ 
ORDER BY 
    so.name
』
ですが、STATS_DATE(ss.object_id, ss.stats_id)が
二回目、三回目近辺の時刻にありませんでした。
STATS_DATE(ss.object_id, ss.stats_id)が更新時刻と考えていたんですが
間違えなんでしょうか？

Answer 4

とりあえずインデックスの断片化について。

>インデックスの断片化により、
>クエリが遅くなった場合、
>必ず遅くなりますでしょうか？
2通りの回答がありえて、Yes とも No とも言えますね。
基準の無い相対的な評価で話を進めたり、仕組みを考慮せずに現象だけで話を進めると、こんな回答になってしまいますね。

インデックスが断片化すると、インデックスが保持する情報が同一でも、その情報を記録するためにより多くの Disk を使用していることになります。
すると、Read/Write や Cache により多くの Disk I/O や Memory を使用することになります。
ですので、インデックスが断片化すると、断片化していない状態に比べて必ず遅くなります。
ただし、インデックス全てが Cache に乗っており、Memory が潤沢にあり、Memory の帯域などにも余裕がある状況であれば、断片化していない状況と比べたときに体感できるような差は無いでしょう。

さて、別の見方から。
インデックスが断片化すると、インデックスが保持する情報が同一でも、その情報を記録するためにより多くの Disk を使用していることになります。
すると、インデックスの階層が深くなりますのでページ分割のときの分割数が増えたり、そもそも Disk の使用量が多いのでファイルの自動拡張がおきやすくなります。
これらはデータ更新時に常に発生するわけではありませんが、発生したときはパフォーマンスの劣化が如実に現れます。

ですので、着目しているポイントや基準値、環境によって、先の質問の答えは Yes にも No にもなりえます。

で、今回は、更新処理での頻度の低い極端なパフォーマンス劣化ですので、あがっている情報からページ分割とファイルの自動拡張の合わせ技で極端にパフォーマンス劣化しているという線が第一候補なように見受けられます。

キーとなる値が変動し、かつ再構築を行ってメンテナンスしているようなインデックスでは、再構築の際に FILLFACTOR を調整してページ分割の発生をコントロールしてください。
無節操な再構築は場合によっては更新処理のパフォーマンスを悪化させます。

あと、通常の環境ではファイルは圧縮しないのが基本です。
必要があって拡張されたので、圧縮したところで再度拡張されるだけです。
拡張される際はパフォーマンス劣化が如実に現れるので、ファイルの圧縮は将来のパフォーマンス劣化を予約するようなものです。

インデックスというのは参照処理の高速化が主目的の機能ですので、更新処理については開発者側が適切に考慮の上で設計し、管理者側が適切に運用してください。

ちょっと今は時間が取れないので、統計情報については割愛・・・。

---

MCITP（Database Developer/Database Administrator）

Answer 5

naginoさん
丁寧にありがとうございます。

説明不足で申し訳ありません。
再構築、ファイル圧縮、サーバー再起動を
処理の前に行った詳細なのですが

6月末に改修プログラムをリリースすることになり
該当処理で使用しているテーブル（tableA）に項目の追加
該当処理で使用しているテーブルの累積テーブル（tableA_累積）に項目の追加
を行いました。

以下手順になります。
①
tableAは500万件
tableA_累積は2000万件あるテーブルなので
自動拡張には時間がかかると懸念し
データベースファイル、ログファイルを拡張した。
※事前にディスクの空き容量を確認してから実施

データベースファイル
10GB→17GB
ログファイル
100MB→13GB

②tableAとtableA_累積はそれぞれ
tableA_bk・tableA_累積_bkという名称に変更した。
プライマリキー作成の名称も変更した。

③新項目の追加されたtableAとtableA_累積の作成（CREATE TABLE）
プライマリキー作成（PRIMARY KEY CLUSTERED）。

④tableA_bk・tableA_累積_bkテーブルから
tableAとtableA_累積へ
SELECT～INSERTを行った。
（新項目はNULL設定）

⑤インデックスの作成を行った。
（CREATE NONCLUSTERED INDEX ）
(SELECT～INSERT後にインデックスを張ったほうが早かったため
後からインデックスの作成。)

⑥tableA_bk・tableA_累積_bkを削除

⑦作業フォルダにバックアップを取得

⑧Management Studioからファイルの圧縮を実行
（2500万件分の空き領域ができるはずので圧縮したかったらしいです）

⑨サーバ再起動


7月上旬に月一で行う該当処理（SELECT～UPDATE）を行ったところ
上記の状態になりました。
7月中旬にインデックスの断片化の調査を行ったところ
プライマリキー以外のインデックスが98％以上の断片化が行われていました。

現在の対応策として
とりあえず再構築して、断片化が30％以上になったら
保守者が再構築しようか。という流れになっているようです。


＞キーとなる値が変動し、かつ再構築を行ってメンテナンスしているようなインデックスでは、
＞再構築の際に FILLFACTOR を調整してページ分割の発生をコントロールしてください。
＞無節操な再構築は場合によっては更新処理のパフォーマンスを悪化させます。
上記の状態は無節操な再構築にあたると思われるので、
FILLFACTORを調整したほうが良いという説得をしたいのですが
場合によっては更新処理のパフォーマンスを悪化するというのはどういうことなのでしょうか？

申し訳ありません。よろしくお願いいたします。

Answer 6

なるほど。
色々考慮すべき事項があるのですが、ここで全て書いてしまうと読むのも大変な分量になるので、いくつか掻い摘んで。

まずページ分割は今回の現象に関係あるかどうかに関わらず、インデックス設計ならびに運用において重要な要素ですので、きちんと理解しておいてください。
詳細は以下を参照してください。
http://www.atmarkit.co.jp/ait/articles/0606/28/news129.html
http://www.atmarkit.co.jp/ait/articles/1007/26/news087_2.html
http://engineermemo.wordpress.com/2012/04/16/

ページ分割は本質的には 50% : 50% の分割ですので、断片化が 50% を超えることはそうそう無いのですが、今回はおそらく UPDATE の処理内容と INDEX のスキーマとの組み合わせがよろしくないので、このようになっていると思われます。
具体的なスキーマ情報が無いと詳細はコメントしづらいですが、可能性としてはありえます。

対策としては、今ぱっと思いつくのは 3 種類ぐらいでしょうか。
1. インデックスの定義の調整(要件やスキーマ次第では不可)
2. 月一の作業後にインデックスをメンテナンス
3. 月一の作業時にインデックスを削除、作業後にインデックスを再作成
要件等よって選択するものですので、優劣は一概には言えません。
仔細はとりあえず割愛しますが、不明点はご質問ください。

FILLFACTOR (と PAD_INDEX) は、月一の作業以外でもインデックスに含まれる列が更新されるようであれば考慮が必要です。
逆に、月一の作業でしか更新されないようであれば、考慮不要(もしくは 100 指定がベター)です。
詳細は以下を参照してください。
http://technet.microsoft.com/ja-jp/library/ms177459.aspx
http://www.atmarkit.co.jp/ait/articles/0606/28/news129_2.html
http://technet.microsoft.com/ja-jp/library/ms186869.aspx

あともう一つ。
>＞無節操な再構築は場合によっては更新処理のパフォーマンスを悪化させます。
>(中略)
>場合によっては更新処理のパフォーマンスを悪化するというのはどういうことなのでしょうか？

たとえば FILLFACTOR を 100 など高い値で再構築した場合、ページ分割の発生確率が高くなるためです。
前掲のページ分割の説明にも関連した記述がありますので、そちらもあわせてご確認ください。

最後に。
「対処を実施」ではなく「他者の納得」がゴールとなると、より深い理解が必要になるものですので、その点はご留意、ご覚悟下さい。
開発系のコミュニティでは DB はそこまでは省みられないようですし、SQL Server のコミュニティが今は特に目立ったものが無いようで、勉強する場が少なくて難しいところではありますが、文字ベースのフォーラムだと限界があるので難しいところですね・・・。
直接図解しながら説明と質疑応答ができたら話が早いんですが。

---

MCITP（Database Developer/Database Administrator）

Answer 7

ごめんなさい。前のレスで統計情報を見ればわかると書いてしまったのですが、
統計情報ではなくて実行プランの間違いです。失礼しました。

前のスレッドも含め読んでみて気になったことをいくつか補足しますね。

今回のクエリが遅くなるタイミングは再起動後の1回目のクエリってことはありませんか？

ざっくり言ってしまうと再起動後の一発目のSelect処理は大抵遅くなりがちです。
それはメモリキャッシュにデータがない状態でのクエリ実行なので、すべてのデータをHDDに読み込みに行きます。
1度読み込んだデータはメモリキャッシュに残りますので、2回目以降の読み込みは早くなります。
ですので、本来数分で終わるはずと思っているのがメモリキャッシュにデータがある状態での実行時間であれば
それよりも時間がかかるのは当たり前の話になります。
※もちろん、サーバーの搭載メモリが少ない場合は2回目以降もHDDに読み込みが発生します

もしデータの断片化が進んでいるのであればこのHDDの読み込みはさらに遅くなります。

ですので、断片化が少ない状態に維持するのはいいとして、
もし効果を測る場合は再起動後の一発目にどれくらいの時間がかかるかを測定してあげないと
今回の問題が解消したとは言えないと思います。

Answer 8

naginoさん、Kozuka Daisuke さん。ありがとうございます。

naginoさん
まだ教えていただいたURLの中身を全部読んでいなく、
もう少し、時間をかけて読み込んでいきたいと思います。

＞「対処を実施」ではなく「他者の納得」がゴールとなると、より深い理解が必要になるものですので、
＞その点はご留意、ご覚悟下さい。

そうですね。私にとっては高すぎるハードルだったかもしれません。。
取り急ぎお礼まで。


Kozuka Daisuke さん
ありがとうございます。
障害発生時、
該当処理(SELECT～UPDATE文)の実行プランの
Management Studioを確認したんですが、
コスト100％になっているものはなく、
推奨するインデックスも表示されていないことは確認済みです。
（というかそういった確認しかしてなかったみたいです）

確かにクエリが大きい処理(10万件)を扱うのは
再起動後一回目です。数百件の処理は行っています。

＞それはメモリキャッシュにデータがない状態でのクエリ実行なので、すべてのデータをHDDに読み込みに行きます。
＞1度読み込んだデータはメモリキャッシュに残りますので、2回目以降の読み込みは早くなります。

すべてのデータをHDDに読み込みに行くというのは物理読み取りでしょうか？
利用状況モニタで見たところ[物理読み取り数/秒]は見たときには0になっており
ずっと[論理読み込み数/秒]をしている状態でした（約１時間）。
(障害が起こって一時間後くらいだったのでそれまでに
長時間物理読み取りをしていた可能性もあるんですが)

Answer 9

こちら、レスが付かないようなので代わりに。

>すべてのデータをHDDに読み込みに行くというのは物理読み取りでしょうか？
初回は物理読み込みですね。
ただし、最近の Disk は高速ですので、例えば 1GByte 程度のデータであれば数分もかからず物理読み込みは終わると思われます。
今回の問題では分単位ではなく時間単位での問題ですので、あまり考慮すべき話ではなさそうに思われます。
おそらく最初の数分で物理読み込みは終わり、その後はメモリ上のバッファーキャッシュにあるデータを繰り返し論理読み込みしているものと思われます。
ですので、処理開始から時間が経過した状態では論理読み込みのみという状況であるのは、特に違和感を感じません。

---

MCITP（Database Developer/Database Administrator）