sp_create_plan_guide (języka Transact-SQL)

Powoduje utworzenie plan wykonania kwerendy do kojarzenia wskazówki kwerendy lub planów kwerend rzeczywiste z kwerendy w bazie danych.Aby uzyskać więcej informacji na temat prowadnic planu zobacz Understanding Plan Guides.

Konwencje składni języka Transact-SQL

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"
  To nazwa plan wykonania kwerendy.Plan guide names are scoped to the current database.plan_guide_name must comply with the rules for identifiers and cannot start with the number sign (#).Maksymalna długość plan_guide_name jest 124 znaków.

• [@ stmt =] N ' statement_text"
  Czy Transact-SQL Instrukcja przeciwko którym w celu utworzenia plan wykonania kwerendy. Gdy SQL Server optymalizator kwerendy rozpoznaje kwerendę, która pasuje do statement_text, plan_guide_name zostanie zastosowana. Do tworzenia plan wykonania kwerendy wykonania kwerendy się powiodła, statement_text musi znajdować się w kontekście, określony przez typ @, @ module_or_batch i @ params parametrów.

  statement_text należy podać w sposób umożliwiający optymalizator kwerendy w celu dopasowania go z odpowiedniej instrukcja dostarczone w partia lub moduł, identyfikowane przez @ module_or_batch i @ params.Aby uzyskać więcej informacji zobacz sekcję "Uwagi".Rozmiar statement_text jest ograniczona jedynie przez ilość dostępnej pamięci serwera.

• [@type = ]N'{ OBJECT | SQL | TEMPLATE }'
  Jest to typ obiektu, w którym statement_text zostanie wyświetlone. Określa kontekst dla dopasowania statement_text Aby plan_guide_name.

  • OBIEKT
    Wskazuje statement_text pojawia się w kontekście Transact-SQL przechowywane procedury, funkcja skalarna, wieloma instrukcjami funkcji wycenione tabela lub Transact-SQL Wyzwalacz DML w bieżącej bazie danych.

  • SQL
    Indicates statement_text appears in the context of a stand-alone statement or batch that can be submitted to SQL Server through any mechanism.Transact-SQL statements submitted by common language runtime (CLR) objects or extended stored procedures, or by using EXEC N'sql_string', are processed as batches on the server and, therefore, should be identified as @type = 'SQL'.Jeżeli określono SQL, kwerendy hint PARAMETRYZACJI {FORCED | SIMPLE} nie może być określony przez parametr @ wskazówek.

  • SZABLON
    Wskazuje plan wykonania kwerendy odnosi się do dowolnej kwerendy, która parameterizes do formularza w statement_text. Jeżeli określono TEMPLATE, tylko PARAMETRYZACJI {FORCED | SIMPLE} wskazówki dotyczącej kwerendy może być określony w parametrze @ wskazówek.Aby uzyskać więcej informacji na temat TEMPLATE planu prowadnic zobacz Specifying Query Parameterization Behavior by Using Plan Guides.

• [@module_or_batch =]{ N'[ schema_name. ] object_name' | N 'batch_text' | NULL}
  Określa nazwę obiektu, w którym statement_text zostanie wyświetlone, lub tekst partia, w którym statement_text zostanie wyświetlone. partia Tekst nie może zawierać USEdatabase Instrukcja.

  Podręcznik plan wykonania kwerendy odpowiadający złożony z aplikacją, zadanie partia batch_texw tym samym formacie, należy podać t znak do znaku, jak jest przesyłany do SQL Server. Wewnętrzny konwersja nie jest wykonywana w celu ułatwienia tego dopasowania.Aby uzyskać więcej informacji zobacz sekcję Spostrzeżenia.

  [schema_name.]object_name specifies the name of a Transact-SQL stored procedure, scalar function, multistatement table-valued function, or Transact-SQL DML trigger that contains statement_text.Jeśli schema_name nie określono, schema_name używa schematu z bieżącego użytkownika. Jeśli określona została wartość NULL i @ typu = SQL, wartość @ module_or_batch zestaw wartość @ stmt.Jeśli @ typ = "TEMPLATE**"**, @ module_or_batch musi mieć wartość NULL.

• [ @params = ]{ N'@parameter_name data_type [ ,...n ]' | NULL }
  Określa definicje wszystkich parametrów, które są osadzone w statement_text. @ params stosuje się tylko wtedy, gdy jeden z następujących czynności jest prawdziwe:

  • @ typu = 'SQL' lub 'TEMPLATE'.Jeśli 'TEMPLATE' @ params nie może być NULL.

  • statement_text jest przedstawione za pomocą sp_executesql i wartość dla parametru @ params jest określony, lub SQL Server przesyła wewnętrznie instrukcja po parametryzacja go. Interfejsy API (łącznie z ODBC, OLE DB i ADO.NET) pojawiają się do przesyłania kwerend parametrycznych z bazy danych SQL Server jak wywołuje sp_executesql lub interfejsu API serwera kursor procedur, dlatego te można również dopasować SQL lub TEMPLATE planowanie prowadnic. Aby uzyskać więcej informacji na temat parametryzacji i prowadnice planu zobacz How SQL Server Matches Plan Guides to Queries.

  @parameter\_name data_type należy podać dokładnie w tym samym formacie, co jest przesyłany do SQL Server albo za pomocą sp_executesql lub złożone wewnętrznie po parametry. Aby uzyskać więcej informacji zobacz sekcję Spostrzeżenia.Jeśli partia nie zawiera parametry, należy określić wartość NULL.Rozmiar @ params jest ograniczona tylko przez pamięci dostępnego serwera.

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

  • N'OPTION (query_hint ,...n ] )
    Określa OPCJĘ klauzula dołączanie do kwerendy, która odpowiada @ stmt.wskazówki dotyczące @ musi być składniowo taki sam, jak OPCJĘ klauzula w instrukcja SELECT i może zawierać dowolną prawidłową sekwencję wskazówki kwerendy.

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

    Zalecane jest przypisywanie do zmiennej plan wykonania XML; w przeciwnym razie, wszystkie znaki pojedynczego cudzysłowu w plan wykonania musi escape przez poprzedzający je z innego pojedynczy znak cudzysłowu.Zobacz przykład E.

  • WARTOŚCI NULL
    Wskazuje, że wszelkie istniejące wskazówkę dotyczącą określonych w klauzula OPTION kwerendy nie są stosowane do kwerendy.Aby uzyskać więcej informacji zobacz Klauzula OPTION (języka Transact-SQL).

Remarks

Argumenty, które mają sp_create_plan_guide należy podać w kolejności, która jest wyświetlana.Gdy znasz wartości dla parametrów sp_create_plan_guide, wszystkie parametr nazwy muszą być określone jawnie lub brak wcale. Na przykład jeśli @name = jest określony, następnie @stmt = , @type =i tak dalej, musi być także określona. Podobnie jeśli @name = jest pominięty i tylko pod warunkiem że wartość parametru, pozostałe nazwy parametrów muszą również być pominięte i dostarczane tylko ich wartości. Argument names are for descriptive purposes only, to help understand the syntax.SQL Server does not verify that the specified parameter name matches the name for the parameter in the position where the name is used.

Można utworzyć więcej niż jeden obiekt lub SQL plan wykonania kwerendy dla tej samej kwerendy i partia lub moduł.Jednak tylko jedna plan wykonania kwerendy można włączyć w danej chwili.

Prowadnice plan typu OBJECT nie można utworzyć przy wartości @ module_or_batch odwołujący się do procedura przechowywana, funkcja lub DML wyzwalacz, który określa klauzulę WITH szyfrowanie, czy jest tymczasowa.

Próbujesz usunąć lub zmodyfikować funkcja, procedura przechowywana lub wyzwalacz DML, do której odwołuje się plan wykonania kwerendy, albo włączony lub wyłączony, spowoduje wystąpienie błędu.Również próby upuścić tabela, która została zdefiniowana na nim wyzwalacz, do której odwołuje się plan wykonania kwerendy spowoduje wystąpienie błędu.

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. Po wykonaniu uaktualnienia serwera należy sprawdzić celowość prowadnic planu w każdej bazy danych.

plan wykonania kwerendy Spełniających wymagania

For plan guides that specify @type = 'SQL' or @type = 'TEMPLATE' to successfully match a query, the values for batch_text and @parameter\_name data_type [,... n ] must be provided in exactly the same format as their counterparts submitted by the application.This means you must provide the batch text exactly as the SQL Server compiler receives it.Aby przechwytywać partia rzeczywisty i tekst parametru, można użyć SQL Server Profiler. Aby uzyskać więcej informacji zobacz Using SQL Server Profiler to Create and Test Plan Guides.

Kiedy @ typu = 'SQL' i @ module_or_batch jest ustawiona na wartość NULL, wartość @ module_or_batch jest ustawiona na wartość @ stmt.Oznacza to, że wartość statement_text należy podać w dokładnie ten sam format, znak do znaku, jak jest przesyłany do SQL Server. Wewnętrzny konwersja nie jest wykonywana w celu ułatwienia tego dopasowania.

Kiedy SQL Server pasuje do wartości statement_text Aby batch_text i @parameter\_name data_type ,... n ], or if @type = 'OBJECT', to the text of the corresponding query inside object_name, the following string elements are not considered:

• Znaki odstępu (tabulatorów, spacji, znaki powrotu karetki i znaki nowego wiersza) wewnątrz ciąg znaków.

• Komentarze)-- or /* */).

• Końcowe średnikami.

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

N'SELECT *

OD T

WHERE a = 10 '

Jednak ten sam ciąg nie będzie można dopasować do tej batch_text:

N'SELECT * FROM T WHERE b = 10 '

SQL Server ignoruje powrót karetki, wysuwu wiersza i znaków spacji wewnątrz pierwszej kwerendy.W drugą kwerendę w sekwencji WHERE b = 10 jest interpretowane inaczej niż WHERE a = 10. Dopasowanie jest przypadek i akcentów (nawet wtedy, gdy sortowanie bazy danych jest rozróżniana wielkość liter), chyba że przypadek wystąpienia słowa kluczowe, gdzie jest niewrażliwe przypadek.Dopasowanie jest niewrażliwe skróconej formy słów kluczowych.Na przykład słowa kluczowe EXECUTE, EXEC, a execute są uznawane za równorzędne.

Aby uzyskać więcej informacji na temat sposobu planu prowadnice są dopasowywane do kwerendy Zobacz Optimizing Queries in Deployed Applications by Using Plan Guides.

Efekt plan wykonania kwerendy wykonania kwerendy w pamięci podręcznej plan

Tworzenie plan wykonania kwerendy na module usuwa plan kwerend dla tego modułu z pamięci podręcznej plan wykonania kwerendy.Tworzenie plan wykonania kwerendy typu OBJECT lub SQL na zadanie partia usuwa plan kwerend dla partia, który ma taką samą wartość mieszania.Tworzenie prowadnicy plan typu TEMPLATE usuwa wszystkie instancje pojedynczą instrukcją z pamięci podręcznej plan wykonania kwerendy w tej bazie danych.

Uprawnienia

Do utworzenia plan wykonania kwerendy typu OBJECT, wymaga uprawnienia ALTER na obiekcie, do którego istnieje odwołanie.Do utworzenia plan wykonania kwerendy typu SQL lub TEMPLATE, wymaga uprawnienia ALTER w bieżącej bazie danych.

Przykłady

A.Tworzenie prowadnicy plan wykonania kwerendy z wpisz OBJECT dla kwerendy w procedura przechowywana

W poniższym przykładzie tworzy prowadnicy plan wykonania kwerendy odpowiadający kwerendy wykonywane w odniesieniu do procedura przechowywana opartych na aplikacji i stosuje się OPTIMIZE FOR Wskazówka dotycząca kwerendy.

Poniżej przedstawiono procedura przechowywana:

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

W tym polu jest plan wykonania kwerendy utworzony na kwerendę w procedura przechowywana:

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 plan wykonania kwerendy typu programu SQL dla kwerendy autonomiczny

Poniższy przykład tworzy plan wykonania kwerendy do kwerendy we wsadzie przesłane przez aplikację, która korzysta z systemu sp_executesql procedury przechowywanej.

Oto partia:

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, należy utworzyć następujące plan wykonania kwerendy wykonania kwerendy:

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 prowadnicy plan typu TEMPLATE sparametryzowana formularza kwerendy

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.

Aby uzyskać więcej informacji dotyczących sposobu uzyskiwania sparametryzowana postaci kwerendy do użycia w podstawie TEMPLATE plan wykonania kwerendy zobacz Designing Plan Guides for Parameterized Queries.

D.Tworzenie plan wykonania kwerendy na podstawie kwerendy przedstawione za pomocą żądanie API kursor

Plan prowadnic można dopasować do kwerendy, które są przesyłane z kursor serwera interfejsu API procedur.Procedury te zawierają sp_cursorprepare sp_cursorprepexec i sp_cursoropen.Aplikacje, które często korzystać z obiektów ADO, OLE DB i interfejsów API ODBC interaktywnie SQL Server za pomocą interfejsu API serwera kursorów. Aby uzyskać więcej informacji zobacz API Server Cursors. Można wyświetlić wywołania interfejsu API serwera kursor procedur w SQL Server Profiler ślady, wyświetlając RPC: uruchamianie śledzenia programu profilującego zdarzenie.

Załóżmy, że następujące dane są wyświetlane w RPC: uruchamianie śledzenia programu profilującego zdarzenie dla kwerendy, aby dostroić z plan wykonania kwerendy:

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 AdventureWorks.Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID WHERE h.OrderDate BETWEEN @P1 AND @P2',@p5 OUTPUT,@p6 OUTPUT,@p7 OUTPUT,'20040101','20050101'
SELECT @p1, @p2, @p5, @p6, @p7;

Należy zauważyć, że plan SELECT kwerendy w wywołaniu sp_cursorprepexec jest za pomocą łączyć korespondencji seryjnej, ale chcesz używać łączyć mieszania. Przedstawione za pomocą kwerendy sp_cursorprepexec jest sparametryzowana, włączając w to zarówno w ciąg kwerendy, jak i w ciąg parametru. Można utworzyć następujące plan wykonania kwerendy wykonania kwerendy zmienić wybór plan wykonania kwerendy przy użyciu ciągów kwerendy i parametru, dokładnie tak samo, 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 BETWEEN @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ę dotyczy niniejszy plan wykonania kwerendy wykonania kwerendy, a łączyć mieszania mają być używane do przetwarzania kwerendy.

Aby dowiedzieć się, jak używać wskazówki dotyczącej kwerendy USE PLAN w plan wykonania kwerendy kwerendy z kursor zobacz Using the USE PLAN Query Hint on Queries with Cursors.

E.Tworzenie plan wykonania kwerendy uzyskując plan wykonania XML z buforowanego plan wykonania 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 sys.dm_exec_query_stats, sys.dm_exec_sql_text, a sys.dm_exec_text_query_plan dynamiczne zarządzanie przegląda 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

See Also

Reference

sp_control_plan_guide (Transact-SQL)

sys.plan_guides (języka Transact-SQL)

Database Engine Stored Procedures (Transact-SQL)

System Stored Procedures (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 (Transact-SQL)

sp_create_plan_guide_from_handle (języka Transact-SQL)

sys.fn_validate_plan_guide (Transact-SQL)

sp_get_query_template (Transact-SQL)