Udostępnij przez

Plan wymuszanie scenariusza: Utworzenia planu określająca planu kwerend

Plan kwerend można wymusić podczas tworzenia przewodnik planu przy użyciu sp_create_plan_guide systemowa procedura składowana i określając przewodnik planu kwerend w formacie XML Showplan kwerendy w @hints parametru.Plan guides są używane do zastosowania wskazówki kwerendy lub planów kwerend do kwerend w wdrożonych aplikacji nie może lub nie chcesz zmieniać aplikacji bezpośrednio.Aby uzyskać więcej informacji na temat planów, zobacz Optymalizacja kwerend w wdrożonych aplikacji za pomocą prowadnic Plan.W tym scenariuszu przewodnik planu kwerend określonych przyłączane do przewodnik planu.

Załóżmy, że aplikacja zawiera następującą procedura składowana:

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

Załóżmy, że większość kwerend, których wykonać tej procedury są wykonać słabo ponieważ plan kwerend nie jest optymalizowany dla obu przedstawiciela lub "najgorszej -przypadek" wartość dla @CountryRegion parametru.Chcesz wymusić tę procedura składowana, aby użyć planu określonej kwerendy, który zoptymalizowano dla określonego kraju lub region.Bezpośrednio nie można jednak zmienić procedura składowana w aplikacji, ponieważ zakupiono aplikację z niezależnym dostawcą.Zamiast tego można utworzyć plan dla kwerendy, określając w podręczniku przewodnik planu, który zoptymalizowano dla wartości reprezentatywnych przewodnik planu kwerend.

Aby dołączyć plan kwerend do przewodnik planu, należy najpierw uzyskać plan kwerend zoptymalizowane dla kwerendy w procedura składowana.Aby to zrobić, wykonywanie kwerendy, określonych w procedura składowana, podstawiając przedstawiciela, lub "najgorszy" wartość stała zamiast @CountryRegion parametru.Następnie kwerendę sys.dm_exec_query_stats dynamiczny widok zarządzania uzyskać plan kwerend z pamięci podręcznej planu.Firma Microsoft zaleca, aby przypisać XML Showplan do zmiennej; w przeciwnym razie należy escape wszelkie znaki pojedynczego cudzysłowu w XML Showplan poprzedzając je z innego pojedynczego cudzysłowu.Na koniec należy utworzyć przewodnik planu Określanie Showplan XML w @hints parametru.

Przykład

Poniższy przykład kodu pokazuje kroki wymagane do uzyskania plan kwerend zoptymalizowane dla Sales.GetSalesOrderByCountryRegion procedura składowana i dołączyć go do przewodnika przewodnik planu.Po wykonaniu procedura składowana, dostosowanego do przewodnik planu kwerendy zdefiniowanej w procedurze i optymalizator kwerendy używa przewodnik planu kwerend, określone w podręczniku przewodnik planu.

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');
  • Last updated on