• [アーティクル]

プラン ガイドを使用したクエリのパラメータ化動作の指定

PARAMETERIZATION データベース オプションが SIMPLE に設定されている場合、SQL Server クエリ オプティマイザはクエリのパラメータ化を選択することがあります。これは、クエリに含まれるリテラル値がすべてパラメータに置き換えられることを意味します。この処理を簡易パラメータ化と呼びます。簡易パラメータ化が有効であれば、クエリのパラメータ化を行う場合と行わない場合を制御することはできません。ただし、PARAMETERIZATION データベース オプションを FORCED に設定することにより、データベース内のすべてのクエリをパラメータ化するように指定できます。この処理を強制パラメータ化と呼びます。

次のような方法でプラン ガイドを使用すると、データベースのパラメータ化の動作を上書きできます。

  • PARAMETERIZATION データベース オプションが SIMPLE に設定されている場合、ある種のクエリについては強制パラメータ化を行うように指定できます。これには、パラメータ化された形式のクエリの TEMPLATE プラン ガイドを作成し、sp_create_plan_guide ストアド プロシージャに PARAMETERIZATION FORCED クエリ ヒントを指定します。このようなプラン ガイドは、すべてのクエリではなく、ある種のクエリにのみパラメータ化を強制する方法と考えることができます。

  • PARAMETERIZATION データベース オプションが FORCED に設定されている場合、ある種のクエリについては、強制パラメータ化ではなく簡易パラメータ化だけを行うように指定できます。これには、強制パラメータ化された形式のクエリの TEMPLATE プラン ガイドを作成し、sp_create_plan_guide に PARAMETERIZATION SIMPLE クエリ ヒントを指定します。

AdventureWorks2008R2 データベースを対象とした次のクエリについて考えてみましょう。

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;

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;

ここでは、データベース管理者が、データベースのすべてのクエリにパラメータ化を強制しないことに決定しました。ただし、前のクエリと定数リテラル値だけが異なり、構文は同じクエリすべてにコンパイル コストが発生するのは避けたいと考えています。つまり、クエリをパラメータ化し、この種のクエリのクエリ プランを再利用できるように考えています。このような場合は、次の手順を実行します。

  1. パラメータ化された形式のクエリを取得します。sp_create_plan_guide で使用するためにこの値を安全に取得する唯一の方法は、sp_get_query_template システム ストアド プロシージャを使う方法です。

  2. パラメータ化された形式のクエリのプラン ガイドを作成し、PARAMETERIZATION FORCED クエリ ヒントを指定します。

    重要な注意事項重要

    SQL Server はクエリのパラメータ化処理の一環として、リテラルの値とサイズに従って、リテラル値を置き換えるパラメータにデータ型を割り当てます。sp_get_query_template の @stmt 出力パラメータに定数リテラルの値が渡される場合も、これと同じ処理が行われます。sp_create_plan_guide の @params 引数に指定されたデータ型は、SQL Server がパラメータ化を行う場合にクエリのデータ型と一致する必要があるので、クエリのパラメータ値としてとり得る値すべてに対応できるように、複数のプラン ガイドを作成しなければならない可能性があります。クエリのパラメータ化後に SQL Server がパラメータに割り当てるデータ型の詳細については、「強制パラメータ化」を参照してください。

次のスクリプトを使用すると、パラメータ化されたクエリの取得と、このクエリのプラン ガイドの作成の両方の処理を行えます。

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)';

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)';

同様に、強制パラメータ化が既に有効になっているデータベースでは、サンプルのクエリや、構文が同じでも定数リテラル値が異なるその他のクエリが、簡易パラメータ化のルールに従ってパラメータ化されるようにすることができます。この場合は、OPTION 句に PARAMETERIZATION FORCED ではなく PARAMETERIZATION SIMPLE を指定します。

注意注

TEMPLATE プラン ガイドは、ステートメントと、単一のステートメントのみで構成されるバッチにより送信されるクエリとを対応付けます。複数のステートメントで構成されるバッチ内のステートメントは、TEMPLATE プラン ガイドで対応付けできません。