Optimieren der Leistung von Data Warehouse-Abfragen durch das Filtern mithilfe einer Bitmap
Die meisten Data Warehouse-Abfragen folgen einem Sternschema und können viele hundert Millionen Zeilen in einer einzigen Abfrage verarbeiten. Standardmäßig erkennt der Abfrageoptimierer in Abfragen Sternschemas und erstellt diesen entsprechende effiziente Abfragepläne. Eine Methode, mit der der Optimierer einen effizienten Plan generieren kann, ist das Filtern mithilfe einer Bitmap. Ein Bitmapfilter verwendet eine kompakte Darstellung einer Gruppe von Werten aus einer Tabelle in einem Bereich der Operatorstruktur, um Zeilen aus einer zweiten Tabelle in einem anderen Bereich der Struktur zu filtern. Im Wesentlichen erzeugt der Filter einen Semi-Verbund. Das bedeutet, dass nur die Zeilen in der zweiten Tabelle, die für die Verknüpfung mit der ersten Tabelle in Frage kommen, verarbeitet werden.
In SQL Server 2008 kann das Filtern mithilfe einer Bitmap wie in SQL Server 2005 nach der Optimierung in den Abfrageplan oder aber dynamisch durch den Abfrageoptimierer während des Generierens des Abfrageplans einbezogen werden. Wenn der Filter dynamisch einbezogen wird, wird dieser als optimierter Bitmapfilter bezeichnet. Durch das optimierte Filtern mithilfe einer Bitmap kann die Leistung von Data Warehouse-Abfragen, in denen Sternschemas verwendet werden, gesteigert werden, indem nicht qualifizierende Zeilen zu einem frühen Zeitpunkt im Abfrageplan aus der Faktentabelle entfernt werden. Ohne optimiertes Filtern mithilfe einer Bitmap werden alle Zeilen in der Faktentabelle über einen Bereich der Operatorstruktur verarbeitet, bevor im Joinvorgang mit den Dimensionstabellen die nicht qualifizierende Zeilen entfernt werden. Wenn das optimierte Filtern mithilfe einer Bitmap angewendet wird, werden die nicht qualifizierenden Zeilen in der Faktentabelle sofort entfernt.
Das optimierte Filtern mithilfe einer Bitmap steht nur in der Enterprise, Developer und Evaluation Edition von SQL Server zur Verfügung.
Grundlegendes zum Filtern mithilfe einer Bitmap
Der Bitmapfilter bietet Vorteile gegenüber dem Bitmapindex. Ein Bitmapindex ist eine alternative Form der Darstellung von Zeilen-ID (RID)-Listen in einem Wertlistenindex. Dazu wird mindestens ein Bitvektor zum Angeben der Zeile in einer Tabelle verwendet, die einen bestimmten Spaltenwert aufweist. Mit beiden Methoden können auf effektive Art und Weise unnötige Zeilen aus der Ergebnisverarbeitung entfernt werden; allerdings bestehen bedeutende Unterschiede zwischen einem Bitmapfilter und einem Bitmapindex. Zunächst einmal handelt es sich bei Bitmapfiltern um arbeitsspeicherinterne Strukturen, d. h., dass aufgrund von an der zugrunde liegenden Tabelle vorgenommenen DML (Data Manipulation Language)-Vorgängen jeglicher Wartungsaufwand hinsichtlich des Index entfällt. Außerdem sind Bitmapfilter sehr klein und können, anders als vorhandene Indizes auf Datenträgern, die in der Regel von der Größe der Tabelle abhängen, auf der sie basieren, dynamisch bei nur minimaler Beeinträchtigung der Abfrageverarbeitungszeit erstellt werden.
Vergleich zwischen dem Filtern mithilfe einer Bitmap und dem optimierten Filtern mithilfe einer Bitmap
Das Filtern mithilfe einer Bitmap und das optimierte Filtern mithilfe einer Bitmap wird im Abfrageplan mit dem Showplanoperator "Bitmap" implementiert. Das Filtern mithilfe einer Bitmap wird nur in parallelen Abfrageplänen mit Hash- oder Zusammenführungsjoins verwendet. Das optimierte Filtern mithilfe einer Bitmap ist nur in parallelen Abfrageplänen mit Hashjoins zulässig. In beiden Fällen wird der Bitmapfilter auf der Seite der Erstellungseingabe (der Dimensionstabelle) einer Hashjoins erstellt; allerdings erfolgt der tatsächliche Filtervorgang innerhalb des Parallelism-Operators, der sich auf der Seite der Untersuchungseingabe (der Faktentabelle) des Hashjoins befindet. Wenn die Verknüpfung auf einer Spalte mit ganzen Zahlen basiert, kann der Filter direkt auf den ursprünglichen Tabellen- oder Indexscanvorgang statt auf den Parallelism-Operator angewendet werden. Diese Technik wird als In-Row-Optimierung bezeichnet.
Wenn das Filtern mithilfe einer Bitmap nach der Optimierung in den Abfrageplan einbezogen wird, wird die Dauer für die Kompilierung der Abfrage verringert; allerdings ist die Anzahl von Abfrageplänen, die vom Optimierer in Betracht gezogen werden können, beschränkt, und die Kardinalitäts- oder Kostenschätzungen werden nicht berücksichtigt.
Das optimierte Filtern mithilfe einer Bitmap bietet die folgenden Vorteile:
Das Filtern in mehreren Dimensionstabellen wird unterstützt.
Mehrere Filter können auf einen einzelnen Operator angewendet werden.
Optimierte Bitmapfilter können auf eine größere Zahl von Operatortypen angewendet werden. Dazu zählen Verteilungsoperatoren wie Distribute Streams und Repartition Streams, Tabellen- oder Indexscanoperatoren sowie Filteroperatoren.
Filter sind auf SELECT-Anweisungen und die in den Anweisungen INSERT, UPDATE, DELETE und MERGE verwendeten schreibgeschützten Operatoren anwendbar.
Filter können bei der Erstellung indizierter Sichten in den zum Auffüllen des Indexes verwendeten Operatoren angewendet werden.
Der Abfrageoptimierer verwendet Kardinalitäts- oder Kostenschätzungen, um zu ermitteln, ob das optimierte Filtern mithilfe einer Bitmap angebracht ist.
Der Abfrageoptimierer kann eine größere Anzahl von Plänen berücksichtigen.
Implementieren des optimierten Filterns mithilfe einer Bitmap
Ein Bitmapfilter ist nur nützlich, wenn er selektiv ist. Der Abfrageoptimierer bestimmt, wann ein optimierter Bitmapfilter eine ausreichende Selektivität aufweist, damit er verwendet werden kann, und auf welche Operatoren der Filter angewendet wird. Der Optimierer platziert die optimierten Bitmapfilter in allen Verzweigungen eines Sternjoins und ermittelt anhand von Kostenregeln, ob der Plan mit den geringsten geschätzten Ausführungskosten verbunden ist. Wenn der optimierte Bitmapfilter unselektiv ist, fällt die Kostenschätzung in der Regel zu hoch aus, und der Plan wird verworfen. Bei der Platzierung optimierter Bitmapfilter im Plan sucht der Optimierer nach Varianten von Hashjoins, z. B. einer rechtsorientierten Struktur von Hashjoins. Joins mit Dimensionstabellen werden implementiert, damit der Join mit der wahrscheinlich höchsten Selektivität zuerst ausgeführt wird.
Der Operator, in dem der optimierte Bitmapfilter angewendet wird, enthält ein Bitmapprädikat im folgenden Format: PROBE([Opt_Bitmap1001], {[Spaltenname]} [, 'IN ROW']). Das Bitmapprädikat gibt Aufschluss über Folgendes:
Den Bitmapnamen, der dem im Bitmap-Operator eingeführten Namen entspricht. Das Präfix Opt_ gibt an, dass ein optimierter Bitmapfilter verwendet wird.
Die untersuchte Spalte. Dies ist der Punkt, ab dem die gefilterten Daten durch die Struktur fließen.
Ob bei der Bitmapuntersuchung die In-Row-Optimierung verwendet wird. Wenn dies der Fall ist, wird die Bitmapuntersuchung mit dem IN ROW-Parameter aufgerufen. Andernfalls fehlt dieser Parameter.
Beispiel
Das folgende Beispiel zeigt eine Abfrage für ein einfaches Sternschema. Die beiden Dimensionstabellen DimProduct und DimCustomer werden mithilfe einer Primärschlüssel-zu-Fremdschlüssel-Verknüpfung über eine einzelne ganzzahlige Spalte mit der Faktentabelle FactInternetSales verknüpft.
USE AdventureWorksDW2008R2;
GO
SELECT *
FROM dbo.FactInternetSales AS F
INNER JOIN dbo.DimProduct AS D1 ON F.ProductKey = D1.ProductKey
INNER JOIN dbo.DimCustomer AS D2 ON F.CustomerKey = D2.CustomerKey
WHERE D1.StandardCost <= 30 AND D2.YearlyIncome <= 50000;
Die folgende Abbildung zeigt den Ausführungsplan für diese Abfrage, wie er möglicherweise in SQL Server 2005 angezeigt wird. An den mit 1A markierten Punkten wurden die Dimensionstabellen gescannt, und die Informationen, die erforderlich sind, um die nicht qualifizierenden Zeilen aus der Faktentabelle (1B) herauszufiltern, sind bekannt. Allerdings zeigen die Eigenschaften des Table Scan-Operators, dass kein Prädikat verwendet wird, um die Anzahl der von der Faktentabelle zurückgegebenen Zeilen einzuschränken.
.gif "SQL Server-Abfrageplan ohne Bitmapfilter")
Die folgende Abbildung hingegen zeigt den Ausführungsplan für dieselbe Abfrage, wie er möglicherweise in SQL Server 2008 angezeigt wird. In den Unterstrukturen beider Dimensionstabellen werden optimierte Bitmap-Operatoren verwendet. Die Eigenschaften des Table Scan-Operators zeigen, dass die Filter (Bitmapuntersuchungen) aus diesen Unterstrukturen direkt auf die Faktentabellenstruktur angewendet werden, um vor dem ersten Joinvorgang die Anzahl der von der Faktentabelle zurückgegebenen Zeilen einzuschränken.
.gif "SQL Server-Abfrageplan mit Bitmapfiltern")
Anforderungen für das optimierte Filtern mithilfe einer Bitmap
Für das optimierte Filtern mithilfe einer Bitmap gelten folgende Anforderungen:
Faktentabellen müssen mindestens 100 Seiten aufweisen. Der Optimierer betrachtet kleinere Tabellen als Dimensionstabellen.
Nur innere Joins zwischen einer Faktentabelle und einer Dimensionstabelle werden berücksichtigt.
Beim Joinprädikat zwischen der Faktentabelle und der Dimensionstabelle muss es sich um einen Join über eine Spalte handeln, jedoch nicht um eine Primärschlüssel-zu-Fremdschlüssel-Beziehung. Eine Spalte mit ganzen Zahlen wird bevorzugt.
Joins mit Dimensionen werden nur berücksichtigt, wenn die die Eingabekardinalitäten der Dimension kleiner sind als die Eingabekardinalität aus der Faktentabelle.