Share via

  • [アーティクル]

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

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

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

  1. クエリの論理的な意味を変更しないで適切なプランが作成されるように、クエリの変更内容を決定します (結合順序を変更したり、FORCE ORDER、結合ヒント、インデックス ヒント、およびその他の技法を使用)。

  2. 書き直したクエリのプランをキャプチャします。このとき、sp_executesql や sp_cursorprepexec を使用したり、スタンドアロン バッチとしてなど、プランが元のクエリの場合と同様の方法で実行されるようにします。

  3. プラン ガイドを作成し、取得した適切なプランを元のクエリに適用します。

  4. SQL Server Profiler を使用して、Plan Guide Successful イベントおよび Plan Guide Unsuccessful イベントを Performance カテゴリから選択し、元のクエリを実行します。SQL Server Profiler を調べてクエリが該当のプラン ガイドを使用していることを確認します。

例

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

USE AdventureWorks;
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

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

USE AdventureWorks;
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
    OPTION (FORCE ORDER)', 
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO

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

DBCC FREEPROCCACHE;
GO
USE AdventureWorks;
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
    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
    ,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 = @xml_showplan;

GO
SELECT * FROM sys.plan_guides;
GO

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

USE AdventureWorks;
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