none
プランガイドの適用条件について RRS feed

  • 質問

  • SQLSERVER 2014 を使用しております。

    実行プランを固定化する目的でSQLプランガイドを作成しているのですが、SQLによっては、適用されたりされなかったりといった事象が発生しています。

    処理途中で統計情報の更新などでリコンパイルされたときに、今までプランガイドが適用できていたものが急に適用しなくなったりします。

    作成しているプランガイドは、

    @type = N'SQL' 

    @hints = N'<ShowPlanXML ・・・

    の形式で実行プランをそのまま適用させています。

    プランガイドが適用される条件について、ご存知の方がいればご教授いただけますでしょうか。

    2019年3月19日 11:14

回答

  • プランガイドの適用条件ですが、次のドキュメントの内容が公開されているものとなるかと思います。
    (古いバージョンの情報ですが、最新の SQL Server でも基本となる考え方は変わっていないはずです)

    SQL Server のバージョンは、2014 ではないのですが、次のクエリに対応するプランガイドを作って試してみました。
    (SQL Server 2014 に最新の更新プログラムを適用することで現象が解決する可能性もあるかもしれないですが)

    EXEC sp_executesql @stmt = N'SELECT L_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY) AS SUM_QTY,
     SUM(L_EXTENDEDPRICE) AS SUM_BASE_PRICE, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS SUM_DISC_PRICE,
     SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)) AS SUM_CHARGE, AVG(L_QUANTITY) AS AVG_QTY,
     AVG(L_EXTENDEDPRICE) AS AVG_PRICE, AVG(L_DISCOUNT) AS AVG_DISC, COUNT(*) AS COUNT_ORDER
    FROM LINEITEM
    WHERE L_SHIPDATE <= dateadd(dd, -90, cast(@0 as datetime))
    GROUP BY L_RETURNFLAG, L_LINESTATUS
    ORDER BY L_RETURNFLAG,L_LINESTATUS'
    , @params = N'@0 datetime', @0 = '1998-12-01'

    このクエリに対応するプランガイドを Type=SQL で作成した場合、プランガイドの対象となるクエリが実行された際に、統計情報の自動更新が発生した場合でも、正常にプランガイドは適用されていました。

    以下の画像は、統計情報の自動更新の発生によりステートメントがリコンパイルされた状態の情報ですが、統計情報の自動更新を発生させたクエリについてもプランガイドが適用されている (plan_guide_successful) となっていることが確認できました。

    ステートメントのテキストとパラメーターのデータ型が一致していれば、統計情報の自動更新が発生した際の該当となったクエリについてもプランガイドは使用されているように見えます。

    すでにご確認されているかもしれませんが、プランガイドの適用状況については、

    のイベントを SQL Profiler または、拡張イベントで取得することで確認ができます。

    何らかの理由で、同一テキストのプランガイドがあるが、プランガイドの実行プランを利用できなかった場合は、「unsuccessful」のイベントが発生します。

    こちらが発生した場合は、プランガイドがあると認識できているが何らかの理由により、プランガイドが利用できなかった可能性がありますので、sys.fn_validate_plan_guide で確認することで原因が確認できるかもしれません。
    (詳細については、アップグレード後のプラン ガイドの検証 を参照してください)

    統計情報の更新後に実行されてタイミングで、「sucsessful」「unsuccessful」が発生していない場合は、パラメーターやテキストの違い等により「同一のクエリがプランガイドに存在すると認識できていない」状態であると考えられます。

    さらなる調査には、クエリの実行方法や状態の細かな調査が必要となるかと思いますので、有償サポート等を利用しないと、原因の追及は難しいかもしれませんね。

    2019年3月27日 6:17
    モデレータ

すべての返信

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

    作成できるプランガイドは3種類あります。 
    この投稿では、@type = N'SQL' です。
    SQL プランガイドは、データベースオブジェクトの一部ではなく、スタンドアロンのTransact-SQLステートメントおよびバッチのコンテキストで実行されるクエリと一致します。
    このタイプは、スタンドアロンのTransact-SQLステートメントとバッチに適用されます。 
    詳しくは、プラン ガイドを参照してください。

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


    MSDN/ TechNet Community Support Haruka

    ~参考になった投稿には「回答としてマーク」をご設定ください。なかった場合は「回答としてマークされていない」も設定できます。同じ問題で後から参照した方が、情報を見つけやすくなりますので、
    ご協力くださいますようお願いいたします。また、MSDNサポートに賛辞や苦情がある場合は、MSDNFSF@microsoft.comまでお気軽にお問い合わせください。~
    2019年3月22日 8:23
    モデレータ
  • Harukaさま

    ご回答ありがとうございます。

    困っている事象としては、同一プログラム(SQL)を並列で5つ実行する処理がありまして、その内、例えば最初の3つは作成したプランガイドが適用されるが、後の2つはプランガイドが適用されないといった事象が発生しています。

    適用される、されないが切り替わるタイミングとしては、処理中に統計情報の自動更新が入ったことによりリコンパイルが発生したケースです。

    また、同じプログラムですので、発行されているSQLはパラーメータ変数値以外は全く同じSQLです。

    例えば、結合しているテーブルの1つが0件(推定行数が0件)であればプランガイドは適用されないとか、何かそういった細かい条件はありますでしょうか?

    2019年3月25日 0:51
  • yasu382さん、こんにちは。フォーラムオペレーターのHarukaです。
    ご返信頂きありがとうございます。

    プランガイドは、それらが作成されたデータベースのみを対象としています。 
    したがって、クエリの実行時に現在のデータベースにあるプランガイドのみをクエリに一致させることができます。

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


    MSDN/ TechNet Community Support Haruka

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

    2019年3月27日 0:41
    モデレータ
  • Harukaさま

    ご回答ありがとうございます。

    データベースは同一です。

    イメージとしては、次のとおりです。

    ※①から順に時系列です。

    ※SQL_A は同じSQLです。パラメータ変数値のみ異なります。

    ①セッション1で SQL_A を実行 ⇒SQLが解析され、プランガイドが適用された。(プランガイドで指示した実行プランとなった)

    ②セッション2で SQL_A を実行 ⇒①で生成された実行プランがそのまま使用された。

    ③セッション3で SQL_A を実行 ⇒①で生成された実行プランがそのまま使用された。

    ④参照テーブルの統計情報が自動更新された。

    ⑤セッション4で SQL_A を実行 ⇒SQLが解析され、プランガイドが適用されずに新たな実行プランが生成された。

    ⑥セッション5で SQL_A を実行 ⇒④で生成された実行プランがそのまま使用された。

    です。

    よく分からないのは、⑤で再解析された際に、なぜプランガイドが適用されなかったのか?です。

    参照テーブルの統計情報などの外部的な要因により、プランガイドが適用されない条件があるのでしょうか?

    という質問になります。

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


    • 編集済み yasu382 2019年3月27日 5:13
    2019年3月27日 5:13
  • プランガイドの適用条件ですが、次のドキュメントの内容が公開されているものとなるかと思います。
    (古いバージョンの情報ですが、最新の SQL Server でも基本となる考え方は変わっていないはずです)

    SQL Server のバージョンは、2014 ではないのですが、次のクエリに対応するプランガイドを作って試してみました。
    (SQL Server 2014 に最新の更新プログラムを適用することで現象が解決する可能性もあるかもしれないですが)

    EXEC sp_executesql @stmt = N'SELECT L_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY) AS SUM_QTY,
     SUM(L_EXTENDEDPRICE) AS SUM_BASE_PRICE, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS SUM_DISC_PRICE,
     SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)) AS SUM_CHARGE, AVG(L_QUANTITY) AS AVG_QTY,
     AVG(L_EXTENDEDPRICE) AS AVG_PRICE, AVG(L_DISCOUNT) AS AVG_DISC, COUNT(*) AS COUNT_ORDER
    FROM LINEITEM
    WHERE L_SHIPDATE <= dateadd(dd, -90, cast(@0 as datetime))
    GROUP BY L_RETURNFLAG, L_LINESTATUS
    ORDER BY L_RETURNFLAG,L_LINESTATUS'
    , @params = N'@0 datetime', @0 = '1998-12-01'

    このクエリに対応するプランガイドを Type=SQL で作成した場合、プランガイドの対象となるクエリが実行された際に、統計情報の自動更新が発生した場合でも、正常にプランガイドは適用されていました。

    以下の画像は、統計情報の自動更新の発生によりステートメントがリコンパイルされた状態の情報ですが、統計情報の自動更新を発生させたクエリについてもプランガイドが適用されている (plan_guide_successful) となっていることが確認できました。

    ステートメントのテキストとパラメーターのデータ型が一致していれば、統計情報の自動更新が発生した際の該当となったクエリについてもプランガイドは使用されているように見えます。

    すでにご確認されているかもしれませんが、プランガイドの適用状況については、

    のイベントを SQL Profiler または、拡張イベントで取得することで確認ができます。

    何らかの理由で、同一テキストのプランガイドがあるが、プランガイドの実行プランを利用できなかった場合は、「unsuccessful」のイベントが発生します。

    こちらが発生した場合は、プランガイドがあると認識できているが何らかの理由により、プランガイドが利用できなかった可能性がありますので、sys.fn_validate_plan_guide で確認することで原因が確認できるかもしれません。
    (詳細については、アップグレード後のプラン ガイドの検証 を参照してください)

    統計情報の更新後に実行されてタイミングで、「sucsessful」「unsuccessful」が発生していない場合は、パラメーターやテキストの違い等により「同一のクエリがプランガイドに存在すると認識できていない」状態であると考えられます。

    さらなる調査には、クエリの実行方法や状態の細かな調査が必要となるかと思いますので、有償サポート等を利用しないと、原因の追及は難しいかもしれませんね。

    2019年3月27日 6:17
    モデレータ
  • ご回答ありがとうございます。

    プランガイドが適用されたかどうかについては、実行時の実行プランをプロファイラで採取してプランガイドの適用有無を確認していました。
    Plan Guide Successful 、Plan Guide Unsuccessful のイベントクラスは未確認でした。

    もう一度再現させて、上記イベントクラスの値を確認します。

    事象としてもう少し詳しく記載しますと、5つ全てでプランガイドが適用されないケースと、上記のように統計情報の自動更新のタイミングで切り替わってしまうケースがあります。

    2019年3月29日 6:29
  • yasu382さん、こんにちは。フォーラムオペレーターのHarukaです。
    ご返信頂きありがとうございます。

    統計を更新すると、プランが無効になります。 
    SQL Serverは(特にデータが変更された場合は)パラメータを再確認し、更新された統計から新しいプランを取得する可能性があります。

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


    MSDN/ TechNet Community Support Haruka

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

    2019年4月5日 0:47
    モデレータ