sp_create_plan_guide (Transact-SQL)

[アーティクル]
07/17/2024

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

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

構文

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_create_plan_guide (Transact-SQL)

構文

引数

[ @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' }

解説

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

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

アクセス許可

例

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

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

C: クエリのパラメーター化された形式の TEMPLATE 型のプランガイドを作成する

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

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

フィードバック

フィードバック

その他のリソース

次の方法で共有

sp_create_plan_guide (Transact-SQL)

構文

引数

[ @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' }

解説

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

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

アクセス許可

例

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

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

C: クエリのパラメーター化された形式の TEMPLATE 型のプラン ガイドを作成する

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

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

関連するコンテンツ

フィードバック

フィードバック

その他のリソース

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

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

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

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

C: クエリのパラメーター化された形式の TEMPLATE 型のプランガイドを作成する

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

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