sp_create_plan_guide (Transact-SQL)

適用対象: SQL Server Azure SQL データベース Azure SQL Managed Instance

クエリ ヒントまたは実際のクエリ プランをデータベース内のクエリに関連付するためのプラン ガイドを作成します。 プラン ガイドの詳細については、「 Plan Guides」を参照してください。

Transact-SQL 構文表記規則

構文

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

引数

[ @name = ] N'name'

プラン ガイドの名前。 @name は sysname で、既定値はなく、最大長は 124 文字です。 プラン ガイド名は現在のデータベースに対して有効です。 @name識別子の規則に従う必要があり番号記号 (#) で始めることはできません。

[ @stmt = ] N'stmt'

プラン ガイドを作成する Transact-SQL ステートメント。 @stmt は nvarchar(max) で、既定値は NULL です。 SQL Server クエリ オプティマイザーが @stmtに一致するクエリを認識すると、 @name が有効になります。 プラン ガイドの作成を成功させるには、@stmt@type、@module_or_batch、および@paramsパラメーターで指定されたコンテキストに表示される必要があります。

@stmt は、クエリ オプティマイザーが、 @module_or_batch および @paramsによって識別されるバッチまたはモジュール内で指定された、対応するステートメントと照合できるようにする方法で提供する必要があります。 詳細については、「解説」を参照してください。 @stmtのサイズは、サーバーの使用可能なメモリによってのみ制限されます。

[ @type = ] { N'OBJECT' |N'SQL' |N'TEMPLATE' }

@stmtが表示されるエンティティの種類。 これは、@nameに一致する@stmtのコンテキストを指定します。 @type は nvarchar(60)であり、次のいずれかの値を指定できます。

• OBJECT

  現在のデータベースの Transact-SQL ストアド プロシージャ、スカラー関数、複数ステートメントテーブル値関数、または Transact-SQL DML トリガーのコンテキストに表示される @stmt を示します。

• SQL

  @stmt、任意のメカニズムを介して SQL Server に送信できるスタンドアロン ステートメントまたはバッチのコンテキストに表示されることを示します。 共通言語ランタイム (CLR) オブジェクトまたは拡張ストアド プロシージャによって送信された Transact-SQL ステートメント、または EXEC N'<sql_string>' を使用して送信された Transact-SQL ステートメントは、サーバー上でバッチとして処理されるため、SQLの@typeとして識別する必要があります。 SQLを指定した場合、クエリ ヒントPARAMETERIZATION { FORCED | SIMPLE }を @hints パラメーターで指定することはできません。

• TEMPLATE

  プラン ガイドが、@stmtで示されているフォームにパラメーター化されるすべてのクエリに適用されることを示します。TEMPLATEを指定した場合は、@hints パラメーターにPARAMETERIZATION { FORCED | SIMPLE }クエリ ヒントのみを指定できます。 プラン ガイドTEMPLATE詳細については、「プラン ガイドを使用したクエリパラメーター化動作の指定を参照してください。

[ @module_or_batch = ] { N' [ schema_name。 ] object_name' |N'batch_text' }

@stmtが表示されるオブジェクトの名前、または@stmtが表示されるバッチ テキストを指定します。 @module_or_batch は nvarchar(max) で、既定値は NULL です。 バッチ テキストに USE <database> ステートメントを含めることはできません。

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

[ <schema_name>. ] <object_name> は、 @stmtを含む 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 ]'

@stmtに埋め込まれるすべてのパラメーターの定義を指定します。@paramsは nvarchar(max) で、既定値は NULL です。 @params は、次のいずれかのオプションに該当する場合にのみ適用されます。

• @type が SQL または TEMPLATE。 TEMPLATE場合は、@paramsをNULLすることはできません。

• @stmt は sp_executesql を使用して送信され、 @params パラメーターの値が指定されているか、SQL Server はパラメーター化後に内部的にステートメントを送信します。 データベース API (ODBC、OLE DB、ADO.NET を含む) からのパラメーター化されたクエリの送信は、 sp_executesql または API サーバー カーソル ルーチンの呼び出しとして SQL Server に表示されるため、 SQL または TEMPLATE プラン ガイドで照合することもできます。

@params は、 sp_executesql を使用して SQL Server に送信される形式とまったく同じ形式で指定するか、パラメーター化後に内部的に送信する必要があります。 詳細については、「解説」を参照してください。 バッチにパラメーターが含まれていない場合は、 NULL を指定する必要があります。 @paramsのサイズは、使用可能なサーバー メモリによってのみ制限されます。

[ @hints = ] { N'OPTION ( query_hint [ , ...n ] )' |N'XML_showplan' }

@hints は nvarchar(max) で、既定値は NULL です。

• OPTION ( <query_hint> [ , ...n ] )

  @stmtに一致するクエリにアタッチするOPTION句を指定します。@hints構文は、SELECT ステートメントのOPTION句と同じである必要があり、クエリ ヒントの有効なシーケンスを含めることができます。

• <XML_showplan>'

  ヒントとして適用される XML 形式のクエリ プラン。

  XML プラン表示を変数に割り当てることをお勧めします。 それ以外の場合は、プラン表示内の単一引用符の前に別の単一引用符を付けてエスケープする必要があります。 E の例参照してください。

• NULL

  クエリの OPTION 句で指定された既存のヒントがクエリに適用されていないことを示します。 詳細については、 OPTION 句を参照してください。

解説

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

同じクエリとバッチまたはモジュールに対して、複数の OBJECT または SQL プラン ガイドを作成できます。 ただし、有効にできるプラン ガイドは常に 1 つだけです。

OBJECT型のプラン ガイドは、WITH ENCRYPTION句を指定するストアド プロシージャ、関数、または DML トリガーを参照する@module_or_batch値に対して作成することはできません。

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

プラン ガイドは、SQL Server のすべてのエディションで使用できるわけではありません。 SQL Server の各エディションでサポートされる機能の一覧については、「SQL Server 2022 の各エディションとサポートされている機能」を参照してください。 プラン ガイドはどのエディションでも表示できます。 また、プラン ガイドを含むデータベースは、どのエディションに対してもアタッチできます。 アップグレード済みのバージョンの SQL Server にデータベースを復元またはアタッチした場合、プラン ガイドはまったく影響を受けません。 サーバーのアップグレードを実行した後、各データベースのプラン ガイドが望ましいかどうかを確認する必要があります。

プラン ガイドの要件の一致

クエリと正常に一致するようにSQLまたはTEMPLATEの@typeを指定するプラン ガイドの場合、@module_or_batchと@paramsの値 [, ...n ]は、アプリケーションによって送信された対応する形式とまったく同じ形式で提供する必要があります。 つまり、SQL Server コンパイラが受け取るとおりにバッチ テキストを指定する必要があります。 実際のバッチとパラメーター テキストをキャプチャするには、SQL Server プロファイラーを使用できます。 詳細については、「SQL Server プロファイラーを使用してプラン ガイドを作成およびテストするを参照してください。

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

SQL Server が @stmt の値と @module_or_batch の値と一致 @paramsし、[,...n ]、または @type が OBJECTされている場合、 <object_name>内の対応するクエリのテキストには、次の文字列要素は考慮されません。

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

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

 N'SELECT *
 FROM T
 WHERE a = 10'

ただし、同じ文字列はこの @module_or_batchと一致しません。

N'SELECT * FROM T WHERE b = 10'

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

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

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

アクセス許可

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_region 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_region;
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_region',
    @type = N'OBJECT',
    @module_or_batch = N'Sales.GetSalesOrderByCountry',
    @params = NULL,
    @hints = N'OPTION (OPTIMIZE FOR (@Country_region = 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 AdventureWorks2022.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2022.Sales.SalesOrderDetail AS d
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;

SELECT *
FROM AdventureWorks2022.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2022.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 AdventureWorks2022.Sales.SalesOrderHeader AS h
              INNER JOIN AdventureWorks2022.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 AdventureWorks2022.Sales.SalesOrderHeader AS h
      INNER JOIN AdventureWorks2022.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)';

重要

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

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

プラン ガイドは、API サーバー カーソル ルーチンから送信されたクエリと一致させることができます。 これらのルーチンには、 sp_cursorprepare、 sp_cursorprepexec、および sp_cursoropenが含まれます。 ADO、OLE DB、ODBC API を使用するアプリケーションは、API サーバー カーソルを使用して SQL Server と頻繁にやり取りします。 RPC:Starting プロファイラー トレース イベントを表示することで、SQL Server プロファイラー トレースで 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 AS h INNER JOIN 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;

このデータを見ると、SELECT の呼び出しの sp_cursorprepexec クエリに対するプランでマージ結合を使用していることがわかりますが、ハッシュ結合を使用するとします。 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)';

アプリケーションによるこのクエリの後続の実行は、このプラン ガイドの影響を受け、ハッシュ結合を使用してクエリを処理します。

E. キャッシュされたプランから XML プラン表示を取得してプラン ガイドを作成する

次の例では、単純なアドホック SQL ステートメントのプラン ガイドを作成します。 このステートメントに必要なクエリ プランは、プラン ガイドで、クエリの XML プラン表示を @hints パラメーターに直接指定することによって提供されます。 この例では、最初に SQL ステートメントを実行してプラン キャッシュにプランを生成します。 この例では、生成されたプランが目的のプランであり、それ以上のクエリ チューニングは必要ないと想定しています。 クエリの XML プラン表示は、 sys.dm_exec_query_stats、 sys.dm_exec_sql_text、および動的管理ビュー sys.dm_exec_text_query_plan クエリを実行することによって取得され、 @xml_showplan 変数に割り当てられます。 次に @xml_showplan 変数が、 sp_create_plan_guide パラメーターで @hints ステートメントに渡されます。 または、 sp_create_plan_guide_from_handle ストアド プロシージャを使用して、プラン キャッシュ内のクエリ プランからプラン ガイドを作成することもできます。

USE AdventureWorks2022;
GO

SELECT City,
    StateProvinceID,
    PostalCode
FROM Person.Address
ORDER BY PostalCode DESC;
GO

DECLARE @xml_showplan NVARCHAR(MAX);

SET @xml_showplan = (
    SELECT query_plan
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
    WHERE st.TEXT LIKE N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;%'
);

EXEC sp_create_plan_guide @name = N'Guide1_from_XML_showplan',
    @stmt = N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = NULL,
    @hints = @xml_showplan;
GO

関連するコンテンツ

• プラン ガイド
• sp_control_plan_guide (Transact-SQL)
• sys.plan_guides (Transact-SQL)
• データベース エンジン ストアド プロシージャ (Transact-SQL)
• システム ストアド プロシージャ (Transact-SQL)
• sys.dm_exec_sql_text (Transact-SQL)
• sys.dm_exec_cached_plans (Transact-SQL)
• sys.dm_exec_query_stats (Transact-SQL)
• sp_create_plan_guide_from_handle (Transact-SQL)
• sys.fn_validate_plan_guide (Transact-SQL)
• sp_get_query_template (Transact-SQL)