계획 적용 시나리오: 쿼리 계획을 지정하는 계획 지침 작성
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
쿼리 계획이 @CountryRegion 매개 변수의 대표 값이나 "worst-case" 값에 대해 최적화되지 않으므로 이 프로시저에서 실행하는 대부분의 쿼리 성능이 좋지 않다고 가정합니다. 특정 국가 또는 지역에 대해 최적화된 특정 쿼리 계획을 사용하도록 이 저장 프로시저를 적용하려고 합니다. 그러나 ISV(Independent Software Vendor)에서 응용 프로그램을 구입했으므로 응용 프로그램의 저장 프로시저를 직접 변경할 수는 없습니다. 대신 쿼리의 계획 지침을 만들고 대표값에 대해 최적화된 계획 지침에 쿼리 계획을 지정할 수 있습니다.
계획 지침에 쿼리 계획을 추가하려면 먼저 저장 프로시저에 쿼리에 대해 최적화된 쿼리 계획을 가져와야 합니다. 이렇게 하려면 저장 프로시저에 정의된 쿼리를 실행하거나 대표값을 대체하거나 @CountryRegion 매개 변수 대신 "worst-case" 상수 값을 사용합니다. sys.dm_exec_query_stats 동적 관리 뷰를 쿼리하여 계획 캐시에서 쿼리 계획을 가져옵니다. XML 실행 계획을 변수에 할당하는 것이 좋습니다. 그렇지 않으면 XML 실행 계획의 작은따옴표 앞에 다른 작은따옴표를 붙여 이스케이프 처리해야 합니다. 마지막으로 @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');