SQL Server 2008

Abfrageleistung in Data Warehouses

Sunil Agarwal and Torsten Grabs and Dr. Joachim Hammer

 

Kurz zusammengefasst:

• Optimierung von Sternverknüpfungsabfragen
• Parallelverarbeitung partitionierter Tabellen
• Zeilen- und Seitenkomprimierung
• Indizierte Sichten mit ausgerichteter Partitionierung

Die Funktionen für das relationale Data Warehousing in SQL Server 2008 sind deutlich leistungsfähiger als noch bei seinem Vorgänger. Trotzdem fragen Sie sich vielleicht, wie Sie diese neuen Möglichkeiten nutzen können, um ein effizientes Data Warehouse für die Entscheidungsunterstützung bei Milliarden von Zeilen zu erstellen. Sie

möchten vielleicht wissen, mit welchen Features sich eine optimale Abfrageleistung für Abfragen und Berichte zur Entscheidungsunterstützung erzielen lässt und welche Leistungssteigerungen von dieser neuen SQL Server®-Version realistischerweise zu erwarten sind.

Je näher der Veröffentlichungstermin rückt, desto mehr Fragen tauchen auf. Wir hoffen, dass Ihnen diese detaillierte Beschreibung einiger der wichtigsten leistungsrelevanten Data Warehousing-Features von SQL Server 2008 bei Ihrer persönlichen Vorbereitung von Nutzen ist.

Logischer Datenbankentwurf: Dimensionsmodellierung

Transaktionalen Branchenanwendungen liegt meist ein normalisiertes Datenbankschema zugrunde. Bei relationalen Data Warehouses liegt das Augenmerk des logischen Datenbankschemas jedoch weniger auf der Normalisierung. Viele relationale Data Warehouses folgen heute einem Dimensionsmodellierungsansatz, den Ralph Kimball und Margy Ross mit ihrem Buch „The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling“ populär gemacht haben.

Wenn Sie sich ausführlich mit dem Data Warehousing befasst haben, sind Ihnen die gebräuchlichsten Schemamuster für relationale Data Warehouses, wie z. B. das Stern- und das Schneeflockenschema, wahrscheinlich bereits vertraut. Die Dimensionsmodellierung unterscheidet zwischen Dimensionstabellen und Faktentabellen. Während die Dimensionstabellen die Masterdaten (wie z. B. Produkte, Kunden, Filialen und Länder) enthalten, sind in der Faktentabelle die Transaktionsdaten (wie z. B. Umsätze, Aufträge, Einkäufe und Rückgaben) gespeichert.

Dimensionstabellen und Faktentabellen sind durch PK/FK-Beziehungen (primary key, Primärschlüssel; foreign key, Fremdschlüssel) miteinander verknüpft. Sie werden feststellen, dass viele Data Warehouses, um den Speicherbedarf gering zu halten, die FK-Einschränkungen nicht durchsetzen. Dadurch wird der Speicheraufwand infolge der zugrunde liegenden Indizes vermieden, und der Wartungsaufwand für die Faktentabelle wird gering gehalten. Die Dimensionstabellen in einem Data Warehouse sind normalerweise recht klein und enthalten in der Regel zwischen einigen Tausend und mehreren Millionen Zeilen. Die Faktentabelle hingegen kann sehr groß sein und zwischen einigen hundert Millionen und mehreren Milliarden Zeilen umfassen. Daher muss beim logischen Entwurf besonders der Speicherbedarf der Faktentabelle berücksichtigt werden.

Dieser Größenfaktor wirkt sich auch darauf aus, welcher Schlüssel aus einer Dimensionstabelle für die Beziehungen zwischen Fakten- und Dimensionstabelle verwendet wird. Zusammengesetzte Schlüssel auf Grundlage des Geschäftsschlüssels der Dimension (also dem realen Bezeichner der Entität, die durch die Dimension repräsentiert wird) decken normalerweise mehrere Spalten ab. Dies stellt jedoch für den zugehörigen Fremdschlüssel in der Faktentabelle ein Problem dar, weil der zusammengesetzte Mehrspaltenschlüssel für jede Zeile der Faktentabelle wiederholt wird.

Daher ist es üblich, die Beziehungen zwischen einer Faktentabelle und ihren Dimensionen mithilfe kleiner Ersatzschlüssel zu implementieren. Ein Ersatzschlüssel ist eine ID-Spalte des Typs „integer“, die als künstlicher Primärschlüssel für die Dimensionstabelle dient. Wenn sich die Faktentabelle auf den kleineren Ersatzschlüssel bezieht, wird der Speicherbedarf für große Faktentabellen deutlich verringert. Abbildung 1 zeigt das Schema eines Data Warehouse mit Dimensionsmodellierung, in dem Dimensions- und Faktentabellen mit Ersatzschlüsseln verwendet werden.

Abbildung 1** Beispiel eines Sternschemas mit einer Faktentabelle und zwei Dimensionstabellen **(Klicken Sie zum Vergrößern auf das Bild)

Der Schneeflockenschemaentwurf verteilt eine oder mehrere Dimensionen auf mehreren Ebenen (z. B. Kunde, Land und Region für eine Kundendimension), wodurch größere Dimensionen, bei denen übermäßige Datenredundanzen vorliegen können, normalisiert werden. Die Ebenen werden durch separate Tabellen repräsentiert, wodurch das Schema die Form einer Schneeflocke erhält. Im Sternschema hingegen werden die Dimensionen nicht auf Tabellen verteilt. Vielmehr hat es die Form eines Sterns, mit der Faktentabelle in der Mitte und den Dimensionstabellen um sie herum gruppiert.

Bei einem Stern- oder Schneeflockenschema mit Dimensionsmodellierung folgen die Abfragen zur Entscheidungsunterstützung einem typischen Muster: Die Abfrage wählt mehrere relevante Measures aus der Faktentabelle aus, verknüpft die Faktenzeilen anhand der Ersatzschlüssel mit einer oder mehreren Dimensionen, belegt die Geschäftsspalten der Dimensionstabellen mit Filterprädikaten, gruppiert nach einer oder mehreren Geschäftsspalten und aggregiert schließlich die aus der Faktentabelle abgerufenen Measures über einen bestimmten Zeitraum. Dieses Muster, gelegentlich auch als Sternverknüpfungsabfrage bezeichnet, wird im Folgenden näher vorgestellt:

select ProductAlternateKey,
CalendarYear,sum(SalesAmount)
from FactInternetSales Fact
     join DimTime 
on Fact.OrderDateKey = TimeKey
     join DimProduct 
on DimProduct.ProductKey =
   Fact.ProductKey
where CalendarYear between 2003 and 2004
      and ProductAlternateKey like 'BK%'
group by ProductAlternateKey,CalendarYear

Physischer Entwurf

Viele SQL-Abfragen in Ihrem relationalen Data Warehouse haben die Struktur einer Sternverknüpfungsabfrage. Die Abfragen zur Entscheidungsunterstützung ändern sich jedoch im Laufe der Zeit, weil die Entscheidungsträger nach neuen Möglichkeiten suchen, die grundlegenden Geschäftsdaten besser zu verstehen. Daher besteht die Arbeitsauslastung von Data Warehouses in der Regel zu einem großen Teil aus Ad-hoc-Abfragen. Dies macht den physischen Entwurf für Abfragen zur Entscheidungsunterstützung und ein Data Warehouse-Schema mit Dimensionsmodellierung so anspruchsvoll.

Bei SQL Server beginnt die Entwicklung eines Data Warehouse gewöhnlich mit einem Blaupausenschema oder einem physischen Entwurf, der nach und nach an die tatsächliche Arbeitsauslastung angepasst und weiterentwickelt wird. Das hier vorgestellte Blaupausenschema können Sie selbstverständlich für Ihre eigene Data Warehouse-Umgebung übernehmen und anpassen. Befolgen Sie dabei jedoch stets die bewährten Methoden für den physischen Datenbankentwurf, und berücksichtigen Sie beispielsweise die Auswirkung von Updateindexwartungen auf die Leistung und den Speicherbedarf von Indizes.

Die Faktentabelle

Das Blaupausenschema versucht, die typische Sternabfrageform vorwegzunehmen und erstellt Indizes über die Faktentabelle. Der gruppierte Index der Faktentabelle nutzt mehrere Dimensionsersatzschlüsselspalten (Fremdschlüsselspalten) als Indexschlüssel. Dabei sollten die am häufigsten genutzten Spalten in der Liste der Indexschlüssel enthalten sein. Sie können sich selbst überzeugen, dass dies ein guter Zugriffspfad für die am häufigsten ausgeführten Abfragen Ihrer Arbeitsauslastung ist.

Zusätzlich wird mit diesem Blaupausenschema für jede Dimensionsersatzspalte (Fremdschlüsselspalte) in der Faktentabelle ein nicht gruppierter Index über eine Spalte erstellt. So entsteht ein überaus effizienter Zugriffspfad für Abfragen, die in einer ihrer Dimensionen sehr selektiv sind.

Ziel des gruppierten Indexes ist es, für die Mehrzahl der Abfragen in der Arbeitsauslastung eine gute Leistung zu erzielen. Der Satz nicht gruppierter Indizes zielt speziell auf Abfragen ab, mit denen Faktentabellenmeasures für einen bestimmten Kunden oder ein bestimmtes Produkt abgerufen werden. Durch diese nicht gruppierten Indizes wird sichergestellt, dass beispielsweise nicht die komplette Faktentabelle durchlaufen werden muss, um die Umsatzzahlen eines einzelnen Kunden abzurufen.

Dimensionstabellen

Wenn Sie das Blaupausenschema auf Dimensionstabellen anwenden, müssen Sie für alle Dimensionstabellen Indizes erstellen. Diese umfassen einen nicht gruppierten Primärschlüssel-Einschränkungsindex auf der Ersatzschlüsselspalte der Dimension sowie einen gruppierten Index über die Spalten des Geschäftsschlüssels der Dimensionsentität. Bei großen Dimensionstabellen sollten Sie erwägen, nicht gruppierte Indizes für Spalten hinzuzufügen, die häufig in stark selektiven Prädikaten verwendet werden.

Der gruppierte Index erleichtert das effiziente Extrahieren, Transformieren und Laden (ETL) im Wartungsfenster des Data Warehouse, wobei es sich oft um einen besonders zeitkritischen Prozess handelt. Bei sich langsam ändernden Dimensionen beispielsweise werden die vorhandenen Zeilen direkt vor Ort aktualisiert, während bislang noch nicht in der Dimension enthaltene Zeilen an die Dimensionstabelle angehängt werden. Für einen erfolgreichen Einsatz dieses Zugriffsmusters ist eine gut funktionierende Suche und Aktualisierung der Dimensionstabelle zum ETL-Zeitpunkt Voraussetzung.

Das hier vorgestellte Blaupausenschema ist ein guter Ausgangspunkt für physische Entwürfe in relationalen Data Warehouses, die mit SQL Server erstellt wurden. Ausgehend von diesem typischen relationalen Data Warehouse-Setup werden wir nun näher auf einige zentrale neue Features von SQL Server 2008 eingehen.

Optimierung von Sternverknüpfungsabfragen

Die größten Kosten bei der Ausführung einer Sternverknüpfungsabfrage in einem relationalen Data Warehouse mit Dimensionsmodellierung entstehen gewöhnlich durch die Verarbeitung der Faktentabelle. Das ist auch ganz logisch, denn selbst stark selektive Abfragen rufen bedeutend mehr Zeilen aus der Faktentabelle als aus einer der Dimensionen ab. Daher ist ein optimaler Zugriffspfad für die Faktentabelle ein wesentlicher Faktor für eine gute Abfrageleistung.

In SQL Server wählt der Abfrageoptimierer automatisch aus einer Reihe von Alternativen den Zugriffspfad mit den niedrigsten geschätzten Kosten aus. Beim Data Warehousing besteht das Hauptziel darin sicherzustellen, dass der Abfrageoptimierer die besten alternativen Zugriffspfade für den Ausführungsplan der Sternverknüpfungsabfrage berücksichtigt. Der Abfrageoptimierer von SQL Server umfasst verschiedene Funktionalitäten, um automatisch gut funktionierende Ausführungspläne für Sternverknüpfungsabfragen erstellen zu können.

Bei Sternverknüpfungsabfragen lässt sich zwischen drei verschiedenen Klassen unterscheiden, wie in Abbildung 2 dargestellt. Diese weit gefassten Klassen helfen dem SQL Server-Modul auch dabei, den richtigen Plan für eine Abfrage auszuwählen. Das Hauptkonzept von SQL Server basiert auf der Selektivität dieser Abfragen in Bezug auf die Faktentabelle. Eine Abfrage gilt als umso selektiver, je weniger Zeilen sie aus der Faktentabelle benötigt. Für die Beurteilung der Abfrageklassen wird der prozentuale Anteil der Zeilen verwendet, die aus der Faktentabelle abgerufen wurden. Diese Prozentzahlen entsprechen Werten aus typischen Kundenumgebungen, stellen aber keine strengen Grenzen für die Erzeugung von Zugriffspfaddefinitionen dar.

Abbildung 2** Selektivitätsbereiche für Sternverknüpfungsabfragen **(Klicken Sie zum Vergrößern auf das Bild)

Die erste Klasse umfasst stark selektive Abfragen, bei denen bis zu 10 Prozent der Zeilen aus der Faktentabelle verarbeitet werden. Die zweite Klasse mit mittlerer Selektivität besteht aus Abfragen, bei denen zwischen 10 und 75 Prozent der Faktentabellenzeilen verarbeitet werden. Abfragen in der dritten Klasse weisen eine geringe Selektivität auf und erfordern die Verarbeitung von über 75 Prozent aller in der Faktentabelle gespeicherten Zeilen. Die Kästchen in der Abbildung zeigen auch die zur Auswahl stehenden grundlegenden Abfrageausführungspläne für die einzelnen Selektivitätsklassen.

Planauswahl anhand der Selektivität

Da stark selektive Sternabfragen normalerweise nicht mehr als 10 Prozent der Faktentabellenzeilen abrufen, können solche Abfragen zufällige Zugriffe auf die Faktentabelle verkraften. Deshalb sind die Abfragepläne für diese Klasse stark abhängig von Verknüpfungen über verschachtelte Schleifen in Verbindung mit (nicht gruppierten) Indexsuchen und Lesezeichensuchen in der Faktentabelle. Diese zufälligen E/A-Zugriffe auf die Faktentabelle werden jedoch von sequenziellen E/A-Zugriffen übertroffen, sobald größere Teile der Faktentabelle abgerufen werden müssen. Dadurch werden alternative Abfragepläne erforderlich, sobald die Anzahl der Zeilen aus der Faktentabelle eine bestimmte Zahl überschreitet.

Da Sternabfragen mittlerer Selektivität einen erheblichen Anteil der Faktentabellenzeilen verarbeiten, werden für den Zugriff auf die Faktentabelle gewöhnlich Hashverknüpfungen mit Faktentabellenscans oder Faktentabellen-Bereichsscans bevorzugt. Um die Leistung dieser Hashverknüpfungen zu verbessern, verwendet SQL Server Bitmusterfilter.

Abbildung 3 zeigt, wie SQL Server mithilfe dieser Bitmusterfilter die Verknüpfungsleistung bei der Ausführung von Sternverknüpfungsabfragen verbessert. Die Abbildung zeigt den Plan für eine Abfrage in zwei Dimensionstabellen (Produkt und Zeit), die anhand ihrer Ersatzschlüssel mit der Faktentabelle verknüpft werden. Die Abfrage wendet für beide Dimensionstabellen Filterprädikate (wie z. B. WHERE-Klauseln) an, sodass sich für jede Dimension nur eine Zeile in Frage kommt. Dies wird durch die kleinen roten Tabellen neben den beiden Verknüpfungsoperatoren angezeigt.

Abbildung 3** Abfrageplan mit Sternverknüpfungen und Verknüpfungsverringerung **(Klicken Sie zum Vergrößern auf das Bild)

Die einzelnen Verknüpfungen werden durch eine Hashverknüpfung implementiert, sodass SQL Server die Informationen zu in Frage kommenden Zeilen aus den Dimensionstabellen abrufen und für beide Dimensionstabellen für Informationen zur Verknüpfungsverringerung verwenden kann. Die grünen Kästchen in der Abbildung stellen die Datenstrukturen mit Informationen zur Verknüpfungsverringerung dar. Nachdem diese Datenstrukturen mittels der zugrunde liegenden Dimensionstabellen ausgefüllt wurden, verschiebt SQL Server sie während der Abfrageausführung automatisch zu dem Operator, der die Faktentabelle verarbeitet (z. B. einem Tabellenscan). Dieser Operator nutzt die Informationen zu den Dimensionstabellenzeilen, um die Zeilen der Faktentabellen zu entfernen, die für die Verknüpfungsbedingungen in Bezug auf die Dimensionen nicht in Frage kommen.

SQL Server bereinigt die Zeilen der Faktentabelle schon sehr früh während der Abfrageverarbeitung, nämlich direkt nach dem Abruf der Zeile aus der Faktentabelle. Dies bewirkt Einsparungen bei der CPU-Nutzung und potentiell auch bei den Datenträger-E/As, da die entfernten Zeilen nicht mehr durch weitere Operatoren des Abfrageplans verarbeitet werden müssen. SQL Server nutzt für die effiziente Implementierung der Datenstrukturen mit Informationen zur Verknüpfungsverringerung zum Zeitpunkt der Abfrageausführung eine Bitmusterdarstellung.

Pipeline zur Sternverknüpfungsoptimierung

Im Optimierungsprozess werden Verknüpfungsabfragen mittels der Standardheuristik optimiert, um so einen grundlegenden Satz von Alternativen für den Abfrageausführungsplan zu erstellen. Um zusätzliche Abfrageplanalternativen zu erstellen, werden dann Erweiterungen für besondere Zwecke aufgerufen.

Im Fall des Data Warehousing erkennt die Erweiterung Stern- und Schneeflockenschemas sowie das Sternabfragemuster und schätzt die Selektivität der Abfrage in Bezug auf die Faktentabelle ein. Wenn das Schema und die Abfrageform den Mustern entsprechen, fügt SQL Server dem Planbereich automatisch weitere Abfragepläne hinzu. Aus diesen wird dann bei der kostenbasierten Optimierung der vielversprechendste Abfrageplan für die Ausführung ausgewählt.

Zur Ausführungszeit der Abfrage überwacht SQL Server auch die tatsächliche Selektivität der Verknüpfungsverringerung zur Laufzeit. Wenn sich die Selektivität ändert, ordnet SQL Server die Datenstrukturen mit den Informationen zur Verknüpfungsverringerung so an, dass die selektivste zuerst angewendet wird.

Sternverknüpfungsheuristik

Viele physische Entwürfe für Data Warehouses folgen dem Sternschema, geben aber die Beziehungen zwischen Fakten- und Dimensionstabellen nicht vollständig an, wie z. B. bei den bereits erwähnten Fremdschlüsseleinschränkungen. Wenn die Fremdschlüsseleinschränkungen nicht explizit angegeben sind, ist SQL Server zur Erkennung von Sternschema-Abfragemustern auf die Heuristik angewiesen. Zur Erkennung von Sternverknüpfungs-Abfragemustern wird folgende Heuristik angewendet:

1. Die größte an der n-ary-Verknüpfung beteiligte Tabelle wird als Faktentabelle behandelt. Für die Mindestgröße der Faktentabelle gelten zusätzliche Einschränkungen. Wenn beispielsweise die größte Tabelle eine bestimmte Größe nicht überschreitet, wird die n-ary-Verknüpfung nicht als Sternverknüpfung betrachtet.
2. Sämtliche Verknüpfungsbedingungen der Binärverknüpfungen in einer Sternverknüpfungsabfrage müssen Gleichheitsprädikate auf nur einer Spalte sein. Alle Verknüpfungen müssen innere Verknüpfungen sein. Dies mag restriktiv scheinen, deckt aber tatsächlich die große Mehrheit der Verknüpfungen zwischen Faktentabelle und Dimensionstabellen auf dem Ersatzschlüssel in typischen Sternschemas ab. Wenn eine Verknüpfung eine komplexere Verknüpfungsbedingung besitzt, die nicht dem oben beschriebenen Muster entspricht, wird die Verknüpfung aus der Sternverknüpfung ausgeschlossen. Ein Fünf-Wege-Verknüpfung beispielsweise kann zu einer Drei-Wege-Sternverknüpfung führen (mit zwei zusätzlichen späteren Verknüpfungen), wenn zwei der Verknüpfungen komplexere Verknüpfungsprädikate besitzen.

Beachten Sie, dass dies heuristische Regeln sind. In einigen Fällen wählt die Heuristik eine Dimensionstabelle als Faktentabelle aus. Dies beeinflusst zwar die Planauswahl, hat aber keinen Einfluss auf die Korrektheit des ausgewählten Plans. Die Binärverknüpfungen in der Sternverknüpfung werden dann nach absteigender Selektivität geordnet. In diesem Zusammenhang ist die Verknüpfungsselektivität definiert als das Verhältnis zwischen der Kardinalität der Eingangsrelation der Faktentabelle und der Kardinalität der Ergebnisrelation der Verknüpfung. Die Verknüpfungsselektivität gibt an, wie stark eine bestimmte Dimension die Kardinalität einer Faktentabelle verringert. Allgemein gilt, dass Verknüpfungen mit höherer Selektivität zuerst behandelt werden sollten.

Der Abfrageprozessor in SQL Server wendet die Optimierung automatisch auf Abfragen an, die dem Sternverknüpfungsmuster und den oben genannten Bedingungen entsprechen, sofern die geschätzten Abfragekosten der sich ergebenden Abfragepläne attraktiv sind. Folglich müssen Sie keine Änderungen an Ihrer Anwendung vornehmen, damit diese von dieser deutlichen Leistungsverbesserung profitieren kann. Beachten Sie, dass einige der Sternverknüpfungsoptimierungen wie z. B. die Verknüpfungsverringerung nur in SQL Server Enterprise Edition verfügbar sind.

Leistungsergebnisse von Sternverknüpfungen

Im Rahmen der Entwicklung der Sternverknüpfungsoptimierung in SQL Server 2008 haben wir eine Reihe von Leistungsstudien mit Benchmark- und tatsächlichen Kundenarbeitsauslastungen durchgeführt. Besonders interessant sind dabei die Ergebnisse dreier dieser Arbeitsauslastungen.

Data Warehouse von Microsoft Sales In diesem Beispiel wird die Leistung eines Data Warehouse verfolgt, das in der Vertriebsabteilung von Microsoft zur internen Entscheidungsunterstützung verwendet wird. Dazu haben wir einen Beispielsnapshot der Datenbank mit einer Größe von etwa 750 GB (einschließlich Indizes) erstellt. Die Verarbeitung von Abfragen bei dieser Arbeitsauslastung ist anspruchsvoll, da viele mehr als 10 Verknüpfungen umfassen.

Einzelhandelskunde Diese Testreihe wurde für einen Data Warehousing-Kunden im Einzelhandel durchgeführt (mit einem Ladengeschäft und einem Onlineshop). Bei diesem Kunden treten ein dimensional modelliertes Schneeflockenschema sowie kanonische Sternverknüpfungsabfragen auf. Für unsere Tests haben wir einen Snapshot des Warehouse mit etwa 100 GB Rohdaten gefüllt.

Arbeitsauslastung für die Entscheidungsunterstützung Bei dieser Testreihe wurde die Leistung einer Arbeitsauslastung zur Entscheidungsunterstützung bei einer 100 GB großen Datenbank mit Dimensionsmodellierung untersucht. Abbildung 4 zeigt das Ergebnis für diese drei Arbeitsauslastungen. Dargestellt sind die normalisierten geometrischen Mittel der Abfrageantwortzeiten über alle Abfragen in der Arbeitsauslastung hinweg. Diese Metrik ist ein guter Indikator dafür, welche Abfrageleistung zu erwarten ist, wenn eine beliebige Abfrage von der Arbeitsauslastung ausgeführt wird. Die Balken in der Abbildung vergleichen die grundlegenden Leistungswerte ohne Sternverknüpfungsoptimierung (1,0) mit der Leistung bei Verwendung der Sternverknüpfungsoptimierung. Alle Testläufe wurden mit SQL Server 2008 durchgeführt.

Abbildung 4** Leistungssteigerung durch Sternverknüpfungsoptimierung **(Klicken Sie zum Vergrößern auf das Bild)

Wie die Abbildung zeigt, haben sich alle Arbeitsauslastungen deutlich verbessert (um 12 bis 30 Prozent). Während die Werte im Einzelfall natürlich variieren können, ist mit einer durchschnittlichen Verbesserung der Arbeitsauslastung für die Entscheidungsunterstützung um rund 15–20 Prozent im Vergleich zum SQL Server-Modul zu rechnen, basierend auf der Erweiterung der neuen sternverknüpfungsspezifischen Optimierungen in SQL Server 2008.

Parallelverarbeitung partitionierter Tabellen

Um die Abfrageverarbeitung in großen Data Warehouses zu beschleunigen, werden große Faktentabellen von den Datenbankadministratoren häufig nach Datum partitioniert. Dabei werden die Daten in verschiedenen FileGroups platziert, was die zu durchsuchende Datenmenge bei der Verarbeitung von Zeilen innerhalb eines bestimmten Datenbereichs verringert. Zugleich wird die Leistung des zugrunde liegenden Datenträgersystems besser genutzt, wenn die FileGroups über eine große Anzahl physischer Datenträger verteilt sind.

Seit SQL Server 2005 besteht die Möglichkeit, eine große Relation in kleinere logische Abschnitte zu partitionieren, um die Verwaltung und Handhabung großer Tabellen zu erleichtern. Damit ließ sich auch erfolgreich die Abfrageverarbeitung optimieren, insbesondere bei großen Anwendungen zur Entscheidungsunterstützung.

Leider mussten einige Nutzer von SQL Server 2005 bei Abfragen auf diesen partitionierten Tabellen Leistungseinbußen feststellen, insbesondere bei der Ausführung auf Mehrprozessorcomputern mit gemeinsam genutztem Speicher. Bei der Verarbeitung paralleler Abfragen auf partitionierten Tabellen in SQL Server 2005 kann es vorkommen, dass nur ein Teil der verfügbaren Threads zum Ausführen der Abfrage zugeteilt wird.

Angenommen, bei einem 64-Kern-Prozessor könnten Abfragen bis zu 64 Threads parallel nutzen, und eine Abfrage betrifft zwei Partitionen. Bei SQL Server 2005 werden der Abfrage nur 2 der 64 Threads zugewiesen, sodass nur zwei Vierundsechzigstel (3,1 Prozent) der CPU-Leistung des Computers genutzt werden. Es hat sich gezeigt, dass bei manchen Abfragen die Leistung bei einer Partitionierung zehn Mal oder noch schlechter sein kann, als wenn dieselbe Abfrage auf demselben Computer mit einer nicht partitionierten Version der gleichen Faktentabelle ausgeführt wird.

SQL Server 2005 wurde nämlich speziell für Abfragen optimiert, die nur eine Partition betreffen. In einem solchen Fall weist der Abfrageprozessor alle verfügbaren Threads für die Durchführung des Scanvorgangs zu. Diese besondere Art der Optimierung bewirkt eine enorme Leistungssteigerung bei Abfragen auf einer Partition auf Mehrkerncomputern. Da war es nur verständlich, dass die Anwender ein vergleichbares Verhalten auch bei Abfragen über mehrere Partitionen erwarteten.

Das neue PTP-Feature (partitioned table parallelism) in SQL Server 2008 verbessert die Abfrageleistung bei Partitionierungen, indem die Rechenleistung der vorhandenen Hardware besser genutzt wird, unabhängig davon, wie viele Partitionen eine Abfrage betriff und wie groß die einzelnen Partitionen tatsächlich sind. In einem typischen Data Warehouse-Szenario mit einer partitionierten Faktentabelle ist bei Abfragen mit parallelen Plänen eine deutliche Leistungssteigerung möglich, insbesondere wenn die Anzahl der verfügbaren Prozessorkerne größer ist als die Anzahl der von der Abfrage betroffenen Partitionen. Dieses neue Feature ist sofort einsatzbereit. Es sind keine Abstimmungs- oder Konfigurationsmaßnahmen erforderlich.

Angenommen, eine Faktentabelle enthält die Umsatzzahlen sortiert nach Verkaufsdatum über vier Partitionen verteilt. Das Diagramm in Abbildung 5 zeigt dieses Beispiel. Beachten Sie, dass es anstelle eines einzelnen gruppierten Indexes für den gesamten Datenbereich (wie im Fall ohne Partitionen) hier für jede Partition der Faktentabelle in der Regel einen gruppierten Index auf der Datumsspalte gibt. Nehmen wir jetzt an, dass die Abfrage A die Umsatzzahlen der letzten sieben Tage zusammenfasst. Während über die letzte Partition (P4) kontinuierlich neue Vertriebsdaten in die Faktentabelle gelangen, berührt die Abfragen sehr wahrscheinlich mehrere Partitionen, je nachdem, wann sie ausgeführt wird. Dies wird in der ersten Zeile des Diagramms dargestellt: Die Abfrage A1 betrifft nur eine einzelne Partition, während die Abfrage A2 zwei Partitionen betrifft, da sich die relevanten Daten zum Zeitpunkt der Ausführung über die Partitionen P3 und P4 erstrecken.

Abbildung 5** Das neue PTP-Feature im Einsatz **(Klicken Sie zum Vergrößern auf das Bild)

Nehmen wir nun an, dass acht Threads verfügbar sind. Bei der Ausführung von A1 und A2 mit SQL Server 2005 kann es zu unerwartetem Verhalten kommen. Wenn der Optimierer in SQL Server 2005 zur Kompilierzeit darüber informiert ist, dass die Abfrage nur eine Partition berührt, wird diese Partition wie eine einzelne, nicht partitionierte Tabelle behandelt. Es wird dann ein Plan erstellt, der mit allen verfügbaren Threads auf die Tabelle zugreift.

Die Abfrage A1, bei der nur eine Partition (P3) betroffen ist, resultiert so in einem Plan, bei dem die Verarbeitung durch acht Threads erfolgt (nicht gezeigt). A2 hingegen betrifft zwei Partitionen. Hier weist das Ausführungsmodul jeder Partition nur einen Thread zu, selbst wenn die zugrunde liegende Hardware über zusätzliche Threads verfügt. Folglich nutzt A2 nur einen sehr kleinen Teil der verfügbaren CPU-Leistung und ist in der Ausführung wahrscheinlich deutlich langsamer als A1.

Die Ausführung von A1 und A2 mit SQL Server 2008 bewirkt eine bessere Nutzung der verfügbaren Hardware, eine bessere Leistung und ein besser vorhersagbares Verhalten. Im Fall von A1 weist das Ausführungsmodul der Datenverarbeitung in P2 wieder alle acht verfügbaren Threads zu (nicht gezeigt). A2 hingegen resultiert in einem parallelen Plan, bei dem das Ausführungsmodul P3 und P4 im Roundrobinstil alle verfügbaren Threads zuweist. Das Ergebnis ist in der untersten Zeile des Diagramms dargestellt: Jede der beiden Partitionen erhält vier Threads. Die CPU wird also weiterhin voll genutzt, und die Leistung von A1 und A2 ist vergleichbar.

Eine solche Roundrobinzuordnung der Threads ermöglicht eine zunehmend bessere Abfrageleistung, je mehr Prozessorkerne im Vergleich zur Anzahl der Partitionen, auf die die Abfrage zugreift, vorhanden sind. Leider ist die Zuordnung der Threads zu Partitionen nicht in jedem Fall so einfach wie in diesem Beispiel.

Die Leistungssteigerung von SQL Server 2005 zu SQL Server 2008 bei partitionierten Tabellen und einem Mehrkernprozessor-PC ist in Abbildung 6 näher dargestellt. Dieses Einzelfalldiagramm illustriert die Scanleistung für partitionierte Tabellen. Bei diesem speziellen Test, der auf einem System mit 64 Kernen und 256 GB RAM durchgeführt wurde, haben wir eine 121 GB große Tabelle in 11 Partitionen zu je 11 GB partitioniert. Für die in dieser Abbildung dargestellte Testreihe wurde eine Heapdateiorganisation verwendet, einmal mit „warmem“ und einmal mit „kaltem“ Puffer. Sämtliche Abfragen führen einfache Datenscans durch.

Abbildung 6** Scanleistung für SQL Server mit aktiviertem neuem PTP-Feature **(Klicken Sie zum Vergrößern auf das Bild)

Auf der Y-Achse ist die Antwortzeit (in Sekunden) dargestellt und auf der X-Achse der Parallelitätsgrad (degree of parallelism, DOP), der der Anzahl an Threads entspricht, die der Abfrage zugewiesen sind. Sowohl bei warmem als auch bei kaltem Puffer sinken die Antwortzeiten, bis der DOP den Wert 22 erreicht. An diesem Punkt tritt die Sättigung des E/A-Systems für den Start mit kaltem Puffer ein. Der Grund dafür ist, dass die Abfrage in diesem Beispiel E/A-gebunden ist. Bei stärker CPU-gebundenen Arbeitsauslastungen besteht diese Einschränkung möglicherweise nicht oder tritt erst bei höheren DOPs auf.

Die Kurve für den Start mit warmem Puffer hingegen zeigt bei steigendem Parallelitätsgrad auch weiterhin sinkende Antwortzeiten. Bei SQL Server 2005 würden beide Kurven etwa bei DOP 11 anfangen abzuflachen, da die Anzahl der Threads pro Partition im Fall mehrerer Partitionen auf 1 begrenzt ist.

Wichtig ist festzuhalten, dass in der Praxis die Verbesserung der Antwortzeiten bei einem höheren Parallelitätsgrad nie linear ist. Vielmehr ähnelt das erwartete Verhalten dem einer Schrittfunktion. Dies spiegelt die Tatsache wider, dass die Abfrage im Prinzip immer auf das langsamste Unterelement wartet. Daher würde das Hinzufügen eines weiteren Threads zu einem Scanvorgang die Ausführungszeit einer Abfrage erst verbessern, wenn alle übrigen Scans ebenfalls zusätzliche Threads erhalten haben, sodass sie schneller ausgeführt werden können.

In weiteren Tests haben wir das neue PTP-Verhalten für verschiedene andere Hardware- und Dateikonfigurationen untersucht. Bei steigendem Parallelitätsgrad über einen Thread bzw. eine Partition hinaus ließ sich in Sachen Skalierung des Durchsatzes ein ähnliches Verhalten feststellen.

Nicht zuletzt verbessert das neue PTP-Feature in SQL Server 2008 auch die Lesbarkeit von Abfrageplänen und ermöglicht einen besseren Einblick in die Ausführung bestimmter Arbeitsauslastungen. Beispielsweise wurde im Rahmen des PTP-Features auch die Darstellung paralleler und serieller Pläne in Showplan-XML verbessert, und auch die Partitionierungsinformationen in Kompilierzeit- und Laufzeitausführungsplänen wurden optimiert.

Datenkomprimierung

Mit der zunehmenden Verbreitung von Business Intelligence lassen Unternehmen mehr und mehr Daten zur Analyse in ihre Data Warehouses einfließen. Das Ergebnis ist ein exponentielles Wachstum der zu verwaltenden Datenmenge. 1995 hat die erste Studie der Winter Corporation zum Thema Datenbankgröße ergeben, dass das weltweit größte System etwa ein Terabyte an Daten enthielt. Zehn Jahre später war die größte Datenbank etwa 100 Mal so groß. Erstaunlicher ist jedoch die Tatsache, dass sich die Größe von Data Warehouses etwa alle zwei Jahre verdreifacht. Daraus ergeben sich neue Herausforderungen bei der Verwaltung solch großer Datenmengen und der Realisierung akzeptabler Leistungsniveaus bei Data Warehouse-Abfragen. Solche Abfragen sind in der Regel komplex, umfassen zahlreiche Verknüpfungen und Aggregate und greifen auf große Datenmengen zu. Zudem ist es nicht ungewöhnlich, dass viele Abfragen E/A-gebunden sind.

Speziell auf dieses Problem zielt die systemeigene Datenkomprimierung ab. Mit SQL Server 2005 SP2 wurde ein neues Speicherformat mit variabler Länge eingeführt – das vardecimal-Format für Dezimalzahlen und numerische Daten. Mit diesem neuen Speicherformat lässt sich die Größe einer Datenbank erheblich verringern. Diese Platzersparnis wiederum kann auf zweierlei Weise helfen, die Leistung von E/A-gebundenen Abfragen zu steigern. Erstens müssen weniger Seiten gelesen werden, und zweitens wird die Lebensdauer der Seiten verbessert, da die Daten komprimiert im Pufferpool gehalten werden. (Anders ausgedrückt: Auf diese Weise wird die Wahrscheinlichkeit erhöht, dass sich die angeforderte Seite bereits im Puffer befindet.) Selbstverständlich sind mit der Platzersparnis durch die Datenkomprimierung auch CPU-Kosten für die Komprimierung und Dekomprimierung der Daten verbunden.

SQL Server 2008 basiert auf dem vardecimal-Speicherformat und verfügt über zwei Arten von Komprimierung: die Zeilenkomprimierung und die Seitenkomprimierung. Die Zeilenkomprimierung erweitert das vardecimal-Speicherformat, indem sämtliche Datentypen fester Länge in einem Speicherformat variabler Länge gespeichert werden.

Beispiele für Datentypen fester Länge sind Integer-, Char- und Gleitkommadatentypen. Obwohl SQL Server diese Datentypen in einem Format variabler Länge speichert, bleibt die Semantik der Datentypen unverändert erhalten (aus Sicht der Anwendung bleibt der Datentyp ein Datentyp fester Länge). Sie können also die Vorteile der Datenkomprimierung nutzen, ohne Änderungen an Ihren Anwendungen vornehmen zu müssen.

Die Seitenkomprimierung minimiert die Datenredundanz in Spalten in einer oder mehreren Zeilen einer gegebenen Seite. Dazu wird eine proprietäre Implementierung des LZ78-Algorithmus (Lempel-Ziv-Algorithmus) genutzt, bei dem die redundanten Daten nur einmal auf der Seite gespeichert werden und bei dem dann von mehreren Spalten auf diese Seite verwiesen wird. Beachten Sie, dass die Seitenkomprimierung die Zeilenkomprimierung praktisch bereits umfasst.

Die Zeilen- und die Seitenkomprimierung können für eine Tabelle, einen Index oder bei partitionierten Tabellen und Indizes für eine oder mehrere Partitionen aktiviert werden. So haben Sie völlige Flexibilität bei der Auswahl der zu komprimierenden Tabellen, Indizes und Partitionen und können die richtige Balance zwischen Platzersparnis und CPU-Kosten finden. Abbildung 7 illustriert dies anhand einer auf unterschiedliche Weise partitionierten Umsatztabelle mit ausgerichteten Indizes.

Abbildung 7** Partitionierte Tabelle mit unterschiedlichen Komprimierungseinstellungen **(Klicken Sie zum Vergrößern auf das Bild)

Jede Partition entspricht einem Quartal, wobei Okt-Dez das aktuelle Quartal ist. Angenommen, auf die ersten beiden Partitionen wird nur selten zugegriffen, auf die dritte Partition häufiger und meistens auf die letzte Partition. In diesem Fall wäre eine mögliche Konfiguration, für die ersten beiden Partitionen die Seitenkomprimierung zu aktivieren (für maximale Platzeinsparung bei minimaler Auswirkung auf die Rechenleistung), für die dritte Partition die Zeilenkomprimierung zu nutzen und die letzte Partition unkomprimiert zu verwenden.

Die Komprimierung können Sie online und offline mithilfe der DDL-Anweisungen (Data Definition Language, Datendefinitionssprache) ALTER TABLE und ALTER INDEX aktivieren. SQL Server verfügt auch über eine gespeicherte Prozedur zum Abschätzen der Platzeinsparungen. Die erreichbaren Platzeinsparungen hängen von der Datenverteilung und dem Schema des komprimierten Objekts ab.

Die Testergebnisse bei vielen Kundendatenbanken lassen vermuten, dass die Mehrzahl der Anwender auf diese Weise die Größe ihrer Datenbank um 50 bis 65 Prozent verringern und die Leistung der E/A-gebundenen Abfragen deutlich verbessern kann. Die Auswirkung auf die Leistung von CPU-gebundenen Abfragen einzuschätzen, ist jedoch etwas schwieriger und hängt von der Komplexität der Abfrage ab. Bei SQL Server entstehen die Kosten für die Dekomprimierung nur bei einem Zugriff auf den Index oder die Tabellen. Wenn die relativen CPU-Kosten für Scanoperatoren im Vergleich zu den CPU-Gesamtkosten der Abfrage gering sind, wie es bei einem Data Warehouse-Szenario normalerweise der Fall ist, dürfte der Einfluss auf die CPU-Nutzung maximal 20–30 Prozent betragen.

Indizierte Sichten mit ausgerichteter Partitionierung

In SQL Server 2008 ermöglichen indizierte Sichten mit ausgerichteter Partitionierung eine effizientere Erstellung und Verwaltung von Zusammenfassungsaggregaten in einem relationalen Data Warehouse und deren Verwendung in Szenarios, in denen eine effiziente Nutzung bislang nicht möglich war. Dies verbessert die Abfrageleistung. Ein typisches Szenario ist eine Faktentabelle, die nach Datum partitioniert ist. Für diese Tabelle sind indizierte Sichten (oder Zusammenfassungsaggregate) definiert, um Abfragen zu beschleunigen. Wenn zu einer neuen Tabellenpartition gewechselt wird, wird automatisch auch zu den entsprechenden Partitionen der indizierten Sichten mit ausgerichteter Partitionierung gewechselt, die für die partitionierte Tabelle definiert sind.

Dies stellt eine erhebliche Verbesserung gegenüber SQL Server 2005 dar, wo die für eine partitionierte Tabelle definierten indizierten Sichten zuerst verworfen werden mussten, bevor mit dem Vorgang ALTER TABLE SWITCH eine Partition gewechselt werden konnte. Die indizierten Sichten mit ausgerichteter Partitionierung in SQL Server 2008 bieten Ihnen die Vorteile indizierter Sichten auf große partitionierte Tabellen, vermeiden dabei aber die Kosten für eine erneute Aggregaterstellung für eine komplette partitionierte Tabelle. Zu den Vorteilen gehören auch die automatische Verwaltung von Aggregaten und die Verwendung vorhandener indizierter Sichten.

Sperrenausweitung auf Partitionsebene

SQL Server unterstützt die Bereichspartitionierung, sodass Daten zur besseren Verwaltbarkeit partitioniert oder gemäß ihrem Nutzungsmuster gruppiert werden können. Beispielsweise könnten Umsatzzahlen auf monatlicher oder vierteljährlicher Basis partitioniert werden. Außerdem können Sie eine Partition ihrer eigenen FileGroup zuordnen und umgekehrt die FileGroup einer Gruppe von Dateien zuweisen. Dies bietet zwei wichtige Vorteile. Erstens können Sie eine Partition als unabhängige Einheit sichern und wiederherstellen. Zweitens können Sie eine FileGroup einem langsamen oder schnellen E/A-Subsystem zuordnen, je nach Nutzungsmuster oder der Abfragelast.

Ein interessanter Punkt dabei ist das Zugriffsmuster der Daten. Möglicherweise müssen die Abfragen und DML-Vorgänge nur auf eine Teilmenge der Partitionen zugreifen und diese ändern. Wenn Sie beispielsweise die Umsatzzahlen des Jahres 2004 analysieren, benötigen Sie nur Zugriff auf die entsprechenden Partitionen. Im Idealfall kommt es (mit Ausnahme der Systemressourcen) zu keiner Beeinflussung durch Abfragen, die gleichzeitig auf Daten in anderen Partitionen zugreifen. In SQL Server 2005 kann der gleichzeitige Zugriff auf Daten in anderen Partitionen zu einer Tabellensperre führen, die den Zugriff auf andere Partitionen beeinflusst.

Um solche Störungen auf ein Minimum zu reduzieren, wurde bei SQL Server 2008 eine Option auf Tabellenebene eingeführt, mit der sich die Sperrenausweitung auf Partitions- oder Tabellenebene steuern lässt. Standardmäßig ist die Sperrenausweitung auf Tabellenebene aktiviert, wie es auch bei SQL Server 2005 der Fall ist. Bei Bedarf kann jedoch die Richtlinie für die Sperrenausweitung für die Tabelle außer Kraft gesetzt werden. Sie könnten die Sperrenausweitung beispielsweise wie folgt festlegen:

Alter table <mytable> set (LOCK_ESCALATION = AUTO)

Dieser Befehl weist SQL Server an, die für das Tabellenschema geeignete Sperrenausweitungsgranularität auszuwählen. Wenn die Tabelle nicht partitioniert ist, gilt die Sperrenausweitung auf Tabellenebene. Wenn die Tabelle partitioniert ist, gilt die Sperrenausweitung auf Partitionsebene. Diese Option dient SQL Server auch als Hinweis, auf die Sperrengranularität auf Tabellenebene zu verzichten.

Zusammenfassung

Dieser Artikel bietet nur einen kurzen Überblick über die verbesserten Features in SQL Server 2008, mit denen Sie die Leistung bei Abfragen zur Entscheidungsunterstützung in relationalen Data Warehouses verbessern können. Bedenken Sie, dass kurze Antwortzeiten bei den Abfragen zur Entscheidungsunterstützung zwar wichtig sind, dass aber auch andere wichtige Anforderungen berücksichtigt werden müssen, die den Rahmen dieses Artikels sprengen würden.

Hier einige Beispiele für weitere Funktionen im Zusammenhang mit relationalem Data Warehousing:

• Unterstützung für die MERGE-Syntax in T-SQL, um (Dimensions-) Daten mit nur einer Anweisung und einem Roundtrip in der Datenbank zu aktualisieren, zu löschen oder einzufügen.
• Optimierte Protokollierungsleistung des SQL Server-Moduls für effizienteres ETL.
• Gruppierungssätze für eine vereinfachte Erstellung von Aggregatabfragen zur Entscheidungsunterstützung in T-SQL.
• Sicherungskomprimierung zur Verminderung der E/A-Anforderungen für vollständige und inkrementelle Sicherungen.
• Ressourcenkontrolle zur Steuerung der Systemressourcenzuordnung zu verschiedenen Arbeitsauslastungen.

Ausführlichere Informationen zu diesen spannenden Features finden Sie auf der SQL Server-Webseite unter microsoft.com/sql.

Unser Dank gilt Boris Baryshnikov, Prem Mehra, Peter Zabback und Shin Zhang für ihre fachliche Unterstützung.

Sunil Agarwal ist Senior Program Manager in der Arbeitsgruppe für das SQL Server-Speichermodul bei Microsoft. Zu seinen Arbeitsschwerpunkten zählen Parallelität, Indizes, tempdb, LOBs, Unterstützbarkeit sowie Massenimport und -export.

Torsten Grabs ist Senior Program Manager Lead für das Kernspeichermodul im Microsoft SQL Server-Team. Er besitzt einen PhD für Datenbanksysteme und verfügt über 10 Jahre Erfahrung in der Arbeit mit SQL Server.

Dr. Joachim Hammer ist Program Manager in der Arbeitsgruppe für Abfrageverarbeitung bei Microsoft. Zu seinen Spezialgebieten gehören die Abfrageoptimierung bei umfassenden Data Warehouse-Anwendungen sowie verteilte Abfragen, ETL und Informationsintegration.

© 2008 Microsoft Corporation und CMP Media, LLC. Alle Rechte vorbehalten. Die nicht genehmigte teilweise oder vollständige Vervielfältigung ist nicht zulässig.