次の方法で共有

プラン適用シナリオ : 書き直されたクエリから取得したプランを適用するためのプラン ガイドの作成

  • [アーティクル]

多くの場合、クエリのプランを向上する最も簡単な方法は、クエリの論理的な意味を変更しないで、クエリ ヒントを使用することにより、結合順序、結合アルゴリズム、インデックスの使用方法をなどを適用するように、手動でクエリを書き直すことです。ただし、クエリが配置済みアプリケーション内に含まれていると、この方法を使用できないことがあります。このような状況では、プラン ガイドを USE PLAN クエリ ヒントと共に使用すると役立つことがあります。プラン ガイドは、クエリのテキストを直接変更できない場合や、変更することが望ましくない場合に、クエリ ヒントをクエリにアタッチすることで機能します。詳細については、「プラン ガイドを使用した配置済みアプリケーションのクエリの最適化」を参照してください。

手動でクエリを書き直し、そのクエリのプランをキャプチャしてから、USE PLAN ヒントが含まれるプラン ガイドを使用してキャプチャしたプランを元のクエリに適用するには、次の手順を実行します。

  1. クエリの論理的な意味を変更しないで適切なプランが作成されるように、結合順序を変更したり、FORCE ORDER、結合ヒント、インデックス ヒント、およびその他の技法を使用することによって、クエリの変更内容を決定します。
  2. 書き直したクエリのプランをキャプチャします。このとき、sp_executesql や sp_cursorprepexec を使用したり、スタンドアロン バッチとしてなど、プランが元のクエリの場合と同様の方法で実行されるようにします。
  3. キャプチャしたプランが含まれる OPTION (USE PLAN) クエリ ヒント句をアタッチすることにより元のクエリのコピーを変更し、キャプチャしたプランをそのクエリに適用できるかどうかをテストします。
  4. テストに失敗した場合は、他に書き直したクエリをテストするか、元のクエリに適用できる適切なプランが取得されるまでデバッグを実行します。
  5. プラン ガイドを作成し、取得した適切なプランを元のクエリに適用します。

例

次のクエリにより生成されたクエリ プランの実行速度が遅すぎるとします。

USE AdventureWorks;
GO
SET STATISTICS XML ON;
GO
EXEC sp_executesql 
@stmt = N'SELECT 
    soh.[SalesPersonID]
    ,c.[FirstName] + '' '' + COALESCE(c.[MiddleName], '''') + '' '' + c.[LastName] AS [FullName]
    ,e.[Title]
    ,st.[Name] AS [SalesTerritory]
    ,soh.[SubTotal]
    ,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] 
FROM [Sales].[SalesPerson] sp 
    INNER JOIN [Sales].[SalesOrderHeader] soh 
    ON sp.[SalesPersonID] = soh.[SalesPersonID]
    INNER JOIN [Sales].[SalesTerritory] st 
    ON sp.[TerritoryID] = st.[TerritoryID] 
    INNER JOIN [HumanResources].[Employee] e 
    ON soh.[SalesPersonID] = e.[EmployeeID] 
    INNER JOIN [Person].[Contact] c 
    ON e.[ContactID] = c.ContactID
WHERE st.[Group] = @p1', 
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO
SET STATISTICS XML OFF;
GO

次のように、論理的な意味を変更しないで、適用する結合順序が変更されるように、このクエリを書き直すことができます。

USE AdventureWorks;
GO
SET STATISTICS XML ON;
GO
EXEC sp_executesql 
@stmt = N'SELECT 
    soh.[SalesPersonID]
    ,c.[FirstName] + '' '' + COALESCE(c.[MiddleName], '''') + '' '' + c.[LastName] AS [FullName]
    ,e.[Title]
    ,st.[Name] AS [SalesTerritory]
    ,soh.[SubTotal]
    ,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] 
FROM [Sales].[SalesPerson] sp 
    INNER JOIN [Sales].[SalesTerritory] st -- Moved this join earlier 
    ON sp.[TerritoryID] = st.[TerritoryID] 
    INNER JOIN [Sales].[SalesOrderHeader] soh 
    ON sp.[SalesPersonID] = soh.[SalesPersonID]
    INNER JOIN [HumanResources].[Employee] e 
    ON soh.[SalesPersonID] = e.[EmployeeID] 
    INNER JOIN [Person].[Contact] c 
    ON e.[ContactID] = c.ContactID
WHERE st.[Group] = @p1
OPTION (FORCE ORDER)',  -- force join order to be as specified in FROM list
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO
SET STATISTICS XML OFF
GO

書き直したクエリの STATISTICS XML クエリ プランをキャプチャし、そのプランを元のクエリでテストした後、次のコードに示すように、そのプランを元のクエリに適用するプラン ガイドを作成します。

EXEC sp_create_plan_guide
@name = N'ForceOrderGuide',
@stmt = N'SELECT 
    soh.[SalesPersonID]
    ,c.[FirstName] + '' '' + COALESCE(c.[MiddleName], '''') + '' '' + c.[LastName] AS [FullName]
    ,e.[Title]
    ,st.[Name] AS [SalesTerritory]
    ,soh.[SubTotal]
    ,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] 
FROM [Sales].[SalesPerson] sp 
    INNER JOIN [Sales].[SalesOrderHeader] soh 
    ON sp.[SalesPersonID] = soh.[SalesPersonID]
    INNER JOIN [Sales].[SalesTerritory] st 
    ON sp.[TerritoryID] = st.[TerritoryID] 
    INNER JOIN [HumanResources].[Employee] e 
    ON soh.[SalesPersonID] = e.[EmployeeID] 
    INNER JOIN [Person].[Contact] c 
    ON e.[ContactID] = c.ContactID
WHERE st.[Group] = @p1', 
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@p1 nvarchar(80)',
@hints = N'OPTION (USE PLAN 
N''… put XML showplan for modified query here …'')'

@hints 文字列内のクエリ プランを置換する前に、4 つの単一引用符 ('''') を使用して、XML クエリ プラン内のすべての単一引用符 (') をエスケープするようにしてください。これは、クエリ プランが 2 つの文字列リテラルの中に入れ子になっているためです。

参照

処理手順

プラン適用シナリオ : USE PLAN クエリ ヒントを使用するプラン ガイドの作成

概念

プラン強制シナリオと例
プラン強制の使用によるクエリ プランの指定

その他の技術情報

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

ヘルプおよび情報

SQL Server 2005 の参考資料の入手