プラン ガイドを使用したクエリのパラメーター化動作の指定Specify Query Parameterization Behavior by Using Plan Guides

適用対象: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

PARAMETERIZATION データベース オプションが SIMPLE に設定されている場合、 SQL ServerSQL Server クエリ オプティマイザーはクエリのパラメーター化を選択することがあります。When the PARAMETERIZATION database option is set to SIMPLE, the SQL ServerSQL Server query optimizer may choose to parameterize the queries. これは、クエリに含まれるリテラル値がすべてパラメーターに置き換えられることを意味します。This means that any literal values that are contained in a query are substituted with parameters. この処理を簡易パラメーター化と呼びます。This process is referred to as simple parameterization. 簡易パラメーター化が有効であれば、クエリのパラメーター化を行う場合と行わない場合を制御することはできません。When SIMPLE parameterization is in effect, you cannot control which queries are parameterized and which queries are not. ただし、PARAMETERIZATION データベース オプションを FORCED に設定することにより、データベース内のすべてのクエリをパラメーター化するように指定できます。However, you can specify that all queries in a database be parameterized by setting the PARAMETERIZATION database option to FORCED. この処理を強制パラメーター化と呼びます。This process is referred to as forced parameterization.

次のような方法でプラン ガイドを使用すると、データベースのパラメーター化の動作をオーバーライドできます。You can override the parameterization behavior of a database by using plan guides in the following ways:

  • PARAMETERIZATION データベース オプションが SIMPLE に設定されている場合、ある種のクエリについては強制パラメーター化を行うように指定できます。When the PARAMETERIZATION database option is set to SIMPLE, you can specify that forced parameterization is attempted on a certain class of queries. これには、パラメーター化された形式のクエリの TEMPLATE プラン ガイドを作成し、 sp_create_plan_guide ストアド プロシージャに PARAMETERIZATION FORCED クエリ ヒントを指定します。You do this by creating a TEMPLATE plan guide on the parameterized form of the query, and specifying the PARAMETERIZATION FORCED query hint in the sp_create_plan_guide stored procedure. このようなプラン ガイドは、すべてのクエリではなく、ある種のクエリにのみパラメーター化を強制する方法と考えることができます。You can consider this kind of plan guide as a way to enable forced parameterization only on a certain class of queries, instead of all queries.

  • PARAMETERIZATION データベース オプションが FORCED に設定されている場合、ある種のクエリについては、強制パラメーター化ではなく簡易パラメーター化だけを行うように指定できます。When the PARAMETERIZATION database option is set to FORCED, you can specify that for a certain class of queries, only simple parameterization is attempted, not forced parameterization. これには、強制パラメーター化された形式のクエリの TEMPLATE プラン ガイドを作成し、 sp_create_plan_guideに PARAMETERIZATION SIMPLE クエリ ヒントを指定します。You do this by creating a TEMPLATE plan guide on the force-parameterized form of the query, and specifying the PARAMETERIZATION SIMPLE query hint in sp_create_plan_guide.

AdventureWorks2012AdventureWorks2012 データベースを対象とした次のクエリについて考えてみましょう。Consider the following query on the AdventureWorks2012AdventureWorks2012 database:

SELECT pi.ProductID, SUM(pi.Quantity) AS Total  
FROM Production.ProductModel AS pm   
    INNER JOIN Production.ProductInventory AS pi   
        ON pm.ProductModelID = pi.ProductID   
WHERE pi.ProductID = 101   
GROUP BY pi.ProductID, pi.Quantity HAVING SUM(pi.Quantity) > 50;  

ここでは、データベース管理者が、データベースのすべてのクエリにパラメーター化を強制しないことに決定しました。As a database administrator, you have determined that you do not want to enable forced parameterization on all queries in the database. ただし、前のクエリと定数リテラル値だけが異なり、構文は同じクエリすべてにコンパイル コストが発生するのは避けたいと考えています。However, you do want to avoid compilation costs on all queries that are syntactically equivalent to the previous query, but differ only in their constant literal values. つまり、クエリをパラメーター化し、この種のクエリのクエリ プランを再利用できるように考えています。In other words, you want the query to be parameterized so that a query plan for this kind of query is reused. このような場合は、次の手順を実行します。In this case, complete the following steps:

  1. パラメーター化された形式のクエリを取得します。Retrieve the parameterized form of the query. sp_create_plan_guide で使用するためにこの値を安全に取得する唯一の方法は、 sp_get_query_template システム ストアド プロシージャを使う方法です。The only safe way to obtain this value for use in sp_create_plan_guide is by using the sp_get_query_template system stored procedure.

  2. パラメーター化された形式のクエリのプラン ガイドを作成し、PARAMETERIZATION FORCED クエリ ヒントを指定します。Create the plan guide on the parameterized form of the query, specifying the PARAMETERIZATION FORCED query hint.

    重要

    SQL ServerSQL Server はクエリのパラメーター化処理の一環として、リテラルの値とサイズに従って、リテラル値を置き換えるパラメーターにデータ型を割り当てます。As part of parameterizing a query, SQL ServerSQL Server assigns a data type to the parameters that replace the literal values, depending on the value and size of the literal. sp_get_query_template@stmt 出力パラメーターに定数リテラルの値が渡される場合も、これと同じ処理が行われます。The same process occurs to the value of the constant literals passed to the stmt output parameter of sp_get_query_template. sp_create_plan_guide@params 引数に指定されたデータ型は、SQL ServerSQL Server がパラメーター化を行う場合にクエリのデータ型と一致する必要があるので、クエリのパラメーター値としてとり得る値すべてに対応できるように、複数のプラン ガイドを作成しなければならない可能性があります。Because the data type specified in the params argument of sp_create_plan_guide must match that of the query as it is parameterized by , you may have to create more than one plan guide to cover the complete range of possible parameter values for the query.

次のスクリプトを使用すると、パラメーター化クエリの取得と、このクエリのプラン ガイドの作成の両方の処理を行えます。The following script can be used both to obtain the parameterized query and then create a plan guide on it:

DECLARE @stmt nvarchar(max);  
DECLARE @params nvarchar(max);  
EXEC sp_get_query_template   
    N'SELECT pi.ProductID, SUM(pi.Quantity) AS Total   
      FROM Production.ProductModel AS pm   
      INNER JOIN Production.ProductInventory AS pi ON pm.ProductModelID = pi.ProductID   
      WHERE pi.ProductID = 101   
      GROUP BY pi.ProductID, pi.Quantity   
      HAVING sum(pi.Quantity) > 50',  
    @stmt OUTPUT,   
    @params OUTPUT;  
EXEC sp_create_plan_guide   
    N'TemplateGuide1',   
    @stmt,   
    N'TEMPLATE',   
    NULL,   
    @params,   
    N'OPTION(PARAMETERIZATION FORCED)';  

同様に、強制パラメーター化が既に有効になっているデータベースでは、サンプルのクエリや、構文が同じでも定数リテラル値が異なるその他のクエリが、簡易パラメーター化のルールに従ってパラメーター化されるようにすることができます。Similarly, in a database in which forced parameterization is already enabled, you can make sure that the sample query, and others that are syntactically equivalent, except for their constant literal values, are parameterized according to the rules of simple parameterization. この場合は、OPTION 句に PARAMETERIZATION FORCED ではなく PARAMETERIZATION SIMPLE を指定します。To do this, specify PARAMETERIZATION SIMPLE instead of PARAMETERIZATION FORCED in the OPTION clause.

注意

TEMPLATE プラン ガイドは、ステートメントと、単一のステートメントのみで構成されるバッチにより送信されるクエリとを対応付けます。TEMPLATE plan guides match statements to queries submitted in batches that consist of a single statement only. 複数のステートメントで構成されるバッチ内のステートメントは、TEMPLATE プラン ガイドで対応付けできません。Statements inside multistatement batches are not eligible to be matched by TEMPLATE plan guides.