Opis planu prowadnic

W tym temacie opisano plan guides i wyjaśniono, jak one używane do optymalizacji wydajności kwerendy nie może lub nie chcesz zmienić tekst kwerendy bezpośrednio.Plan guides może być przydatne w podzbiór kwerend w aplikacji bazy danych, rozmieszczone od dostawcy firm nie wykonuje się zgodnie z oczekiwaniami.Optymalizacji wpływ prowadnice planu kwerend, dołączając do nich wskazówki kwerendy lub planu kwerend stałych.W podręczniku plan określić Transact-SQL oświadczenie, że ma być zoptymalizowany i albo OPCJĘ klauzulę zawierający kwerendę wskazówek, które chcesz użycia lub plan określonej kwerendy chcesz wykorzystać do optymalizacji kwerendy.Gdy kwerenda jest wykonywana, SQL Server odpowiada Transact-SQL instrukcji do przewodnik planu i dołącza klauzulę opcja w czasie wykonywania kwerendy lub używa przewodnik planu kwerendy.

Ostrzeżenie

Plan prowadnice mogą być używane tylko na SQL Server , deweloper, oceny, wersje; jednak plan guides są widoczne w dowolnej wersji.Można również dołączyć bazę danych, zawierającą plan guides do dowolnej wersji.Plan guides pozostają niezmienione, podczas przywracanie lub dołączania bazy danych do uaktualnionych wersja SQL Server 2008.

Dopasowywanie przewodników planu kwerend

Plan guides mogą być tworzone do kwerend, które są wykonywane w następujących okolicznościach:

• przewodnik planu obiekt pasuje do kwerendy, na których wykonywanie w kontekście Transact-SQL przechowywane procedury, zdefiniowanej przez użytkownika funkcji skalarnych, multi-statement tabela-ważnych funkcji zdefiniowanych przez użytkownika i wyzwalaczy LŚD.

• przewodnik planu SQL dopasowuje kwerend, wykonać w kontekście autonomicznego Transact-SQL instrukcji i partii, które są nie jest częścią obiektu bazy danych.Prowadnice planu opartego na serwerze SQL można również dopasować kwerend, które parameterize do określonego formularza.

• przewodnik planu szablon odpowiada autonomicznych kwerend, które parameterize do określonego formularza.Prowadnice planu są używane do zastąpienia bieżącej bazy danych PARAMETRYZACJA zestawu opcji dla klasy kwerend bazy danych.Aby uzyskać więcej informacji, zobacz Parametryzacja proste i Parametryzacja wymuszony.

Aby uzyskać więcej informacji, zobacz Jak SQL Server dopasowuje Plan prowadnic do kwerend.

OBIEKT Plan Guides

Załóżmy, że następującą procedura składowana, która bierze @Country_region parametr, istnieje w aplikacji bazy danych, który rozmieścił przeciwko AdventureWorks2008R2 bazy 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 składowana zostały skompilowane i zoptymalizowany pod kątem @Country_region = N'AU' (Australia).Jednakże ponieważ są stosunkowo niewielką liczbą zamówień sprzedaży, które pochodzą z Australii, wydajność spada, gdy kwerenda jest wykonywana przy użyciu wartości parametrów krajów z więcej 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' będzie prawdopodobnie działać lepiej dla wszystkich możliwych wartości @Country_region parametru.

Może rozwiązać ten problem, modyfikując procedura składowana, aby dodać OPTIMIZE FOR wskazówkę dotyczącą kwerendy na kwerendę.Jednakże ponieważ procedura składowana jest rozmieszczonej aplikacji, nie można bezpośrednio modyfikować kod aplikacji.Zamiast tego można utworzyć następujące przewodnik planu w AdventureWorks2008R2 bazy 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''))'

Gdy kwerenda określona w sp_create_plan_guide instrukcja wykonuje, kwerenda jest modyfikowany przed optymalizacji uwzględnienie OPTIMIZE FOR (@Country = N''US'') klauzula.

Prowadnice Plan SQL

SQL plan guides stosuje się do autonomicznego Transact-SQL instrukcji i instancje.Często te sprawozdania są składane przez aplikację za pomocą sp_executesql systemowa procedura składowana.Na przykład, rozważmy następujące autonomicznego partia:

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

Aby zapobiec planu równoległego generowany na tej kwerendzie, należy utworzyć następujące plan i zestaw MAXDOP wskazówkę dotyczącą kwerendy do 1 w @hints parametru.

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)';

Ostrzeżenie

partia Zawiera oświadczenie, w którym chcesz utworzyć przewodnik planu nie może zawierać wykorzystanie database instrukcja.

Ważne:
Wartości, które są dostarczane do @module_or_batch i @params argumenty sp_create_plan guide Instrukcja musi odpowiadać odpowiedni tekst przedstawiony w rzeczywistej kwerendy.Aby uzyskać więcej informacji, zobacz sp_create_plan_guide (języka Transact-SQL) i Za pomocą SQL Server Profiler, aby utworzyć i przetestować Plan prowadnic.

SQL przewodnik planuna kwerendach, które parameterize tym samym formularzu, gdy USTAWIONA opcja bazy danych PARAMETRYZACJA WYMUSZONY lub szablonu można także tworzyć s przewodnik planu jest tworzony, określając, że klasa sparametryzowana kwerend.Aby uzyskać więcej informacji, zobacz Projektowanie przewodników planu kwerend parametrycznych.

Szablon planu prowadnic

SZABLON plan guides są używane do zmiany zachowania parametryzacja dla określonej kwerendy, formularze.przewodnik planu szablonu można tworzyć w jednej z następujących sytuacji:

• PARAMETRYZACJA opcji bazy danych USTAWIONO WYMUSZONY, ale istnieją mają być opracowane zgodnie z zasadami proste parametryzacja kwerend.

• PARAMETRYZACJA opcji bazy danych USTAWIONO proste (ustawienie domyślne), ale ma wymuszone parametryzacja do próby na klasy kwerend.

Aby uzyskać więcej informacji, zobacz Określanie zachowania parametryzacji kwerendy przy użyciu prowadnic Plan.

Poniższy przykład tworzy przewodnik planu, odpowiadający kwerenda parameterizes do określonego formularza i kieruje SQL Server wymusić parametryzacji kwerendy.Następujące dwie kwerendy są równoważne syntaktycznie, ale różnią się jedynie ich stała wartości literału.

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

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

Oto przewodnik planu sparametryzowana formularza kwerendy:

EXEC sp_create_plan_guide 
    @name = N'TemplateGuide1',
    @stmt = N'SELECT * FROM AdventureWorks2008R2.Sales.SalesOrderHeader AS h
              INNER JOIN AdventureWorks2008R2.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ść @stmt parametr jest formą sparametryzowanych kwerend.Tylko niezawodnym sposobem uzyskania tej wartości do użytku w sp_create_plan_guide jest użycie sp_get_query_template systemowa procedura składowana.Poniższy skrypt umożliwia zarówno uzyskanie sparametryzowanych kwerend, a następnie utworzyć przewodnik planu na nim.

DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template 
    N'SELECT * FROM AdventureWorks2008R2.Sales.SalesOrderHeader AS h
      INNER JOIN AdventureWorks2008R2.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)';

Ważne:
Wartość stała literały w @stmt Parametr przekazany do sp_get_query_template może wpłynąć na typ danych, która została wybrana dla parametru, który zastępuje literal.Wpłynie to przewodnik planu dopasowywania.Należy utworzyć kilka przewodnik planu do obsługi zakresy wartości różnych parametrów.

Umożliwia także szablon plan guides wraz z przewodników planu SQL.Na przykład można utworzyć przewodnik planu szablonu, upewnij się, że klasa kwerend jest sparametryzowana.przewodnik planu SQL można utworzyć w formularzu sparametryzowana tej kwerendy.

Stosowanie środka planu kwerend do planu

Stosuje się plan środków kwerendy przewodnik planu typu obiektu lub SQL.Plan guides dotyczące planu kwerend stałe są przydatne, gdy wiedzieć o istniejących planów wykonywania wykonujący lepiej niż wybranego przez optymalizator dla określonej kwerendy.

Poniższy przykład tworzy przewodnik planu ad hoc prostej instrukcja języka SQL.Plan kwerend odpowiednie dla tej instrukcja podano w przewodnik planu określając Showplan XML kwerendy bezpośrednio w @hints parametru.Przykład najpierw wykonuje instrukcja języka SQL do wygenerowania planu w pamięci podręcznej planu.Do celów w tym przykładzie zakłada się, że odpowiedni plan jest generowany plan i dostrajania dodatkowe kwerendy nie jest wymagane.Showplan XML kwerendy jest uzyskiwana przez badanie sys.dm_exec_query_stats, sys.dm_exec_sql_text, i sys.dm_exec_text_query_plan dynamicznego zarządzania widoki i przypisany do @xml_showplan zmiennej.@xml_showplan Zmienna jest następnie przekazywany do sp_create_plan_guide instrukcja w @hints parametru.Lub przewodnik planu z przewodnik planu kwerend w pamięci podręcznej przewodnik planu można utworzyć za pomocą sp_create_plan_guide_from_handle procedura składowana.

USE AdventureWorks2008R2;
GO
SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;
GO
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 City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;%');

EXEC sp_create_plan_guide 
    @name = N'Guide1_from_XML_showplan', 
    @stmt = N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = @xml_showplan;
GO

Sprawdzanie poprawności Plan Guides po uaktualnieniu

Firma Microsoft zaleca ponownej i badania przewodnik planu definicje po uaktualnieniu do nowej wersji aplikacji SQL Server.Może zmienić wymagania dotyczące dostrajania wydajności i zachowanie odpowiedniego przewodnik planu przewodnik.Chociaż przewodnik nieprawidłowy plan nie spowoduje działanie kwerendy, plan skompilowany bez użycia przewodnik planu i nie może być najlepszym wyborem.Po uaktualnieniu bazy danych w SQL Server 2008, zaleca się wykonanie następujących zadań:

• Sprawdzanie poprawności istniejący plan guides za pomocą sys.fn_validate_plan_guide funkcja.

• Za pomocą SQL Server Profiler, monitor planów misguided niektóre okres czas za pomocą Plan niepomyślnych przewodnik zdarzeń.

Zobacz także

Odwołanie

sp_create_plan_guide (języka Transact-SQL)

sp_create_plan_guide_from_handle (języka Transact-SQL)

sp_control_plan_guide (języka Transact-SQL)

sys.plan_guides (języka Transact-SQL)

fn_validate_plan_guide (języka Transact-SQL)

Wskazówki kwerendy (Transact-SQL)

Koncepcje

Projektowanie i wdrażanie planów

Parametryzacja wymuszony

Parametryzacja proste

Inne zasoby

Optymalizacja kwerend w wdrożonych aplikacji za pomocą prowadnic Plan