Artikel
09/24/2010

Entwerfen und Implementieren von Planhinweislisten

Sie können mit Planhinweislisten die Leistung von Abfragen optimieren, wenn Sie den Text der Abfrage nicht direkt ändern möchten oder können. Planhinweislisten beeinflussen die Abfrageoptimierung, indem Abfragehinweise oder ein fester Abfrageplan an die Abfragen angefügt werden. Planhinweislisten können zum Abgleich von Abfragen erstellt werden, die in den folgenden Kontexten ausgeführt werden:

OBJECT-Planhinweislisten dienen zum Abgleich von Abfragen, die im Kontext von gespeicherten Transact-SQL-Prozeduren, benutzerdefinierten Skalarfunktionen, benutzerdefinierten Tabellenwertfunktionen mit mehreren Anweisungen und DML-Triggern ausgeführt werden.
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.
Eine TEMPLATE-Planhinweisliste zur Übereinstimmung mit eigenständigen Abfragen, die in einer angegebenen Form parametrisiert werden. Diese Planhinweislisten werden verwendet, um die aktuelle PARAMETERIZATION-Datenbank-SET-Option für eine bestimmte Abfrageklasse außer Kraft zu setzen.

Weitere Informationen finden Sie unter Grundlegendes zu Planhinweislisten.

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.

Es empfiehlt sich, die Definitionen der Planhinweislisten nach einem Upgrade Ihrer Anwendung auf eine neue Version von SQL Server neu zu bewerten und zu testen. Die Anforderungen an die Leistungsoptimierung und das Übereinstimmungsverhalten der Planhinweislisten kann sich verändern. Eine ungültige Planhinweisliste führt zwar nicht zu einem Abfragefehler, der Plan wird jedoch ohne die Planhinweisliste kompiliert. Nachdem Sie eine Datenbank auf SQL Server 2008 aktualisiert haben, sollten Sie die folgenden Aufgaben durchführen, um bestehende Planhinweislisten mit der sys.fn_validate_plan_guide-Funktion zu überprüfen. Alternativ können Sie auch mit dem Plan Guide Unsuccessful-Ereignis in SQL Server Profiler nach ungültigen Planhinweislisten suchen.

Hinweis
Planhinweislisten können nur in der Standard, Developer, Evaluation und Enterprise Edition von SQL Server verwendet werden, sie sind jedoch in allen Versionen 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 2008 erhalten.

Planhinweislisten können nur in der Standard, Developer, Evaluation und Enterprise Edition von SQL Server verwendet werden, sie sind jedoch in allen Versionen 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 2008 erhalten.

Anfügen von Abfragehinweisen an eine Planhinweisliste

In einer Planhinweisliste können beliebige Kombinationen gültiger Abfragehinweise verwendet werden. Wenn eine Planhinweisliste mit einer Abfrage übereinstimmt, wird die in der Hinweisklausel einer Planhinweisliste angegebene OPTION-Klausel vor dem Kompilieren und Optimieren zur Abfrage hinzugefügt. Wenn eine mit einer Planhinweisliste übereinstimmende Abfrage bereits eine OPTION-Klausel besitzt, ersetzen die in der Planhinweisliste angegebenen Abfragehinweise die in der Abfrage enthaltenen. Damit eine Planhinweisliste mit einer Abfrage übereinstimmt, die bereits eine OPTION-Klausel besitzt, müssen Sie die OPTION-Klausel der Abfrage aufnehmen, wenn Sie den Text der Abfrage angeben, der mit der sp_create_plan_guide-Anweisung übereinstimmen soll. Wenn Sie möchten, dass die in der Planhinweisliste angegebenen Abfragehinweise den bereits in der Abfrage vorhandenen Hinweisen hinzugefügt werden, statt diese zu ersetzen, müssen Sie in der OPTION-Klausel der Planhinweisliste sowohl die ursprünglichen Hinweise als auch die zusätzlichen Hinweise angeben.

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.

Allgemeine Abfragehinweise in Planhinweislisten

Abfragen, für die sich Planhinweislisten eignen, basieren im Allgemeinen auf Parametern und weisen möglicherweise eine unzureichende Leistung 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, können der OPTIMIZE FOR-Abfragehinweis und der RECOMPILE-Abfragehinweis verwendet werden. Durch OPTIMIZE FOR wird SQL Server angewiesen, bei der Abfrageoptimierung einen bestimmten Parameterwert zu verwenden. Durch RECOMPILE wird der Server angewiesen, einen Abfrageplan nach der Ausführung wieder zu verwerfen. Hierdurch wird der Abfrageoptimierer gezwungen, den Abfrageplan erneut zu kompilieren, wenn dieselbe Abfrage das nächste Mal ausgeführt wird. Ein Beispiel hierzu finden Sie unter Grundlegendes zu Planhinweislisten.

Darüber hinaus können Sie die Tabellenhinweise INDEX und FORCESEEK als Abfragehinweise angeben. Wenn diese Hinweise als Abfragehinweise angegeben werden, verhalten sie sich genauso wie eine Inline-Tabelle oder ein Sichthinweis. Der INDEX-Hinweis zwingt den Abfrageoptimierer, nur die angegebenen Indizes zu verwenden, um auf die Daten in der referenzierten Tabelle oder Sicht zuzugreifen. Der FORCESEEK-Hinweis zwingt den Abfrageoptimierer, nur einen Indexsuchvorgang zu verwenden, um auf die Daten in der referenzierten Tabelle oder Sicht zuzugreifen. Diese Hinweise stellen zusätzliche Planhinweislistenfunktionen bereit und ermöglichen einen größeren Einfluss auf die Optimierung von Abfragen, die die Planhinweisliste verwenden. Ein Beispiel hierzu finden Sie unter Verwenden der INDEX- und FORCESEEK-Abfragehinweise in Planhinweislisten.

Anfügen eines Abfrageplans an eine Planhinweisliste

Planhinweislisten, die einen festen Abfrageplan anwenden, sind hilfreich, wenn Sie wissen, dass ein vorhandener Ausführungsplan für eine bestimmte Abfrage bessere Ergebnisse erzielt als der vom Abfrageoptimierer ausgewählte Ausführungsplan. Beachten Sie, dass der Abfrageoptimierer den Plan für die Abfrage nicht mehr an Änderungen der Statistiken und Indizes anpassen kann, wenn ein fester Plan auf eine Abfrage angewendet wird. Wenn Sie Planhinweislisten einsetzen möchten, die feste Abfragepläne verwenden, sollten Sie die Vorteile der Anwendung eines festen Plans gegen die Unfähigkeit abwägen, den Plan automatisch an Änderungen der Datenverteilung und der verfügbaren Indizes anzupassen.

Sie können einen bestimmten Abfrageplan an eine Planhinweisliste anfügen, indem Sie den XML-Showplan des Plans im xml_showplan-Parameter der sp_create_plan_guide-Anweisung angeben oder indem Sie das Planhandle eines zwischengespeicherten Plans in der sp_create_plan_guide_from_handle-Anweisung angeben. Beide Methoden übernehmen den festen Abfrageplan für die Zielabfrage.

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 AdventureWorks die aktuelle Datenbank ist und die folgende Abfrage ausgeführt wird:

SELECT * FROM Person.Contact;

Dann können nur in der AdventureWorks-Datenbank vorhandene Planhinweislisten mit dieser Abfrage verglichen werden.

Wenn jedoch AdventureWorks die aktuelle Datenbank ist und die folgenden Anweisungen ausgeführt werden:

USE DB1;

SELECT * FROM Person.Contact;

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 abgeglichen 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' festgelegt wurde und @module_or_batch auf NULL gesetzt wird, wird der Wert von @module_or_batch auf den Wert von @stmt eingestellt. 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.

Planhinweislisten sollten Sie in der Regel mithilfe von SQL Server Profiler testen und dabei überprüfen, ob Ihre Abfrage mit Ihrer Planhinweisliste übereinstimmt. Das Testen von SQL- oder TEMPLATE-basierten Planhinweislisten durch das Ausführen von Batches aus SQL Server Management Studio kann zu unvorhergesehenen Ergebnissen führen. Weitere Informationen finden Sie unter Verwenden von SQL Server Profiler zum Erstellen und Testen von Planhinweislisten.

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.