[アーティクル]
10/25/2012

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

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

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

クエリの論理的な意味を変更しないで適切なプランが作成されるように、クエリの変更内容を決定します (結合順序を変更したり、FORCE ORDER、結合ヒント、インデックスヒント、およびその他の技法を使用)。
書き直したクエリのプランをキャプチャします。このとき、sp_executesql や sp_cursorprepexec を使用したり、スタンドアロンバッチとしてなど、プランが元のクエリの場合と同様の方法で実行されるようにします。
プランガイドを作成し、取得した適切なプランを元のクエリに適用します。
SQL Server Profiler を使用して、Plan Guide Successful イベントおよび Plan Guide Unsuccessful イベントを Performance カテゴリから選択し、元のクエリを実行します。SQL Server Profiler を調べてクエリが該当のプランガイドを使用していることを確認します。

例

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

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

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

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

USE AdventureWorks2008R2;
GO
EXEC sp_executesql 
@stmt = N'SELECT
    soh.SalesPersonID
    ,p.FirstName + '' '' + COALESCE(p.MiddleName, '''') + '' '' + p.LastName AS FullName
    ,e.JobTitle
    ,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.BusinessEntityID = soh.SalesPersonID
    INNER JOIN Sales.SalesTerritory st ON sp.TerritoryID = st.TerritoryID
    INNER JOIN HumanResources.Employee e ON soh.SalesPersonID = e.BusinessEntityID 
    INNER JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID
    WHERE st.[Group] = @p1
    OPTION (FORCE ORDER)', 
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO

USE AdventureWorks2008R2;
GO
EXEC sp_executesql 
@stmt = N'SELECT
    soh.SalesPersonID
    ,p.FirstName + '' '' + COALESCE(p.MiddleName, '''') + '' '' + p.LastName AS FullName
    ,e.JobTitle
    ,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.BusinessEntityID = soh.SalesPersonID
    INNER JOIN Sales.SalesTerritory st ON sp.TerritoryID = st.TerritoryID
    INNER JOIN HumanResources.Employee e ON soh.SalesPersonID = e.BusinessEntityID 
    INNER JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID
    WHERE st.[Group] = @p1
    OPTION (FORCE ORDER)', 
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO

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

DBCC FREEPROCCACHE;
GO
USE AdventureWorks2008R2;
GO
EXEC sp_executesql 
@stmt = N'SELECT
    soh.SalesPersonID
    ,p.FirstName + '' '' + COALESCE(p.MiddleName, '''') + '' '' + p.LastName AS FullName
    ,e.JobTitle
    ,st.Name AS SalesTerritory
    ,soh.SubTotal
    ,YEAR(DATEADD(m, 6, soh.OrderDate)) AS FiscalYear 
    FROM Sales.SalesPerson sp 
    INNER JOIN Sales.SalesOrderHeader AS soh ON sp.BusinessEntityID = soh.SalesPersonID
    INNER JOIN Sales.SalesTerritory AS st ON sp.TerritoryID = st.TerritoryID
    INNER JOIN HumanResources.Employee AS e ON soh.SalesPersonID = e.BusinessEntityID 
    INNER JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID
    WHERE st.[Group] = @p1
    OPTION (FORCE ORDER)', 
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO

DECLARE @xml_showplan nvarchar(max);
SET @xml_showplan = (SELECT query_plan
    FROM sys.dm_exec_query_stats AS qs 
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
    WHERE st.text LIKE N'%Sales.SalesPerson%' AND st.text LIKE N'%OPTION (FORCE ORDER)%');

EXEC sp_create_plan_guide
@name = N'ForceOrderGuide1',
@stmt = N'SELECT
    soh.SalesPersonID
    ,p.FirstName + '' '' + COALESCE(p.MiddleName, '''') + '' '' + p.LastName AS FullName
    ,e.JobTitle
    ,st.Name AS SalesTerritory
    ,soh.SubTotal
    ,YEAR(DATEADD(m, 6, soh.OrderDate)) AS FiscalYear 
    FROM Sales.SalesPerson AS sp 
    INNER JOIN Sales.SalesOrderHeader AS soh ON sp.BusinessEntityID = soh.SalesPersonID
    INNER JOIN Sales.SalesTerritory ASst ON sp.TerritoryID = st.TerritoryID
    INNER JOIN HumanResources.Employee ASe ON soh.SalesPersonID = e.BusinessEntityID 
    INNER JOIN Person.Person ASp ON e.BusinessEntityID = p.BusinessEntityID
    WHERE st.[Group] = @p1', 
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@p1 nvarchar(80)',
@hints = @xml_showplan;

GO
SELECT * FROM sys.plan_guides;
GO

DBCC FREEPROCCACHE;
GO
USE AdventureWorks2008R2;
GO
EXEC sp_executesql 
@stmt = N'SELECT
    soh.SalesPersonID
    ,p.FirstName + '' '' + COALESCE(p.MiddleName, '''') + '' '' + p.LastName AS FullName
    ,e.JobTitle
    ,st.Name AS SalesTerritory
    ,soh.SubTotal
    ,YEAR(DATEADD(m, 6, soh.OrderDate)) AS FiscalYear 
    FROM Sales.SalesPerson sp 
    INNER JOIN Sales.SalesOrderHeader AS soh ON sp.BusinessEntityID = soh.SalesPersonID
    INNER JOIN Sales.SalesTerritory AS st ON sp.TerritoryID = st.TerritoryID
    INNER JOIN HumanResources.Employee AS e ON soh.SalesPersonID = e.BusinessEntityID 
    INNER JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID
    WHERE st.[Group] = @p1
    OPTION (FORCE ORDER)', 
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO

DECLARE @xml_showplan nvarchar(max);
SET @xml_showplan = (SELECT query_plan
    FROM sys.dm_exec_query_stats AS qs 
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
    WHERE st.text LIKE N'%Sales.SalesPerson%' AND st.text LIKE N'%OPTION (FORCE ORDER)%');

EXEC sp_create_plan_guide
@name = N'ForceOrderGuide1',
@stmt = N'SELECT
    soh.SalesPersonID
    ,p.FirstName + '' '' + COALESCE(p.MiddleName, '''') + '' '' + p.LastName AS FullName
    ,e.JobTitle
    ,st.Name AS SalesTerritory
    ,soh.SubTotal
    ,YEAR(DATEADD(m, 6, soh.OrderDate)) AS FiscalYear 
    FROM Sales.SalesPerson AS sp 
    INNER JOIN Sales.SalesOrderHeader AS soh ON sp.BusinessEntityID = soh.SalesPersonID
    INNER JOIN Sales.SalesTerritory ASst ON sp.TerritoryID = st.TerritoryID
    INNER JOIN HumanResources.Employee ASe ON soh.SalesPersonID = e.BusinessEntityID 
    INNER JOIN Person.Person ASp ON e.BusinessEntityID = p.BusinessEntityID
    WHERE st.[Group] = @p1', 
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@p1 nvarchar(80)',
@hints = @xml_showplan;

GO
SELECT * FROM sys.plan_guides;
GO

元のクエリを実行する前に、SQL Server Profiler を使用してトレースを作成し、Plan Guide Successful イベントおよび Plan Guide Unsuccessful イベントを Performance カテゴリから選択します。元のクエリを実行し、クエリの結果をトレースの出力で確認します。

USE AdventureWorks2008R2;
GO
EXEC sp_executesql 
@stmt = N'SELECT
    soh.SalesPersonID
    ,p.FirstName + '' '' + COALESCE(p.MiddleName, '''') + '' '' + p.LastName AS FullName
    ,e.JobTitle
    ,st.Name AS SalesTerritory
    ,soh.SubTotal
    ,YEAR(DATEADD(m, 6, soh.OrderDate)) AS FiscalYear 
    FROM Sales.SalesPerson AS sp 
    INNER JOIN Sales.SalesOrderHeader AS soh ON sp.BusinessEntityID = soh.SalesPersonID
    INNER JOIN Sales.SalesTerritory ASst ON sp.TerritoryID = st.TerritoryID
    INNER JOIN HumanResources.Employee ASe ON soh.SalesPersonID = e.BusinessEntityID 
    INNER JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID
    WHERE st.[Group] = @p1', 
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO

USE AdventureWorks2008R2;
GO
EXEC sp_executesql 
@stmt = N'SELECT
    soh.SalesPersonID
    ,p.FirstName + '' '' + COALESCE(p.MiddleName, '''') + '' '' + p.LastName AS FullName
    ,e.JobTitle
    ,st.Name AS SalesTerritory
    ,soh.SubTotal
    ,YEAR(DATEADD(m, 6, soh.OrderDate)) AS FiscalYear 
    FROM Sales.SalesPerson AS sp 
    INNER JOIN Sales.SalesOrderHeader AS soh ON sp.BusinessEntityID = soh.SalesPersonID
    INNER JOIN Sales.SalesTerritory ASst ON sp.TerritoryID = st.TerritoryID
    INNER JOIN HumanResources.Employee ASe ON soh.SalesPersonID = e.BusinessEntityID 
    INNER JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID
    WHERE st.[Group] = @p1', 
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO

Share via

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

例

関連項目

タスク

参照

概念

その他の技術情報

その他のリソース

Share via

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

例

関連項目

タスク

参照

概念

その他の技術情報

その他のリソース

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