UNION ALL Optimization
Die Verkettung von zwei oder mehr Datensätzen wird am häufigsten in T-SQL mit der UNION ALL -Klausel ausgedrückt. Da der SQL Server-Optimierer häufig Dinge wie Joins und Aggregate neu anordnen kann, um die Leistung zu verbessern, ist zu erwarten, dass SQL Server auch die Neuordnung von Verkettungseingaben in Betracht ziehen würde, wo dies einen Vorteil bieten würde. Der Optimierer könnte beispielsweise die Vorteile des Umschreibens von A UNION ALL B als B UNION ALL A .
Tatsächlich macht der SQL Server-Optimierer dies nicht. Genauer gesagt gab es in SQL Server-Releases bis 2008 R2 eine eingeschränkte Unterstützung für die Neuordnung von Verkettungseingaben, aber dies wurde in SQL Server 2012 entfernt und ist seitdem nicht mehr aufgetaucht.
SQL Server 2008 R2
Intuitiv ist die Reihenfolge der Verkettungseingaben nur wichtig, wenn ein Zeilenziel vorhanden ist. Standardmäßig optimiert SQL Server Ausführungspläne auf der Grundlage, dass alle qualifizierten Zeilen an den Client zurückgegeben werden. Wenn ein Zeilenziel in Kraft ist, versucht der Optimierer, einen Ausführungsplan zu finden, der die ersten Zeilen schnell erzeugt.
Zeilenziele können auf verschiedene Arten festgelegt werden, z. B. mit TOP, einem FAST n Abfragehinweis oder mit EXISTS (der naturgemäß höchstens eine Zeile finden muss). Wo es kein Zeilenziel gibt (dh der Client benötigt alle Zeilen), spielt es im Allgemeinen keine Rolle, in welcher Reihenfolge die Verkettungseingaben gelesen werden: Jede Eingabe wird auf jeden Fall vollständig verarbeitet.
Die eingeschränkte Unterstützung in Versionen bis SQL Server 2008 R2 gilt, wenn das Ziel genau eine Zeile ist. In diesem speziellen Fall ordnet SQL Server die Verkettungseingaben auf der Grundlage der erwarteten Kosten neu an.
Dies geschieht nicht während der kostenbasierten Optimierung (wie zu erwarten), sondern als Last-Minute-Rewrite der normalen Optimierer-Ausgabe. Diese Anordnung hat den Vorteil, dass der kostenbasierte Plansuchraum nicht vergrößert wird (möglicherweise eine Alternative für jede mögliche Neuordnung), während dennoch ein Plan erzeugt wird, der optimiert ist, um die erste Zeile schnell zurückzugeben.
Beispiele
Die folgenden Beispiele verwenden zwei Tabellen mit identischem Inhalt: Eine Million Zeilen mit ganzen Zahlen von eins bis zu einer Million. Eine Tabelle ist ein Heap ohne nicht gruppierte Indizes; die andere hat einen eindeutigen gruppierten Index:
CREATE TABLE dbo.Expensive( Val bigint NOT NULL); CREATE TABLE dbo.Cheap( Val bigint NOT NULL, CONSTRAINT UNIQUE CLUSTERED (Val));GOINSERT dbo.Cheap WITH (TABLOCKX) (Val)SELECT TOP (1000000) Val = ROW_NUMBER() OVER (ORDER BY SV1.number)FROM master.dbo.spt_values AS SV1CROSS JOIN master.dbo.spt_values AS SV2ORDER BY ValOPTION (MAXDOP 1);GOINSERT dbo.Expensive WITH (TABLOCKX) (Val)SELECT C.ValFROM dbo.Cheap AS COPTION (MAXDOP 1);
Kein Zeilenziel
Die folgende Abfrage sucht in jeder Tabelle nach denselben Zeilen und gibt die Verkettung der beiden Mengen zurück:
SELECT E.Val FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005 UNION ALL SELECT C.ValFROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005;
Der vom Abfrageoptimierer erstellte Ausführungsplan lautet:
Die Warnung auf dem root SELECT Operator macht uns auf den offensichtlich fehlenden Index in der Heap-Tabelle aufmerksam. Die Warnung für den Tabellenscan-Operator wird von Sentry One Plan Explorer hinzugefügt. Es lenkt unsere Aufmerksamkeit auf die E / A-Kosten des im Scan verborgenen Restprädikats.
Die Reihenfolge der Eingaben zur Verkettung spielt hier keine Rolle, da wir kein Zeilenziel gesetzt haben. Beide Eingaben werden vollständig gelesen, um alle Ergebniszeilen zurückzugeben. Von Interesse (obwohl dies nicht garantiert ist) beachten Sie, dass die Reihenfolge der Eingaben der Textreihenfolge der ursprünglichen Abfrage folgt. Beachten Sie auch, dass die Reihenfolge der Endergebniszeilen ebenfalls nicht angegeben ist, da wir keine ORDER BY Klausel der obersten Ebene verwendet haben. Wir gehen davon aus, dass die bewusste und endgültige Bestellung für die jeweilige Aufgabe keine Bedeutung hat.
Wenn wir die geschriebene Reihenfolge der Tabellen in der Abfrage wie folgt umkehren:
SELECT C.ValFROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005 UNION ALL SELECT E.Val FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005;
Der Ausführungsplan folgt der Änderung und greift zuerst auf die gruppierte Tabelle zu (dies ist wiederum nicht garantiert):
Es kann erwartet werden, dass beide Abfragen dieselben Leistungsmerkmale aufweisen, da sie dieselben Operationen nur in einer anderen Reihenfolge ausführen.
Mit einem Zeilenziel
Das Fehlen einer Indizierung in der Heap-Tabelle macht das Auffinden bestimmter Zeilen normalerweise teurer als dieselbe Operation in der gruppierten Tabelle. Wenn wir den Optimierer nach einem Plan fragen, der die erste Zeile schnell zurückgibt, würden wir erwarten, dass SQL Server die Verkettungseingaben neu anordnet, sodass die gesamte gruppierte Tabelle zuerst konsultiert wird.
Verwenden der Abfrage, die zuerst die Heap-Tabelle erwähnt, und Verwenden eines FAST 1-Abfragehinweises, um das Zeilenziel anzugeben:
SELECT E.Val FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005 UNION ALL SELECT C.ValFROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005OPTION (FAST 1);
Der geschätzte Ausführungsplan, der für eine Instanz von SQL Server 2008 R2 erstellt wurde, lautet:
Beachten Sie, dass die Verkettungseingaben neu angeordnet wurden, um die geschätzten Kosten für die Rückgabe der ersten Zeile zu reduzieren. Beachten Sie auch, dass der fehlende Index und die verbleibenden E / A-Warnungen verschwunden sind. Keines der beiden Probleme ist bei dieser Planform von Bedeutung, wenn das Ziel darin besteht, eine einzelne Zeile so schnell wie möglich zurückzugeben.
Dieselbe Abfrage, die auf SQL Server 2016 ausgeführt wird (unter Verwendung eines der Kardinalitätsschätzungsmodelle), lautet:
SQL Server 2016 hat die Verkettungseingaben nicht neu angeordnet. Die Plan Explorer-E / A-Warnung wurde zurückgegeben, aber leider hat der Optimierer diesmal keine fehlende Indexwarnung ausgegeben (obwohl dies relevant ist).
Allgemeine Neuordnung
Wie bereits erwähnt, ist das Umschreiben nach der Optimierung, das Verkettungseingaben neu anordnet, nur für:
- SQL Server 2008 R2 und früher
- Ein Zeilenziel von genau einer
Wenn wir wirklich nur eine Zeile zurückgeben wollen, anstatt einen Plan, der optimiert ist, um die erste Zeile schnell zurückzugeben (aber letztendlich immer noch alle Zeilen zurückzugeben), können wir eine TOP -Klausel mit einer abgeleiteten Tabelle oder einem Common Table Expression (CTE) verwenden:
SELECT TOP (1) UA.ValFROM( SELECT E.Val FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005 UNION ALL SELECT C.Val FROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005) AS UA;
Auf SQL Server 2008 R2 oder früher erzeugt dies den optimalen Plan für die neu geordnete Eingabe:
Auf SQL Server 2012, 2014 und 2016 erfolgt keine Neuordnung nach der Optimierung:
Wenn wir mehr als wenn eine Zeile zurückgegeben wird, z. B. mit TOP (2), wird das gewünschte Umschreiben nicht auf SQL Server 2008 R2 angewendet, selbst wenn ein FAST 1 Hinweis ebenfalls verwendet wird. In dieser Situation müssen wir auf Tricks zurückgreifen, wie die Verwendung von TOP mit einer Variablen und einem OPTIMIZE FOR Hinweis:
DECLARE @TopRows bigint = 2; -- Number of rows actually needed SELECT TOP (@TopRows) UA.ValFROM( SELECT E.Val FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005 UNION ALL SELECT C.Val FROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005) AS UAOPTION (OPTIMIZE FOR (@TopRows = 1)); -- Just a hint
Der Abfragehinweis reicht aus, um ein Zeilenziel von eins zu setzen, während der Laufzeitwert der Variablen sicherstellt, dass die gewünschte Anzahl von Zeilen (2) zurückgegeben wird.
Der tatsächliche Ausführungsplan auf SQL Server 2008 R2 lautet:
Beide zurückgegebenen Zeilen stammen von der neu geordneten Sucheingabe, und der Tabellenscan wird überhaupt nicht ausgeführt. Plan Explorer zeigt die Zeilenzahlen in Rot an, da die Schätzung für eine Zeile (aufgrund des Hinweises) galt, während zur Laufzeit zwei Zeilen gefunden wurden.
Ohne UNION ALL
Dieses Problem ist auch nicht auf Abfragen beschränkt, die explizit mit UNION ALL . Andere Konstruktionen wie EXISTS und OR können ebenfalls dazu führen, dass der Optimierer einen Verkettungsoperator einführt, der unter der fehlenden Neuordnung der Eingabe leiden kann. Es gab kürzlich eine Frage zu Datenbankadministratoren Stack Exchange mit genau diesem Problem. Transformieren der Abfrage aus dieser Frage, um unsere Beispieltabellen zu verwenden:
SELECT CASE WHEN EXISTS ( SELECT 1 FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005 ) OR EXISTS ( SELECT 1 FROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005 ) THEN 1 ELSE 0 END;
Der Ausführungsplan für SQL Server 2016 enthält die folgende Tabelle für die erste Eingabe:
Unter SQL Server 2008 R2 ist die Reihenfolge der Eingaben optimiert, um das einreihige Ziel des Semi-Joins widerzuspiegeln:
Im optimaleren Plan wird der Heap-Scan niemals ausgeführt.
Problemumgehungen
In einigen Fällen ist es für den Abfrageschreiber offensichtlich, dass eine der Verkettungseingaben immer billiger auszuführen ist als die anderen. Wenn dies zutrifft, ist es durchaus zulässig, die Abfrage so umzuschreiben, dass die billigeren Verkettungseingaben zuerst in schriftlicher Reihenfolge angezeigt werden. Dies bedeutet natürlich, dass der Abfrageschreiber sich dieser Optimierungsbeschränkung bewusst sein und bereit sein muss, sich auf undokumentiertes Verhalten zu verlassen.
Ein schwierigeres Problem tritt auf, wenn die Kosten für die Verkettungseingaben je nach den Umständen variieren, möglicherweise abhängig von Parameterwerten. Die Verwendung von OPTION (RECOMPILE) hilft unter SQL Server 2012 oder höher nicht. Diese Option kann unter SQL Server 2008 R2 oder früher hilfreich sein, jedoch nur, wenn auch die Anforderung für das Ziel einer einzelnen Zeile erfüllt ist.
Wenn Bedenken bestehen, sich auf das beobachtete Verhalten zu verlassen (Abfrageplan-Verkettungseingaben, die der Abfragetextreihenfolge entsprechen), kann eine Planführung verwendet werden, um die Planform zu erzwingen. Wo verschiedene Eingangsaufträge für verschiedene Umstände optimal sind, können mehrfache Planführer benutzt werden, in denen die Bedingungen im Voraus genau kodiert werden können. Dies ist jedoch kaum ideal.
Abschließende Gedanken
Der SQL Server-Abfrageoptimierer enthält tatsächlich eine kostenbasierte Erkundungsregel, UNIAReorderInputs , die in der Lage ist, Variationen der Verkettungseingabereihenfolge zu generieren und Alternativen während der kostenbasierten Optimierung zu erkunden (nicht als Single-Shot-Post-Optimierungs-Rewrite).
Diese Regel ist derzeit nicht für die allgemeine Verwendung aktiviert. Soweit ich das beurteilen kann, wird es nur aktiviert, wenn ein Plan guide oder USE PLAN Hinweis vorhanden ist. Auf diese Weise kann die Engine erfolgreich einen Plan erzwingen, der für eine Abfrage generiert wurde, die für das Umschreiben der Eingabe-Neuordnung qualifiziert ist, auch wenn die aktuelle Abfrage nicht qualifiziert ist.
Ich habe den Eindruck, dass diese Erkundungsregel bewusst auf diese Verwendung beschränkt ist, da Abfragen, die von einer Neuordnung der Verkettungseingaben im Rahmen einer kostenbasierten Optimierung profitieren würden, als nicht häufig genug angesehen werden, oder weil befürchtet wird, dass sich der zusätzliche Aufwand nicht auszahlen würde. Meine eigene Ansicht ist, dass die Neuordnung der Verkettungsoperatoreingabe immer dann untersucht werden sollte, wenn ein Zeilenziel in Kraft ist.
Es ist auch eine Schande, dass das (eingeschränktere) Umschreiben nach der Optimierung in SQL Server 2012 oder höher nicht wirksam ist. Dies könnte auf einen subtilen Fehler zurückzuführen sein, aber ich konnte in der Dokumentation, in der Wissensdatenbank oder in Connect nichts darüber finden. Ich habe hier ein neues Verbindungselement hinzugefügt.
Update 9. August 2017: Dies wurde jetzt unter Trace Flag 4199 für SQL Server 2014 und 2016 behoben, siehe KB 4023419:
FIX: Abfragen mit UNION ALL und einem Zeilenziel werden in SQL Server 2014 oder höheren Versionen im Vergleich zu SQL Server 2008 R2 möglicherweise langsamer ausgeführt
Leave a Reply