Grundlegendes zu Planhinweislisten

Artikel
12/15/2008

SQL Server 2005 führt die gespeicherte Systemprozedur sp_create_plan_guide ein, die Planhinweislisten erstellt, um die Leistung von Abfragen zu optimieren. Diese Prozedur können Sie verwenden, wenn Sie den Text einer Abfrage nicht direkt verändern können oder wollen. Die Verwendung von Planhinweislisten bietet sich z. B. an, wenn eine kleine Teilmenge von Abfragen in der Datenbankanwendung eines Drittanbieters nicht erwartungsgemäß funktioniert. Planhinweislisten beeinflussen die Abfrageoptimierung durch Anfügen von Abfragehinweisen an Abfragen. In der sp_create_plan_guide-Anweisung geben Sie die zu optimierende Abfrage an sowie die OPTION-Klausel, die die Abfragehinweise zum Optimieren der Abfrage enthält. Wenn die Abfrage ausgeführt wird, sucht SQL Server eine übereinstimmende Planhinweisliste für die Abfrage und fügt ihr zur Laufzeit die OPTION-Klausel an.

Hinweis:
Planhinweislisten können nur in der Standard, Developer, Evaluation und Enterprise Edition und der Enterprise Edition von SQL Server 2005 erstellt und verwendet, jedoch in allen Editionen gelöscht werden.

Abfragen, für die sich Planhinweislisten eignen, basieren im Allgemeinen auf Parametern und weisen möglicherweise unzureichende Leistungen auf, weil sie zwischengespeicherte Abfragepläne verwenden, deren Parameterwerte nicht das passende repräsentative (oder Worst-Case)-Szenario darstellen. Um dieses Problem zu lösen, kann der OPTIMIZE FOR- oder der RECOMPILE-Abfragehinweis verwendet werden. Durch OPTIMIZE FOR wird SQL Server angewiesen, bei der Abfrageoptimierung einen bestimmten Parameterwert zu verwenden. Durch RECOMPILE wird SQL Server angewiesen, den für die Abfrage generierten Abfrageplan nach der Ausführung zu verwerfen. Dadurch wird der Abfrageoptimierer gezwungen, den Abfrageplan erneut zu kompilieren, wenn dieselbe Abfrage das nächste Mal ausgeführt wird.

USE PLAN ist ein weiterer häufig mit Planhinweislisten verwendeter Abfragehinweis. Dieser Abfragehinweis ist geeignet, wenn Sie bereits über einen vorhandenen Ausführungsplan verfügen, den Sie jedoch für eine bestimmte Abfrage durch einen vom Abfrageoptimierer ausgewählten Abfragehinweis ersetzen wollen, weil Sie wissen, dass er bessere Ergebnisse zeitigt. USE PLAN erzwingt die Verwendung eines bestimmten Abfrageplanes in SQL Server, wenn dieser beim Ausführen der Abfrage explizit in der Syntax des Abfragehinweises angegeben ist. Eine Planhinweisliste, die den USE PLAN-Abfragehinweis anwendet, erweist sich insbesondere dann als nützlich, wenn es darauf ankommt, für eine Abfrage einen guten Ausführungsplan zu erhalten, indem durch das Umschreiben der Abfrage die Verknüpfungsreihenfolge oder das Verwenden von Verknüpfungshinweisen oder Indexhinweisen erzwungen werden soll. Weitere Informationen finden Sie unter Szenario zum Erzwingen eines Planes: Erstellen einer Planhinweisliste zum Erzwingen eines Planes, der aus einer umgeschriebenen Abfrage abgerufen wird.

Weitere Informationen zu RECOMPILE, OPTIMIZE FOR, USE PLAN und anderen Abfragehinweisen finden Sie unter Abfragehinweis (Transact-SQL).

Vorsicht:
Bei falscher Verwendung von Abfragehinweisen in Planhinweislisten kann es zu Kompilierungs-, Ausführungs- oder Leistungsproblemen kommen. Daher sollten Planhinweislisten nur von erfahrenen Entwicklern und Datenbankadministratoren verwendet werden.

Planhinweislisten können zur Übereinstimmung mit Abfragen erstellt werden, die im folgenden Kontext ausgeführt werden:

OBJECT-Planhinweislisten zur Übereinstimmung mit Abfragen, die im Kontext von gespeicherten Prozeduren in Transact-SQL, skalaren Funktionen, Tabellenwertfunktionen mit mehreren Anweisungen und DML-Triggern ausgeführt werden.
Eine SQL-Planhinweisliste zur Übereinstimmung mit 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 zur Übereinstimmung mit Abfragen verwendet werden, die in einer angegebenen Form parametrisiert werden.
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 außer Kraft zu setzen.

OBJECT-Planhinweislisten

Betrachten Sie die folgende gespeicherte Prozedur mit dem Parameter @Country in einer Datenbankanwendung, die in der AdventureWorks-Datenbank bereitgestellt wird:

CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country nvarchar(60))
AS
BEGIN
    SELECT *
    FROM Sales.SalesOrderHeader h, Sales.Customer c, 
        Sales.SalesTerritory t
    WHERE h.CustomerID = c.CustomerID
        AND c.TerritoryID = t.TerritoryID
        AND CountryRegionCode = @Country
END

Beachten Sie, dass diese gespeicherte Prozedur kompiliert und für den Parameterwert @Country = N'AU' (Australien) optimiert wurde. Es bestehen jedoch nur relativ wenige aus Australien kommende Bestellungen. Wenn die Abfrage mit Parameterwerten für Länder/Regionen mit häufigeren Bestellungen ausgeführt wird, beeinträchtigt dies die Leistung. Da die meisten Bestellungen aus den USA kommen, würde ein für @Country=N'US' generierter Abfrageplan eine bessere Leistung für alle möglichen Werte des Parameters @Country erbringen.

Dieses Problem können Sie lösen, indem Sie die gespeicherte Prozedur ändern und der Abfrage den OPTIMIZE FOR-Abfragehinweis hinzufügen. 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 AdventureWorks-Datenbank die folgende Planhinweisliste erstellen.

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

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

SQL-Planhinweislisten

SQL-Planhinweislisten eignen sich für Anweisungen und Batches, die häufig mithilfe der gespeicherten Systemprozedur sp_executesql von einer Anwendung übermittelt werden. Betrachten Sie beispielsweise den folgenden eigenständigen Batch:

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

Erstellen Sie die folgende Planhinweisliste, um zu verhindern, dass ein paralleler Ausführungsplan für diese Abfrage generiert wird:

sp_create_plan_guide 
@name = N'Guide1', 
@stmt = N'SELECT TOP 1 * FROM Sales.SalesOrderHeader h ORDER BY OrderDate DESC',  
@type = N'SQL',
@module_or_batch = NULL, 
@params = NULL, 
@hints = N'OPTION (MAXDOP 1)'

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

Wichtig:
Die für die Argumente @module_or_batch und @params der sp_create_plan guide-Anweisung angegebenen Werte müssen genau mit dem entsprechenden 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 ist, oder wenn eine TEMPLATE-Planhinweisliste erstellt wurde, die angibt, dass eine bestimmte Abfrageklasse parametrisiert werden soll. Weitere Informationen finden Sie unter Entwerfen von Planhinweislisten für parametrisierte Abfragen.

TEMPLATE-Planhinweislisten

TEMPLATE-Planhinweislisten werden verwendet, um das Parametrisierungsverhalten für bestimmte Abfrageformen außer Kraft zu setzen. 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.

Weitere Informationen finden Sie unter Angeben des Abfrageparametrisierungsverhaltens mithilfe von Planhinweislisten.

Sie haben auch die Möglichkeit, TEMPLATE-Planhinweislisten zusammen mit SQL-Planhinweislisten zu verwenden. Beispielsweise können Sie eine TEMPLATE-Planhinweisliste erstellen, um sicherzustellen, dass eine bestimmte Abfrageklasse parametrisiert wird. Anschließend können Sie eine SQL-Planhinweisliste für die parametrisierte Form dieser Abfragen erstellen.

Siehe auch

Freigeben über

Grundlegendes zu Planhinweislisten

OBJECT-Planhinweislisten

SQL-Planhinweislisten

TEMPLATE-Planhinweislisten

Siehe auch

Konzepte

Andere Ressourcen

Hilfe und Informationen

Zusätzliche Ressourcen