Udostępnij za pośrednictwem

  • Artykuł

Understanding Plan Guides

W tym temacie opisano prowadnic plan i wyjaśniono, jak mogły być używane w celu zoptymalizowania wydajności kwerendy nie może lub nie chcesz zmieniać tekst kwerendy bezpośrednio.Plan prowadnic mogą być przydatne podczas mały podzbiór kwerend w aplikacji bazy danych, rozmieszczone od innej firmy (sprzedawcy) nie są wykonywane zgodnie z oczekiwaniami.Plan prowadnic wpływ optymalizacji kwerendy przez dołączenie wskazówki kwerendy lub plan kwerend środka do nich.W przewodniku plan wykonania kwerendy można określić języka Transact-instrukcja języka SQL czy ma być zoptymalizowany i albo OPTION klauzulę zawierający kwerendę w pamięci podręcznej wskazówek wykorzystania lub plan wykonania kwerendy określonej kwerendy, którego chcesz użyć w celu zoptymalizowania kwerendy.Wykonuje kwerendę, SQL Server pasuje do instrukcja języka Transact-SQL do plan wykonania kwerendy i dołącza klauzulę OPTION do kwerendy w czasie wykonywania lub używa plan wykonania kwerendy kwerendy.

Uwaga

Plan prowadnic mogą być używane tylko w SQL Server Standardowe, Developer, oceny i Enterprise wersji; jednak plan prowadnice są widoczne w żadnej wersji. Można również dołączyć bazę danych zawierającą przewodniki planu do żadnej wersji.Linie pomocnicze planu pozostają bez zmian podczas przywracanie lub dołączania bazy danych do uaktualnionej wersja SQL Server 2008.

Dopasowanie prowadnic plan dla kwerendy

Plan prowadnic mogą być utworzone do kwerendy, które są wykonywane w następujących sytuacjach:

  • plan wykonania kwerendy wykonania kwerendy OBJECT pasuje do kwerendy, które są wykonać w kontekście Transact-SQL procedur przechowywanych, funkcji wartość skalarna zdefiniowanej przez użytkownika, multi-instrukcja wycenione tabela zdefiniowaną przez użytkownika funkcji i wyzwalaczy DML.

  • plan wykonania kwerendy wykonania kwerendy SQL pasuje do kwerendy, które są wykonać w kontekście autonomiczny Transact-SQL instrukcje i instancji, które nie są częścią obiektu bazy danych. Przewodniki programu opartego na serwerze SQL można również dopasować kwerendy, które parameterize do określonego formularza.

  • plan wykonania kwerendy wykonania kwerendy TEMPLATE jest taka sama, jak autonomiczne kwerendy, które parameterize do określonego formularza.Prowadnice plan są używane do zastąpienia bieżącej bazy danych PARAMETRYZACJI opcji zestaw dla klasy z kwerendy bazy danych.Aby uzyskać więcej informacji zobacz Parametryzacja proste i Parametryzacja wymuszony.

Aby uzyskać więcej informacji zobaczHow SQL Server Matches Plan Guides to Queries.

Linie pomocnicze plan OBJECT

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.

Linie pomocnicze plan SQL

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

Uwaga

Wsadowy, który zawiera informację, na którym chcesz utworzyć plan wykonania kwerendy nie może zawierać USE database Instrukcja.

Important noteImportant 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.

Linie pomocnicze TEMPLATE plan

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 noteImportant 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.

Stosowanie ustaloną plan kwerend do plan wykonania kwerendy

Można zastosować planu kwerendy stałych plan wykonania kwerendy typu OBJECT lub SQL.Prowadnice planu dotyczące planu kwerendy stałe są przydatne, gdy wiedzieć o plan wykonania, które wykonuje się lepiej niż ten, wybranego przez optymalizator dla określonej kwerendy.

Poniższy przykład tworzy plan wykonania kwerendy dla prostej instrukcja języka SQL ad hoc.Plan kwerendy żądany dla tej instrukcja znajduje się w plan wykonania kwerendy określając plan wykonania XML dla tej kwerendy bezpośrednio w programie @hints parametr. W przykładzie najpierw wykonuje instrukcję SQL, aby wygenerować planu w pamięci podręcznej planu.Dla celów tego przykładu zakłada się, że wygenerowany plan jest odpowiedni plan i dostrajanie dodatkowe kwerendy nie jest wymagane.plan wykonania XML dla tej kwerendy jest uzyskiwany za pomocą kwerend wysyłanych do widoków dynamicznego zarządzania sys.dm_exec_query_stats sys.dm_exec_sql_text i sys.dm_exec_text_query_plan i jest przypisany do @xml\_showplan Zmienna. The @xml\_showplan variable is then passed to the sp_create_plan_guide instrukcja in the @hints parameter. Lub plan wykonania kwerendy można utworzyć plan kwerend w pamięci podręcznej plan wykonania kwerendy przy użyciu sp_create_plan_guide_from_handle procedura przechowywana.

USE AdventureWorks;
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 prowadnice plan po uaktualnieniu

Firma Microsoft zaleca re-evaluating i testowanie plan wykonania kwerendy definicje Przewodnik po uaktualnieniu aplikacji do nowej wersji programu SQL Server. Wymagania dotyczące dostosowywania działania i zachowanie odpowiedniego plan wykonania kwerendy przewodnik mogą ulec zmianie.Mimo że przewodnik nieprawidłowy plan wykonania kwerendy nie spowoduje, że kwerenda nie powiedzie się, plan jest kompilowany bez korzystania z plan wykonania kwerendy i nie może być najlepszym wyborem.Po uaktualnieniu bazy danych SQL Server 2008, zaleca się wykonanie następujących zadań: