Freigeben über

sp_create_plan_guide (Transact-SQL)

  • Artikel

Aktualisiert: 15. September 2007

Erstellt eine Planhinweisliste für die Zuordnung von Abfragehinweisen zu Abfragen in einer Datenbank. Weitere Informationen zu Planhinweislisten finden Sie unter Optimieren von Abfragen in bereitgestellten Anwendungen mit Planhinweislisten.

Themenlink (Symbol)Transact-SQL-Syntaxkonventionen

Syntax

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 ] )' | NULL }

Argumente

  • [ @name= ] N'plan_guide_name'
    Gibt den Namen für die Identifizierung der Planhinweisliste an. Die Gültigkeit der Namen von Planhinweislisten beschränkt sich auf die aktuelle Datenbank. plan_guide_name muss die Regeln für Bezeichner erfüllen und kann nicht mit dem Nummernzeichen (#) beginnen.

  • [ @stmt= ] N'statement_text'
    Ist eine Transact-SQL-Anweisung, für die eine Planhinweisliste erstellt werden soll. Erkennt der SQL Server-Abfrageoptimierer eine Abfrage, die mit statement_text übereinstimmt, tritt plan_guide_name in Kraft. Damit eine Planhinweisliste erfolgreich erstellt werden kann, muss statement_text in dem von den Parametern @type, @module_or_batch und @params angegebenen Kontext enthalten sein.

    statement_text muss so angegeben werden, dass SQL Server eine Übereinstimmung mit der entsprechenden Anweisung feststellen kann, die in dem von @module_or_batch und @params identifizierten Batch oder Modul bereitgestellt wird. statement_text wird in ein internes Standardformat konvertiert, bevor SQL Server eine derartige Übereinstimmung festzustellen versucht (Leerzeichen, Kommentare und Groß-/Kleinschreibung von Schlüsselwörtern werden nicht berücksichtigt). Weitere Informationen finden Sie im Abschnitt mit Hinweisen. Die Größe von statement_text wird nur durch den verfügbaren Speicherplatz des Servers eingeschränkt.

  • [ @type = ] N'{ OBJECT | SQL | TEMPLATE }'
    Ist der Typ der Entität, in der statement_text enthalten ist. Damit wird der Kontext angegeben, in dem die Übereinstimmung von statement_text und plan_guide_name festgestellt werden soll.

    • OBJECT
      Gibt an, dass statement_text im Kontext einer gespeicherten Transact-SQL-Prozedur, Skalarfunktion, aus mehreren Anweisungen bestehenden Funktion mit Tabellenrückgabe oder eines Transact-SQL-DML-Triggers in der aktuellen Datenbank enthalten ist.
    • SQL
      Gibt an, dass statement_text im Kontext einer eigenständigen Anweisung oder eines eigenständigen Batches enthalten ist, die bzw. der über einen beliebigen Mechanismus an SQL Server übermittelt werden kann. Transact-SQL-Anweisungen, die von CLR-Objekten (Common Language Runtime), erweiterten gespeicherten Prozeduren oder mithilfe von EXEC N'sql_string' übermittelt werden, werden als Batches auf dem Server verarbeitet und sollten deshalb als @type ='SQL' identifiziert werden. Wird SQL angegeben, kann der PARAMETERIZATION { FORCED | SIMPLE }-Abfragehinweis im @hints-Parameter nicht angegeben werden.
    • TEMPLATE
      Gibt an, dass die Planhinweisliste auf alle Abfragen angewendet wird, die zu dem in statement_text angegebenen Format parametrisiert werden. Wird TEMPLATE angegeben, kann nur der PARAMETERIZATION { FORCED | SIMPLE }-Abfragehinweis im @hints-Parameter angegeben werden. Weitere Informationen zu TEMPLATE-Planhinweislisten finden Sie unter Angeben des Abfrageparametrisierungsverhaltens mithilfe von Planhinweislisten.

  • [ @module_or_batch = ] { N'[ schema_name**.** ] object_name**'** | N'batch_text' | NULL }
    Gibt entweder den Namen des Objekts an, in dem statement_text vorkommt, oder den Batchtext, der statement_text enthält. Der Batchtext kann keine USE database-Anweisung enthalten.

    Damit eine Planhinweisliste mit einem von einer Anwendung bereitgestellten Batch übereinstimmt, muss batch_tex im gleichen Format, Zeichen für Zeichen, wie beim Übermitteln an SQL Server bereitgestellt werden. Es findet keine interne Konvertierung zur Vereinfachung der Übereinstimmung statt. Weitere Informationen finden Sie im Abschnitt mit Hinweisen.

    [schema_name.]object_name gibt den Namen einer gespeicherten Transact-SQL-Prozedur, Skalarfunktion, aus mehreren Anweisungen bestehenden Funktion mit Tabellenrückgabe oder eines Transact-SQL-DML-Triggers zurück, die bzw. der statement_text enthält. Wird schema_name nicht angegeben, verwendet schema_name das Schema des aktuellen Benutzers. Wird NULL angegeben und @type='SQL', wird der Wert von @module_or_batch auf den Wert von @stmt festgelegt. Wenn @type='TEMPLATE', muss @module_or_batch NULL sein.

  • [ @params = ] { N'@parameter_name data_type [ ,...n ]' | NULL }
    Gibt die Definitionen aller in statement_text eingebetteten Parameter an. @params ist nur anwendbar, wenn eine der folgenden Aussagen zutrifft:

    • @type='SQL' oder 'TEMPLATE'. Bei 'TEMPLATE' kann @params nicht NULL sein.
    • statement_text wird mithilfe von sp_executesql übermittelt, und ein Wert für den @params-Parameter wird angegeben, oder SQL Server übermittelt eine Anweisung intern, nachdem sie parametrisiert wurde. Die Übermittlung parametrisierter Abfragen von Datenbank-APIs (einschließlich ODBC, OLE DB und ADO.NET) werden in SQL Server als Aufrufe von sp_executesql oder von API-Servercursorroutinen angezeigt; deshalb können Übereinstimmungen auch von SQL- oder TEMPLATE-Planhinweislisten festgestellt werden. Weitere Informationen zur Parametrisierung und zu Planhinweislisten finden Sie unter Funktionsweise der Übereinstimmung von Planhinweislisten und Abfragen (SQL Server).

    @parameter_name data_type muss im exakt gleichen Format wie an SQL Server übermittelt werden, entweder mithilfe von sp_executesql oder durch internes Übermitteln nach der Parametrisierung. Weitere Informationen finden Sie im Abschnitt mit Hinweisen. Wenn der Batch keine Parameter enthält, muss NULL angegeben werden. Die Größe von @params wird nur durch den verfügbaren Arbeitsspeicher des Servers begrenzt.

  • [@hints = ] { **N'**OPTION **(**query_hint [ ,...n ] )' | NULL }
    Gibt eine OPTION-Klausel an, die an eine mit @stmt übereinstimmende Abfrage angefügt wird. @hints muss syntaktisch mit einer OPTION-Klausel in einer SELECT-Anweisung übereinstimmen und kann eine beliebige gültige Sequenz von Abfragehinweisen enthalten. NULL weist darauf hin, dass keine OPTION-Klausel angefügt wird. Weitere Informationen finden Sie unter OPTION-Klausel (Transact-SQL).

Hinweise

Die Argumente für sp_create_plan_guide müssen in der angezeigten Reihenfolge bereitgestellt werden. Wenn Sie Werte für die Parameter von sp_create_plan_guide angeben, müssen entweder alle oder überhaupt keine Parameternamen explizit angegeben werden. Wird z. B. @name = angegeben, müssen auch @stmt = , @type = usw. angegeben werden. Ebenso dürfen, wenn @name = nicht angegeben und nur der Parameterwert bereitgestellt wird, die übrigen Parameterwerte ebenfalls nicht angegeben und nur ihre Werte bereitgestellt werden. Argumentnamen dienen nur zu Beschreibungszwecken, zum besseren Verständnis der Syntax. SQL Server überprüft nicht, ob der angegebene Parametername mit dem Namen des Parameters in der Position übereinstimmt, in der der Name verwendet wird.

Es kann nur eine Planhinweisliste für eine bestimmte Kombination aus @module_or_batch und @stmt erstellt werden.

Planhinweislisten vom Typ OBJECT können nicht für einen @module_or_batch-Wert erstellt werden, der auf eine gespeicherte Prozedur, Funktion oder einen DML-Trigger verweist, in der bzw. dem die WITH ENCRYPTION-Klausel angegeben wird oder die bzw. der temporär ist.

Der Versuch, eine Funktion, gespeicherte Prozedur oder einen DML-Trigger zu löschen oder zu ändern, auf die bzw. den eine aktivierte oder deaktivierte Planhinweisliste verweist, führt zu einem Fehler. Auch der Versuch, eine Tabelle mit einem Trigger zu löschen, auf den eine Planhinweisliste verweist, führt zu einem Fehler.

ms179880.note(de-de,SQL.90).gifHinweis:
Planhinweislisten können nur in der Standard, Developer, Evaluation und Enterprise Edition von SQL Server verwendet werden, sie sind aber in allen Editionen sichtbar. Sie können auch in jeder Edition eine Datenbank anfügen, die Planhinweislisten enthält. Planhinweislisten bleiben beim Wiederherstellen oder Anfügen einer Datenbank in einer aktualisierten Version von SQL Server 2008 erhalten. Nach dem Serverupdate sollten Sie in jeder Datenbank prüfen, ob die Planhinweislisten wirklich erwünscht sind.

Anforderungen für Planhinweislisten

Bei Planhinweislisten, die @type='SQL' oder @type='TEMPLATE' angeben, müssen die Werte für batch_text und @parameter_name data_type [,...n ] in exakt dem gleichen Format wie die von der Anwendung übermittelten Gegenstücke bereitgestellt werden, damit eine Übereinstimmung mit einer Abfrage festgestellt werden kann. Das bedeutet, dass Sie den Batchtext genau so bereitstellen müssen, wie er vom SQL Server-Compiler empfangen wird. Mithilfe von SQL Server Profiler können Sie den eigentlichen Batch- und Parametertext erfassen. Weitere Informationen finden Sie unter Verwenden von SQL Server Profiler zum Erstellen und Testen von Planhinweislisten.

Wenn @type = 'SQL' und @module\_or\_batch auf NULL festgelegt sind, wird @module\_or\_batch auf den Wert @stmt festgelegt. Dies bedeutet, dass der Wert für statement_text im gleichen Format, Zeichen für Zeichen, wie beim Übermitteln an SQL Server bereitgestellt werden muss. Es findet keine interne Konvertierung zur Vereinfachung der Übereinstimmung statt.

Wenn SQL Server versucht, eine Übereinstimmung zwischen dem Wert von statement_textmit batch_text und @parameter_name data_type [,...n ] und dem Text der entsprechenden Abfrage in object_name festzustellen oder wenn @type='OBJECT' ist, werden die folgenden Zeichenfolgenelemente nicht berücksichtigt:

  • Leerzeichen (Tabstopps, Leerzeichen, Wagenrücklauf oder Zeilenvorschub) innerhalb der Zeichenfolge.
  • Kommentare (-- oder /* */).
  • Nachfolgende Semikolons

So kann SQL Server beispielsweise eine Übereinstimmung zwischen der statement_text-Zeichenfolge N'SELECT * FROM T WHERE a = 10' und dem folgenden batch_text feststellen:

N'SELECT *

FROM T

WHERE a=10'

Dieselbe Zeichenfolge würde diesem batch_text jedoch nicht zugeordnet werden:

N'SELECT * FROM T WHERE b = 10'

SQL Server ignoriert die Zeichen Wagenrücklauf und Zeilenvorschub sowie Leerzeichen in der ersten Abfrage. In der zweiten Abfrage wird die Sequenz WHERE b = 10 nicht auf die gleiche Art interpretiert wie WHERE a = 10. Bei der Feststellung der Übereinstimmung wird nach Groß- und Kleinschreibung sowie nach Akzenten unterschieden (selbst wenn die Sortierung der Datenbank die Groß-/Kleinschreibung nicht berücksichtigt), mit Ausnahme von Schlüsselwörtern, bei denen keine Unterscheidung nach Groß-/Kleinschreibung stattfindet. Bei der Feststellung der Übereinstimmung wird nicht nach verkürzten Formen von Schlüsselwörtern unterschieden. So werden beispielsweise die Schlüsselwörter EXECUTE, EXEC und execute als gleichwertig angesehen.

Weitere Informationen über das Feststellen der Übereinstimmung von Planhinweislisten und Abfragen finden Sie unter Optimieren von Abfragen in bereitgestellten Anwendungen mit Planhinweislisten.

Auswirkungen der Planhinweisliste auf den Plancache

Durch Erstellen einer Planhinweisliste in einem Modul wird der Abfrageplan für dieses Modul aus dem Plancache entfernt. Durch Erstellen einer Planhinweisliste vom Typ OBJECT oder SQL in einem Batch wird der Abfrageplan für einen Batch mit dem gleichen Hashwert entfernt. Durch Erstellen einer Planhinweisliste vom Typ TEMPLATE werden alle Batches mit einer einzigen Anweisung aus dem Plancache innerhalb dieser Datenbank entfernt.

Berechtigungen

Das Erstellen einer Planhinweisliste vom Typ OBJECT (durch Angeben von @type='OBJECT') setzt die ALTER-Berechtigung für das Objekt voraus, auf das verwiesen wird. Zum Erstellen einer Planhinweisliste vom Typ SQL oder TEMPLATE wird die ALTER-Berechtigung für die aktuelle Datenbank benötigt.

Beispiele

A. Erstellen einer Planhinweisliste vom Typ OBJECT für eine Abfrage in einer gespeicherten Prozedur

Im folgenden Beispiel wird eine Planhinweisliste erstellt, die einer im Kontext einer anwendungsbasierten gespeicherten Prozedur ausgeführten Abfrage zugeordnet wird, und der OPTIMIZE FOR-Hinweis auf die Abfrage angewendet.

Dies ist die gespeicherte Prozedur:

IF OBJECT_ID(N'Sales.GetSalesOrderByCountry', N'P') IS NOT NULL
    DROP PROCEDURE Sales.GetSalesOrderByCountry;
GO
CREATE PROCEDURE Sales.GetSalesOrderByCountry 
    (@Country 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;
END
GO

Dies ist die für die Abfrage in der gespeicherten Prozedur erstellte Planhinweisliste:

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',
    @type = N'OBJECT',
    @module_or_batch = N'Sales.GetSalesOrderByCountry',
    @params = NULL,
    @hints = N'OPTION (OPTIMIZE FOR (@Country = N''US''))';

B. Erstellen einer Planhinweisliste vom Typ SQL für eine eigenständige Abfrage

Im folgenden Beispiel wird eine Planhinweisliste erstellt, die einer Abfrage in einem Batch zugeordnet wird, der von einer Anwendung übermittelt wird, die die gespeicherte Systemprozedur sp_executesql verwendet.

Dies ist der Batch:

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

Erstellen Sie die folgende Planhinweisliste, damit kein zweiter Plan für die parallele Ausführung für diese Abfrage generiert wird:

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. Erstellen einer Planhinweisliste vom Typ TEMPLATE für die parametrisierte Form einer Abfrage

Im folgenden Beispiel wird eine Planhinweisliste erstellt, die mit einer beliebigen Abfrage übereinstimmt, die zu einer bestimmten Form parametrisiert wird. Außerdem wird SQL Server angewiesen, die Parametrisierung der Abfrage zu erzwingen. Die folgenden beiden Abfragen sind syntaktisch gleichwertig, unterscheiden sich jedoch in ihren konstanten Literalwerten.

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;

Dies ist die Planhinweisliste für die parametrisierte Form der Abfrage:

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

Im vorhergehenden Beispiel entspricht der Wert des @stmt-Parameters der parametrisierten Form der Abfrage. Die einzig zuverlässige Möglichkeit, diesen Wert für sp_create_plan_guide zu erhalten, besteht in der Verwendung der gespeicherten Systemprozedur sp_get_query_template. Mithilfe des folgenden Skripts kann sowohl die parametrisierte Abfrage abgerufen als auch anschließend eine Planhinweisliste dafür erstellt werden.

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)';
ms179880.note(de-de,SQL.90).gifWichtig:
Der Wert der konstanten Literale in dem an sp_get_query_template übergebenen @stmt-Parameter kann sich auf den Datentyp auswirken, der für den Parameter, der das Literal ersetzt, gewählt wird. Dies wirkt sich auf die Feststellung der Übereinstimmung durch die Planhinweisliste aus. Möglicherweise muss mehr als eine Planhinweisliste für verschiedene Parameterwertbereiche erstellt werden.

Weitere Informationen zum Abrufen der parametrisierten Form einer Abfrage für die Verwendung in einer auf TEMPLATE basierten Planhinweisliste finden Sie unter Entwerfen von Planhinweislisten für parametrisierte Abfragen.

D. Erstellen einer Planhinweisliste für eine Abfrage, die über eine API-Cursoranforderung übermittelt wird

Planhinweislisten können Übereinstimmungen für Abfragen feststellen, die von API-Servercursorroutinen übermittelt werden. Zu diesen Routinen gehören sp_cursorprepare, sp_cursorprepexec und sp_cursoropen. Anwendungen, die ADO-, OLE DB- und ODBC-APIs verwenden, arbeiten häufig mit SQL Server mithilfe von API-Servercursorn zusammen. Weitere Informationen finden Sie unter API-Servercursor. Das Aufrufen von API-Servercursorroutinen in SQL Server Profiler-Ablaufverfolgungen kann mithilfe des RPC:Starting-Ablaufverfolgungsereignisses angezeigt werden.

Angenommen, die folgenden Daten werden in einem RPC:Starting-Ablaufverfolgungsereignis für eine Abfrage angezeigt, die mithilfe einer Planhinweisliste optimiert werden soll:

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;

Sie stellen fest, dass im Plan für die SELECT-Abfrage im Aufruf von sp_cursorprepexec eine Mergeverknüpfung verwendet wird, Sie möchten jedoch eine Hashverknüpfung verwenden. Die mithilfe von sp_cursorprepexec übermittelte Abfrage ist parametrisiert, einschließlich einer Abfragezeichenfolge und einer Parameterzeichenfolge. Sie können die folgende Planhinweisliste erstellen, um die Wahl des Planes zu ändern, indem die Abfrage- und Parameterzeichenfolgen, Zeichen für Zeichen, so wie sie angezeigt werden, im Aufruf von sp_cursorprepexec verwendet werden.

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

Diese Planhinweisliste wirkt sich auf nachfolgende Ausführungen dieser Abfrage durch die Anwendung aus, und eine Hashverknüpfung wird zur Verarbeitung der Abfrage verwendet.

Informationen zum Verwenden des USE PLAN-Abfragehinweises in einer Planhinweisliste für eine mit einem Cursor übermittelte Abfrage finden Sie unter Verwenden des USE PLAN-Abfragehinweises für Abfragen mit Cursorn.

Siehe auch

Verweis

sp_control_plan_guide (Transact-SQL)
sys.plan_guides
Gespeicherte Prozeduren für das Datenbankmodul (Transact-SQL)
Gespeicherte Systemprozeduren (Transact-SQL)

Hilfe und Informationen

Informationsquellen für SQL Server 2005

Änderungsverlauf

Version Verlauf

05. Dezember 2005
Neuer Inhalt:
  • Es wurde hinzugefügt, dass der Abfragehinweis PARAMETERIZATION { FORCED | SIMPLE } nicht für SQL-Planhinweislisten angegeben werden kann.
  • Im Abschnitt mit Hinweisen wurden die Richtlinien für die Syntaxreihenfolge und Konsistenz verdeutlicht.
Geänderter Inhalt:
  • Es wurde verdeutlicht, dass in Planhinweislisten vom Typ OBJECT nicht auf verschlüsselte oder temporäre Objekte verwiesen werden kann.

15. September 2007
Geänderter Inhalt:
  • Die Anforderungen für Planhinweislisten für statement_text, wenn @type = 'SQL' und @module_or_batch auf NULL festgelegt sind, wurden verdeutlicht.
  • Informationen zu den Auswirkungen des Erstellens von Planhinweislisten auf den Plancache wurden hinzugefügt.