sp_create_plan_guide (języka Transact-SQL)

Utworzenie przewodnik planu do kojarzenia wskazówki kwerendy lub planów kwerend rzeczywiste z kwerend w bazie danych.Aby uzyskać więcej informacji dotyczących planów, zobacz Opis planu prowadnic.

Języka Transact-SQL składni konwencje

Składnia

sp_create_plan_guide [ @name = ] N'plan_guide_name'
    , [ @stmt = ] N'statement_text'
    , [ @type = ] N'{ OBJECT | SQL | TEMPLATE }'
    , [ @module_or_batch = ]
      { 
                    N'[ schema_name. ] object_name'
        | N'batch_text'
        | NULL
      }
    , [ @params = ] { N'@parameter_name data_type [ ,...n ]' | NULL } 
    , [ @hints = ] { N'OPTION ( query_hint [ ,...n ] )' 
                 | N'XML_showplan'
                 | NULL }

Argumenty

• [ @ Nazwa = ] N'plan_guide_name"
  Jest nazwą przewodnik planu.Plan przewodnik nazwy są o zakresie do bieżącej bazy danych.plan_guide_namemuszą być zgodne z zasadami identyfikatorów i nie może rozpoczynać się znakiem numeru (#).Maksymalna długość plan_guide_name 124 znaków.

• [ @ stmt = ] N'statement_text"
  Jest Transact-SQL instrukcja tworzenia przewodnik planu.Gdy SQL Server optymalizator kwerendy rozpoznaje kwerendy, które odpowiadają statement_text, plan_guide_name ma wpływ.Przy tworzeniu przewodnik planu się powiodła statement_text musi znajdować się w kontekście określonego przez typu @, @ module_or_batch, i @ params parametry.

  statement_textmuszą być podane w sposób, który pozwala na optymalizator kwerendy zgodnie z odpowiedniej instrukcja dostarczonych w partia lub moduł identyfikowane przez @ module_or_batch i @ params.Aby uzyskać więcej informacji zobacz temat "Uwagi" sekcja.Rozmiar statement_text jest ograniczona jedynie przez ilość dostępnej pamięci serwera.

• [@ typu = ]N'{obiektu | SQL | SZABLON}'
  Typ obiekt , w którym jest statement_text jest wyświetlany.Określa kontekst dla dopasowania statement_text do plan_guide_name.

  • OBIEKT
    Wskazuje statement_text pojawia się w kontekście Transact-SQLprocedura składowana, wartość skalarnafunkcja, zostanie tabela-wycenione funkcjalub Transact-SQLwyzwalaczDML w bieżącej bazie danych.

  • SQL
    Wskazuje statement_text pojawia się w kontekście autonomicznych instrukcja lub partia mogły być przesyłane do SQL Server za pomocą żadnego mechanizmu.Transact-SQLsprawozdania przedłożone przez wspólne language runtime (CLR) obiektów lub rozszerzonych procedur przechowywanych lub szefowie N'sql_string", są przetwarzane jako instancje na serwerze i dlatego powinny zostać uznane za typu @ = 'SQL'.Jeżeli określono SQL kwerendy hint PARAMETRYZACJI {WYMUSZONY | PROSTE} nie może być określony w @ wskazówki parametru.

  • SZABLON
    Wskazuje przewodnik planu dotyczy każdą kwerendę, która parameterizes do formularza w statement_text.Jeśli szablon tylko PARAMETRYZACJI {WYMUSZONY | Wskazówka dotycząca kwerendy PROSTEJ} można określić w @ wskazówki parametru.Aby uzyskać więcej informacji na temat szablonu plan guides zobacz Określanie zachowania parametryzacji kwerendy przy użyciu prowadnic Plan.

• [@module_or_batch ={ N' schema_name. ] object_name' | N'batch_text' | WARTOŚĆ NULL.}
  Określa nazwę obiektu, w którym statement_text pojawi się, lub tekst partia , w której statement_text jest wyświetlany.Tekst partia nie może zawierać USEdatabase instrukcja.

  przewodnik planu do partia złożone z aplikacji batch_text muszą być podane w tym samym formacie znak do znaku, które są przedkładane SQL Server.Konwersja wewnętrznego nie jest wykonywane w celu ułatwienia tej zgodności.Aby uzyskać więcej informacji zobacz sekcję Spostrzeżenia.

  [schema_name.]object_name Określa nazwę Transact-SQL procedura składowana, wartość skalarna funkcja, zostanie tabela-wycenione funkcjalub Transact-SQL DML wyzwalacz, który zawiera statement_text.Jeśli schema_name nie jest określony, schema_name używa schematu bieżącego użytkownika.Jeśli podana wartość NULL i @ Typ = "SQL', wartość @ module_or_batch jest zestaw na wartość @ stmt.Jeśli @ Typ = "szablonu**'**, @ module_or_batch musi mieć wartość NULL.

• [ @params = { N' @parameter_name data_type , ...n ' | WARTOŚĆ NULL.}
  Określa definicje wszystkich parametrów, które są osadzone w statement_text.@ params stosuje się tylko po jednej z następujących obowiązuje:

  • @ Typ = "SQL' lub 'szablonu'.Jeśli 'szablonu', @ params nie może mieć wartości NULL.

  • statement_textprzedstawione za pomocą sp_executesql i wartość dla @ params parametr jest określony, lub SQL Server wewnętrznie przesyła instrukcja po parametryzacja typie.Przesyłanie kwerend parametrycznych z bazy danych API (włączając ODBC, OLE DBi ADO.NET) wydaje się SQL Server jako wywołania sp_executesql lub API serwera kursor procedur; dlatego ich może również zostać dopasowany przez SQL lub szablon plan linie pomocnicze.Aby uzyskać więcej informacji na temat prowadnic parametryzacji i plan zobacz Jak SQL Server dopasowuje Plan prowadnic do kwerend.

  @parameter_name data_type należy dostarczyć w dokładnie ten sam format, które są przedkładane SQL Server przy użyciu sp_executesql lub przestawionych parametryzacji wewnętrznie.Aby uzyskać więcej informacji zobacz sekcję Spostrzeżenia.Jeżeli partia zawiera parametry, należy określić wartość NULL.Rozmiar @ params jest ograniczona jedynie przez pamięci dostępnego serwera.

• [@hints = { N'OPTION (query_hint , ...n )' | N'XML_showplan' | WARTOŚĆ NULL.}

  • N'OPTION (query_hint , ...n )
    Określa OPCJĘ klauzula dołączanie do kwerendy, która odpowiada @ stmt.@ wskazówki musi nosić syntaktycznie opcja klauzula w instrukcjaSELECT i mogą zawierać prawidłową sekwencją podpowiedzi do kwerendy.

  • N'XML_showplan"
    Jest plan kwerend w formacie XML mają być stosowane jako wskazówkę.

    Zalecane jest przypisywanie XML Showplan do zmiennej; w przeciwnym razie należy escape wszelkie znaki pojedynczego cudzysłowu w Showplan poprzedzając je innym pojedynczego cudzysłowu.Zobacz przykład E.

  • NULL
    Wskazuje, że wszelkie istniejące wskazówkę dotyczącą określonego w opcji klauzula kwerendy nie jest stosowane do kwerendy.Aby uzyskać więcej informacji, zobacz Opcja klauzuli (Transact-SQL).

Uwagi

Argumenty do sp_create_plan_guide należy podawać w kolejności wyświetlanej.Kiedy podać wartości dla parametrów sp_create_plan_guide, wszystkie nazwy musi być jawnie określony parametr lub none.Na przykład jeśli @name = jest określony, następnie @stmt = , @type =, itd., musi być także określona.Podobnie jeśli @name = jest pominięty, a jedynie podać wartość parametru, pozostałe nazwy parametrów muszą być także pominięte w i dostarczane wyłącznie ich wartości.Nazwy argumentu są opisowe wyłącznie w celach pomóc w zrozumieniu składni.SQL Servernie sprawdza, czy określony parametr nazwa odpowiada nazwie parametru w miejscu, gdzie nazwa jest używana.

Można utworzyć więcej niż jednego obiektu lub SQL przewodnik planu dla tej samej kwerendy i partia lub moduł.Jednakże tylko jeden przewodnik planu mogą być włączone w danym czas.

Plan linii pomocniczych nie można utworzyć obiektu dla typu @ module_or_batch wartość, która odwołuje się do procedura składowana, funkcjalub wyzwalacza DML , który określa klauzula WITH ENCRYPTION lub który jest tymczasowa.

Próby drop lub zmodyfikowania funkcja, procedura składowanalub wyzwalacza DML , który jest wywoływany przez przewodnik planu, włączony lub wyłączony, powoduje błąd.Również próby upuść tabela , która ma wyzwalacz zdefiniowane na nim jest wywoływany przez przewodnik planu powoduje błąd.

Ostrzeżenie

Plan prowadnice mogą być używane tylko na SQL Server , deweloper, oceny, wersje; jednak plan prowadnice są widoczne w żadnej wersji.Można także dołączyć bazę danych, zawierającą plan guides do dowolnej wersji.Plan guides pozostają bez zmian podczas przywracanie lub dołączyć do uaktualnionej wersja bazy danych SQL Server 2008.Po wykonaniu uaktualnienia serwera należy sprawdzić celowość plan guides w każdej bazie danych.

Plan przewodnik spełniających wymagania

Dla planu linie pomocnicze, które określają @ Typ = "SQL' lub @ Typ ="szablonu' pomyślnie odpowiadającą kwerendzie wartości dla batch_text i @parameter_name data_type ,...n muszą być podane w tym samym formacie, jak ich odpowiedniki przedłożone przez aplikację.Oznacza to, musisz podać tekst partia dokładnie tak jak SQL Server kompilatora odbiera typie.Aby przechwytywać rzeczywistej partia i parametr tekst, można użyć SQL Server Profiler.Aby uzyskać więcej informacji, zobacz Za pomocą SQL Server Profiler, aby utworzyć i przetestować Plan prowadnic.

Gdy @ Typ = "SQL' i @ module_or_batch jest zestaw wartość null, wartość @ module_or_batch jest zestaw na wartość @ stmt.Oznacza to, że wartość dla statement_text w dokładnie ten sam format, należy podać znak do znaku, które są przedkładane SQL Server.Konwersja wewnętrznego nie jest wykonywane w celu ułatwienia tej zgodności.

Po SQL Server odpowiada wartości statement_text do batch_text i @parameter_name data_type ,...n , lub jeśli @ Typ = **"**obiektu', tekst kwerendy odpowiadające wewnątrz object_name, następujące elementy ciąg nie są uważane za:

• Znaki odstępu (karty, spacje, znaki powrotu karetki lub źródła wiersza) wewnątrz ciąg.

• Comments (-- or /* */).

• Końcowe średnikami

Na przykład SQL Server można dopasować statement_text ciąg N'SELECT * FROM T WHERE a = 10' do następującego batch_text:

N'SELECT *

FROM T

WHERE a=10'

Jednak ten sam ciąg nie zostaną dopasowane do tej batch_text:

N'SELECT * FROM T WHERE b = 10'

SQL Serverignoruje powrotu karetki, wysuwu wiersza i znaków spacji wewnątrz pierwszej kwerendy.W drugiej kwerendy sekwencji WHERE b = 10 jest interpretowane inaczej niż WHERE a = 10.Dopasowanie jest przypadek- i accent-sensitive (nawet jeśli sortowanie bazy danych jest przypadek-niewrażliwe), z wyjątkiem przypadek słowa kluczowe, w przypadku gdy przypadek jest wielkość liter.Dopasowanie jest niewrażliwe skróconej formy słów kluczowych.Na przykład słowa kluczowe EXECUTE, EXEC, i execute są uważane za równoważne.

Aby uzyskać więcej informacji na temat jak plan prowadnice są dopasowywane do kwerendy, zobacz Optymalizacja kwerend w wdrożonych aplikacji za pomocą prowadnic Plan.

Plan przewodnik wpływ na Plan pamięci podręcznej

Tworzenie przewodnik planu na module usuwa plan kwerend dla tego modułu z pamięci podręcznej planu.Tworzenie przewodnik planu typu obiektu lub SQL partia usuwa plan kwerend dla partia , która ma taką samą wartość mieszania.Tworzenie przewodnik planu typu szablonu powoduje usunięcie wszystkich partiiinstrukcja jedno - z pamięci podręcznej planu w tej bazie danych.

Uprawnienia

Aby utworzyć przewodnik planu typu obiektu, wymaga uprawnień ALTER obiektu, do którego istnieje odwołanie.Aby utworzyć przewodnik planu typu SQL lub szablonu, wymaga uprawnień zmiany w bieżącej bazie danych.

Przykłady

A.Tworzenie przewodnik planu typu OBJECT dla kwerendy w procedura składowana

Poniższy przykład tworzy odpowiada kwerenda została wykonana w kontekście aplikacji opartych na procedura składowana przewodnik planu i stosuje się OPTIMIZE FOR wskazówkę dotyczącą kwerendy.

Oto procedura składowana:

IF OBJECT_ID(N'Sales.GetSalesOrderByCountry', N'P') IS NOT NULL
    DROP PROCEDURE Sales.GetSalesOrderByCountry;
GO
CREATE PROCEDURE Sales.GetSalesOrderByCountry 
    (@Country_region nvarchar(60))
AS
BEGIN
    SELECT *
    FROM Sales.SalesOrderHeader AS h 
    INNER JOIN Sales.Customer AS c ON h.CustomerID = c.CustomerID
    INNER JOIN Sales.SalesTerritory AS t 
        ON c.TerritoryID = t.TerritoryID
    WHERE t.CountryRegionCode = @Country_region;
END
GO

przewodnik planu utworzony na kwerendę w procedura składowanajest następujący:

EXEC sp_create_plan_guide 
    @name =  N'Guide1',
    @stmt = N'SELECT *
              FROM Sales.SalesOrderHeader AS h 
              INNER JOIN Sales.Customer AS c 
                 ON h.CustomerID = c.CustomerID
              INNER JOIN Sales.SalesTerritory AS t 
                 ON c.TerritoryID = t.TerritoryID
              WHERE t.CountryRegionCode = @Country_region',
    @type = N'OBJECT',
    @module_or_batch = N'Sales.GetSalesOrderByCountry',
    @params = NULL,
    @hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))';

B.Tworzenie przewodnik planu typu SQL dla kwerendy autonomiczny

Poniższy przykład tworzy przewodnik planu pasuje do kwerendy w partia przedłożone przez aplikację, która korzysta z sp_executesql systemu procedura składowana.

Oto partia:

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

Aby zapobiec sytuacji, w której plan wykonywanie równoległe generowany na tej kwerendzie, należy utworzyć następujące przewodnik planu:

EXEC sp_create_plan_guide 
    @name = N'Guide1', 
    @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)';

C.Tworzenie przewodnik planu typu szablonu sparametryzowana formularz kwerendy

Poniższy przykład tworzy pasuje do kwerendy parameterizes do określonego formularza i kieruje przewodnik planu SQL Server do wymuszenia 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ść dla @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 systemu 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 mieć wpływ na typ danych, która została wybrana dla parametru, który zastępuje literal.Wpłynie to na dopasowanie przewodnik planu .Może być konieczne utworzenie więcej niż jeden przewodnik planu do obsługi zakresy wartości różnych parametrów.

Aby uzyskać więcej informacji dotyczących sposobu uzyskiwania sparametryzowana formularza kwerendy w opartym na SZABLONIE przewodnik planu, zobacz Projektowanie przewodników planu kwerend parametrycznych.

D.Tworzenie przewodnik planu na kwerendzie przedstawione za pomocą żądaniakursor API

Plan prowadnic można dopasować do kwerend, które są przesyłane z procedur kursor serwera API .Procedury te obejmują sp_cursorprepare, sp_cursorprepexec, i sp_cursoropen.Aplikacje używające ADO, OLE DBi funkcji interfejsu API ODBC często interakcji z SQL Server za pomocą API serwera kursory.Aby uzyskać więcej informacji, zobacz Kursory API serwera.Zobacz wywołania API serwera kursor procedur w SQL Server Profiler śladów wyświetlając RPC:Starting profiler śledzenia zdarzenie.

Załóżmy, że następujące dane są wyświetlane w RPC:Starting profiler śledzenia zdarzenie dla kwerendy, aby dostroić przewodnik planu:

DECLARE @p1 int;
SET @p1=-1;
DECLARE @p2 int;
SET @p2=0;
DECLARE @p5 int;
SET @p5=4104;
DECLARE @p6 int;
SET @p6=8193;
DECLARE @p7 int;
SET @p7=0;
EXEC sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(255),@P2 varchar(255)',N'SELECT * FROM Sales.SalesOrderHeader h INNER JOIN AdventureWorks2008R2.Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID WHERE h.OrderDate >= @P1 AND <= @P2',@p5 OUTPUT,@p6 OUTPUT,@p7 OUTPUT,'20040101','20050101'
SELECT @p1, @p2, @p5, @p6, @p7;

Zauważ, że plan dla SELECT kwerendy w wywołaniu sp_cursorprepexec jest za pomocą korespondencji seryjnej łączyć, ale chcesz używać skrótu łączyć.Kwerenda przedstawione za pomocą sp_cursorprepexec jest opatrzone tym ciąg kwerendy i parametru ciąg.Można tworzyć następujące przewodnik planu Aby zmienić wybór planu przy użyciu kwerendy i ciągi parametr dokładnie tak, jak są wyświetlane znaków dla znaku w wywołaniu sp_cursorprepexec.

EXEC sp_create_plan_guide 
    @name = N'APICursorGuide',
    @stmt = N'SELECT * FROM Sales.SalesOrderHeader AS h 
              INNER JOIN Sales.SalesOrderDetail AS d 
                ON h.SalesOrderID = d.SalesOrderID 
              WHERE h.OrderDate >= @P1 AND <= @P2',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = N'@P1 varchar(255),@P2 varchar(255)',
    @hints = N'OPTION(HASH JOIN)';

Następne wykonania tej kwerendy przez aplikację będzie miała wpływ ten przewodnik planui mieszania łączyć będą używane do przetwarzania kwerendy.

Aby uzyskać informacje dotyczące używania wskazówkę dotyczącą kwerendy za pomocą planu w przewodnik planu dla kwerendy, złożone z kursor, zobacz Za pomocą planu użycia kwerendy wskazówkę dotyczącą na kwerendach z kursorów.

E.Tworzenie przewodnik planu uzyskując XML Showplan z buforowanego planu

Poniższy przykład tworzy przewodnik planu SQLad hoc prostainstrukcja. Plan kwerend odpowiedni dla tej instrukcja podano w przewodnik planu przez określenie Showplan XML dla bezpośrednio w programie query @hints parametru.Przykład wykonuje najpierw SQL instrukcja 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 zmienną.@xml_showplan Zmienna jest następnie przekazywany do sp_create_plan_guideinstrukcja w @hints parametru.Lub przewodnik planu z planu kwerend w pamięci podręcznej 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

Zobacz także

Odwołanie

sp_control_plan_guide (języka Transact-SQL)

sys.plan_guides (języka Transact-SQL)

Aparat bazy danych przechowywane procedury (języka Transact-SQL)

System przechowywane procedury języka Transact-SQL)

sys.dm_exec_sql_text (języka Transact-SQL)

sys.dm_exec_cached_plans (języka Transact-SQL)

sys.dm_exec_query_stats (języka Transact-SQL)

sp_create_plan_guide_from_handle (języka Transact-SQL)

fn_validate_plan_guide (języka Transact-SQL)

sp_get_query_template (języka Transact-SQL)