none
特定の処理タイミングで実行プランが変わる。

    質問

  • sqlserver2008R2で1年前にサービスを開始したシステムがあり、最近、取り扱いデータ量が突然増え、日中のバッチ処理が止まってしまうトラブルが発生してしまいました。そのバッチ処理は各グループキー単位でループさせ処理を行います。(①INSERT、②UPDATE、③DELETEをループ)統計情報の自動更新をONに設定。

    そこで、ある程度データがたまった段階で数万件程度のループ処理は問題なく終了するのですが、百数十万件のデータ処理が実行されると、1回目のループの①INSERTの処理はすぐ終わるのですが(1回目のループでは、②③の処理は流れるが実際は0件処理となる)、同じグループキーの2回目ループのUPDATE、DELETEの処理でSQLが返ってこなくなりました。原因を探った所、正常に終了した場合(数万件で処理した場合)とは、返ってこなくなるSQLで実行プランが異なっていることが分かりました。

    そこで、統計情報取得処理を再取得し処理を実行したところ問題なく処理が終了することが分かりました。

    しかし、殆どが数万件単位でのループ処理の為、ループの中で統計情報取得処理を入れてしまうと累積データがたまるに連れて統計情報再取得の実行時間が重くなる事を懸念しており、出来れば更新処理を実装したくありません。現在update statics table文で必要最低限のTBL&タイミングでの統計情報更新を取得するよう考えていますが、他に対策等はありますでしょうか。

    ちなみに、他のサイトで自動更新タイミングを意図的に発生させるためdeleteまたはupdateのwhere句付きのものを発行すれば自動更新されるとの書き込みがありました。ループの最後に処理が終わったレコードのdelete文を入れることは可能なのですが、deleteまたはupdateのwhere句付きの文を発行することは自動更新タイミングと関係がありますでしょうか。

    2012年5月29日 15:55

すべての返信

  • こんにちは。


    バッチ処理が止まるとは、具体的にどのような事象ですか?

    統計情報の問題であるとした根拠はなんですか?
    2012年5月31日 0:16
  • バッチ処理が止まるというのは具体的にクエリが返ってこない事象になります。該当の事象を統計情報の更新の問題であるとした根拠は、処理が順調に流れている時と流れない時の止まっている該当クエリの実行計画が変わっている為&同グループキーで1回目と2回目の間の処理で必ず発生する(※)為です。実行計画を参照すると、一度処理を行ったグループキーのデータはハッシュ結合で処理を行っていましたが、処理が止まったときはネステッドループ結合での実行計画となっていました。

    今回の処理はグループキーでループし、また同グループキーデータの中で時系列データでループ処理を行っています。

    問題のクエリは結合処理のデータ量が多い為(または該当結合データの統計情報が取得出来ていない為)、クエリが返ってこない状況と推測しています。統計情報が適切に更新されていないであろうとした理由は、クエリのチューニング(ヒント句やクエリの見直し)を実施しても、本ケース(※)に限り処理自体のスピードは通常時より大幅に遅くなる為です。

    (※)グループキー単位での処理というのがポイントで、一度Aというグループキーの1回目の処理を行い、Aグループの次のループの2回目の処理の間に統計情報が取られれば以降のAグループ全体の処理は正常に終了する(または次のバッチ処理でAグループのデータが来ても問題ない)のですが、システムに取り込むデータとして初めてAグループの処理を行い次のAグループの処理を行うと、1回目の処理で大量に各中間TBLにAグループのデータが登録される為、1回目でデータが登録された時点でのAグループの統計情報が取られていない為、実行計画が変わってしまっているのではないかと推測しています。

    2012年5月31日 13:44
  • こんにちは。

    データの分布やINDEXの状態などが解らないので、的外れな話になるかもしれませんが、そこはご容赦をしてください。


    ネステッドループで使う統計情報が無いため、遅くなっているという見立てはその通りだと思います.

    で、ここでチューニングについて話を詰めていくのは難しいので、一般論のようになってしまいますが・・・
    (ばらばらと書いてしまいますが、申し訳ないです)




    もし、件数が対象スキーマの総量と同程度であるとすれば、DB上で処理を行うよりも、いったんファイルなどに落として、グループキー毎にシーケンシャルな処理を並列で実行させ、最後にDBへ統合するという事で、改善を図れる可能性があるかもしれません。
    件数が対象スキーマの総量と同程度である場合、ほぼすべてのデータに対してCRUD処理が行われることになり(結果的にデータが全部入れ替わるに近い状態)、DB上での処理が効率的であるかが気になるところです。
    グループキーによって、アクセスする領域が明確となっているようですので、これ以外にも何か状況にマッチした方法があるかもしれません。


    もし、この件数が総量の(仮に)10%程度であるならば、INDEX・ヒントを考える事で、統計情報にあまり左右されないアクセスを行える可能性があると思います。
    (すでに行われているかもしれませんが)

    OracleのNOLOG MODEのようなものってSQL Server だと、単純復旧モードでしたっけ?一時的にログの運用をなしにするという方法も、状況次第ではあり得るかと思います。


    >クエリのチューニング(ヒント句やクエリの見直し)を実施しても、本ケース(※)に限り処理自体のスピードは通常時より大幅に遅くなる為です。

    この「大幅に」が実際にどの程度かは検証してみた方が良いと思います。



    グループキーによって明にアクセス領域が分断されているようですので、論理・格納構造を分割するという手段も有効かもしれません。アクセスするINDEXの範囲が縮小できる可能性があると思います。
    自分の体験では、件数が数千万~数億件だとしても、INDEXをうまく設計すると統計情報に完全には依存せずに一定の性能をたたき出せる事がありました。
    (でも、これは使い方に依存してしまいますよね。)

    結合方法(どちらを外部にするか)によっては、コストが大幅に変わる事もありますので、こちらについても見直し・・・は行っていますよね多分。
    とはいえ、もう一度SQLステートメントの再検査と検証は(文法では無くてコスト面で)やった方が良いかもしれません。
    問題の状態・条件下で、INDEX SCANなのかSeekなのかもチェックしてみると良いと思います。
    ヒント句の見直しというより、内部表の作成に使われる事に最適化されたINDEXを追加利用するなども良いかもしれません。(すでに行われているかもしれませんね)

    更新動作が、主たるDBオブジェクトに対してのみであり、結合される側が連鎖して更新されないのであれば、あらかじめビューや専用のテーブルを作成してから結合するという手も有りかと思います。(ネステッドループが発生しにくい状況を作れないかなーという事です)

    お話の雰囲気から思うに、統計情報に依存しないようにINDEXやヒント句を設計する事や、結合の方法について検討するのが良いのではないかという印象を受けました。


    最後に、ネステッドループであるとしても、SQLが返ってこない(数分?)という状況は、SQLステートメントの見直しが必要なんじゃないかな・・っていう気もします。
    2012年6月1日 5:18
  • 統計情報の更新は、ランダムに選んだサンプルを使って行なわれるため、テーブル全体のデータ量が増えても統計更新に使われるデータ量はそれほど大きく変わらず、処理時間が極端に長くなるということはないと思います。

    統計更新自体は、自動でも手動でも処理を開始するきっかけが違うだけで処理内容は変わらないので、無理に自動更新が発生するように delete を行うというのは、全く意味がないように思います。

    2012年6月4日 8:07