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

  • [アーティクル]

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

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

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

参照

概念

プラン ガイドを使用した配置済みアプリケーションのクエリの最適化

その他の技術情報

クエリ パフォーマンス
sp_create_plan_guide (Transact-SQL)

ヘルプおよび情報

SQL Server 2005 の参考資料の入手