• [アーティクル]

パラメータ化クエリのプラン ガイドの設計

パラメータ化されたクエリに対してプラン ガイドを作成できます。クエリのパラメータ化は、次のいずれかの理由で行います。

  • sp_executesql を使用してクエリを送信する。

  • 強制パラメータ化がデータベースで有効になっている。これにより、すべての適格なクエリがパラメータ化されます。

  • このクエリが属しているクエリのクラスに別のプラン ガイドを作成し、パラメータ化を指定した。

パラメータ化クエリのプラン ガイドを作成するときは、すべてのクエリに対して基本的に同じ形式にパラメータ化するためのプラン ガイドを作成します。ただし、定数リテラル値だけは異なります。たとえば、強制パラメータ化が有効になっているデータベースでは、次の 2 つのクエリは同じ形式にパラメータ化されます。

SELECT pi.ProductID, SUM(pi.Quantity) AS Total 
FROM Production.ProductModel pm INNER JOIN Production.ProductInventory 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 pm INNER JOIN Production.ProductInventory 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 pm INNER JOIN Production.ProductInventory pi ON pm.ProductModelID = pi.ProductID 
WHERE pi.ProductID = 101 
GROUP BY pi.ProductID, pi.Quantity 
HAVING sum(pi.Quantity) > 100;

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

パラメータ化クエリのプラン ガイドを作成するには、SQL 型のプラン ガイドを作成します。その際、sp_create_plan_guide ストアド プロシージャにクエリのパラメータ化形式を指定します。

たとえば、上記の例のいずれかのクエリのパラメータ化形式を取得し、ハッシュ結合の使用をオプティマイザに強制するプラン ガイドを作成するには、次の手順を実行します。

  1. sp_get_query_template を実行して、クエリのパラメータ化形式を取得します。

  2. sp_executesql または PARAMETERIZATION FORCED データベース SET オプションを使用して、SQL Server でクエリがまだパラメータ化されていない場合は、パラメータ化を強制するために TEMPLATE 型のプラン ガイドを作成します。

  3. パラメータ化クエリの SQL 型のプラン ガイドを作成します。

次のバッチを使用すると、これらの 3 つのすべての手順を実行できます。

--Obtain the paramaterized form of the query:
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 pm 
    INNER JOIN Production.ProductInventory 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;
--Force parameterization of the query. (This step is only required
--if the query is not already being parameterized.)
EXEC sp_create_plan_guide N'TemplateGuide1', 
    @stmt, 
N'TEMPLATE', 
NULL, 
@params, 
N'OPTION(PARAMETERIZATION FORCED)';
--Create a plan guide on the parameterized query
EXEC sp_create_plan_guide N'GeneralizedGuide1', 
@stmt, 
N'SQL', 
NULL, 
@params, 
N'OPTION(HASH JOIN)';

--Obtain the paramaterized form of the query:
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 pm 
    INNER JOIN Production.ProductInventory 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;
--Force parameterization of the query. (This step is only required
--if the query is not already being parameterized.)
EXEC sp_create_plan_guide N'TemplateGuide1', 
    @stmt, 
N'TEMPLATE', 
NULL, 
@params, 
N'OPTION(PARAMETERIZATION FORCED)';
--Create a plan guide on the parameterized query
EXEC sp_create_plan_guide N'GeneralizedGuide1', 
@stmt, 
N'SQL', 
NULL, 
@params, 
N'OPTION(HASH JOIN)';

これでプラン ガイドは、指定した形式にパラメータ化されるすべてクエリに適用されますが、含まれる定数リテラル値は異なります。