プランガイドの適用条件について
質問
回答
-
プランガイドの適用条件ですが、次のドキュメントの内容が公開されているものとなるかと思います。
(古いバージョンの情報ですが、最新の 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」が発生していない場合は、パラメーターやテキストの違い等により「同一のクエリがプランガイドに存在すると認識できていない」状態であると考えられます。
さらなる調査には、クエリの実行方法や状態の細かな調査が必要となるかと思いますので、有償サポート等を利用しないと、原因の追及は難しいかもしれませんね。
- 編集済み Masayuki.OzawaMVP, Moderator 2019年3月27日 12:27
- 回答としてマーク yasu382 2020年6月13日 7:51
すべての返信
-
プランガイドの適用条件ですが、次のドキュメントの内容が公開されているものとなるかと思います。
(古いバージョンの情報ですが、最新の 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」が発生していない場合は、パラメーターやテキストの違い等により「同一のクエリがプランガイドに存在すると認識できていない」状態であると考えられます。
さらなる調査には、クエリの実行方法や状態の細かな調査が必要となるかと思いますので、有償サポート等を利用しないと、原因の追及は難しいかもしれませんね。
- 編集済み Masayuki.OzawaMVP, Moderator 2019年3月27日 12:27
- 回答としてマーク yasu382 2020年6月13日 7:51
