Planhinweislisten

Mit Planhinweislisten können Sie die Leistung von Abfragen optimieren, wenn Sie den Text der eigentlichen Abfrage in SQL Server 2012 nicht direkt ändern möchten oder können. Planhinweislisten beeinflussen die Abfrageoptimierung, indem Abfragehinweise oder ein fester Abfrageplan an die Abfragen angefügt werden. Die Verwendung von Planhinweislisten bietet sich z. B. an, wenn eine kleine Teilmenge von Abfragen in der Datenbankanwendung eines Drittanbieters nicht erwartungsgemäß funktioniert. In der Planhinweisliste geben Sie die Transact-SQL-Anweisung an, die optimiert werden soll, sowie entweder eine OPTION-Klausel mit den zu verwendenden Abfragehinweisen oder einen spezifischen Abfrageplan, der für die Optimierung der Abfrage verwendet werden soll. Wenn die Abfrage ausgeführt wird, vergleicht SQL Server die Transact-SQL-Anweisung mit der Planhinweisliste und fügt der Abfrage entweder zur Laufzeit die OPTION-Klausel hinzu oder verwendet den angegebenen Abfrageplan.

Die maximale Anzahl der erstellbaren Planhinweislisten ist lediglich durch die verfügbaren Systemressourcen begrenzt. Planhinweislisten sollten jedoch nur begrenzt für unternehmenswichtige Abfragen verwendet werden, deren Leistung verbessert oder stabilisiert werden soll. Planhinweislisten sollten nicht verwendet werden, um die überwiegende Abfragelast einer bereitgestellten Anwendung zu beeinflussen.

Hinweis

Planhinweislisten können nicht in jeder Edition von Microsoft SQL Server verwendet werden. Eine Liste der Funktionen, die von den Editionen von SQL Server unterstützt werden, finden Sie unter Von den SQL Server 2012-Editionen unterstützte Funktionen. Planhinweislisten sind in jeder Edition sichtbar. Sie können auch in allen Versionen eine Datenbank anfügen, die Planhinweislisten enthält. Planhinweislisten bleiben beim Wiederherstellen oder Anfügen einer Datenbank in einer aktualisierten Version von SQL Server erhalten.

Typen von Planhinweislisten

Die folgenden Typen von Planhinweislisten können erstellt werden.

• OBJECT-Planhinweisliste
  OBJECT-Planhinweislisten dienen zum Abgleich von Abfragen, die im Kontext von gespeicherten Transact-SQL-Prozeduren, benutzerdefinierten Skalarfunktionen, benutzerdefinierten Tabellenwertfunktionen mit mehreren Anweisungen und von DML-Triggern ausgeführt werden.

  Angenommen, die folgende gespeicherte Prozedur, die den @Country\_region-Parameter annimmt, existiert in einer Datenbankanwendung, die in der AdventureWorks2012 -Datenbank bereitgestellt wird:

  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;
  

  Gehen Sie weiterhin davon aus, dass diese gespeicherte Prozedur kompiliert und für @Country\_region = N'AU' (Australien) optimiert wurde. Aus Australien kommen jedoch nur relativ wenige Bestellungen. Wenn die Abfrage mit Parameterwerten für Länder oder Regionen mit mehr Bestellungen ausgeführt wird, verringert dies die Leistung. Da die meisten Bestellungen aus den USA kommen, würde ein für @Country\_region = N'US' generierter Abfrageplan wahrscheinlich eine bessere Leistung für alle möglichen Werte des @Country\_region-Parameters erbringen.

  Sie könnten dieses Problem lösen, indem Sie die gespeicherte Prozedur so ändern, dass der Abfrage der OPTIMIZE FOR-Abfragehinweis hinzugefügt wird. Da sich die gespeicherte Prozedur jedoch in einer bereitgestellten Anwendung befindet, können Sie den Code der Anwendung nicht direkt ändern. Stattdessen können Sie in der AdventureWorks2012 -Datenbank die folgende Planhinweisliste erstellen.

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

  Wenn die in der sp_create_plan_guide-Anweisung angegebene Abfrage ausgeführt wird, wird sie vor der Optimierung so geändert, dass sie die OPTIMIZE FOR (@Country = N''US'')-Klausel enthält.

• SQL-Planhinweisliste
  SQL-Planhinweislisten dienen zum Abgleich von Abfragen, die im Kontext von eigenständigen Transact-SQL-Anweisungen und -Batches, die nicht Teil eines Datenbankobjekts sind, ausgeführt werden. SQL-basierte Planhinweislisten können auch zum Abgleich von Abfragen verwendet werden, die in einer bestimmten Form parametrisiert werden. SQL-Planhinweislisten werden für eigenständige Transact-SQL-Anweisungen und -Batches verwendet. Diese Anweisungen werden häufig von einer Anwendung mithilfe der gespeicherten sp_executesql-Systemprozedur übermittelt. Betrachten Sie beispielsweise den folgenden eigenständigen Batch:

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

  Um zu verhindern, dass ein paralleler Ausführungsplan für diese Abfrage generiert wird, erstellen Sie die folgende Planhinweisliste und legen den MAXDOP-Abfragehinweis im @hints-Parameter auf 1 fest.

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

  Wichtig
  Die für das @module_or_batch-Argument und das @params-Argument der sp_create_plan guide-Anweisung angegebenen Werte müssen mit dem Text übereinstimmen, der in der Abfrage übermittelt wird. Weitere Informationen finden Sie unter sp_create_plan_guide (Transact-SQL) und Verwenden von SQL Server Profiler zum Erstellen und Testen von Planhinweislisten.

  SQL-Planhinweislisten können auch für Abfragen erstellt werden, die in derselben Form parametrisiert werden, wenn die PARAMETERIZATION-Datenbankoption mithilfe von SET auf FORCED festgelegt wird oder wenn eine TEMPLATE-Planhinweisliste erstellt wird, die eine parametrisierte Abfrageklasse angibt.

• TEMPLATE-Planhinweisliste
  Eine TEMPLATE-Planhinweisliste zur Übereinstimmung mit eigenständigen Abfragen, die in einer angegebenen Form parametrisiert werden. Diese Planhinweislisten werden verwendet, um die aktuelle PARAMETERIZATION-Datenbankoption für eine bestimmte Abfrageklasse zu überschreiben.

  Sie können eine TEMPLATE-Planhinweisliste in folgenden Situationen erstellen:

  • Die Datenbankoption PARAMETERIZATION ist auf FORCED festgelegt, es gibt aber Abfragen, die nach den Regeln der einfachen Parametrisierung kompiliert werden sollen.

  • Die Datenbankoption PARAMETERIZATION ist auf SIMPLE festgelegt (die Standardeinstellung), für eine Klasse von Abfragen soll aber eine erzwungene Parametrisierung versucht werden.

Voraussetzungen für den Planhinweislistenabgleich

Planhinweislisten beziehen sich auf die Datenbank, in der sie erstellt werden. Daher können nur die Planhinweislisten gegen die Abfrage geprüft werden, die in der zum Zeitpunkt der Ausführung einer Abfrage aktuellen Datenbank vorhanden sind. Beispiel: Wenn AdventureWorks2012 die aktuelle Datenbank ist und die folgende Abfrage ausgeführt wird:

SELECT FirstName, LastName FROM Person.Person;

Dann können nur in der AdventureWorks2012 -Datenbank vorhandene Planhinweislisten mit dieser Abfrage verglichen werden. Wenn jedoch AdventureWorks2012 die aktuelle Datenbank ist und die folgenden Anweisungen ausgeführt werden:

USE DB1;

SELECT FirstName, LastName FROM Person.Person;

Dann können nur in DB1 vorhandene Planhinweislisten mit dieser Abfrage verglichen werden, weil die Abfrage im Kontext von DB1 ausgeführt wird.

Bei SQL- und TEMPLATE-basierten Planhinweislisten vergleicht SQL Server die Werte der Argumente @module\_or\_batch und @params mit einer Abfrage, indem die beiden Werte Zeichen für Zeichen verglichen werden. Das bedeutet, dass Sie den Text genau so bereitstellen müssen, wie er von SQL Server im tatsächlichen Batch empfangen wird.

Wenn @type = 'SQL' und @module\_or\_batch auf NULL gesetzt wird, wird der Wert von @module\_or\_batch auf den Wert von @stmt festgelegt. Dies bedeutet, dass der Wert für statement_text Zeichen für Zeichen in genau dem Format bereitgestellt werden muss, in dem er an SQL Server übermittelt wird. Es findet keine interne Konvertierung zur Vereinfachung dieses Abgleichs statt.

Wenn für eine Anweisung sowohl eine reguläre Planhinweisliste (SQL oder OBJECT) als auch eine TEMPLATE-Planhinweisliste gelten können, wird nur die reguläre Planhinweisliste verwendet.

Hinweis
Der Batch, der die Anweisung enthält, für die Sie eine Planhinweisliste erstellen wollen, darf keine USE database-Anweisung enthalten.

Auswirkungen von Planhinweislisten auf den Plancache

Wenn Sie eine Planhinweisliste für ein Modul erstellen, wird der Abfrageplan für dieses Modul aus dem Plancache entfernt. Wenn Sie eine Planhinweisliste des Typs OBJECT oder SQL für einen Batch erstellen, wird der Abfrageplan für einen Batch mit demselben Hashwert entfernt. Wenn Sie eine Planhinweisliste des Typs TEMPLATE erstellen, werden alle Batches mit einer Anweisung aus dem Plancache in dieser Datenbank entfernt.

Verwandte Tasks

Siehe auch

Verweis

sp_create_plan_guide (Transact-SQL)

sp_create_plan_guide_from_handle (Transact-SQL)

sp_control_plan_guide (Transact-SQL)

sys.plan_guides (Transact-SQL)

sys.fn_validate_plan_guide (Transact-SQL)