Verstehen von Abfrageplänen

Abgeschlossen

Das Verständnis dafür, wie Datenbankoptimierer funktionieren, ist unerlässlich, bevor man sich mit den Details von Ausführungsplänen befasst. SQL Server verwendet einen kostenbasierten Abfrageoptimierer, der die Kosten für mehrere mögliche Pläne basierend auf den verwendeten Spalten und den potenziellen Indizes für jeden Vorgang im Abfrageplan berechnet. Diese Informationen helfen dem Optimierer, die Gesamtkosten für jeden Plan zu ermitteln. Komplexe Abfragen können Tausende möglicher Ausführungspläne aufweisen, der Optimierer wertet jedoch nicht jeden einzelnen aus. Stattdessen verwendet es Heuristiken, um Pläne zu identifizieren, die wahrscheinlich gut funktionieren, und wählt dann den niedrigsten Kostenplan aus den bewerteten aus.

Da der Abfrageoptimierer kostenbasiert ist, ist es von entscheidender Bedeutung, es mit genauen Eingaben für die Entscheidungsfindung zu versorgen. SQL Server basiert auf Statistiken, um die Verteilung von Daten in Spalten und Indizes nachzuverfolgen, und diese Statistiken müssen auf dem neuesten Stand gehalten werden, um zu vermeiden, dass suboptimale Ausführungspläne generiert werden. Obwohl SQL Server seine Statistiken automatisch aktualisiert, wenn sich Daten in einer Tabelle ändern, können häufigere Aktualisierungen bei schnell wechselnden Daten notwendig sein. Der Optimierer berücksichtigt viele Faktoren beim Erstellen eines Plans, einschließlich der Kompatibilitätsstufe der Datenbank, Zeilenschätzungen basierend auf Statistiken und verfügbaren Indizes.

Sendet ein Benutzer eine Abfrage an die Datenbank-Engine, wird der folgende Prozess gestartet:

1. Die Abfrage wird auf korrekte Syntax analysiert, und wenn korrekt, wird ein Parsebaum der Datenbankobjekte generiert.
2. Der Parsetree wird dann in eine Datenbank-Engine-Komponente eingegeben, die als Algebrizer zum Binden bezeichnet wird. In diesem Schritt wird überprüft, ob Spalten und Objekte in der Abfrage vorhanden sind und die zu verarbeitenden Datentypen identifiziert werden. Die Ausgabe ist ein Abfrageverarbeitungsbaum, der als Eingabe für den nächsten Schritt dient.
3. Die Abfrageoptimierung ist CPU-intensiv, sodass das Datenbankmodul Ausführungspläne in einem speziellen Speicherbereich zwischenspeichert, der als Plancache bezeichnet wird. Wenn bereits ein Plan für die Abfrage vorhanden ist, wird er aus dem Cache abgerufen. Jede Abfrage im Cache verfügt über einen Hashwert, der basierend auf der T-SQL in der Abfrage generiert wird, die als query_hash bezeichnet wird. Das Modul generiert eine query_hash für die aktuelle Abfrage und sucht im Plancache nach Übereinstimmungen.
4. Wenn kein Plan vorhanden ist, verwendet der Abfrageoptimierer seinen kostenbasierten Optimierer, um mehrere Ausführungsplanoptionen basierend auf Statistiken zu den Spalten, Tabellen und Indizes zu generieren, die in der Abfrage verwendet werden. Die Ausgabe ist ein Abfrageausführungsplan.
5. Die Abfrage wird mithilfe eines Ausführungsplans aus dem Plancache oder einem neuen Plan ausgeführt, der im vorherigen Schritt generiert wurde. Die Ausgabe ist die Ergebnisse Ihrer Abfrage.

Hinweis

Weitere Informationen zur Funktionsweise des Abfrageprozessors finden Sie im Leitfaden zur Abfrageverarbeitungsarchitektur

Betrachten wir dazu ein Beispiel. Betrachten Sie die folgende Abfrage:

SELECT orderdate,
        AVG(salesAmount)
FROM FactResellerSales
WHERE ShipDate = '2013-07-07'
GROUP BY orderdate;

In diesem Beispiel sucht SQL Server nach dem Vorhandensein der Spalten "OrderDate", "ShipDate" und " SalesAmount " in der Tabelle "FactResellerSales" . Wenn diese Spalten vorhanden sind, generiert SQL Server einen Hashwert für die Abfrage und untersucht den Plancache für einen übereinstimmenden Hashwert. Wenn ein übereinstimmenden Hashwert gefunden wird, versucht das Modul, den Plan wiederzuverwenden. Wenn kein übereinstimmenden Hashwert gefunden wird, untersucht SQL Server die verfügbaren Statistiken in den Spalten "OrderDate " und "ShipDate ".

Die WHERE Klausel, die auf die Spalte ShipDate verweist, wird als Prädikat in dieser Abfrage bezeichnet. Wenn ein nicht gruppierter Index vorhanden ist, der die Spalte "ShipDate " enthält, enthält SQL Server ihn wahrscheinlich in den Plan, vorausgesetzt, die Kosten sind niedriger als das Abrufen von Daten aus dem gruppierten Index. Der Optimierer wählt dann den niedrigsten Kostenplan aus den verfügbaren Optionen aus und führt die Abfrage aus.

Abfragepläne kombinieren eine Reihe relationaler Operatoren zum Abrufen von Daten und Erfassen von Informationen wie geschätzten Zeilenanzahlen. Ein weiteres Element des Ausführungsplans ist der Speicher, der für Vorgänge wie das Verknüpfen oder Sortieren von Daten erforderlich ist, die als Speichererteilung bezeichnet werden. Die Speicherzuteilung hebt die Bedeutung von Statistiken hervor. Wenn SQL Server schätzt, dass ein Operator 10.000.000 Zeilen zurückgibt, wenn er tatsächlich 100 zurückgibt, wird der Abfrage eine größere Speichererteilung zugewiesen. Eine übermäßig große Speichererteilung kann zwei Probleme verursachen. Zunächst kann die Abfrage auf eine RESOURCE_SEMAPHORE Wartezeit stoßen, die darauf hinweist, dass sie darauf wartet, dass SQL Server eine große Menge Arbeitsspeicher zuweist. Standardmäßig wartet SQL Server 25-mal so lange wie die Kosten der Abfrage in Sekunden, bevor die Ausführung erfolgt, jedoch höchstens 24 Stunden. Zweitens, wenn beim Ausführen der Abfrage nicht genügend Arbeitsspeicher verfügbar ist, werden die Daten in die tempdb ausgelagert, was langsamer ist als das Arbeiten im Arbeitsspeicher.

Der Ausführungsplan speichert auch andere Metadaten zur Abfrage, z. B. die Datenbankkompatibilitätsebene, den Grad der Parallelität und die Parameter, die angegeben werden, wenn die Abfrage parametrisiert ist.

Abfragepläne können entweder in einer grafischen Darstellung oder in einem textbasierten Format angezeigt werden. Textbasierte Optionen werden mit SET-Befehlen aufgerufen und gelten nur für die aktuelle Verbindung. Diese Pläne können überall angezeigt werden, wo Sie T-SQL-Abfragen ausführen können.

Die meisten DBAs bevorzugen grafische Pläne, da sie es Ihnen ermöglichen, den Plan als Ganzes zu sehen, einschließlich der Form des Plans. Es gibt mehrere Möglichkeiten zum Anzeigen und Speichern grafischer Abfragepläne. Das häufigste Tool für diesen Zweck ist SQL Server Management Studio. Darüber hinaus gibt es Tools von Drittanbietern, die das Anzeigen grafischer Ausführungspläne unterstützen.

Es gibt drei verschiedene Arten von Ausführungsplänen.

Geschätzter Ausführungsplan

Dieser Typ des Ausführungsplans wird vom Abfrageoptimierer generiert. Die Metadaten und die Größe der Abfragespeicherzuteilung basieren auf Schätzungen aus den Statistiken, die zum Zeitpunkt der Abfragekompilierung in der Datenbank vorhanden sind. Um einen textbasierten geschätzten Plan anzuzeigen, führen Sie den Befehl SET SHOWPLAN_ALL ON aus, bevor Sie die Abfrage ausführen. Wenn Sie die Abfrage ausführen, werden die Schritte des Ausführungsplans angezeigt, die Abfrage wird jedoch nicht ausgeführt, und Es werden keine Ergebnisse angezeigt. Die SET-Option bleibt wirksam, bis Sie sie deaktiviert haben.

Tatsächlicher Ausführungsplan

Diese Art von Plan entspricht dem geschätzten Plan; Sie enthält jedoch auch den Ausführungskontext für die Abfrage. Dieser Kontext enthält die geschätzten und tatsächlichen Zeilenanzahlen, alle Ausführungswarnungen, den tatsächlichen Grad der Parallelität (Anzahl der verwendeten Prozessoren) und die verstrichenen und CPU-Zeiten, die während der Ausführung verwendet werden. Um einen textbasierten tatsächlichen Plan anzuzeigen, führen Sie den Befehl SET STATISTICS PROFILE ON aus, bevor Sie die Abfrage ausführen. Die Abfrage wird ausgeführt, und Sie erhalten sowohl den Plan als auch die Ergebnisse.

Live-Abfragestatistik

Diese Plananzeigeoption kombiniert die geschätzten und tatsächlichen Pläne in einem animierten Plan, der den Ausführungsfortschritt durch die Operatoren anzeigt. Die Live-Abfragestatistik wird jede Sekunde aktualisiert und zeigt die tatsächliche Anzahl der Zeilen an, die die Operatoren durchlaufen. Ein weiterer Vorteil von Live Query Statistics besteht darin, dass die Übergabe von Operator zu Operator angezeigt wird, was bei der Behandlung von Leistungsproblemen hilfreich sein kann. Da diese Art von Plan animiert wird, ist sie nur als grafischer Plan verfügbar.