次の方法で共有

プラン ガイドについて

  • [アーティクル]

SQL Server 2005 では、クエリのパフォーマンスを最適化するプラン ガイドを作成するための sp_create_plan_guide システム ストアド プロシージャが導入されています。このプロシージャは、クエリのテキストを直接変更できない場合や変更を望まない場合に使用できます。プラン ガイドは、サード パーティ ベンダから提供されているデータベース アプリケーション内のクエリの小さなサブセットのパフォーマンスが期待どおりではない場合に役立ちます。プラン ガイドは、クエリ ヒントをクエリにアタッチすることにより、クエリの最適化を促します。sp_create_plan_guide ステートメントには、最適化対象のクエリと、クエリの最適化に使用するクエリ ヒントが含まれた OPTION 句を指定します。クエリが実行されると、SQL Server によりクエリがプラン ガイドと照合され、実行時にクエリに OPTION 句がアタッチされます。

ms190417.note(ja-jp,SQL.90).gifメモ :
プラン ガイドを作成および使用できるのは、SQL Server 2005 Standard、Developer、Evaluation、および Enterprise Edition だけです。プラン ガイドの削除はすべてのエディションで実行できます。

通常、プラン ガイドからメリットを得られる可能性があるクエリは、パラメータ ベースです。このようなクエリはキャッシュされたクエリ プランを使用しますが、パラメータ値が最悪のシナリオや最も典型的なシナリオに対応していないために、クエリのパフォーマンスが低くなっていることがあります。この問題に対処するのに、OPTIMIZE FOR クエリ ヒントと RECOMPILE クエリ ヒントを使用できます。OPTIMIZE FOR は、クエリの最適化時にパラメータに特定の値を使用するように SQL Server に指示するクエリ ヒントです。RECOMPILE は、クエリ プランを実行後に破棄し、次に同じクエリが実行されるときに、クエリ オプティマイザにより新しいクエリ プランに強制的に再コンパイルされるようにサーバーに指示するクエリ ヒントです。

プラン ガイドと共に使用するもう 1 つの一般的なヒントは、USE PLAN クエリ ヒントです。このクエリ ヒントでは、オプティマイザが特定のクエリに選択した実行プランを、パフォーマンスの高い既存の実行プランに置き換える場合に適用されます。SQL Server では、USE PLAN により、ヒント構文に明示的に指定された特定のクエリ プランをクエリの実行時に適用します。結合順序の適用、結合ヒントの使用、インデックス ヒントの使用などを行うようにクエリを書き直すことによって、最も簡単により適切な実行プランが得られる場合に、USE PLAN クエリ ヒントを適用するプラン ガイドが特に役立ちます。詳細については、「プラン適用シナリオ : 書き直されたクエリから取得したプランを適用するためのプラン ガイドの作成」を参照してください。

RECOMPILE、OPTIMIZE FOR、USE PLAN、およびその他のクエリ ヒントの詳細については、「クエリ ヒント (Transact-SQL)」を参照してください。

ms190417.Caution(ja-jp,SQL.90).gif注意 :
クエリ ヒントの使用方法が正しくないプラン ガイドは、コンパイル、実行、またはパフォーマンスに関する問題の原因になることがあります。プラン ガイドは、上級開発者とデータベース管理者のみが使用するようにしてください。

プラン ガイドを作成すると、次のコンテキストで実行されるクエリを照合できます。

  • OBJECT プラン ガイドでは、Transact-SQL ストアド プロシージャ、スカラ関数、複数ステートメント テーブル値関数、および DML トリガのコンテキストで実行されるクエリが照合されます。
  • SQL プラン ガイドでは、データベース オブジェクトの一部ではないスタンドアロン Transact-SQL ステートメントとスタンドアロン バッチのコンテキストで実行されるクエリが照合されます。また、SQL ベースのプラン ガイドを使用して、指定した形式にパラメータ化されたクエリを照合することもできます。
  • TEMPLATE プラン ガイドでは、指定した形式にパラメータ化されたスタンドアロン クエリが照合されます。これらのプラン ガイドは、クエリのクラスのデータベースの現在の PARAMETERIZATION データベース SET オプションを上書きするために使用されます。

OBJECT プラン ガイド

@Country パラメータを受け取る次のストアド プロシージャが、AdventureWorks データベースに対して配置されたデータベース アプリケーションに存在するとします。

CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country nvarchar(60))
AS
BEGIN
    SELECT *
    FROM Sales.SalesOrderHeader h, Sales.Customer c, 
        Sales.SalesTerritory t
    WHERE h.CustomerID = c.CustomerID
        AND c.TerritoryID = t.TerritoryID
        AND CountryRegionCode = @Country
END

このストアド プロシージャは @Country = N'AU' (オーストラリア) 用にコンパイルおよび最適化されていることに気付いたとします。しかし、オーストラリアからの販売注文は比較的少数です。このクエリが、販売注文数の多い国のパラメータ値を使用して実行されると、パフォーマンスが低下します。販売注文数が最も多い国は米国なので、@Country=N'US' 用に生成されたクエリ プランのパフォーマンスは、@Country パラメータにどの値を使用しても低下しません。

ストアド プロシージャを変更し、OPTIMIZE FOR クエリ ヒントをクエリに追加することで、この問題に対処できます。ただし、ストアド プロシージャは配置済みアプリケーション内にあるので、アプリケーション コードを直接変更することはできません。代わりに、AdventureWorks データベースに次のプラン ガイドを作成できます。

sp_create_plan_guide 
@name = N'Guide1',
@stmt = N'SELECT * FROM Sales.SalesOrderHeader h,
        Sales.Customer c,
        Sales.SalesTerritory t
        WHERE h.CustomerID = c.CustomerID 
            AND c.TerritoryID = t.TerritoryID
            AND CountryRegionCode = @Country',
@type = N'OBJECT',
@module_or_batch = N'Sales.GetSalesOrderByCountry',
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@Country = N''US''))'

sp_create_plan_guide ステートメントの指定したクエリが実行されると、そのクエリは最適化される前に変更され、同時に指定した OPTIMIZE FOR (@Country = N''US'') 句が含められます。

SQL プラン ガイド

SQL プラン ガイドは、アプリケーションから sp_executesql システム ストアド プロシージャから頻繁に送信されるステートメントとバッチに適用されます。たとえば、次のスタンドアロン バッチについて考えてみましょう。

SELECT TOP 1 * FROM Sales.SalesOrderHeader h ORDER BY OrderDate DESC

このクエリに並列実行プランが生成されないようにするには、次のプラン ガイドを作成します。

sp_create_plan_guide 
@name = N'Guide1', 
@stmt = N'SELECT TOP 1 * FROM Sales.SalesOrderHeader h ORDER BY OrderDate DESC',  
@type = N'SQL',
@module_or_batch = NULL, 
@params = NULL, 
@hints = N'OPTION (MAXDOP 1)'
ms190417.note(ja-jp,SQL.90).gifメモ :
プラン ガイドの作成対象のステートメントを含むバッチには、USE database ステートメントを含めることはできません。
ms190417.note(ja-jp,SQL.90).gif重要 :
sp_create_plan guide ステートメントの @module_or_batch 引数と @params 引数に指定する値は、実際のクエリで送信される、対応するテキストと完全に一致している必要があります。詳細については、「sp_create_plan_guide (Transact-SQL)」および「SQL Server Profiler を使用したプラン ガイドの作成とテスト」を参照してください。

PARAMETERIZATION データベース オプションを FORCED に設定するか、またはクエリのクラスをパラメータ化するように指定して TEMPLATE プラン ガイドを作成すると、同じ形式にパラメータ化されるクエリに SQL プラン ガイドを作成することもできます。詳細については、「パラメータ化クエリのプラン ガイドの設計」を参照してください。

TEMPLATE プラン ガイド

TEMPLATE プラン ガイドは、特定のクエリ形式のパラメータ化動作を上書きするときに使用されます。TEMPLATE プラン ガイドは、次のいずれかの状況で作成できます。

  • PARAMETERIZATION データベース オプションを FORCED に設定したが、簡易パラメータ化のルールに従ってコンパイルするクエリがある場合。
  • PARAMETERIZATION データベース オプションを SIMPLE (既定値) に設定したが、特定のクラスのクエリについて強制パラメータ化が必要である場合。

詳細については、「プラン ガイドを使用したクエリのパラメータ化動作の指定」を参照してください。

TEMPLATE プラン ガイドを SQL プラン ガイドと併用することもできます。たとえば、TEMPLATE プラン ガイドを作成することで、特定のクラスのクエリについて確実にパラメータ化を行うことができます。これにより、そのパラメータ化された形式のクエリに対して SQL プラン ガイドを作成できます。

参照

概念

プラン ガイドを使用した配置済みアプリケーションのクエリの最適化
プラン ガイドのデザインと実装

その他の技術情報

クエリ パフォーマンス
sp_create_plan_guide (Transact-SQL)
sp_control_plan_guide (Transact-SQL)
sys.plan_guides

ヘルプおよび情報

SQL Server 2005 の参考資料の入手