Plan Forcing Scenario: Create a Plan Guide That Specifies a Query Plan
Można wymusić planu kwerend, podczas tworzenia planu przy użyciu sp_create_plan_guide systemu przechowywane procedury i określając planu kwerend w formacie XML plan wykonania kwerendy w @hints parametr. Plan prowadnice są używane do zastosować do kwerend w aplikacjach wdrożonego wskazówki kwerendy lub planów kwerend nie może lub nie chcesz zmieniać aplikacji bezpośrednio.Aby uzyskać więcej informacji na temat prowadnic planu zobacz Optimizing Queries in Deployed Applications by Using Plan Guides. W tym scenariuszu dołączany plan wykonania kwerendy określonej kwerendy do plan wykonania kwerendy.
Załóżmy, że aplikacja zawiera następującą procedura przechowywana:
USE AdventureWorks;
PRZEJDŹ DO
CREATE PROCEDURE Sales.GetSalesOrderByCountryRegion (@ CountryRegion nvarchar(60))
JAK
ROZPOCZĘCIE
SELECT h.SalesOrderID, h.OrderDate, h.Comment
FROM AS Sales.SalesOrderHeader h, AS Sales.Customer c, t Sales.SalesTerritory AS
WHERE h.CustomerID = c.CustomerID c.TerritoryID AND = t.TerritoryID oraz CountryRegionCode = @ CountryRegion;
KONIEC;
PRZEJDŹ DO
Załóżmy, że większość kwerend, których wykonać tej procedury jest wykonywana słabo ponieważ planu kwerendy nie jest optymalizowany dla przedstawiciela lub wartość "" najgorszy" @CountryRegion parametr. Chcesz wymusić tę procedura przechowywana, aby użyć planu określonej kwerendy, który zoptymalizowano dla określonego kraju lub region.Bezpośrednio można jednak zmienić procedura przechowywana w aplikacji, ponieważ zakupionych aplikacji do dostawcy niezależnego oprogramowania.Zamiast tego można utworzyć plan wykonania kwerendy dla kwerendy, określający plan kwerend w podręczniku plan wykonania kwerendy, który zoptymalizowano dla reprezentatywnych wartości.
Aby dołączyć plan wykonania kwerendy kwerendy do plan wykonania kwerendy, należy najpierw uzyskać plan wykonania kwerendy kwerendy zoptymalizowane dla kwerendy w procedura przechowywana.Aby to zrobić, wykonywanie kwerendy, określone w procedurze przechowywanej podstawianie przedstawiciela lub "" najgorszy"stała wartość, zamiast @CountryRegion parametr. Następnie kwerendy dynamiczny widok zarządzania sys.dm_exec_query_stats uzyskać planu kwerendy z pamięci podręcznej planu.Firma Microsoft zaleca, aby przypisać plan wykonania XML do zmiennej; w przeciwnym razie escape musi wszelkie znaki pojedynczego cudzysłowu w plan wykonania XML przez poprzedzający je z innego pojedynczy znak cudzysłowu.Na koniec należy utworzenia plan wykonania kwerendy Określanie plan wykonania XML w @hints parametr.
Przykład
Poniższy przykład kodu demonstruje czynności wymagane do uzyskania plan wykonania kwerendy kwerendy zoptymalizowane dla Sales.GetSalesOrderByCountryRegion Procedura przechowywana i dołączyć go do plan wykonania kwerendy. Po wykonaniu procedura przechowywana, kwerendy, określone w procedurze zostanie dopasowany do plan wykonania kwerendy i optymalizator kwerendy używa plan wykonania kwerendy kwerend, określone w plan wykonania kwerendy.
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');