Clever Geek Handbook

SQL Serverが2〜3時間ごとに切り替えて最適でないクエリ実行プランを使用する方法

最後の数日間、私は興味深い仕事に取り組んでおり、コミュニティと興味深い経験を共有したいと思っています。



問題は何ですか：

レポート用のデータを選択するためのストアドプロシージャ（ストレージ）を開始します-3秒かかり、戦闘中のプロファイラーを見る-ユーザーは同じ結果になります。 しかし、3時間が経過し、同じパラメーターで同じストレージが2分間、ユーザーに対しても同様に実行されています。 さらに、使用されたテーブルのデータは挿入/削除されず、環境は変更されず、管理者は設定を行いませんでした。



リクエストの前にローカライズ： 



INSERT INTO @table_variable1 SELECT ... FROM dbo.view_with_unions v1 WITH (READUNCOMMITTED) LEFT JOIN @table_variable2 AS t1 ON t1.Code = v1.DirectionDimensionCode LEFT JOIN other_table v2 WITH (READUNCOMMITTED) ON v2.Code = v1.SaleType WHERE ...


プロファイラーは実行計画を設定し、保存時間が長くなると、問題のあるリクエストの実行計画も変更されることに気付きました。



すでに何か！



それから、実装の面で何が変化しているかをより詳しく調べ始めました。 長期的には、NestedLoopマージが使用され、高速マージではHashMatchが使用されることが判明しました。



クイックプラン：







遅い（SQL Serverが2時間後に切り替える）：







HASH JOINを使用するためのヒントを登録したくなかったのは、 SQL Serverが依然として間違った計画を選択する理由を理解する必要があります。



最初の考えは、統計に何か問題があると考えていましたが、プロファイラーの実行計画では、実際の行数は0で、推定行数は1でした。 したがって、統計とカーディナリティの問題を調査するほど差は大きくありません。







しかし、実際の行数= 0を時々見ると、疑問がありました。実際、常に1行が返されるわけではありません。 それは間違っていることが判明しました。プロファイラーは、リクエストが完了して実際のデータがわかる前に実行計画をインターセプトします。 したがって、実際の行数にはゼロ以外は表示できません。



では、実際の行数の実際の値を見てみましょう！



その後、疑問が生じました-推定行数が常に1に等しいのはなぜですか？ 結局、インデックスが使用され、現在の統計があります。 また、行の推定数の値は毎回1です。しかし、意外なことに、SQL Serverは、選択性の低い列で始まる場合（つまり、0、1、NULLなどの異なる値の数が少ない場合）統計を使用しません。 したがって、インデックスキーの最初の列を最後の場所に移動しました。 これらの列のすべての条件がWHEREに課され、ANDでリストされていることを確認した後、統計付きのインデックスが使用に適していることを意味します。



診断：

  1. プロファイラーは実行プランに実際の行数を表示せず、n / aではなくゼロを書き込みます。 これを覚えておく必要があります！
  2. 私たちの場合、SQL Serverは統計を使用して行の推定数を決定しません。
  3. そして、たとえ彼が統計を使い始めたとしても、彼は非常に間違っています。




解決策：

  1. プロファイラーから手動で長いクエリを実行し、実際の実際の行数を確認します
  2. SQL Serverにインデックスの統計を使用する機会を与える必要があります。このため、キーの最初の列には多くの異なる値が必要です（たとえば、3つの0、1、NULLではありません）。 なぜなら 最初の列の値がわずかに異なる（選択性が低い）場合、SQL Serverには行数を適切に予測する機能がないため、このような統計は使用されません。
  3. インデックスの再構築後、推定行数予測の品質を向上させるために、WITH FULLSCANオプションで統計を更新する必要があります。



    UPDATE STATISTICS [dbo]。[ Table_from_union_for_view ] WITH FULLSCAN;

    行く




そして今、すべての要求は2秒以内に完了し、実行計画は前の要求からのミックスとして使用され、Management Studioの実際の行数を確認します。







しかし、それだけではありません！



そのような作業戦略は、実装の計画をキャッシュします。これには、プラス面とマイナス面の両方があります。



ポジティブ：

実行速度は実際に1〜2秒です



マイナス：

定期的に、要求は約20〜40秒間処理され、その後1〜2秒間再び実行され続けます。 これは、問題の要求の行数の変化にジャンプがある場合に発生します（大から小、または小から大のいずれか）。



しかし、SQL Serverはこれを克服する機会を与えてくれます！



これを行うには、OPTION（RECOMPILE）オプションを使用します。これにより、実行ごとに実行計画が再構築されます。 これにより、各リクエストの実行時間は3〜4秒に増加しますが、日中は20〜40秒は実行されません。 ところで、OPTION（RECOMPILE）は、一時オブジェクトとテーブル変数を使用するときに最も正確なカーディナリティスコアを取得するのにも役立ちます。これは、推定行数の決定に使用され、クエリ実行プランの選択に使用されます。 （一時オブジェクトとその中のRECOMPILEオプションの本質については、 sqlblog.com / blogs / paul_white / archive / 2012/08/15 / temporary - tables - in - stored - procedures.aspxで非常に良い記事で説明されています ）



ここでは、ほとんどの要求が1〜2秒実行されるか、1日の間に20秒を超える要求が実行されないか、より重要なことを自分で決定する必要があります。


More articles:


All Articles