プラン適用シナリオ : クエリ プランを指定するプラン ガイドの作成
sp_create_plan_guide システム ストアド プロシージャを使用してプラン ガイドを作成するときに、@hints パラメーターにクエリの XML プラン表示形式でクエリ プランを指定して、クエリ プランを適用できます。プラン ガイドは、配置済みのアプリケーションを直接変更できないときに、クエリ ヒントまたはクエリ プランをクエリに適用するために使用されます。プラン ガイドの詳細については、「プラン ガイドを使用した配置済みアプリケーションのクエリの最適化」を参照してください。このシナリオでは、特定のクエリ プランをプラン ガイドに適用します。
アプリケーションに次のストアド プロシージャが実装されているとします。
USE AdventureWorks2008R2;
GO
CREATE PROCEDURE Sales.GetSalesOrderByCountryRegion (@CountryRegion nvarchar(60))
AS
BEGIN
SELECT h.SalesOrderID, h.OrderDate, h.Comment
FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, Sales.SalesTerritory AS t
WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode = @CountryRegion;
END;
GO
USE AdventureWorks2008R2;
GO
CREATE PROCEDURE Sales.GetSalesOrderByCountryRegion (@CountryRegion nvarchar(60))
AS
BEGIN
SELECT h.SalesOrderID, h.OrderDate, h.Comment
FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, Sales.SalesTerritory AS t
WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode = @CountryRegion;
END;
GO
クエリ プランが @CountryRegion パラメーターの代表的な値または "最悪のケースの" 値のいずれにも最適化されていないため、このプロシージャを実行する多くのクエリでパフォーマンスが低下しているものとします。このストアド プロシージャを適用して、特定の国や地域に対して最適化されている特定のクエリ プランを使用します。ただし、このアプリケーションは ISV (独立系ソフトウェア ベンダー) から購入したものなので、アプリケーション内のストアド プロシージャを直接変更することができません。そこで、代わりに、クエリのプラン ガイドを作成して、プラン ガイドに代表的な値に対して最適化されているクエリ プランを指定します。
クエリ プランをプラン ガイドに適用するには、最初にストアド プロシージャのクエリに対して最適化されたクエリ プランを取得する必要があります。そのためには、ストアド プロシージャで定義されたクエリを実行して、@CountryRegion パラメーターを代表的な定数値または "最悪のケースの" 定数値に置き換えます。次に、sys.dm_exec_query_stats 動的管理ビューに対してクエリを実行し、プラン キャッシュからクエリ プランを取得します。XML プラン表示を変数に割り当てることをお勧めします。XML プラン表示を変数に割り当てない場合は、XML プラン表示内の単一引用符をエスケープする必要があります。これを行うには、単一引用符の前にもう 1 つ単一引用符を追加します。最後に、@hints パラメーターに XML プラン表示を指定するプラン ガイドを作成します。
例
次のコード例では、Sales.GetSalesOrderByCountryRegion ストアド プロシージャに対して最適化されたクエリ プランを取得し、そのプランをプラン ガイドに適用するために必要な手順を示します。ストアド プロシージャが実行されると、プロシージャで定義されたクエリがプラン ガイドと照合され、プラン ガイドで指定されたクエリ プランがクエリ オプティマイザーで使用されます。
CREATE PROCEDURE Sales.GetSalesOrderByCountryRegion (@CountryRegion nvarchar(60))
AS
BEGIN
SELECT h.SalesOrderID, h.OrderDate, h.Comment
FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, Sales.SalesTerritory AS t
WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode = @CountryRegion;
END;
GO
-- Execute the query based on a representative or "worst-case" scenario.
GO
SELECT h.SalesOrderID, h.OrderDate, h.Comment
FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, Sales.SalesTerritory AS t
WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode = N'US';
GO
-- Retrieve the query plan for the previous query. Assign the query plan to a variable and attach the query plan to a plan guide.
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'SELECT h.SalesOrderID, h.OrderDate, h.Comment
FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, Sales.SalesTerritory AS t
WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode = N''US'';%');
EXEC sp_create_plan_guide
@name = N'Guide_for_GetSalesByCountryRegion',
@stmt = N'SELECT h.SalesOrderID, h.OrderDate, h.Comment
FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, Sales.SalesTerritory AS t
WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode = @CountryRegion',
@type = N'OBJECT',
@module_or_batch = N'Sales.GetSalesOrderByCountryRegion',
@params = NULL,
@hints = @xml_showplan;
GO
SELECT * FROM sys.plan_guides
WHERE scope_object_id = OBJECT_ID(N'Sales.GetSalesOrderByCountryRegion');
CREATE PROCEDURE Sales.GetSalesOrderByCountryRegion (@CountryRegion nvarchar(60))
AS
BEGIN
SELECT h.SalesOrderID, h.OrderDate, h.Comment
FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, Sales.SalesTerritory AS t
WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode = @CountryRegion;
END;
GO
-- Execute the query based on a representative or "worst-case" scenario.
GO
SELECT h.SalesOrderID, h.OrderDate, h.Comment
FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, Sales.SalesTerritory AS t
WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode = N'US';
GO
-- Retrieve the query plan for the previous query. Assign the query plan to a variable and attach the query plan to a plan guide.
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'SELECT h.SalesOrderID, h.OrderDate, h.Comment
FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, Sales.SalesTerritory AS t
WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode = N''US'';%');
EXEC sp_create_plan_guide
@name = N'Guide_for_GetSalesByCountryRegion',
@stmt = N'SELECT h.SalesOrderID, h.OrderDate, h.Comment
FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, Sales.SalesTerritory AS t
WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode = @CountryRegion',
@type = N'OBJECT',
@module_or_batch = N'Sales.GetSalesOrderByCountryRegion',
@params = NULL,
@hints = @xml_showplan;
GO
SELECT * FROM sys.plan_guides
WHERE scope_object_id = OBJECT_ID(N'Sales.GetSalesOrderByCountryRegion');