Understanding Plan Guides

Załóżmy, że następującą procedura przechowywana, która ma @Country\_region parametr, istnieje w aplikacji bazy danych, która jest rozmieszczany względem AdventureWorks Baza danych:

CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country_region nvarchar(60))
AS
BEGIN
    SELECT *
    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 = @Country_region
END;

Załóżmy, że ta procedura przechowywana został skompilowany i zoptymalizowane pod kątem @Country\_region = N'AU' (Australia). Jednak ze względu na to, że dostępne są stosunkowo niewielką liczbą zamówień sprzedaży, które pochodzą z Australii, wydajność spada, gdy kwerenda jest wykonywany przy użyciu wartości parametrów krajów z kilku zamówień sprzedaży.Ponieważ pochodzą najbardziej zamówień sprzedaży w Stanach Zjednoczonych planu kwerend, który jest generowany dla @Country\_region = N'US' prawdopodobnie będą działać lepiej dla wszystkich możliwych wartości @Country\_region parametr.

Może rozwiązać ten problem przez zmodyfikowanie procedura przechowywana, aby dodać OPTIMIZE FOR Wskazówka dotycząca kwerendy do kwerendy. Jednak ponieważ procedura przechowywana jest rozmieszczonej aplikacji, nie można bezpośrednio modyfikować kodu aplikacji.Zamiast tego należy utworzyć następujące plan wykonania kwerendy wykonania kwerendy w AdventureWorks Baza danych.

sp_create_plan_guide 
@name = N'Guide1',
@stmt = N'SELECT *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 = @Country_region',
@type = N'OBJECT',
@module_or_batch = N'Sales.GetSalesOrderByCountry',
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))'

Kiedy kwerenda określiła w sp_create_plan_guide wykonuje instrukcję, kwerenda jest modyfikowany przed optymalizację, aby uwzględnić OPTIMIZE FOR (@Country = N''US'') Klauzula.

Przewodniki programu SQL stosuje się do autonomicznego Transact-SQL instrukcje i instancji. Często te instrukcje są przesyłane przez aplikację za pomocą sp_executesql systemu procedura przechowywana.Na przykład należy wziąć pod uwagę następujące wsadowego autonomicznych:

SELECT TOP 1 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC;

Aby zapobiec sytuacji, w której plan wykonywanie równoległe generowane dla tej kwerendy, utworzyć następujące plan wykonania kwerendy i ustawić MAXDOP Wskazówka dotycząca kwerendy do 1 w @hints parametr.

sp_create_plan_guide 
@name = N'Guide2', 
@stmt = N'SELECT TOP 1 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC',
@type = N'SQL',
@module_or_batch = NULL, 
@params = NULL, 
@hints = N'OPTION (MAXDOP 1)';

Important Note:
Wartości, które są dostarczane do @ module_or_batch and @ params argumentówPrzewodnik sp_create_plan instrukcja musi odpowiadać odpowiedni tekst w rzeczywistej kwerendy.Aby uzyskać więcej informacji zobacz sp_create_plan_guide (języka Transact-SQL) i Using SQL Server Profiler to Create and Test Plan Guides.

Przewodniki programu SQL można też utworzyć na kwerendy, które parameterize na tym samym formularzu, gdy opcja parametry bazy danych jest zestaw do FORCED lub podczas planowania szablon przewodnik jest tworzony, określając klasy sparametryzowanej z kwerendy.Aby uzyskać więcej informacji zobaczDesigning Plan Guides for Parameterized Queries.

TEMPLATE planu prowadnice są używane do zmienić zachowanie parametryzacji formularzy określonej kwerendy.Aby utworzyć prowadnicę plan wykonania kwerendy TEMPLATE, przy użyciu jednego z następujących sytuacji:

• Opcja parametry bazy danych jest zestaw do FORCED, ale istnieją mają być tworzone zgodnie z regułami proste parametryzacji kwerendy.

• Opcja parametry bazy danych jest zestaw do SIMPLE (ustawienie domyślne), ale ma wymuszone parametryzacji do się próby na klasy z kwerendy.

Aby uzyskać więcej informacji zobaczSpecifying Query Parameterization Behavior by Using Plan Guides.

W następującym przykładzie zostanie utworzony odpowiadający dowolny typ kwerendy, która parameterizes do określonego formularza i kieruje prowadnicy plan wykonania kwerendy SQL Server Aby wymusić parametryzacji kwerendy. Dwie następujące kwerendy są równoważne składniowo, ale różnią się jedynie ich stała wartości literałów.

SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;

SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45640;

W tym polu jest plan wykonania kwerendy wykonania kwerendy w formularzu sparametryzowana kwerendy:

EXEC sp_create_plan_guide 
    @name = N'TemplateGuide1',
    @stmt = N'SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
              INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d 
                  ON h.SalesOrderID = d.SalesOrderID
              WHERE h.SalesOrderID = @0',
    @type = N'TEMPLATE',
    @module_or_batch = NULL,
    @params = N'@0 int',
    @hints = N'OPTION(PARAMETERIZATION FORCED)';

W poprzednim przykładzie, wartości @stmt parametr jest sparametryzowana formularzu kwerendy. Tylko niezawodnym sposobem uzyskania tej wartości w odniesieniu do użycia w sp_create_plan_guide polega na użyciu sp_get_query_template systemu procedura przechowywana.Poniższy skrypt może służyć zarówno do uzyskania kwerendy parametryczne, a następnie utworzyć plan wykonania kwerendy na nim.

DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template 
    N'SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
      INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d 
          ON h.SalesOrderID = d.SalesOrderID
      WHERE h.SalesOrderID = 45639;',
    @stmt OUTPUT, 
    @params OUTPUT
EXEC sp_create_plan_guide N'TemplateGuide1', 
    @stmt, 
    N'TEMPLATE', 
    NULL, 
    @params, 
    N'OPTION(PARAMETERIZATION FORCED)';

Important Note:
Wartość stała literały w @stmt Parametr przekazany do sp_get_query_template mogą mieć wpływ na typ danych, który został wybrany dla tego parametru, który zastępuje literał. Będzie to miało wpływu na prowadnicy plan wykonania kwerendy dopasowania.Czasami trzeba utworzyć więcej niż jeden podręcznik plan wykonania kwerendy obsługi zakresy wartości różnych parametrów.

Umożliwia także TEMPLATE prowadnic planu wraz z przewodników programu SQL.Na przykład można utworzyć prowadnicę plan wykonania kwerendy TEMPLATE, aby upewnić się, że klasa kwerendy jest parametrów.Następnie można utworzyć plan wykonania kwerendy wykonania kwerendy SQL w formularzu sparametryzowana tej kwerendy.