• Artikel

Empfehlungen für die Abfrageoptimierung

Einige Abfragen beanspruchen mehr Ressourcen als andere. Dazu gehören Abfragen, die große Resultsets zurückgeben oder WHERE-Klauseln enthalten, die nicht eindeutig sind. Kein noch so intelligenter Abfrageoptimierer kann den Ressourcenaufwand dieser Konstrukte im Vergleich zu einer weniger komplexen Abfrage ausschalten. SQL Server verwendet den optimalen Zugriffsplan, die Abfrageoptimierung ist jedoch auf das Mögliche beschränkt.

Trotzdem gibt es folgende Möglichkeiten zum Verbessern der Abfrageleistung:

  • Weiteren Speicher hinzufügen. Diese Lösung kann besonders hilfreich sein, wenn auf dem Server viele komplexe Abfragen, und einige von ihnen sehr langsam, ausgeführt werden.

  • Mehrere Prozessoren verwenden. Auf Computern mit mehreren Prozessoren kann Database Engine (Datenbankmodul) parallele Abfragen ausführen. Weitere Informationen finden Sie unter Parallele Abfrageverarbeitung.

  • Umschreiben der Abfrage. Berücksichtigen Sie dabei folgende Probleme:

    • Wenn die Abfrage Cursor verwendet, bestimmen Sie, ob die Cursorabfrage mithilfe eines effizienteren Cursortyps (beispielsweise dem schnellen Vorwärtscursor) oder einer einzigen Abfrage effizienter geschrieben werden kann. Einzelabfragen sind Cursorvorgängen in der Regel an Leistung überlegen. Da ein Satz von Cursoranweisungen in der Regel einen äußeren Schleifenvorgang darstellt, in der jede Zeile in der äußeren Schleife einmal mithilfe einer inneren Anweisung verarbeitet wird, verwenden Sie stattdessen nach Möglichkeit eine GROUP BY-Anweisung, eine CASE-Anweisung oder eine Unterabfrage. Weitere Informationen finden Sie unter Cursortypen (Datenbankmodul) und Grundlegende Informationen zu Abfragen.

    • Wenn eine Anwendung eine Schleife verwendet, ziehen Sie in Betracht, die Schleife in die Abfrage zu legen. Oft enthält eine Anwendung eine Schleife, die eine häufig auszuführende parametrisierte Abfrage enthält, sodass ein Netzwerkroundtrip zwischen dem Computer, der die Anwendung ausführt, und SQL Server erforderlich ist. Erstellen Sie stattdessen eine einzelne, komplexere Abfrage, die eine temporäre Tabelle verwendet. Dann ist nur ein Netzwerkroundtrip erforderlich, und der Abfrageoptimierer kann die Einzelabfrage besser optimieren. Weitere Informationen finden Sie unter Prozedurales Transact-SQL und Transact-SQL-Variablen.

    • Verwenden Sie nicht mehrere Aliase für eine Tabelle in derselben Abfrage, um Indexschnittmengen zu simulieren. Dies ist nicht mehr erforderlich, da SQL Server automatisch Indexschnittmengen berücksichtigt und in der Lage ist, in einer Abfrage mehrere Indizes für eine Tabelle zu verwenden. Betrachten Sie folgende Beispielabfrage:

      SELECT * FROM lineitem 
WHERE partkey BETWEEN 17000 AND 17100 AND
    shipdate BETWEEN '1/1/1994' AND '1/31/1994'

      SQL Server verwendet Indizes für die beiden Spalten partkey und shipdate und führt dann einen Hash Match-Vorgang zwischen den beiden Teilmengen aus, um die Indexschnittmenge zu erhalten.

    • Verwenden Sie Abfrageparametrisierung, um eine Wiederverwendung von zwischengespeicherten Abfrageausführungsplänen zu ermöglichen. Wenn mehrere Abfragen den gleichen Abfrage- und Abfrageplan-Hashwert verwenden, können Sie durch Erstellen einer parametrisierten Abfrage die Leistung optimieren. Durch das Aufrufen einer Abfrage mit Parametern anstelle mehrerer Abfragen mit Literalwerten kann der zwischengespeicherte Abfrageausführungsplan wiederverwendet werden. Weitere Informationen finden Sie unter Suchen und Optimieren von ähnlichen Abfragen mit Abfrage und Abfrageplanhashes und Zwischenspeichern und Wiederverwenden von Ausführungsplänen.

      Kann die Anwendung nicht geändert werden, können Sie mithilfe von TEMPLATE-Planhinweislisten mit erzwungener Parametrisierung ein ähnliches Ergebnis erzielen. Weitere Informationen finden Sie unter Angeben des Abfrageparametrisierungsverhaltens mithilfe von Planhinweislisten.

    • Verwenden Sie Abfragehinweise nur, wenn erforderlich. Abfragen mit Hinweisen, die auf früheren Versionen von SQL Server ausgeführt werden, sollten ohne Angabe der Hinweise getestet werden. Die Hinweise verhindern möglicherweise, dass der Abfrageoptimierer einen besseren Ausführungsplan wählt. Weitere Informationen finden Sie unter SELECT (Transact-SQL).

  • Verwenden Sie query_plan_hash, um die Abfrageausführungspläne für Abfragen zu erfassen, zu speichern und zu vergleichen. Nach einer Änderung der Systemkonfiguration können Sie beispielsweise die Abfrageplan-Hashwerte für unternehmenswichtige Abfragen mit den ursprünglichen Abfrageplan-Hashwerten vergleichen. Abweichungen zwischen den Abfrageplan-Hashwerten geben Aufschluss darüber, ob durch die Änderung der Systemkonfiguration die Abfrageausführungspläne für wichtige Abfragen aktualisiert wurden. Darüber hinaus können Sie eine aktuelle Abfrage mit länger Ausführungszeit anhalten, wenn der Abfrageplan-Hashwert in sys.dm_exec_requests vom Basishashwert des Abfrageplans abweicht und letzterer eine gute Leistung gezeigt hat. Weitere Informationen finden Sie unter Suchen und Optimieren von ähnlichen Abfragen mit Abfrage und Abfrageplanhashes.

  • Verwenden Sie die Konfigurationsoption query governor. Mit der Konfigurationsoption query governor kann verhindert werden, dass Abfragen mit langen Ausführungszeiten ausgeführt und dadurch Systemressourcen aufgebraucht werden. Standardmäßig lässt die Option zu, dass alle Abfragen unabhängig von ihrer Laufzeit ausgeführt werden. Sie können die Abfragekontrolle jedoch so festlegen, dass die Ausführungszeit aller Abfragen für alle Verbindungen oder aller Abfragen für eine bestimmte Verbindung eine festgelegte Anzahl von Sekunden nicht überschreiten darf. Da die Abfragekontrolle auf der geschätzten Ausführungszeit und nicht auf der tatsächlich verstrichenen Zeit basiert, verursacht sie keinen Laufzeitaufwand. Sie beendet darüber hinaus Abfragen mit langer Ausführungszeit, bevor sie gestartet werden, anstatt sie auszuführen, bis eine vorgegebene Zeiteinschränkung erreicht ist. Weitere Informationen finden Sie unter query governor cost limit (Option) und SET QUERY_GOVERNOR_COST_LIMIT (Transact-SQL).

  • Optimieren Sie die Wiederverwendung von Abfrageplänen aus dem Plancache. Von Database Engine (Datenbankmodul) werden Abfragepläne für die mögliche Wiederverwendung in einem Cache zwischengespeichert. Wenn ein Abfrageplan nicht zwischengespeichert wird, kann er nie wiederverwendet werden. Nicht zwischengespeicherte Abfragepläne müssen stattdessen bei jedem Ausführen kompiliert werden, was zu einer schlechteren Leistung führt. Die folgenden Transact-SQL-SET-Anweisungsoptionen verhindern, dass zwischengespeicherte Abfragepläne wiederverwendet werden. Ein Transact-SQL-Batch mit diesen SET-Optionen im aktivierten Zustand (ON) kann seine Abfragepläne nicht gemeinsam mit dem gleichen Batch nutzen, der mit deaktivierten SET-Optionen (OFF) kompiliert worden ist:

    SET ANSI_NULL_DFLT_OFF

    SET ANSI_NULL_DFLT_ON

    SET ANSI_NULLS

    SET ANSI_PADDING

    SET ANSI_WARNINGS

    SET ARITHABORT

    SET CONCAT_NULL_YIELDS_NULL

    SET DATEFIRST

    SET DATEFORMAT

    SET FORCEPLAN

    SET LANGUAGE

    SET NO_BROWSETABLE

    SET NUMERIC_ROUNDABORT

    SET QUOTED_IDENTIFIER

    SET TEXTSIZE

     

    Darüber hinaus wirkt sich die SET ANSI_DEFAULTS-Option auf die Wiederverwendung der zwischengespeicherten Abfragepläne aus, weil sie zum Ändern der folgenden Optionen verwendet werden kann: ANSI_NULLS, ANSI_NULL_DFLT_ON, ANSI_PADDING, ANSI_WARNINGS, CURSOR_CLOSE_ON_COMMIT, IMPLICIT_TRANSACTIONS und QUOTED_IDENTIFIER SET. Beachten Sie, dass die meisten SET-Optionen, die mithilfe von SET ANSI_DEFAULTS geändert werden können, solche sind, die sich auf die Wiederverwendung von Abfrageplänen auswirken können.

    Sie können einige dieser SET-Optionen mit den folgenden Methoden ändern:

HinweisHinweis

Um die Neukompilierung von Abfrageplänen durch SET-Optionen zu verhindern, richten Sie SET-Optionen zur Verbindungszeit ein und stellen Sie sicher, dass sie sich für die Dauer der Verbindung nicht ändern. Einige SET-Optionen müssen auf bestimmte Werte festgelegt werden, damit indizierte Sichten oder Indizes auf berechneten Spalten verwendet werden können. Weitere Informationen finden Sie unter SET-Optionen mit Auswirkungen auf Ergebnisse.