sp_create_plan_guide (Transact-SQL)

[アーティクル]
12/16/2008

更新 : 2007 年 9 月 15 日

クエリヒントをデータベース内のクエリと関連付けるためのプランガイドを作成します。プランガイドの詳細については、「プランガイドを使用した配置済みアプリケーションのクエリの最適化」を参照してください。

構文

 sp_create_plan_guide [ @name = ] N'plan_guide_name'     , [ @stmt = ] N'statement_text'     , [ @type = ] N'{ OBJECT | SQL | TEMPLATE }'     , [ @module_or_batch = ]       {                      N'[ schema_name. ] object_name'         | N'batch_text'         | NULL       }     , [ @params = ] { N'@parameter_name data_type [ ,...n ]' | NULL }      , [ @hints = ] { N'OPTION ( query_hint [ ,...n ] )' | NULL }

引数

[ @name= ] N'plan_guide_name'
プランガイドを識別する名前を指定します。プランガイド名は現在のデータベースに対して有効です。plan_guide_name は識別子の規則に従う必要があり、番号記号 (#) で始めることはできません。

[ @stmt= ] N'statement_text'
プランガイドを作成する対象の Transact-SQL ステートメントです。SQL Server クエリオプティマイザが statement_text に適合するクエリを認識すると、plan_guide_name が有効になります。プランガイドの作成を成功させるには、statement_text が、@type、@module_or_batch、および @params パラメータで指定されるコンテキストに存在する必要があります。

statement_text は、SQL Server が @module_or_batch および @params で識別されるバッチまたはモジュール内の対応するステートメントと照合できるように指定する必要があります。statement_text が標準の内部形式に変換されてから、SQL Server でこの照合が行われます (空白、コメント、およびキーワードの大文字小文字は考慮されません)。詳細については、「解説」を参照してください。statement_text のサイズは、サーバーで使用可能なメモリの最大値までに制限されます。

[ @type = ] N'{ OBJECT | SQL | TEMPLATE }'
statement_text が存在するエンティティの種類です。statement_text と plan_guide_name を照合するコンテキストを指定します。
- OBJECT
  statement_text が現在のデータベースの Transact-SQL ストアドプロシージャ、スカラ関数、複数ステートメントのテーブル値関数、または Transact-SQL DML トリガのコンテキストに存在することを示します。
- SQL
  statement_text が、任意のメカニズムを通じて SQL Server に送信できるスタンドアロンのステートメントまたはバッチに存在することを示します。共通言語ランタイム (CLR) オブジェクトまたは拡張ストアドプロシージャ、あるいは EXEC N'sql_string' を使用して送信される Transact-SQL ステートメントは、サーバー上のバッチとして処理されるため、@type ='SQL' として指定する必要があります。SQL を指定した場合は、@hints パラメータで PARAMETERIZATION { FORCED | SIMPLE } クエリヒントを指定できません。
- TEMPLATE
  プランガイドが、statement_text で示されたフォームにパラメータ化されるクエリに対して適用されることを示します。TEMPLATE を指定した場合は、@hints パラメータで PARAMETERIZATION { FORCED | SIMPLE } クエリヒントのみ指定できます。TEMPLATE プランガイドの詳細については、「プランガイドを使用したクエリのパラメータ化動作の指定」を参照してください。

[ @module_or_batch = ] { N'[ schema_name**.] object_name'** | N'batch_text' | NULL }
statement_text が存在するオブジェクト、または statement_text が存在するバッチテキストの名前を指定します。バッチテキストには、USE database ステートメントを含めることはできません。

プランガイドをアプリケーションから送信されたバッチに適合させるためには、batch_text を、SQL Server に送信するときと同じ形式で、同じ文字で指定する必要があります。この適合を容易にするために内部変換は実行されません。詳細については、「解説」を参照してください。

[schema_name.]object_name は、statement_text を含む Transact-SQL ストアドプロシージャ、スカラ関数、複数ステートメントのテーブル値関数、または Transact-SQL DML トリガの名前を指定します。schema_name を指定しないと、schema_name には現在のユーザーのスキーマが使用されます。NULL が指定され、@type='SQL' である場合、@module_or_batch の値は、@stmt の値に設定されます。@type='TEMPLATE' の場合は、@module_or_batch を NULL にする必要があります。

[ @params = ] { N'@parameter_name data_type [ ,...n ]' | NULL }
statement_text に埋め込まれるすべてのパラメータの定義を指定します。@params は、次の条件のいずれかに該当する場合にのみ適用されます。
- @type='SQL' または 'TEMPLATE' の場合。'TEMPLATE' の場合は、@params は NULL にできません。
- statement_text が sp_executesql を使用して送信され、@params パラメータの値が指定されている場合、または SQL Server が内部でステートメントをパラメータ化した後に送信する場合。データベース API (ODBC、OLE DB、ADO.NET など) からのパラメータ化クエリの送信は、sp_executesql または API サーバーカーソルルーチンの呼び出しとして SQL Server に示されるため、SQL または TEMPLATE プランガイドでも適合させることができます。パラメータ化およびプランガイドの詳細については、「SQL Server がプランガイドをクエリに照合するプロセス」を参照してください。
@parameter_name data_type は、SQL Server に sp_executesql を使用して送信するか、パラメータ化してから内部的に送信するときとまったく同じ形式で指定する必要があります。詳細については、「解説」を参照してください。バッチにパラメータが含まれていない場合は、NULL を指定する必要があります。@params のサイズは、サーバーの使用可能なメモリによってのみ制限されます。

[@hints = ] { **N'**OPTION (query_hint [ ,...n ] )' | NULL }
@stmt に適合するクエリにアタッチする OPTION 句を指定します。@hints は、SELECT ステートメントの OPTION 句と構文的に同じにする必要があります。また、有効な一連のクエリヒントを含めることができます。NULL は、OPTION 句がないことを示します。詳細については、「OPTION 句 (Transact-SQL)」を参照してください。

解説

sp_create_plan_guide の引数は、表示される順序で指定する必要があります。sp_create_plan_guide のパラメータに値を指定する場合、パラメータ名はすべて明示的に指定するか、すべて指定しないかのいずれかにする必要があります。たとえば、@name = を指定する場合は、@stmt =、@type = なども指定する必要があります。同様に、@name = を省略してパラメータ値だけを指定する場合は、その他のパラメータ名も省略し、値だけを指定する必要があります。引数の名前は、構文を理解しやすくするための説明目的のものです。SQL Server では、指定したパラメータ名と、その名前が使用されている位置にあるパラメータの名前が一致しているかどうかは確認されません。

特定の @module_or_batch および @stmt の組み合わせに対して作成できるのは 1 つのプランガイドのみです。

@module_or_batch 値で参照するストアドプロシージャ、関数、または DML トリガが、WITH ENCRYPTION 句を指定するものであるか一時的なものである場合、この値に対して OBJECT タイプのプランガイドは作成できません。

プランガイドで参照される関数、ストアドプロシージャ、または DML トリガを削除または変更しようとすると、有効かどうかにかかわらず、エラーが発生します。プランガイドで参照され、トリガが定義されているテーブルを削除しようとする場合もエラーが発生します。

メモ :
プランガイドを使用できるのは SQL Server Standard、Developer、Evaluation、および Enterprise Edition だけですが、プランガイドはどのエディションでも表示できます。また、プランガイドを含むデータベースは、どのエディションに対してもアタッチできます。アップグレード済みのバージョンの SQL Server 2008 にデータベースを復元またはアタッチした場合、プランガイドはまったく影響を受けません。サーバーのアップグレード後に、各データベース内のプランガイドが適切かどうかを確認する必要があります。

プランガイドを使用できるのは SQL Server Standard、Developer、Evaluation、および Enterprise Edition だけですが、プランガイドはどのエディションでも表示できます。また、プランガイドを含むデータベースは、どのエディションに対してもアタッチできます。アップグレード済みのバージョンの SQL Server 2008 にデータベースを復元またはアタッチした場合、プランガイドはまったく影響を受けません。サーバーのアップグレード後に、各データベース内のプランガイドが適切かどうかを確認する必要があります。

プランガイドの照合要件

@type='SQL' または @type='TEMPLATE' を指定するプランガイドをクエリに正しく適合させるためには、batch_text および @parameter_name data_type [,...n ] の値を、アプリケーションで送信される対応の値と完全に同じ形式で指定する必要があります。つまり、バッチテキストを、SQL Server コンパイラが受信したときとまったく同じように指定する必要があります。実際のバッチおよびパラメータテキストをキャプチャするには、SQL Server Profiler を使用する必要があります。詳細については、「SQL Server Profiler を使用したプランガイドの作成とテスト」を参照してください。

@type = 'SQL' で、@module_or_batch が NULL に設定されている場合、@module_or_batch の値は、@stmt の値に設定されます。つまり、statement_text の値を、SQL Server に送信するときとまったく同じ形式で、同じ文字で指定する必要があります。この適合を容易にするために内部変換は実行されません。

SQL Server が statement_text の値を batch_text および @parameter_name data_type [,...n ] と照合するとき、または @type='OBJECT' を object_name 内の対応するクエリのテキストと照合するときは、次の文字列要素は考慮されません。

文字列内の空白文字 (タブ、スペース、復帰、改行)
コメント (-- または /* */)
末尾のセミコロン

たとえば、SQL Server は、statement_text 文字列 N'SELECT * FROM T WHERE a = 10' を次の batch_text に一致させることができます。

N'SELECT *

FROM T

WHERE a=10'

ただし、この文字列は次の batch_text には一致しません。

N'SELECT * FROM T WHERE b = 10'

SQL Server は、最初のクエリ内にある復帰、改行、空白文字を無視します。2 つ目のクエリのシーケンス WHERE b = 10 は、WHERE a = 10 とは異なるものと解釈されます。照合処理では、大文字小文字が区別されないキーワードを除き、(データベースの照合順序で大文字小文字が区別されない場合でも) 大文字小文字およびアクセントが区別されます。キーワードの省略形は区別されません。たとえば、キーワード EXECUTE、EXEC、および execute は同じものと解釈されます。

プランガイドをクエリに適合させる方法の詳細については、「プランガイドを使用した配置済みアプリケーションのクエリの最適化」を参照してください。

プランキャッシュに対するプランガイドの効果

モジュールでプランガイドを作成すると、そのモジュールのクエリプランがプランキャッシュから削除されます。バッチで OBJECT 型または SQL 型のプランガイドを作成すると、同じハッシュ値を持つバッチのクエリプランが削除されます。TEMPLATE 型のプランガイドを作成すると、そのデータベース内のプランキャッシュから単一ステートメントのすべてのバッチが削除されます。

権限

@type='OBJECT' を指定して OBJECT タイプのプランガイドを作成するには、参照するオブジェクトに対する ALTER 権限が必要です。SQL または TEMPLATE タイプのプランガイドを作成するには、現在のデータベースに対する ALTER 権限が必要です。

例

A. ストアドプロシージャのクエリに対する OBJECT タイプのプランガイドを作成する

次の例では、アプリケーションベースのストアドプロシージャのコンテキストで実行されるクエリに適合するプランガイドを作成し、OPTIMIZE FOR ヒントをクエリに適用します。

ストアドプロシージャは次のようになります。

IF OBJECT_ID(N'Sales.GetSalesOrderByCountry', N'P') IS NOT NULL
    DROP PROCEDURE Sales.GetSalesOrderByCountry;
GO
CREATE PROCEDURE Sales.GetSalesOrderByCountry 
    (@Country nvarchar(60))
AS
BEGIN
    SELECT *
    FROM Sales.SalesOrderHeader AS h 
    INNER JOIN Sales.Customer AS c ON h.CustomerID = c.CustomerID
    INNER JOIN Sales.SalesTerritory AS t 
        ON c.TerritoryID = t.TerritoryID
    WHERE t.CountryRegionCode = @Country;
END
GO

ストアドプロシージャのクエリに対して作成されるプランガイドは次のようになります。

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

B. スタンドアロンクエリに対する SQL タイプのプランガイドを作成する

次の例では、sp_executesql システムストアドプロシージャを使用するアプリケーションで送信されるバッチ内のクエリに適合するプランガイドを作成します。

バッチは、次のようになります。

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

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

EXEC sp_create_plan_guide 
    @name = N'Guide1', 
    @stmt = N'SELECT TOP 1 * 
              FROM Sales.SalesOrderHeader 
              ORDER BY OrderDate DESC', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (MAXDOP 1)';

C. パラメータ化形式のクエリに対する TEMPLATE タイプのプランガイドを作成する

次の例では、指定されたフォームにパラメータ化されるクエリに適合するプランガイドを作成し、SQL Server に対してクエリのパラメータ化を強制的に実行させます。次の 2 つのクエリは構文的には同じですが、定数リテラル値のみが異なります。

SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;

SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45640;

パラメータ化形式のクエリに対するプランガイドは次のようになります。

EXEC sp_create_plan_guide 
    @name = N'TemplateGuide1',
    @stmt = N'SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
              INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d 
                  ON h.SalesOrderID = d.SalesOrderID
              WHERE h.SalesOrderID = @0',
    @type = N'TEMPLATE',
    @module_or_batch = NULL,
    @params = N'@0 int',
    @hints = N'OPTION(PARAMETERIZATION FORCED)';

この例では、@stmt パラメータの値は、パラメータ化形式のクエリになっています。この値を取得して sp_create_plan_guide で使用できるようにするには、sp_get_query_template システムストアドプロシージャを使用するのが唯一信頼できる方法です。次のスクリプトを使用すると、パラメータ化クエリを取得してそのクエリに対してプランガイドを作成することができます。

DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template 
    N'SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
      INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d 
          ON h.SalesOrderID = d.SalesOrderID
      WHERE h.SalesOrderID = 45639;',
    @stmt OUTPUT, 
    @params OUTPUT
EXEC sp_create_plan_guide N'TemplateGuide1', 
    @stmt, 
    N'TEMPLATE', 
    NULL, 
    @params, 
    N'OPTION(PARAMETERIZATION FORCED)';

重要 :
sp_get_query_template に渡される `@stmt` パラメータの定数リテラルの値は、リテラルを置き換えるパラメータで選択されるデータ型に影響する場合があります。この値は、プランガイドの適合にも影響します。場合によっては、異なるパラメータ値範囲に対応する複数のプランガイドを作成する必要があります。

重要 :

sp_get_query_template に渡される @stmt パラメータの定数リテラルの値は、リテラルを置き換えるパラメータで選択されるデータ型に影響する場合があります。この値は、プランガイドの適合にも影響します。場合によっては、異なるパラメータ値範囲に対応する複数のプランガイドを作成する必要があります。

パラメータ化形式のクエリを取得して TEMPLATE ベースのプランガイドで使用する方法の詳細については、「パラメータ化クエリのプランガイドの設計」を参照してください。

D. API カーソル要求を使用して送信されるクエリでプランガイドを作成する

プランガイドは、API サーバーカーソルルーチンから送信されるクエリを適合できます。これらのルーチンには、sp_cursorprepare、sp_cursorprepexec、および sp_cursoropen があります。ADO、OLE DB、および ODBC API を使用するアプリケーションは、API サーバーカーソルを使用して SQL Server と頻繁に対話します。詳細については、「API サーバーカーソル」を参照してください。RPC:Starting プロファイルトレースイベントを表示して、SQL Server Profiler トレース内の API サーバーカーソルルーチンの呼び出しを参照できます。

たとえば、次のデータが、プランガイドに合わせて調整するクエリの RPC:Starting プロファイルトレースイベントに示されているものとします。

DECLARE @p1 int;
SET @p1=-1;
DECLARE @p2 int;
SET @p2=0;
DECLARE @p5 int;
SET @p5=4104;
DECLARE @p6 int;
SET @p6=8193;
DECLARE @p7 int;
SET @p7=0;
EXEC sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(255),@P2 varchar(255)',N'SELECT * FROM Sales.SalesOrderHeader h INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID WHERE h.OrderDate BETWEEN @P1 AND @P2',@p5 OUTPUT,@p6 OUTPUT,@p7 OUTPUT,'20040101','20050101'
SELECT @p1, @p2, @p5, @p6, @p7;

このデータを見ると、sp_cursorprepexec の呼び出しの SELECT クエリに対するプランでマージ結合を使用していることがわかりますが、ハッシュ結合を使用するとします。sp_cursorprepexec を使用して送信されるクエリは、クエリ文字列およびパラメータ文字列の両方を含めて、パラメータ化されます。sp_cursorprepexec の呼び出しで、表示されているとおり完全に同じであるクエリ文字列とパラメータ文字列を使用して、次のプランガイドを作成し、プランの選択を変更できます。

EXEC sp_create_plan_guide 
    @name = N'APICursorGuide',
    @stmt = N'SELECT * FROM Sales.SalesOrderHeader AS h 
              INNER JOIN Sales.SalesOrderDetail AS d 
                ON h.SalesOrderID = d.SalesOrderID 
              WHERE h.OrderDate BETWEEN @P1 AND @P2',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = N'@P1 varchar(255),@P2 varchar(255)',
    @hints = N'OPTION(HASH JOIN)';

今後アプリケーションでこのクエリを実行すると、ここで作成したプランガイドの影響を受け、クエリの処理にはハッシュ結合が使用されます。

カーソルで送信されるクエリに対するプランガイドでの USE PLAN クエリヒントの使用方法の詳細については、「カーソルを使用したクエリでの USE PLAN クエリヒントの使用」を参照してください。

参照

ヘルプおよび情報

SQL Server 2005 の参考資料の入手

変更履歴

リリース	履歴
2005 年 12 月 5 日	新しい内容 SQL プランガイドに PARAMETERIZATION { FORCED \| SIMPLE } クエリヒントを指定できないことを追加。「解説」セクションで、構文の順序と一貫性に関するガイドラインを明記。変更内容 OBJECT プランガイドで、暗号化されたオブジェクトや一時オブジェクトを参照できないことを明記。
2007 年 9 月 15 日	変更内容 : @type = 'SQL' で、@module_or_batch が NULL に設定されている場合の、statement_text に対するプランガイド照合要件を明記しました。プランガイドの作成がプランキャッシュに及ぼす影響についての情報を追加しました。

2005 年 12 月 5 日

新しい内容

SQL プランガイドに PARAMETERIZATION { FORCED | SIMPLE } クエリヒントを指定できないことを追加。
「解説」セクションで、構文の順序と一貫性に関するガイドラインを明記。

変更内容

OBJECT プランガイドで、暗号化されたオブジェクトや一時オブジェクトを参照できないことを明記。

2007 年 9 月 15 日

変更内容 :

@type = 'SQL' で、@module_or_batch が NULL に設定されている場合の、statement_text に対するプランガイド照合要件を明記しました。
プランガイドの作成がプランキャッシュに及ぼす影響についての情報を追加しました。

sp_create_plan_guide (Transact-SQL)

構文

引数

解説

プランガイドの照合要件

プランキャッシュに対するプランガイドの効果

権限

例

A. ストアドプロシージャのクエリに対する OBJECT タイプのプランガイドを作成する

B. スタンドアロンクエリに対する SQL タイプのプランガイドを作成する

C. パラメータ化形式のクエリに対する TEMPLATE タイプのプランガイドを作成する

D. API カーソル要求を使用して送信されるクエリでプランガイドを作成する

参照

関連項目

ヘルプおよび情報

変更履歴

その他のリソース

sp_create_plan_guide (Transact-SQL)

構文

引数

解説

プラン ガイドの照合要件

プラン キャッシュに対するプラン ガイドの効果

権限

例

A. ストアド プロシージャのクエリに対する OBJECT タイプのプラン ガイドを作成する

B. スタンドアロン クエリに対する SQL タイプのプラン ガイドを作成する

C. パラメータ化形式のクエリに対する TEMPLATE タイプのプラン ガイドを作成する

D. API カーソル要求を使用して送信されるクエリでプラン ガイドを作成する

参照

関連項目

ヘルプおよび情報

変更履歴

その他のリソース

プランガイドの照合要件

プランキャッシュに対するプランガイドの効果

A. ストアドプロシージャのクエリに対する OBJECT タイプのプランガイドを作成する

B. スタンドアロンクエリに対する SQL タイプのプランガイドを作成する

C. パラメータ化形式のクエリに対する TEMPLATE タイプのプランガイドを作成する

D. API カーソル要求を使用して送信されるクエリでプランガイドを作成する