Partitionierte Tabellen und Indizes in SQL Server 2005

Veröffentlicht: 22. Mrz 2005

Von Kimberly L. Tripp

Die tabellenbasierten Partitionierungs-Funktionen in SQL Server 2005 bieten Flexibilität und Leistung und vereinfachen dadurch die Erstellung und Verwaltung partitionierter Tabellen. Verfolgen Sie den Fortschritt in der Leistungsfähigkeit von der logischen und manuellen Partitionierung von Tabellen bis zu den neuesten Partitionierungs-Funktionen. Erfahren Sie, warum, wann und wie Sie mit SQL Server 2005 partitionierte Tabellen entwerfen, implementieren und verwalten. Dieser Artikel enthält auch Links zu englischsprachigen Seiten. (41 gedruckte Seiten)

Hinweis zu diesem Artikel
Die in diesem Artikel beschriebenen Funktionen und Pläne geben die aktuelle Entwicklung für die nächste Version von SQL Server wieder. Sie stellen keine Produktspezifikationen dar und können sich ändern. Es wird nicht garantiert, weder implizit noch auf andere Weise, dass diese Funktionen in der endgültigen Produktversion enthalten sind.Bei einigen Funktionen wird in diesem Artikel davon ausgegangen, dass der Leser mit den Funktionen und Diensten von SQL Server 2000 vertraut ist. Hintergrundinformationen finden Sie auf der SQL Server-Website oder im Buch SQL Server 2000 Resource Kit (in Englisch).Dies ist keine Produktspezifikation.

Downloaden Sie das zugehörige Codebeispiel SQL2005PartitioningScripts.exe (in Englisch).

Auf dieser Seite

 Warum partitionieren?
 Entwicklungsgeschichte des Partitionierens
 Definitionen und Terminologie
 Schritte zum Erstellen von partitionierten Tabellen
 Umsetzung in der Praxis: Fallstudien
 Schlussbemerkung

Warum partitionieren?

Was sind Partitionen und warum sollten Sie diese verwenden? Das lässt sich einfach beantworten: Zur Verbesserung der Skalierbarkeit und Verwaltbarkeit von großen Tabellen und von Tabellen mit unterschiedlichen Zugriffsmustern. Normalerweise erstellen Sie eine Tabelle zum Speichern von Informationen über eine bestimmte Entität, z. B. Kunden oder Umsätze, und jede Tabelle verfügt über Attribute, die nur diese eine Entität beschreiben. Obwohl Tabellen mit jeweils nur einer Entität am einfachsten zu entwerfen und zu verstehen sind, sind solche Tabellen in Bezug auf Leistung, Skalierbarkeit und Verwaltbarkeit nicht unbedingt optimiert, insbesondere, wenn die Tabellen größer werden.

Was ist unter einer "großen" Tabelle zu verstehen? Obwohl der Umfang einer VLDB (Very Large Database - Sehr große Datenbank) in Hunderten Gigabyte oder sogar in Terabyte gemessen wird, beschreibt dieser Begriff nicht notwendigerweise die Größe einzelner Tabellen innerhalb der Datenbank. Eine große Datenbank ist eine Datenbank, die den gewünschten Leistungsanforderungen nicht entspricht oder bei der die Betriebs- oder Wartungskosten die vorgegebenen Wartungs- oder Budgetanforderungen übersteigen. Diese Anforderungen gelten auch für Tabellen. Eine Tabelle kann als groß erachtet werden, wenn ihre Verfügbarkeit durch Aktivitäten anderer Benutzer oder durch Wartungsmaßnahmen eingeschränkt wird. Die Umsatztabelle wird beispielsweise als groß erachtet, wenn die Leistung stark beeinträchtigt ist oder wenn aufgrund von Wartungsmaßnahmen für zwei Stunden pro Tag, pro Woche oder auch nur pro Monat kein Zugriff auf die Tabelle möglich ist. In einigen Fällen sind gelegentliche Ausfallzeiten akzeptabel, doch häufig können diese durch einen besseren Entwurf und mithilfe von Partitionierung vermieden oder auf ein Minimum beschränkt werden. Im Zusammenhang mit Partitionierung sind die Größen einzelner Tabellen wichtiger als die der gesamten Datenbank.

Neben der Größe einer Tabelle können auch unterschiedliche Zugriffsmuster ein Leistungs- und Verfügbarkeitskriterium sein, wenn für verschiedene Datensätze innerhalb der Tabelle unterschiedliche Verwendungsmuster gelten. Auch wenn keine unterschiedlichen Verwendungsmuster vorliegen, kann Partitionierung in Fällen, in denen es unterschiedliche Verwendungsmuster gibt, die Verwaltung, Leistung und Verfügbarkeit verbessern. Um bei dem Beispiel der Umsatztabelle zu bleiben, könnten für die Daten des aktuellen Monats Lese- und Schreibrechte gelten, während für die Daten der vorhergehenden Monate (oft der umfangreichere Teil der Tabelle) nur Leserechte gelten. In einem solchen Fall, in dem die Daten unterschiedlich verwendet werden, oder in Fällen, in denen der Verwaltungsaufwand zum Einfügen oder Löschen von Daten unverhältnismäßig hoch ist, kann das Antwortverhalten der Tabelle beeinträchtigt sein. Dies wiederum schränkt die Verfügbarkeit und Skalierbarkeit des Servers ein.

Zusätzlich werden häufige Wartungsmaßnahmen für statische Daten durchgeführt, wenn umfangreiche Datengruppen auf verschiedene Arten verwendet werden. Das kann sowohl kostspielige Folgen, z. B. Leistungsprobleme, Blockaden, Sicherungen (Kosten für Speicherplatz und Zeitaufwand sowie Betriebskosten), als auch einen negativen Einfluss auf die gesamte Skalierbarkeit des Servers haben.

Wie kann Partitionierung helfen? Wenn Tabellen und Indizes sehr umfangreich werden, kann Partitionierung durch das Aufteilen der Daten in kleinere, leichter verwaltbare Abschnitte hilfreich sein. In diesem Artikel liegt der Schwerpunkt auf der horizontalen Partitionierung, bei der umfangreiche Gruppen von Zeilen in mehreren getrennten Partitionen gespeichert werden. Die Definition der partitionierten Gruppe wird Ihren Anforderungen entsprechend angepasst, festgelegt und verwaltet. In Microsoft SQL Server 2005 können Sie Tabellen mithilfe definierter Bereiche bzw. Listen auf der Grundlage bestimmter Datenverwendungsmuster partitionieren. Durch die Erweiterung um Funktionen, die rund um die neue Tabellen- und Indexstruktur entworfen wurden, bietet SQL Server 2005 eine Vielzahl von Optionen zur langfristigen Verwaltung von partitionierten Tabellen und Indizes.

Wenn eine umfangreiche Tabelle auf einem System mit mehreren Prozessoren vorliegt, kann die Partitionierung der Tabelle durch parallel ausgeführte Operationen überdies die Leistung steigern. Bei sehr aufwändigen Operationen auf äußerst umfangreichen Datenmengen (z. B. viele Millionen Zeilen) kann die Leistung durch die parallele Ausführung mehrerer Operationen auf einzelnen Teilsätzen gesteigert werden. Ein Beispiel für Leistungsgewinne durch Partitionen zeigen die Aggregationen in früheren Versionen von SQL Server. SQL Server kann beispielsweise auf verschiedenen Partitionen unabhängig voneinander arbeiten und anschließend die Aggregate zusammenfügen, anstatt eine einzelne umfangreiche Tabelle zu aggregieren. In SQL Server 2005 können Abfragen, die umfangreiche Datenmengen verknüpfen, direkt von der Partitionierung profitieren. SQL Server 2000 unterstützte zwar die parallele Verknüpfung von Teilsätzen, diese mussten jedoch dynamisch erstellt werden. In SQL Server 2005 werden verknüpfte Tabellen, z. B. Orders (Bestellungen) und OrderDetails (Bestelldetails), die mit demselben Partitionierungsschlüssel und derselben Partitionierungsfunktion partitioniert wurden, als ausgerichtet bezeichnet. Wenn der Optimierer feststellt, dass zwei partitionierte und ausgerichtete Tabellen verknüpft werden, kann SQL Server 2005 die Daten, die sich auf derselben Partition befinden, zuerst verknüpfen und die Ergebnisse anschließend zusammenfassen. Dadurch kann SQL Server 2005 Computer mit mehreren Prozessoren effektiver nutzen.

 

Entwicklungsgeschichte des Partitionierens

Das Konzept der Partitionierung wird in SQL Server seit langem verwendet. In der Tat wurden Formen von Partitionierung in jeder Produktversion unterstützt. Ohne die speziell zur Erstellung und Verwaltung eines Partitionierungsschemas entworfenen Funktionen war die Partitionierung allerdings oft umständlich und wurde wenig genutzt. Außerdem wurden die Vorteile der Partitionierung oft aufgehoben, da das Schema von Benutzern und Entwicklern aufgrund eines komplizierteren Datenbankentwurfs falsch verstanden wurde. Da das Konzept jedoch bedeutende Leistungsgewinne versprach, wurde diese Funktion ab SQL Server 7.0 verbessert, indem die Software Partitionierungen mithilfe von partitionierten Sichten zur Verfügung stellte. Derzeit bietet SQL Server 2005 mithilfe partitionierter Tabellen die größten Vorteile zur Partitionierung umfangreicher Datenmengen.

Partitionierung in Versionen vor SQL Server 7.0

Bis einschließlich SQL Server 6.5 musste die Partitionierung Teil des Entwurfs sein und im gesamten Datenzugriffscode und allen Abfragen eingearbeitet werden. Oftmals konnte die Leistungsfähigkeit einiger Operationen verbessert werden, indem mehrere Tabellen erstellt wurden und anschließend mithilfe gespeicherter Prozeduren, Sichten oder Clientanwendungen auf die Tabellen zugegriffen wurde. Die Folge war jedoch ein komplizierterer Entwurf. Jeder Benutzer und Entwickler musste auf die Verwendung und ordnungsgemäße Referenzierung der richtigen Tabellen achten. Jede Partition wurde einzeln erzeugt und verwaltet, und zur Vereinfachung des Zugriffs wurden Sichten verwendet. Mit dieser Lösung wurden jedoch nur geringe Leistungsgewinne erzielt. Wenn der Zugriff für Benutzer und Anwendungen mithilfe einer Sicht auf der Grundlage einer UNION-Abfrage vereinfacht wurde, musste der Abfrageprozessor auf jede zu Grunde liegende Tabelle zugreifen, um die für die Ergebnismenge erforderlichen Daten zu ermitteln. Wenn nur eine begrenzte Teilmenge der zu Grunde liegenden Tabellen benötigt wurde, musste jeder Benutzer und Entwickler den Datenbankentwurf kennen, um nur die entsprechenden Tabellen zu referenzieren.

Partitionierte Sichten in SQL Server 7.0

Die Herausforderungen, die sich durch die manuelle Erstellung von Partitionen in Versionen vor SQL Server 7.0 stellten, bezogen sich in erster Linie auf die Leistung. Sichten vereinfachten zwar den Anwendungsentwurf, Benutzerzugriffe und das Erstellen von Abfragen, boten jedoch keine Leistungsgewinne. In SQL Server 7.0 wurden Sichten mit Einschränkungen kombiniert, so dass der Abfrageoptimierer irrelevante Tabellen (oder auch Partitionen) aus dem Abfrageplan entfernen und den Gesamtaufwand wesentlich verringern konnte, wenn eine auf einer UNION-Abfrage basierende Sicht auf mehrere Tabellen zugriff.

Betrachten Sie die Sicht YearlySales (Umsätze jährlich) in Abbildung 1. Anstatt alle Umsätze in einer großen Tabelle aufzunehmen, könnten Sie zwölf Monatstabellen (SalesJanuary (Umsätze Januar), SalesFebruary (Umsätze Februar) usw.) erstellen und anschließend Sichten für jedes Quartal sowie eine Sicht für das ganze Jahr, YearlySales, definieren.

Abbildung 1: Partitionierte Sichten in SQL Server 7.0/2000

Der Zugriff von Benutzern, die mit der folgenden Abfrage auf die Sicht YearlySales zugreifen, wird ausschließlich auf die Tabelle SalesJanuary erfolgen.

SELECT ys.* 
FROM dbo.YearlySales AS ys
WHERE ys.SalesDate = '20030113'

Solange die Einschränkungen vertrauenswürdig sind und Abfragen auf die Sicht eine WHERE-Klausel verwenden, um die Ergebnisse auf der Grundlage des Partitionsschlüssels (der Spalte, für die die Einschränkung definiert wurde) einzuschränken, greift SQL Server nur auf die erforderlichen Basistabellen zu. VertrauenswürdigeEinschränkungen sind Einschränkungen, für die SQL Server garantieren kann, dass alle Daten den in der Einschränkung festgelegten Eigenschaften entsprechen. In der Standardeinstellung wird eine Einschränkung mit WITH CHECK erzeugt. Diese Einstellung verursacht für die Tabelle eine Schemasperre, so dass die Daten der Einschränkung entsprechend überprüft werden können. Wurden die vorhandenen Daten der Überprüfung entsprechend für gültig erklärt, wird die Einschränkung hinzugefügt. Sobald die Schemasperre entfernt ist, müssen weitere Daten, die eingefügt, aktualisiert und gelöscht werden, der gültigen Einschränkung entsprechen. Durch dieses Vorgehen bei der Erstellung von vertrauenswürdigen Einschränkungen können Entwickler die Komplexität ihres Entwurfs mithilfe von Sichten wesentlich verringern, ohne direkt auf die relevante Tabelle zugreifen zu müssen (oder diese auch nur zu kennen). Mit vertrauenswürdigen Einschränkungen verbessert SQL Server die Leistung, indem nicht benötigte Tabellen aus dem Ausführungsplan entfernt werden.

Hinweis
Eine Einschränkung kann auf verschiedene Weisen "nicht vertrauenswürdig" werden, z. B. durch eine BULK INSERT-Anweisung ohne Angabe des CHECK_CONSTRAINTS-Arguments oder durch die Erstellung einer Einschränkung mit der Option NOCHECK. Wenn eine Einschränkung nicht vertrauenswürdig ist, prüft der Abfrageprozessor wieder alle Basistabellen, da er nicht feststellen kann, ob die angeforderten Daten sich in der richtigen Basistabelle befinden.

Partitionierte Sichten in SQL Server 2000

Obwohl in SQL Server 7.0 der Entwurf wesentlich vereinfacht und die Leistung für SELECT-Anweisungen verbessert wurde, wurden keine Verbesserungen für Datenänderungsanweisungen erzielt. INSERT-, UPDATE- und DELETE-Anweisungen wurden nur für Basistabellen unterstützt, nicht jedoch direkt für Sichten, die Tabellen auf der Grundlage einer UNION-Abfrage zusammenführten. In SQL Server 2000 profitieren Datenänderungsanweisungen auch von den Funktionen für partitionierte Sichten, die in SQL Server 7.0 eingeführt wurden. SQL Server kann Änderungen über die Sicht an die entsprechende Basistabelle weiterleiten, da Datenänderungsanweisungen dieselbe Struktur wie partitionierte Sichten verwenden können. Für den Partitionierungsschlüssel und für dessen Erstellung sind zusätzliche Einschränkungen erforderlich, um den Schlüssel ordnungsgemäß zu konfigurieren. Die Grundprinzipien sind insofern gleich, dass nicht nur SELECT-Abfragen, sondern auch Änderungen direkt an die entsprechenden Basistabellen gesendet werden. Ausführliche Informationen zu Einschränkungen, Einrichtung, Konfiguration und bewährten Verfahren für die Partitionierung in SQL Server 2000 finden Sie im Artikel Verwenden von Partitionen in einem Microsoft SQL Server 2000-Datawarehouse.

Partitionierte Tabellen in SQL Server 2005

Während die Leistung von partitionierten Sichten durch die Verbesserungen in SQL Server 7.0 und SQL Server 2000 wesentlich gesteigert wurde, vereinfachten sie die Verwaltung, den Entwurf und die Entwicklung von partitionierten Daten nicht. Bei der Verwendung von partitionierten Sichten müssen alle Basistabellen, für die die Sicht definiert wurde, einzeln erstellt und verwaltet werden. Der Entwurf von Anwendungen ist einfacher und die Benutzer profitieren, da sie nicht mehr wissen müssen, auf welche Basistabellen direkt zugegriffen wird. Die Verwaltung ist jedoch kompliziert, da viele Tabellen einschließlich ihrer Datenintegritäts-Beschränkungen verwaltet werden müssen. Aufgrund der Verwaltungsaufgaben wurden partitionierte Sichten häufig nur dann zum Aufteilen von Tabellen verwendet, wenn Daten archiviert oder geladen werden mussten. Die Operationen zum Verschieben von Daten in die schreibgeschützte Tabelle oder zum Löschen von Daten aus dieser Tabelle waren sehr aufwändig, d. h. sie benötigten Zeit, Speicherplatz für Protokolle und verursachten häufig Blockierungen.

Zudem musste der Optimierer die Abfragepläne für jede Partition prüfen und festlegen (die Indizes konnten sich geändert haben), da der Entwickler aufgrund der Partitionierungsstrategien in früheren Versionen erst einzelne Tabellen und Indizes erstellen und diese anschließend mit einer UNION-Abfrage in Sichten zusammenführen musste. Daher stieg in SQL Server 2000 die Abfrageoptimierungsdauer oft linear zur Anzahl der verarbeiteten Partitionen.

In SQL Server 2005 verfügt jede Partition per Definition über dieselben Indizes. Betrachten Sie z.B. ein Szenario, in dem die aktuellen Online Transaction Processing-Monatsdaten (OLTP - Onlinetransaktionsverarbeitung) am Ende jedes Monats in eine Analysetabelle verschoben werden müssen. Die für schreibgeschützte Abfragen verwendete Analysetabelle ist eine Tabelle mit einem gruppierten und zwei nicht gruppierten Indizes. Das Laden von einem Gigabyte Daten in die bereits indizierte und aktive Tabelle verursacht eine Blockierung für die aktuellen Benutzer, da die Tabelle und/oder die Indizes fragmentiert und/oder gesperrt werden. Außerdem dauert der Ladevorgang ziemlich lange, da bei jeder neuen Zeile Verwaltungsaufgaben für die Tabelle und die Indizes erledigt werden müssen. Es gibt verschiedene Möglichkeiten, das Laden von großen Datenmengen zu beschleunigen. Diese können jedoch alle anderen Benutzer in ihrer Arbeit beeinträchtigen und Parallelzugriffe auf Kosten der Geschwindigkeit verhindern.

Wenn diese Daten in einer neu erstellten, leeren und nicht indizierten (Heap-)Tabelle isoliert werden, kann zuerst der Ladevorgang und anschließend die Indizierung ausgeführt werden. Bei dieser Vorgehensweise können Sie häufig einen Leistungsgewinn um das Zehnfache oder mehr feststellen. Tatsächlich können Sie beim Laden in eine nicht indizierte Tabelle die Vorteile mehrerer Prozessoren nutzen, indem mehrere Datendateien oder mehrere Segmente aus derselben Datei (definiert durch die Positionen der Start- und Endzeile) parallel geladen werden. Da beide Methoden von der Parallelverarbeitung profitieren, können dadurch weitere Leistungsgewinne erzielt werden.

In jeder Version von SQL Server wird durch Partitionierung nicht nur diese feinere Steuerung ermöglicht, es müssen sich auch nicht mehr alle Daten an einem Speicherort befinden, allerdings müssen viele Objekte erstellt und verwaltet werden. In früheren Versionen konnte eine funktionierende Partitionierungsstrategie durch das dynamische Erstellen und Löschen von Tabellen und die Änderung der UNION-Sicht erreicht werden. In SQL Server 2005 gibt es jedoch eine weit elegantere Lösung: Sie können einfach neu gefüllte Partitionen als zusätzliche Partitionen eines bestehenden Partitionsschemas einfügen und alte Partitionen auslagern. Der Vorgang benötigt nur kurze Zeit und kann mithilfe von parallelen Ladevorgängen großer Datenmengen und paralleler Erstellung von Indizes noch effizienter ausgeführt werden. Wichtiger noch, die Partition wird außerhalb des Bereichs der Tabelle bearbeitet, so dass für Abfragen auf die Tabelle keine Auswirkungen bestehen, bis die Partition hinzugefügt wird. Normalerweise dauert das Hinzufügen der Partition nur einige Sekunden.

Auch beim Entfernen von Daten ist die Leistungssteigerung erheblich. Wenn in einer Datenbank ein änderbarer Datenausschnitt benötigt wird, in den neue Daten eingefügt (z. B. Daten für den aktuellen Monat) und aus dem die ältesten Daten (möglicherweise der entsprechende Monat des vorherigen Jahres) entfernt werden, kann bei dieser Datenmigration die Leistung mithilfe von Partitionierung um Größenordnungen gesteigert werden. Wenn Ihnen dies extrem erscheint, betrachten Sie den Unterschied zur Leistung ohne Partitionierung: Wenn sich alle Daten in einer einzelnen Tabelle befinden, erfordert das Löschen von einem Gigabyte alter Daten die zeilenweise Bearbeitung der Tabelle sowie die Bearbeitung der zugehörigen Indizes. Das Löschen der Daten erzeugt erhebliche Protokollieraktivitäten. Dabei darf das Protokoll während des Löschvorgangs nicht gekürzt werden (beachten Sie, dass das Löschen eine einzelne Auto-Commit-Transaktion ist; Sie können den Umfang der Transaktion jedoch steuern, indem Sie mehrere Löschvorgänge ausführen, wenn dies möglich ist), und es ist daher möglicherweise ein umfangreicheres Protokoll erforderlich. Das Entfernen derselben Daten erfordert mit Partitionierung allerdings auch das Entfernen der speziellen Partition aus der partitionierten Tabelle (dies ist eine Metadatenoperation) und das anschließende Löschen oder Kürzen der eigenständigen Tabelle.

Ohne zu wissen, wie Partitionen am besten entworfen werden, sind Entwickler sich möglicherweise nicht darüber bewusst, dass Dateigruppen in Verbindung mit Partitionen ideal zur Implementierung der Partitionierung geeignet sind. Dateigruppen ermöglichen das Speichern einzelner Tabellen auf unterschiedlichen physischen Datenträgern. Wenn sich eine einzelne Tabelle (mithilfe von Dateigruppen) über mehrere Dateien erstreckt, ist der physische Speicherort der Daten nicht vorhersagbar. SQL Server steigert die Leistung von Systemen, bei denen keine Parallelverarbeitung erwartet wird, durch die gleichmäßigere Verwendung aller Datenträger mithilfe von Dateigruppen. Dadurch ist der spezifische Speicherort der Daten weniger wichtig.

Hinweis
In Abbildung 2 sind drei Dateien in einer Dateigruppe enthalten. Zwei Tabellen, Orders und OrdersDetails, wurden in dieser Dateigruppe abgelegt. Beim Ablegen von Tabellen in einer Dateigruppe füllt SQL Server die Dateien der Dateigruppe gleichmäßig. Dabei werden jeder Datei Bereiche (Segmente zu 64 KB, die acht Seiten zu 8 KB entsprechen) zugewiesen, sobald für die Objekte in der Dateigruppe Speicherplatz erforderlich ist. Wenn die Tabellen Orders und OrdersDetails erzeugt werden, ist die Dateigruppe leer. Wenn eine Bestellung eingeht, werden die Daten in die Tabellen eingegeben: eine Zeile pro Bestellung in Orders und eine Zeile pro Position in OrdersDetails. SQL Server weist der Tabelle Orders einen Bereich von Datei 1 und der Tabelle OrdersDetails einen anderen Bereich von Datei 2 zu. Wahrscheinlich wächst die Tabelle OrdersDetails schneller als die Tabelle Orders. Der nächsten Tabelle, für die Platz erforderlich ist, wird der folgende Bereich zugewiesen. Beim Füllen der Tabelle OrdersDetails wird dieser der nächste Bereich von Datei 3 zugewiesen, und SQL Server verwendet die Dateien der Dateigruppe nach dem Roundrobin-Verfahren. Beachten Sie in Abbildung 2 die Zuordnung zwischen den Tabellen, den Bereichen und den entsprechenden Dateigruppen. Die Bereiche werden dem Speicherplatzbedarf entsprechend zugewiesen und dem Ablauf entsprechend nummeriert.

Abbildung 2: Proportionales Füllen mithilfe von Dateigruppen

SQL Server verteilt die Zuweisungen weiterhin gleichmäßig auf alle Objekte innerhalb der Dateigruppe. Während SQL Server bei bestimmten Vorgängen mit mehreren Datenträgern effektiver ausgeführt wird, ist der Einsatz von vielen Datenträgern aus Sicht der Verwaltung und Wartung nicht optimal, insbesondere, wenn die Verwendungsmuster sehr vorhersehbar (und isoliert) sind. Da die Daten nicht an bestimmten Speicherorten auf dem Datenträger vorliegen, besteht nicht die Möglichkeit, sie für Wartungszwecke (z. B. für die Datensicherung) zu isolieren.

In SQL Server 2005 kann eine Tabelle mit partitionierten Tabellen mithilfe einer Funktion und eines Schemas so entworfen werden, dass alle Zeilen, die über denselben Partitionierungsschlüssel verfügen, direkt (und immer) an einem bestimmten Speicherort abgelegt werden. Die Funktion definiert sowohl die Grenzen der Partition als auch die Partition, in der der erste Wert gespeichert werden soll. Im Fall einer LEFT-Partitionsfunktion dient der erste Wert als obere Grenze in der ersten Partition. Im Fall einer RIGHT-Partitionsfunktion dient der erste Wert als untere Grenze in der zweiten Partition (Partitionsfunktionen werden später in diesem Artikel eingehender erläutert). Sobald die Funktion definiert ist, kann ein Partitionsschema auf der Grundlage einer Partitionsfunktion erstellt werden, um die physische Zuordnung der Partitionen zu den jeweiligen Speicherorten innerhalb der Datenbank zu definieren. Wenn mehrere Tabellen dieselbe Funktion (jedoch nicht notwendigerweise dasselbe Schema) verwenden, werden Zeilen mit demselben Partitionierungsschlüssel entsprechend gruppiert. Dieses Konzept wird "Ausrichtung" (Alignment) genannt. SQL Server kann durch das Ausrichten von Zeilen mit demselben Partitionsschlüssel aus mehreren Tabellen, die sich auf demselben oder auf unterschiedlichen physischen Datenträgern befinden, ausschließlich mit den notwendigen Datengruppen jeder Tabelle arbeiten, wenn der Optimierer dies auswählt. Um eine Ausrichtung zu erreichen, müssen zwei partitionierte Tabellen oder Indizes mit ihren jeweiligen Partitionen übereinstimmen. Sie müssen bezüglich der Partitionierungsspalten gleichwertige Partitionsfunktionen verwenden. Unter folgenden Bedingungen können zwei Partitionsfunktionen zur Ausrichtung von Daten verwendet werden:

• Beide Partitionsfunktionen verwenden dieselbe Anzahl von Argumenten und Partitionen.

• Der in jeder Funktion verwendete Partitionierungsschlüssel ist vom gleichen Typ (in Bezug auf Länge, Genauigkeit und Dezimalstellen sowie Sortierung, sofern zutreffend).

• Die Werte für die Bereichsgrenzen sind gleich (einschließlich der Kriterien für LEFT/RIGHT-Bereichsgrenzen).

Hinweis
Auch wenn zwei Partitionsfunktionen für die Ausrichtung von Daten entworfen wurden, können Sie einen nicht ausgerichteten Index erhalten, wenn dieser nicht nach derselben Spalte wie die partitionierte Tabelle partitioniert ist.

Eine strengere Form der Ausrichtung ist die Anordnung (Collocation), bei der zwei ausgerichtete Objekte über ein Equi-Join-Prädikat miteinander verknüpft werden. Dabei findet die Gleichheitsverknüpfung über die Partitionierungsspalte statt. Dies ist im Zusammenhang mit Abfragen, Unterabfragen oder ähnlichen Konstrukten wichtig, in denen ein Equi-Join-Prädikat verwendet wird. Anordnung ist nützlich, da Abfragen, die Tabellen über die Partitionsspalten verknüpfen, im Allgemeinen wesentlich schneller ausgeführt werden. Betrachten Sie die Tabellen Orders und OrdersDetails in Abbildung 2. Sie können ein Partitionsschema erstellen, das die Zuordnung zu drei Dateigruppen vornimmt, anstatt die Dateien proportional zu füllen. Dazu definieren Sie bei der Definition der Tabellen Orders und OrdersDetails, dass diese dasselbe Schema verwenden sollen. Miteinander in Beziehung stehende Daten, die über denselben Partitionsschlüssel verfügen, werden in derselben Datei gespeichert. Dadurch werden die für die Verknüpfung benötigten Daten isoliert. Wenn in Beziehung stehende Zeilen aus mehreren Tabellen auf dieselbe Art partitioniert werden, kann SQL Server die Partitionen miteinander verknüpfen, ohne eine gesamte Tabelle oder mehrere Partitionen (wenn die Tabelle eine andere Partitionierungsfunktion verwendet) nach passenden Zeilen durchsuchen zu müssen. In diesem Fall werden die Objekte nicht nur aufgrund desselben Schlüssels ausgerichtet, sondern auch aufgrund des Speicherorts, da dieselben Daten in denselben Dateien gespeichert sind.

In Abbildung 3 wird gezeigt, dass zwei Objekte dasselbe Partitionsschema verwenden können und alle Datenzeilen mit demselben Partitionierungsschlüssel in derselben Dateigruppe abgelegt werden. Wenn in Beziehung stehende Daten ausgerichtet sind, kann SQL Server 2005 umfangreiche Datenmengen effektiv parallel bearbeiten. Beispielsweise befinden sich alle Umsatzdaten für den Januar (für beide Tabellen, Orders und OrderDetails) in der ersten Dateigruppe, für den Februar in der zweiten Dateigruppe und so weiter.

Abbildung 3: Nach dem Speicherort ausgerichtete Tabellen

SQL Server ermöglicht die Partitionierung auf der Grundlage von Bereichen. Tabellen und Indizes können so entworfen werden, dass sie zur besseren Ausrichtung dasselbe Schema verwenden. Ein guter Entwurf verbessert die Gesamtleistung wesentlich, doch was geschieht, wenn sich die Datennutzung im Laufe der Zeit ändert? Was ist zu tun, wenn eine zusätzliche Partition benötigt wird? Bei SQL Server 2005 gehörten einfaches Hinzufügen, Entfernen und Verwalten von Partitionen außerhalb der partitionierten Tabellen zu den Hauptentwurfszielen.

SQL Server 2005 hat die Partitionierung unter Berücksichtigung von Verwaltung, Entwicklung und Anwendung vereinfacht. Einige der Leistungs- und Verwaltungsvorteile sind:

• Vereinfachung von Entwurf und Implementierung umfangreicher Tabellen, die aus Leistungs- oder Verwaltungsgründen partitioniert werden müssen

• Laden von Daten in eine neue Partition einer vorhandenen partitionierten Tabelle mit einer minimalen Unterbrechung des Datenzugriffs für die restlichen Partitionen

• Laden von Daten mit gleicher Leistung in eine neue Partition einer vorhandenen partitionierten Tabelle wie beim Laden derselben Daten in eine neue, leere Tabelle

• Archivieren und/oder Entfernen eines Teils einer partitionierten Tabelle bei minimaler Beeinträchtigung des Zugriffs auf den Rest der Tabelle

• Partitionen, die durch Hinzufügen und Auslagern von Partitionen zu bzw. aus partitionierten Tabellen verwaltet werden

• Bessere Skalierung und Parallelverarbeitung für extrem umfangreiche Operationen über mehrere in Beziehung stehende Tabellen

• Bessere Leistung für alle Partitionen

• Schnellere Abfrageoptimierung, da nicht jede Partition separat optimiert werden muss

 

Definitionen und Terminologie

Sie müssen mit einigen neuen Konzepten, Begriffen und neuer Syntax vertraut sein, um Partitionen in SQL Server 2005 zu implementieren. Sehen Sie sich zunächst die Struktur einer Tabelle in Bezug auf die Erstellung und Anordnung an, um diese neuen Konzepte zu verstehen. In früheren Versionen stellte eine Tabelle sowohl ein physisches als auch ein logisches Konzept dar. Bei den partitionierten Tabellen und Indizes von SQL Server 2005 können Sie jedoch auswählen, wie und wo Sie eine Tabelle speichern. In SQL Server 2005 können Tabellen und Indizes mit der gleichen Syntax erstellt werden wie in früheren Versionen: als eine einzelne tabellarische Struktur, die in der DEFAULT-Dateigruppe oder einer benutzerdefinierten Dateigruppe abgelegt wird. Außerdem können Tabellen und Indizes in SQL Server 2005 nach einem Partitionierungs-Schema erstellt werden. Das Partitionierungs-Schema ordnet das Objekt einer oder mehreren Dateigruppen zu. Das Partitionierungs-Schema verwendet eine Partitionierungs-Funktion, um festzulegen, welche Daten am jeweils geeigneten physischen Speicherort abgelegt werden. Die Partitionierungs-Funktion definiert den Algorithmus, der zur Verteilung der Zeilen verwendet wird. Das Schema ordnet den Partitionen den jeweils geeigneten physischen Speicherort zu (z. B. eine Dateigruppe). Mit anderen Worten, die Tabelle stellt nach wie vor ein logisches Konzept dar, die physische Speicherung auf dem Datenträger kann sich jedoch radikal von der in früheren Versionen unterscheiden. Die Tabelle kann über ein Schema verfügen.

Bereichspartitionen

Bereichspartitionen sind Tabellenpartitionen, die durch bestimmte, anpassbare Datenbereiche definiert sind. Die Bereichsgrenzen von Bereichspartitionen werden vom Entwickler ausgewählt und können bei einer Änderung der Verwendungsmuster geändert werden. Diesen Bereichen liegen typischerweise Daten oder geordnete Gruppen von Daten zugrunde.

Bereichspartitionen werden in erster Linie zur Datenarchivierung, zur Entscheidungshilfe (wenn häufig nur bestimmte Datenbereiche benötigt werden, z. B. ein bestimmter Monat eines Quartals) und für Kombinationen aus OLTP und Entscheidungshilfesystemen verwendet, bei denen sich die Datennutzung während des Lebenszyklus einer Zeile ändert. Der größte Vorteil von partitionierten Tabellen und Indizes in SQL Server 2005 besteht in der Möglichkeit, sehr spezielle Datenbereiche zu bearbeiten, besonders hinsichtlich der Archivierung und der Wartung. Durch Bereichspartitionen können alte Daten sehr schnell archiviert und ersetzt werden. Bereichspartitionen eignen sich am besten bei typischen Datenzugriffen zur Entscheidungshilfe über große Datenbereiche. In diesem Fall ist es wichtig, wo genau die Daten gespeichert sind, so dass man notfalls nur auf die entsprechenden Partitionen zugreifen muss. Außerdem können Sie diese Daten einfach und schnell hinzufügen, sobald Transaktionsdaten verfügbar sind. Da Sie für jede der Partitionen die Bedingungen für die Bereichsgrenzen definieren müssen, ist die Definition von Bereichspartitionen anfangs komplizierter. Außerdem müssen Sie ein Schema erstellen, um jede Partition einer oder mehreren Dateigruppen zuzuordnen. Diese folgen jedoch häufig einem gleich bleibenden Muster, so dass sie, einmal erstellt, wohl leicht programmtechnisch verwaltet werden können (siehe Abbildung 4).

Abbildung 4: Nach Bereichen partitionierte Tabelle mit 12 Partitionen

Definieren des Partitionierungsschlüssels

Der erste Schritt bei der Partitionierung von Tabellen und Indizes ist die Definition der Daten für den Partitionierungs-Schlüssel. Der Partitionierungs-Schlüssel muss aus genau einer Spalte der Tabelle bestehen und bestimmte Kriterien erfüllen. Die Partitionsfunktion definiert den Datentyp, der dem Schlüssel zugrunde liegt (auch als logische Trennung von Daten bekannt). Die Funktion definiert zwar diesen Schlüssel, jedoch nicht die physische Speicherung der Daten auf dem Datenträger. Die Speicherung der Daten wird durch das Partitionsschema festgelegt. Durch das Schema werden die Daten also mindestens einer Dateigruppe zugeordnet, die die Daten bestimmten Dateien und daher bestimmten Datenträgern zuordnet. Das Schema verwendet dazu immer eine Funktion: Wenn die Funktion fünf Partitionen definiert, muss das Schema fünf Dateigruppen verwenden. Die Dateigruppen müssen sich nicht unterscheiden. Sie erzielen allerdings eine bessere Leistung, wenn Sie mehrere Datenträger und vorzugsweise mehrere Prozessoren verwenden. Bei der Verwendung des Schemas mit einer Tabelle legen Sie die Spalte fest, die als Argument für die Partitionsfunktion dient.

Die Datenmenge wird für Bereichspartitionen durch eine logische, datengesteuerte Bereichsgrenze aufgeteilt. Tatsächlich müssen die Datenpartitionen nicht genau ausgeglichen sein. Die Datennutzung schreibt eine Bereichspartition vor, wenn die Tabelle nach einem Muster verwendet wird, das spezielle Grenzen für die Analyse definiert (auch bekannt als Bereiche). Der Partitions-Schlüssel für eine Bereichsfunktion muss aus genau einer Spalte bestehen, und die Partitionierungs-Funktion enthält dann die gesamte Domäne, auch wenn diese Daten möglicherweise nicht in der Tabelle enthalten sind (der Datenintegrität bzw. den Dateneinschränkungen entsprechend). Bereichsgrenzen werden also für jede Partition definiert, die erste und die letzte Partition schließen jedoch möglicherweise die äußeren linken Zeilen (Werte unterhalb der niedrigsten Bereichsgrenze) und die äußeren rechten Zeilen (Werte oberhalb der höchsten Bereichsgrenze) ein. Daher müssen Partitionen mit CHECK-Einschränkungen kombiniert werden, um die Wertedomäne auf eine bestimmte Datenmenge zu beschränken. Die Verwendung von CHECK-Einschränkungen zur Durchsetzung Ihrer Geschäftsregeln und von Einschränkungen zur Erhaltung der Datenintegrität ermöglicht die Beschränkung der Datenmenge auf einen begrenzten Bereich. Bereichspartitionen sind ideal geeignet, wenn Wartung und Verwaltung die regelmäßige Archivierung großer Datenbereiche erfordern und Abfragen auf große Datenmengen zugreifen, die in einer Teilmenge der Bereiche enthalten sind.

Indexpartitionierung

Zusätzlich zur Partitionierung der Daten einer Tabelle können Sie auch Indizes partitionieren. Wenn zur Partitionierung der Tabelle und der zugehörigen Indizes dieselbe Funktion verwendet wird, ist die Leistung meistens optimal. Die Tabelle und der Index gelten als ausgerichtet, wenn die Indizes und die Tabelle dieselbe Partitionierungs-Funktion und Spalten in derselben Reihenfolge verwenden. Wenn ein Index für eine bereits partitionierte Tabelle erstellt wird, richtet SQL Server den neuen Index automatisch mit dem Partitionierungs-Schema der Tabelle aus, es sei denn, der Index wird ausdrücklich anders partitioniert. Sind eine Tabelle und die zugehörigen Indizes ausgerichtet, kann SQL Server Partitionen effektiver zu partitionierten Tabellen hinzufügen und daraus entfernen, da alle in Beziehung stehenden Daten und Indizes mit dem gleichen Algorithmus aufgeteilt werden.

Wenn Tabellen und Indizes nicht nur mit derselben Partitionsfunktion, sondern auch mit demselben Partitionsschema definiert wurden, gelten sie als nach dem Speicherort ausgerichtet. Ein Vorteil der Ausrichtung nach dem Speicherort besteht darin, dass sich alle Daten innerhalb derselben Bereichsgrenzen auf denselben Datenträgern befinden. In diesem Fall können Datensicherungen auf einen bestimmten Zeitraum begrenzt werden und Ihre Strategien hinsichtlich der Häufigkeit und des Sicherungstyps variieren je nach Datenänderungshäufigkeit. Weitere Vorteile ergeben sich, wenn Tabellen und Indizes in derselben Datei oder Dateigruppe verknüpft oder aggregiert werden. SQL Server profitiert von der Parallelisierung einer Operation über mehrere Partitionen. Im Fall von Ausrichtung nach dem Speicherort und mehreren Prozessoren kann jeder Prozessor beim Datenzugriff eine bestimmte Datei oder Dateigruppe direkt und ohne Konflikte bearbeiten, da sich alle erforderlichen Daten auf demselben Datenträger befinden. Dadurch können mehrere Prozesse ohne Unterbrechung gleichzeitig ausgeführt werden.

Weitere Informationen finden Sie unter SQL Server 2000 Books Online (in Englisch) im Abschnitt "Special Guidelines for Partitioned Indexes".

Besondere Bedingungen für Partitionen: Teilen, Zusammenführen und Austauschen

Mehrere neue Funktionen und Konzepte zur Partitionsverwaltung unterstützen Sie bei partitionierten Tabellen. Da Partitionen für große, skalierbare Tabellen verwendet werden, ändert sich die bei der Erstellung der Partitionsfunktion gewählte Anzahl der Partitionen im Laufe der Zeit. Sie können die ALTER TABLE-Anweisung mit der neuen Option zum Teilen verwenden, um einer Tabelle eine weitere Partition hinzuzufügen. Wenn eine Partition geteilt wird, können Daten in die neue Partition verschoben werden. Um die Leistung beizubehalten, sollten Zeilen nicht verschoben werden. Dieses Szenario wird in der Fallstudie weiter unten in diesem Artikel beschrieben.

Lagern Sie umgekehrt zum Entfernen einer Partition die Daten aus und führen Sie die Bereichsgrenzen zusammen. Bei Bereichspartitionen wird eine Merge-Anforderung gestellt, indem angegeben wird, welche Bereichsgrenze entfernt werden soll. Wenn nur die Daten für einen bestimmten Zeitraum benötigt werden und die Datenarchivierung regelmäßig ausgeführt wird (z. B. monatlich), könnten Sie eine Datenpartition (den ältesten Monat) archivieren, sobald die Daten für den aktuellen Monat verfügbar sind. Sie könnten z. B. die Daten für ein Jahr vorhalten und am Ende jedes Monats die Daten des aktuellen Monats hinzufügen, während Sie die Daten des ältesten Monats auslagern. Dabei können Sie zwischen den zum Lesen und Schreiben verfügbaren OLTP-Daten und den schreibgeschützten Daten des Vormonats unterscheiden. Wie im folgenden Szenario dargestellt wird, besteht dieser Vorgang aus einem speziellen Ablauf von Aktionen, durch den dieser effizient ausgeführt wird.

Sie halten die schreibgeschützten Daten eines Jahres verfügbar. In der Tabelle sind aktuell Daten von September 2003 bis August 2004 enthalten. Der aktuelle Monat, September 2004, befindet sich in einer anderen Datenbank, die für OLTP optimiert ist. Die schreibgeschützte Version der Tabelle enthält 13 Partitionen: zwölf Partitionen, die Daten enthalten (September 2003 bis August 2004), und eine leere Endpartition. Diese letzte Partition ist leer, da eine Bereichspartition immer die gesamte Domäne beinhaltet, sowohl Werte unterhalb der niedrigsten Bereichsgrenze als auch Werte oberhalb der höchsten Bereichsgrenze. Wenn Sie Daten mithilfe eines Szenarios mit änderbarem Ausschnitt verwalten möchten, benötigen Sie ohnehin eine leere Partition zum Teilen, in die neue Daten geschrieben werden können. Bei einer mit LEFT-Bereichsgrenzen definierten Partitionsfunktion erstreckt sich die leere Partition logisch bis zu den RIGHT-Bereichsgrenzen. Eine leere Partition am Ende kann geteilt werden (für neue Daten). So müssen nicht Zeilen aus der letzten Partition (da keine leere Partition vorhanden ist) in die hinzuzufügende neue Dateigruppe verschoben werden (wenn die Partition geteilt wird, um neue Daten aufzunehmen). Dies ist ein ziemlich kompliziertes Konzept, das in der Fallstudie weiter unten in diesem Artikel noch eingehender erläutert wird. Die Idee besteht darin, dass jedes Hinzufügen oder Löschen von Daten nur als Metadatenoperation ausgeführt werden soll. Um sicherzustellen, dass Operationen nur auf den Metadaten ausgeführt werden, sollten Sie den sich ändernden Teil der Tabelle strategisch verwalten. Verwenden Sie eine CHECK-Einschränkung zur Beschränkung der Daten in der Basistabelle, um sicherzustellen, dass diese Partition leer ist. In diesem Fall sollte sich der Bereich für OrderDate (Bestelldatum) vom 1. September 2003 bis zum 1. September 2004 erstrecken. Wenn die letzte definierte Bereichsgrenze den Wert "31. August 11:59:59.997 Uhr" enthält (mehr über "997" erfahren Sie später), bleibt die letzte Partition durch die Kombination aus der Partitionsfunktion und dieser Einschränkung leer. Da dies nur Konzepte sind, ist es wichtig, zu wissen, dass Teilen und Zusammenführen mithilfe von ALTER PARTITION FUNCTION und das Austauschen von Daten mithilfe von ALTER TABLE ausgeführt werden.

Abbildung 5: Bereichsgrenzen einer Bereichspartition vor dem Laden von Daten bzw. vor der Datenarchivierung

Wenn Daten für den Oktober (in der OLTP-Datenbank) eingetragen werden, sollten die Daten für den September in die partitionierte Tabelle verschoben werden, die Sie für die Analyse verwenden. Der Austausch von Daten in den Tabellen wird sehr schnell ausgeführt, und die Vorbereitungen können außerhalb der partitionierten Tabelle durchgeführt werden. Dieses Szenario wird in der noch folgenden Fallstudie eingehend erklärt, die Idee besteht jedoch darin, "Stagingtabellen" zu verwenden, die schließlich zu Partitionen in der partitionierten Tabelle werden. Bei diesem Vorgang wird eine Partition einer Tabelle in eine nicht partitionierte Tabelle innerhalb derselben Dateigruppe ausgelagert (Abbildung 6). Da die nicht partitionierte Tabelle innerhalb derselben Dateigruppe bereits vorhanden ist (dies ist entscheidend für den Erfolg der Aktion), kann SQL Server diese Auslagerung als Metadaten-Änderung ausführen. Als Änderung, die nur die Metadaten betrifft, kann diese in Sekunden ausgeführt werden, im Gegensatz zum Löschen, das Stunden dauern und in großen Tabellen zu Blockierungen führen kann. Sie verfügen nach der Auslagerung dieser Partition weiterhin über 13 Partitionen. Die erste (älteste) Partition ist jetzt leer, und die letzte (neueste, ebenfalls leere) Partition muss geteilt werden.

Abbildung 6: Auslagern einer Partition

Verwenden Sie die neue Mergeoption (in Abbildung 7 dargestellt) mit ALTER TABLE, um die älteste Partition (September 2003) zu entfernen. Das effektive Zusammenführen mit einer Bereichsgrenze entfernt eine Bereichsgrenze und damit eine Partition. Dadurch wird die Anzahl der Partitionen, in die Daten geladen werden, auf n-1 (in diesem Fall 12) verringert. Das Zusammenführen von Partitionen sollte eine sehr schnell auszuführende Operation sein, wenn keine Zeilen verschoben werden müssen (da für die Bereichsgrenze, mit der zusammengeführt wird, keine Datenzeilen vorliegen). Da die erste Partition leer ist, muss in diesem Fall keine Zeile von der ersten in die zweite Partition verschoben werden. Wenn die erste Partition nicht leer ist und die Zusammenführung mit der Bereichsgrenze ausgeführt wird, müssen Zeilen aus der ersten in die zweite Partition verschoben werden. Dies kann sehr aufwändig sein, lässt sich aber in dem am häufigsten verwendeten Szenario mit änderbarem Ausschnitt vermeiden, indem eine leere Partition mit einer aktiven Partition zusammengeführt wird und keine Zeilen verschoben werden.

Abbildung 7: Zusammenführen von Partitionen

Abschließend muss die neue Tabelle in die partitionierte Tabelle eingefügt werden. Um dies als Metadaten-Änderung auszuführen, muss das Laden und Erstellen der Indizes in einer neuen Tabelle außerhalb der Grenzen der partitionierten Tabelle erfolgen. Teilen Sie zuerst den letzten, neuesten und leeren Bereich in zwei Partitionen, um die Partition einzufügen. Zusätzlich müssen Sie die Einschränkung der Tabelle aktualisieren, damit auf den neuen Bereich zugegriffen werden kann. Die partitionierte Tabelle erhält wieder 13 Partitionen. Im Szenario mit änderbarem Ausschnitt bleibt die letzte Partition mit einer LEFT-Partitionsfunktion immer leer.

Abbildung 8: Teilen einer Partition

Abschließend stehen die neu geladenen Daten zum Einfügen in die zwölfte Partition für den Monat September 2004 bereit.

Abbildung 9: Einfügen einer Partition

Die Tabelle sieht dann folgendermaßen aus:

Abbildung 10: Bereichsgrenzen einer Bereichspartition nach dem Laden von Daten bzw. der Datenarchivierung

Da nur eine Partition auf einmal hinzugefügt oder entfernt werden kann, sollten Tabellen, denen mehrere Partitionen hinzugefügt oder aus denen mehrere Partitionen entfernt werden müssen, neu erstellt werden. Um zu dieser neuen Partitionierungsstruktur zu wechseln, erstellen Sie zuerst die neue partitionierte Tabelle und laden dann die Daten in diese neu erstellte Tabelle. Dies ist eine weit bessere Methode, als bei jeder Teilung erneut für die gesamte Tabelle einen Ausgleich herzustellen. Dies wird mithilfe einer neuen Partitionsfunktion, eines neuen Partitionsschemas und dem anschließenden Verschieben der Daten in die neu partitionierte Tabelle erreicht. Um die Daten zu verschieben, kopieren Sie diese zuerst mithilfe der Anweisung INSERT neueTabelle SELECT Spaltenliste FROM alteTabelle, und löschen Sie anschließend die ursprünglichen Tabellen. Verhindern Sie während dieses Vorgangs Änderungen durch Benutzer, um Datenverluste zu vermeiden.

Weitere Informationen finden Sie in der SQL Server-Onlinedokumentation unter "ALTER PARTITION FUNCTION" und "ALTER TABLE".

 

Schritte zum Erstellen von partitionierten Tabellen

Nachdem Sie nun die grundsätzlichen Vorzüge von partitionierten Tabellen kennengelernt haben, werden im folgenden Abschnitt die Implementierung einer partitionierten Tabelle und die für diesen Prozess wichtigen Features ausführlich beschrieben. Der logische Ablauf gestaltet sich wie folgt:

Abbildung 11: Schritte zum Erstellen von partitionierten Tabellen oder Indizes

Feststellen, ob ein Objekt partitioniert werden soll

Das Partitionieren bietet enorme Vorteile, erfordert jedoch zusätzlichen administrativen Aufwand und fügt der Implementierung von Objekten Komplexität hinzu. Dies kann unter Umständen alle Vorteile aufwiegen. Insbesondere kleine Tabellen werden Sie sicher nicht partitionieren, noch weniger eine Tabelle, die allen Anforderungen an Leistung und Verwaltung entspricht. Das weiter oben besprochene Vertriebsszenario verwendet eine Partitionierung, um das Verschieben von Zeilen und Daten zu vermeiden - bedenken Sie bei der Entscheidung, eine Partitionierung zu implementieren, ob Ihr Szenario solche Anforderungen aufweist.

Ermitteln des Partitionierungs-Schlüssels und der Anzahl von Partitionen

Wenn Sie die Leistung und Verwaltbarkeit von großen Datenmengen verbessern möchten und definierte Zugriffsmuster existieren, kann eine Bereichspartitionierung Konflikte verhindern und den Verwaltungsaufwand reduzieren, da schreibgeschützte Daten diesen nicht erfordern. Zum Ermitteln der Anzahl von Partitionen sollten Sie überprüfen, ob in Ihren Daten logische Gruppen und Muster existieren. Wenn Sie regelmäßig nur mit einer kleinen Zahl dieser definierten Teilmengen arbeiten, sollten Sie Bereiche definieren, damit Abfragen isoliert lediglich auf die entsprechenden Daten zugreifen (d. h. nur auf die spezifische Partition).

Weitere Informationen finden Sie in der SQL Server-Onlinedokumentation unter "Entwerfen von partitionierten Tabellen und Indizes".

Feststellen, ob mehrere Dateigruppen verwendet werden sollen

Zum Erreichen einer optimalen Leistung und Verwaltbarkeit sollten Sie zum Aufteilen Ihrer Daten Dateigruppen einsetzen. Die Anzahl von Dateigruppen ist teilweise von den Hardwareressourcen abhängig: Grundsätzlich ist eine identische Anzahl von Dateigruppen und Partitionen zu empfehlen; dabei befinden sich diese Dateigruppen oft auf verschiedenen Datenträgern. Dieser Grundsatz gilt jedoch lediglich für Systeme, auf denen Analysen gewöhnlich die gesamten Daten einbeziehen. Wenn mehrere CPUs im Einsatz sind, kann SQL Server mit mehreren Partitionen parallel arbeiten und daher die Gesamtverarbeitungszeit für umfangreiche und komplexe Berichte oder Analysen erheblich verringern. In diesem Fall können Sie die Vorteile paralleler Verarbeitung nutzen, ebenso den Wechsel von Partitionen innerhalb der partitionierten Tabelle.

Erstellen von Dateigruppen

Wenn Sie eine partitionierte Tabelle zur Verbesserung der Lastverteilung über mehrere Dateien verteilen möchten, müssen Sie mindestens eine Dateigruppe erstellen. Dateigruppen bestehen aus mindestens einer Datei, und jede Partition muss einer Dateigruppe zugewiesen werden. Eine einzelne Dateigruppe kann für mehrere Partitionen verwendet werden. Für eine bessere Datenverwaltung, bspw. für eine feinere Backupsteuerung, sollten Sie Ihre partitionierten Tabellen jedoch so entwerfen, dass sich lediglich verknüpfte oder logisch gruppierte Daten in einer Dateigruppe befinden. Durch die Verwendung von ALTER DATABASE können Sie einen logischen Dateigruppennamen erstellen und anschließend Dateien hinzufügen. Um für die Datenbank AdventureWorks eine Dateigruppe mit dem Namen 2003Q3 zu erstellen, können Sie ALTER DATABASE auf die folgende Art verwenden:

ALTER DATABASE AdventureWorks ADD FILEGROUP [2003Q3]

Sobald eine Dateigruppe existiert, verwenden Sie ALTER DATABASE zum Hinzufügen von Dateien zur Dateigruppe.

ALTER DATABASE AdventureWorks
ADD FILE 
  (NAME = N'2003Q3',
  FILENAME = N'C:\AdventureWorks\2003Q3.ndf',
  SIZE = 5MB,
  MAXSIZE = 100MB,
  FILEGROWTH = 5MB)
TO FILEGROUP [2003Q3]

Eine Tabelle kann in einer Datei (oder mehreren Dateien) erstellt werden, indem Sie in der ON-Klausel von CREATE TABLE eine Dateigruppe festlegen. Eine unpartitionierte Tabelle kann jedoch nicht in mehreren Dateigruppen erstellt werden. Verwenden Sie zum Erstellen einer Tabelle in einer einzelnen Dateigruppe die ON-Klausel von CREATE TABLE. Um eine partitionierte Tabelle zu erstellen, müssen Sie zunächst über einen funktionsfähigen Mechanismus zum Partitionieren verfügen. Die Kriterien zum Partitionieren sind in Form einer Partitionsfunktion logisch von der Tabelle getrennt. Diese Partitionsfunktion liegt als separate Definition zur Tabelle vor. Diese physische Trennung ist nützlich, da so mehrere Objekte die Partitionsfunktion verwenden können. Aus diesem Grund besteht der erste Schritt zum Partitionieren einer Tabelle aus dem Erstellen einer Partitionsfunktion.

Erstellen der Partitionsfunktion für eine Bereichspartition

Bereichspartitionen müssen über Bereichsgrenzen definiert werden. Zudem können Werte von den beiden Bereichsgrenzen nicht ausgeschlossen werden, selbst wenn eine Tabelle mit einer CHECK-Einschränkung beschränkt wird. Um einen gelegentlichen Austausch von Daten in die Tabelle zu ermöglichen, benötigen Sie eine endgültige und leere Partition.

Definieren Sie für eine Bereichspartition zunächst die Bereichsgrenzen: für fünf Partitionen definieren Sie vier Bereichsgrenzen und legen für die einzelnen Werte fest, ob der Wert die obere Grenze für die erste (LEFT) oder die untere Grenze für die zweite (RIGHT) Partition darstellt. Je nach linker oder rechter Kennzeichnung erhalten Sie immer eine leere Partition, da die Partition über keine explizit angegebene Bereichsgrenze verfügt.

Wenn der erste Wert (bzw. die Bereichsgrenze) einer Partitionsfunktion bspw. "20001001" ist, ergeben sich für die angrenzenden Partitionen die folgenden Werte:

Für LEFT
erste Partition enthält alle Daten <= "20001001"
zweite Partition enthält alle Daten > "20001001"
Für RIGHT
erste Partition enthält alle Daten < "20001001"
zweite Partition enthält alle Daten >= "20001001"

Da Bereichspartitionen gewöhnlich auf datetime-Daten basieren, sollten Sie die Auswirkungen berücksichtigen. Das Verwenden von datetime hat bestimmte Auswirkungen: es gibt immer ein Datum und eine Uhrzeit. Ein Datum ohne Zeitangabe impliziert die Uhrzeit "12:00 A.M.". Wird für diesen Datentyp LEFT verwendet, befinden sich die Daten für das Datum 1. Oktober, 0:00 Uhr, in der ersten Partition, alle weiteren Oktoberdaten in der zweiten Partition. Naheliegend ist die Verwendung von Startwerten mit RIGHT und Endwerten mit LEFT. Die folgenden drei Klauseln erzeugen logisch identische Partitionierungs-Strukturen:

RANGE LEFT FOR VALUES ('20000930 23:59:59.997',
               '20001231 23:59:59.997', 
               '20010331 23:59:59.997',
               '20010630 23:59:59.997')

ODER

RANGE RIGHT FOR VALUES ('20001001 00:00:00.000', 
                  '20010101 00:00:00.000', 
                  '20010401 00:00:00.000',
                  '20010701 00:00:00.000')

ODER

RANGE RIGHT FOR VALUES ('20001001', '20010101', '20010401', '20010701')

Hinweis
Das Verwenden von datetime-Datentypen macht dieses Beispiel etwas komplexer. Daher müssen Sie die Bereichsgrenzen richtig einrichten. Beachten Sie die Einfachheit für RIGHT, da als Standardzeit "12:00:00.000 A.M." festgelegt ist. Die zusätzliche Komplexität für LEFT beruht auf der Genauigkeit des datetime-Datentyps. Es MUSS der Wert "23:59:59.997" gewählt werden, da datetime-Daten keine Genauigkeit auf Millisekundenebene garantieren. Stattdessen haben datetime-Daten eine Genauigkeit von 3,33 Millisekunden. Im Fall von "23:59:59.999" ist dieser exakte Zeitgebertick nicht verfügbar, daher wird der Wert auf den nächsten Tick gerundet: "12:00:00.000 A.M." des folgenden Tages. Durch diese Rundung können die Grenzen nicht genau definiert werden. Bei datetime-Daten müssen Sie genau angegebene Millisekundenwerte vorsichtig behandeln.

Hinweis
Partitionierungs-Funktionen erlauben auch das Einbinden von Funktionen als Bestandteil der Definition. Sie können also auch DATEADD(ms,-3,'20010101') verwenden statt die Zeit explizit mit "20001231 23:59:59.997" festzulegen.

Weitere Informationen finden Sie in der SQL Server-Onlinedokumentation unter "Datum und Uhrzeit" im Referenzabschnitt zu Transact-SQL.

Verwenden Sie zum Speichern jeweils eines Viertels der Daten von Orders in den vier aktiven Partitionen (eine Partition pro Quartal) und zum Erstellen einer fünften Partition zur späteren Verwendung (wiederum als Platzhalter beim Verschieben von Daten in und aus der partitionierten Tabelle) eine LEFT-Partitionsfunktion mit vier Bereichsgrenzen:

CREATE PARTITION FUNCTION OrderDateRangePFN(datetime)
AS 
RANGE LEFT FOR VALUES ('20000930 23:59:59.997',
            '20001231 23:59:59.997',
            '20010331 23:59:59.997',
            '20010630 23:59:59.997')

Beachten Sie, dass die Definition von vier Bereichsgrenzen zum Erstellen von fünf Partitionen führt. Überprüfen Sie die von dieser Partitionsfunktion erstellten Datenmengen wie folgt:

Bereichsgrenze "20000930 23:59:59.997" als LEFT (legt das Muster fest):
Die äußerst linke Partition enthält alle Werte <= "20000930 23:59:59.997"Bereichsgrenze "20001231 23:59:59.997":
Die zweite Partition enthält alle Werte > "20000930 23:59:59.997", jedoch <= "20001231 23:59:59.997"
Bereichsgrenze "20010331 23:59:59.997":
Die dritte Partition enthält alle Werte > "20001231 23:59:59.997", jedoch <= "20010331 23:59:59.997"
Bereichsgrenze "20010630 23:59:59.997":
Die vierte Partition enthält alle Werte > "20010331 23:59:59.997", jedoch <= "20010630 23:59:59.997"
Schließlich enthält die fünfte Partition alle Werte > "20010630 23:59:59.997".

Erstellen des Partitionsschemas

Nachdem Sie eine Partitionsfunktion erstellt haben, müssen Sie diese mit einem Partitionsschema verknüpfen, um die Partitionen an bestimmte Dateigruppen zu leiten. Beim Definieren eines Partitionsschemas müssen Sie für jede Partition eine Dateigruppe benennen, auch wenn sich mehrere Partitionen innerhalb derselben Dateigruppe befinden. Für die weiter oben erstellte Bereichspartition (OrderDateRangePFN) gibt es fünf Partitionen. Die letzte (und leere) Partition wird in der PRIMARY-Dateigruppe erstellt. Diese Partition muss nicht an einem besonderen Speicherort gespeichert werden, da sie zu keiner Zeit Daten enthalten wird.

CREATE PARTITION SCHEME OrderDatePScheme 
AS 
PARTITION OrderDateRangePFN 
TO ([2000Q3], [2000Q4], [2001Q1], [2001Q2], [PRIMARY])

Hinweis
Befinden sich alle Partitionen innerhalb derselben Dateigruppe, kann die folgende, einfachere Syntax verwendet werden:

CREATE PARTITION SCHEME OrderDatePScheme 
AS 
PARTITION OrderDateRangePFN 
ALL TO ([PRIMARY])

Erstellen der partitionierten Tabelle

Wenn die Partitionsfunktion (die logische Struktur) und das Partitionsschema (die physische Struktur) definiert sind, kann die Tabelle erstellt werden, um deren Vorteile zu nutzen. Die Tabelle legt fest, welches Schema verwendet werden soll, das Schema wiederum legt die Funktion fest. Um alle drei zusammenzufassen, müssen Sie die Spalte angeben, auf die die Partitionierungsfunktion angewendet werden soll. Bereichspartitionen verweisen immer auf exakt eine Spalte in der Tabelle, die dem Datentyp der in der Partitionsfunktion festgelegten Grenzbedingungen entsprechen sollte. Wenn die Tabelle den möglichen Datenbereich begrenzen soll (im Gegensatz zu einem unendlichen Bereich), müssen Sie eine CHECK-Einschränkung hinzufügen.

CREATE TABLE [dbo].[OrdersRange]  
(
   [PurchaseOrderID] [int] NOT NULL,
   [EmployeeID] [int] NULL,
   [VendorID] [int] NULL,
   [TaxAmt] [money] NULL,
   [Freight] [money] NULL,
   [SubTotal] [money] NULL,
   [Status] [tinyint] NOT NULL ,
   [RevisionNumber] [tinyint] NULL ,
   [ModifiedDate] [datetime] NULL ,
   [ShipMethodID] [tinyint] NULL,
   [ShipDate] [datetime] NOT NULL, 
   [OrderDate] [datetime] NOT NULL
      CONSTRAINT OrdersRangeYear
         CHECK ([OrderDate] >= '20030701' 
                         AND [OrderDate] <= '20040630 11:59:59.997'), 
   [TotalDue] [money] NULL
) 
ON OrderDatePScheme (OrderDate)
GO

Erstellen von Indizes: partitioniert oder nicht partitioniert?

In der Standardeinstellung verwenden die für eine partitionierte Tabelle erstellten Indizes dasselbe Partitionierungsschema und dieselbe Partitionierungsspalte. Wenn dies der Fall ist, sind Index und Tabelle ausgerichtet. Obwohl nicht erforderlich, ermöglicht das Ausrichten einer Tabelle und der zugehörigen Indizes eine einfachere Verwaltung und Administration, insbesondere im Szenario mit änderbarem Ausschnitt.

Beispielsweise muss die Partitionierungsspalte zum Erstellen von eindeutigen Indizes eine der Schlüsselspalten sein. Dadurch wird die Prüfung der entsprechenden Partition und damit Eindeutigkeit sichergestellt. Wenn Sie eine Tabelle anhand einer Spalte partitionieren und für eine andere Spalte einen eindeutigen Index erstellen müssen, können diese daher nicht ausgerichtet werden. In diesem Fall könnte der Index möglicherweise für die eindeutige Spalte partitioniert werden (handelt es sich um einen mehrspaltigen eindeutigen Schlüssel, kann jede der Schlüsselspalten verwendet werden), oder eine Partitionierung wird überhaupt nicht durchgeführt. Bedenken Sie, dass dieser Index entfernt und neu erstellt werden muss, wenn Daten in der partitionierten Tabelle ausgetauscht werden.

Hinweis
Wenn Sie eine Tabelle mit vorhandenen Daten laden und sofort Indizes hinzufügen möchten, erreichen Sie gewöhnlich eine höhere Leistung, als wenn Sie die Daten in eine nicht partitionierte und nicht indizierte Tabelle laden und die Indizes zum Partitionieren der Daten erst anschließend erstellen. Durch Definieren eines gruppierten Index in einem Partitionsschema partitionieren Sie die Tabelle im Anschluss an den Ladevorgang effektiv. Dies ist gleichzeitig eine sehr geeignete Methode zum Partitionieren von bestehenden Tabellen. Zum Erstellen der gleichen Tabelle als nicht partitionierte Tabelle und des gruppierten Index als partitionierten, gruppierten Index, ersetzen Sie die ON-Klausel in CREATE TABLE mit einer einzelnen Dateigruppe als Ziel. Erstellen Sie den gruppierten Index im Partitionsschema, nachdem die Daten geladen sind.

 

Umsetzung in der Praxis: Fallstudien

Nachdem Sie die mit der Partitionierung verbundenen Konzepte, Vorteile und Codebeispiele kennengelernt haben, verfügen Sie wahrscheinlich über ein gutes Verständnis für den Ablauf. Für jeden einzelnen Schritt gibt es jedoch spezifische Einstellungen und Optionen, in einigen Fällen sind bestimmte Voraussetzungen zu beachten. In diesem Abschnitt werden die bisherigen Informationen zusammengefügt.

Bereichspartitionierung: Umsatzdaten

Umsatzdaten ändern sich ständig. Die Daten des aktuellen Monats sind Transaktionsdaten, die Daten des vergangenen Monats werden hauptsächlich für Analysen verwendet. Analysen werden gewöhnlich auf monatlicher, vierteljährlicher und jährlicher Datenbasis durchgeführt. Verschiedene Analysten möchten unter Umständen große und unterschiedliche Datenbestände bearbeiten. Durch Partitionierung können solche Aktivitäten besser gekapselt werden. Im Beispielszenario stammen die aktiven Daten aus 283 Standorten, und sie werden in zwei Standard-ASCII-Dateien bereitgestellt. Alle Dateien werden am Monatsersten, spätestens um 3 Uhr, auf einem zentralen Dateiserver abgelegt. Die einzelnen Dateien sind unterschiedlich groß, enthalten durchschnittlich jedoch etwa 86.000 Vertriebsvorgänge (Bestellungen) pro Monat. Jede Bestellung enthält durchschnittlich 2,63 Positionszeilen, dementsprechend enthalten die OrderDetails-Dateien durchschnittlich 226.180 Zeilen. Jeden Monat werden ungefähr 25 Millionen neue Orders- und 64 Millionen OrderDetails-Zeilen hinzugefügt. Der Server für Berichtsanalysen hält die relevanten Daten über einen Zeitraum von zwei Jahren bereit. Das Datenvolumen von zwei Jahren liegt knapp unter 600 Millionen Orders- und knapp über 1,5 Milliarden OrderDetails-Zeilen. Da Analysen häufig die Daten der einzelnen Monate eines Quartals oder die Monatsdaten mit dem Vorjahreszeitraum vergleichen, wird eine Bereichspartitionierung verwendet. Als Grenze für jeden Bereich wird ein Monat verwendet.

Anhand der in Abbildung 11 dargestellten Schritte wird die Tabelle partitioniert. Die Basis der Bereichspartitionierung ist dabei OrderDate. Aus den Anforderungen für diesen neuen Server ergibt sich, dass Analysten gewöhnlich Daten von bis zu sechs aufeinander folgenden Monaten oder bis zu drei Monaten des aktuellen und des vergangenen Jahres zusammenstellen und analysieren (bspw. "Januar bis März 2003" im Vergleich zu "Januar bis März 2004"). Zum Maximieren des Diskstriping und gleichzeitigen Isolieren der meisten Datengruppierungen verwenden mehrere Dateigruppen dieselben physikalischen Datenträger, die Dateigruppen weisen dabei jedoch einen Versatz von sechs Monaten auf, um Festplattenkonflikte zu reduzieren. Die aktuellen Daten stammen vom Oktober 2004, und alle 283 Standorte verwalten ihre aktuellen Vertriebsdaten lokal. Der Server hält Daten von Oktober 2002 bis Ende September 2004 bereit. Um die Vorteile des neuen Multiprozessorsystems mit 16 Prozessoren und SAN (Storage Area Network) auszunutzen, gibt es für jeden Monat eine eigene Datei in einer Dateigruppe, die auf einem gespiegelten Datenträgerset (RAID 1+0) abgelegt wird. Ausgehend vom physischen Datenlayout über Dateigruppen zu logischen Laufwerken wird im folgenden Diagramm (Abbildung 12) dargestellt, wo sich die Daten auf monatlicher Basis befinden.

Abbildung 12: Partitionierte Tabelle "Orders"

Jedes der 12 logischen Laufwerke wird in einer RAID 1+0-Konfiguration eingesetzt. Daher sind für Orders und OrderDetails insgesamt 48 Laufwerke erforderlich. Das SAN unterstützt 78 Laufwerke, die anderen 30 werden für das Transaktionsprotokoll, TempDB, Systemdatenbanken und weitere kleinere Tabellen wie Customers (Kunden, 9 Millionen Zeilen) und Products (Produkte, 386.750 Zeilen) verwendet. Die Tabellen Orders und OrderDetails verwenden identische Bereichsgrenzen und Positionierungen auf Datenträgern, ebenso ein identisches Partitionierungsschema. Als Ergebnis (in Abbildung 13 werden lediglich die beiden logischen Laufwerke E:\ und F:\ dargestellt) befinden sich die Daten der Tabellen Orders und OrderDetails für die jeweiligen Monate auf denselben Datenträgern:

Abbildung 13: Bereichspartition zur Blockpositionierung auf Festplattenarrays

Obwohl dies schwierig erscheint, ist es relativ einfach zu erstellen. Die schwierigste Aufgabe beim Entwerfen der partitionierten Tabelle besteht aus dem Bereitstellen von Daten aus der großen Anzahl von Quellen: 283 Standorte benötigen einen einheitlichen Bereitstellungsmechanismus. Auf dem zentralen Server ist jedoch nur jeweils eine Orders-Tabelle und eine OrderDetails-Tabelle zu definieren. Erstellen Sie zunächst die Partitionsfunktion und das Partitionsschema, um beide Tabellen als partitionierte Tabellen zu erstellen. Da ein Partitionsschema die physische Positionierung einer Partition auf dem Datenträger festlegt, müssen die Dateigruppen ebenfalls vorhanden sein. Da für diese Tabelle Dateigruppen notwendig sind, besteht der nächste Schritt aus dem Erstellen der Dateigruppen. Die folgende Syntax ist für jede Dateigruppe identisch, alle 24 Dateigruppen müssen jedoch erstellt werden. Das Skript RangeCaseStudyFilegroups.sql enthält die Anweisungen zum Erstellen aller 24 Dateigruppen.

Hinweis:
Ohne angepasste Laufwerksbuchstaben können Sie dieses Skript nicht ausführen. Das Skript enthält jedoch eine "setup"-Tabelle, die für ein vereinfachtes Testen angepasst werden kann. Sie können für die Laufwerksbuchstaben bzw. Speicherorte zum Testen und Kennenlernen der Syntax ein einzelnes Laufwerk verwenden. Achten Sie auf die Angabe von Dateigrößen in MB (nicht in GB) und verwenden Sie, je nach verfügbarem Speicherplatz, eine geringere Anfangsgröße.

Für die Datenbank SalesDB werden 24 Dateien und Dateigruppen erstellt. Die verwendete Syntax ist identisch, mit Ausnahme von Speicherort, Dateiname und Dateigruppenname:

ALTER DATABASE SalesDB    
ADD FILE      
   (NAME = N'SalesDBFG1File1',
        FILENAME = N'E:\SalesDB\SalesDBFG1File1.ndf',
       SIZE = 20GB,
        MAXSIZE = 35GB,
  FILEGROWTH = 5GB) 
TO FILEGROUP [FG1]
GO

Nachdem alle 24 Dateien und Dateigruppen erstellt sind, können Sie die Partitionsfunktion und das Partitionsschema definieren. Verwenden Sie zum Prüfen Ihrer Dateien und Dateigruppen sp_helpfile bzw. sp_helpfilegroup.

Die Partitionsfunktion wird in der Spalte OrderDate definiert. Der verwendete Datentyp ist datetime. Beide Tabellen müssen OrderDate speichern, damit sie auf dessen Basis partitioniert werden können. Werden beide Tabellen nach demselben Schlüsselwert partitioniert, liegt der Wert des Partitionierungs-Schlüssels zwar redundant vor, dies ist jedoch für die Vorteile der Ausrichtung notwendig, und in den meisten Fällen sollte es sich um eine relativ begrenzte Spalte handeln (der datetime-Datentyp hat eine Länge von 8 Byte). Wie weiter oben in diesem Artikel unter "Erstellen der Partitionsfunktion für eine Bereichspartition" beschrieben, handelt es sich bei der Funktion um eine Bereichspartitionsfunktion, in der sich die erste Bereichsgrenze in der ersten Partition (LEFT) befindet.

CREATE PARTITION FUNCTION TwoYearDateRangePFN(datetime)
AS 
RANGE LEFT FOR VALUES ('20021031 23:59:59.997',      -- Oct 2002
            '20021130 23:59:59.997',   -- Nov 2002
            '20021231 23:59:59.997',   -- Dec 2002
            '20030131 23:59:59.997',   -- Jan 2003
            '20030228 23:59:59.997',   -- Feb 2003
            '20030331 23:59:59.997',   -- Mar 2003
            '20030430 23:59:59.997',   -- Apr 2003
            '20030531 23:59:59.997',   -- May 2003
            '20030630 23:59:59.997',   -- Jun 2003
            '20030731 23:59:59.997',   -- Jul 2003
            '20030831 23:59:59.997',   -- Aug 2003
            '20030930 23:59:59.997',   -- Sep 2003
            '20031031 23:59:59.997',   -- Oct 2003
            '20031130 23:59:59.997',   -- Nov 2003
            '20031231 23:59:59.997',   -- Dec 2003
            '20040131 23:59:59.997',   -- Jan 2004
            '20040229 23:59:59.997',   -- Feb 2004
            '20040331 23:59:59.997',   -- Mar 2004
            '20040430 23:59:59.997',   -- Apr 2004
            '20040531 23:59:59.997',   -- May 2004
            '20040630 23:59:59.997',   -- Jun 2004
            '20040731 23:59:59.997',   -- Jul 2004
            '20040831 23:59:59.997',   -- Aug 2004
            '20040930 23:59:59.997')   -- Sep 2004
GO

Da die äußerst linke und die äußerst rechte Bereichsgrenze enthalten sind, erstellt diese Partitionsfunktion 25 Partitionen. Die Tabelle verwaltet eine 25. Partition, die leer bleiben wird. Für diese leere Partition wird keine besondere Dateigruppe benötigt, da diese durch eine Einschränkung der Tabellendaten zu keiner Zeit Daten enthalten wird. Um die Daten auf die entsprechenden Laufwerke zu verteilen, wird ein Partitionsschema verwendet, das die Partition der Dateigruppe zuordnet. Das Partitionsschema verwendet einen expliziten Dateigruppennamen für jede der 24 Dateigruppen, die Daten enthalten werden, und die PRIMARY-Dateigruppe für die 25. (leere) Partition.

CREATE PARTITION SCHEME [TwoYearDateRangePScheme]
AS 
PARTITION TwoYearDateRangePFN TO 
( [FG1], [FG2], [FG3], [FG4], [FG5], [FG6], 
              [FG7], [FG8], [FG9], [FG10],[FG11],[FG12],
              [FG13],[FG14],[FG15],[FG16],[FG17],[FG18],
              [FG19],[FG20],[FG21],[FG22],[FG23],[FG24], 
              [PRIMARY] )
GO

Eine Tabelle kann mit der zu früheren Versionen identischen Syntax erstellt werden, indem die Standard-Dateigruppe oder eine benutzerdefinierte Dateigruppe als nicht partitionierte Tabelle verwendet wird, oder indem ein Schema zum Erstellen einer partitionierten Tabelle Verwendung findet. Welche Option sich am besten eignet, ist von der Füllmethode für die Tabelle und der Anzahl der erstellten Partitionen abhängig. Das Auffüllen eines Heaps und das anschließende Erstellen des gruppierten Index führt wahrscheinlich zu einer besseren Leistung als ein Laden in eine bereits indizierte Tabelle. Außerdem können Sie bei Existenz mehrerer CPUs die Daten durch parallele BULK INSERT-Vorgänge in die Tabelle laden und anschließend die Indexerstellung ebenfalls parallel ausführen. Erstellen Sie die Tabelle Orders wie gewohnt und laden Sie anschließend die vorhandenen Daten mit INSERT SELECT-Anweisungen, die Daten aus der Beispieldatenbank AdventureWorks abrufen. Um die Tabelle Orders als partitionierte Tabelle zu erstellen, geben Sie in der ON-Klausel der Tabelle das Partitionsschema an. Die Tabelle Orders wird mit der folgenden Syntax erstellt:

CREATE TABLE SalesDB.[dbo].[Orders]  
(
   [PurchaseOrderID] [int] NOT NULL,
   [EmployeeID] [int] NULL,
   [VendorID] [int] NULL,
   [TaxAmt] [money] NULL,
   [Freight] [money] NULL,
   [SubTotal] [money] NULL,
   [Status] [tinyint] NOT NULL,
   [RevisionNumber] [tinyint] NULL,
   [ModifiedDate] [datetime] NULL,
   [ShipMethodID]   tinyint NULL,
   [ShipDate] [datetime] NOT NULL, 
   [OrderDate] [datetime] NULL
      CONSTRAINT OrdersRangeYear
CHECK ([OrderDate] >= '20021001' 
         AND [OrderDate] < '20041001'), 
   [TotalDue] [money] NULL
) ON TwoYearDateRangePScheme(OrderDate)
GO

Da die Tabelle OrderDetails ebenfalls dieses Schema verwenden wird (sie muss OrderDate enthalten), wird die Tabelle mit der folgenden Syntax erstellt:

CREATE TABLE [dbo].[OrderDetails](
   [OrderID] [int] NOT NULL,
   [LineNumber] [smallint] NOT NULL,
   [ProductID] [int] NULL,
   [UnitPrice] [money] NULL,
   [OrderQty] [smallint] NULL,
   [ReceivedQty] [float] NULL,
   [RejectedQty] [float] NULL,
   [OrderDate] [datetime] NOT NULL
      CONSTRAINT OrderDetailsRangeYearCK
         CHECK ([OrderDate] >= '20021001' 
             AND [OrderDate] < '20041001'), 
   [DueDate] [datetime] NULL,
   [ModifiedDate] [datetime] NOT NULL 
      CONSTRAINT [OrderDetailsModifiedDateDFLT] 
         DEFAULT (getdate()),
   [LineTotal]  AS (([UnitPrice]*[OrderQty])),
   [StockedQty]  AS (([ReceivedQty]-[RejectedQty]))
) ON TwoYearDateRangePScheme(OrderDate)
GO

Der nächste Schritt zum Laden der Daten besteht aus zwei INSERT-Anweisungen. Diese Anweisungen verwenden die neue AdventureWorks-Datenbank, aus der Daten kopiert werden. Installieren Sie die AdventureWorks-Beispieldatenbank, um diese Daten zu kopieren:

INSERT dbo.[Orders]
   SELECT o.[PurchaseOrderID] 
         , o.[EmployeeID]
         , o.[VendorID]
         , o.[TaxAmt]
         , o.[Freight] 
         , o.[SubTotal] 
         , o.[Status] 
         , o.[RevisionNumber] 
         , o.[ModifiedDate] 
         , o.[ShipMethodID] 
         , o.[ShipDate] 
         , o.[OrderDate] 
         , o.[TotalDue] 
   FROM AdventureWorks.Purchasing.PurchaseOrderHeader AS o
      WHERE ([OrderDate] >= '20021001'
             AND [OrderDate] < '20041001')
GO
INSERT dbo.[OrderDetails]
   SELECT    od.PurchaseOrderID
         , od.LineNumber
         , od.ProductID
         , od.UnitPrice
         , od.OrderQty
         , od.ReceivedQty
         , od.RejectedQty
         , o.OrderDate
         , od.DueDate
         , od.ModifiedDate
   FROM AdventureWorks.Purchasing.PurchaseOrderDetail AS od
      JOIN AdventureWorks.Purchasing.PurchaseOrderHeader AS o
            ON o.PurchaseOrderID = od.PurchaseOrderID
      WHERE (o.[OrderDate] >= '20021001' 
             AND o.[OrderDate] < '20041001')
GO

Nachdem die Daten in die partitionierte Tabelle geladen sind, können Sie eine neu integrierte Systemfunktion verwenden, um die Partition zu ermitteln, in der sich die Daten befinden. Die folgende Abfrage ist hilfreich, da sie zu jeder Partition, die Daten enthält, die Anzahl der Zeilen in jeder Partition sowie das kleinste und das größte OrderDate anzeigt. Eine Partition, die keine Zeilen enthält, wird durch die Abfrage nicht zurückgegeben.

SELECT $partition.TwoYearDateRangePFN(o.OrderDate) 
         AS [Partition Number]
   , min(o.OrderDate) AS [Min Order Date]
   , max(o.OrderDate) AS [Max Order Date]
   , count(*) AS [Rows In Partition]
FROM dbo.Orders AS o
GROUP BY $partition.TwoYearDateRangePFN(o.OrderDate)
ORDER BY [Partition Number]
GO
SELECT $partition.TwoYearDateRangePFN(od.OrderDate) 
         AS [Partition Number]
   , min(od.OrderDate) AS [Min Order Date]
   , max(od.OrderDate) AS [Max Order Date]
   , count(*) AS [Rows In Partition]
FROM dbo.OrderDetails AS od
GROUP BY $partition.TwoYearDateRangePFN(od.OrderDate)
ORDER BY [Partition Number]
GO

Nachdem die Tabellen gefüllt sind, können Sie abschließend die gruppierten Indizes erstellen. In diesem Beispiel wird der gruppierte Index mit dem Primärschlüssel erstellt, da ein Partitionierungsschlüssel beide Tabellen identifiziert (fügen Sie dem Index für OrderDetails die Spalte LineNumber (Zeilennummer) hinzu, um Eindeutigkeit zu erzielen). Das Standardverhalten von Indizes für partitionierte Tabellen besteht aus der Ausrichtung des Index mit der partitionierten Tabelle auf Basis desselben Schemas - das Schema muss nicht angegeben werden.

ALTER TABLE Orders
ADD CONSTRAINT OrdersPK
   PRIMARY KEY CLUSTERED (OrderDate, OrderID)
GO
ALTER TABLE dbo.OrderDetails
ADD CONSTRAINT OrderDetailsPK
   PRIMARY KEY CLUSTERED (OrderDate, OrderID, LineNumber)
GO

Hier die vollständige Syntax mit Angabe des Partitionsschemas:

ALTER TABLE Orders
ADD CONSTRAINT OrdersPK
   PRIMARY KEY CLUSTERED (OrderDate, OrderID)
   ON TwoYearDateRangePScheme(OrderDate)
GO
ALTER TABLE dbo.OrderDetails
ADD CONSTRAINT OrderDetailsPK
   PRIMARY KEY CLUSTERED (OrderDate, OrderID, LineNumber)
   ON TwoYearDateRangePScheme(OrderDate)
GO

Verknüpfen von partitionierten Tabellen

Zum Verknüpfen von ausgerichteten Tabellen stellt SQL Server 2005 die Option zur Verfügung, die Tabellen in einem einzelnen oder in mehreren Schritten zu verknüpfen, indem zunächst die einzelnen Partitionen verknüpft und anschließend die Teilmengen zusammengefügt werden. Unabhängig von der Art der Verknüpfung prüft SQL Server immer, ob Partitionierungsebenen möglicherweise ausgeschlossen werden können.

Ausschließen von Partitionen

In der folgenden Abfrage werden Daten aus den Tabellen Order und OrderDetails abgerufen, die im vorherigen Szenario erstellt wurden. Die Abfrage wird nur Informationen für das dritte Quartal zurückgeben. Üblicherweise enthält das dritte Quartal die Monate mit einer geringeren Auftragsverarbeitung, in 2004 gehörten diese Monate jedoch zu den umsatzstärksten. In diesem Beispiel interessieren wir uns für Entwicklungen im dritten Quartal in Bezug auf Products (Menge der bestellten Produkte und deren Bestelldaten). Um sicherzustellen, dass ausgerichtete, partitionierte Tabellen beim Verknüpfen von einem Ausschließen von Partitionen profitieren, müssen Sie den Partitionierungsbereich für jede Tabelle angeben. In diesem Beispiel wird die Verknüpfung zwischen den Tabellen zeigen, dass der Wert für OrderDate in den Tabellen identisch sein muss, da der Primärschlüssel der Tabelle Orders ein zusammengesetzter Schlüssel aus OrderDate und OrderID (Bestell-ID) ist. Das Suchargument (SARG) wird auf beide partitionierte Tabellen angewendet. Die Abfrage zum Abrufen dieser Daten lautet:

SELECT o.OrderID, o.OrderDate, o.VendorID, od.ProductID, od.OrderQty
FROM dbo.Orders AS o
INNER JOIN dbo.OrderDetails AS od 
      ON o.OrderID = od.OrderID
         AND o.OrderDate = od.OrderDate
WHERE o.OrderDate >= '20040701' 
   AND o.OrderDate <= '20040930 11:59:59.997'
GO

Wie in Abbildung 14 gezeigt wird, gibt es einige Schlüsselelemente, auf die beim Überprüfen der Ausgabe von aktuellen oder erwarteten Ergebnissen geachtet werden sollte: Zunächst (beim Verwenden von SQL Server Management Studio) wird beim Zeigen auf eine der abgefragten Tabellen entweder Estimated Number of Executions (geschätzte Anzahl von Ausführungen) oder Number of Executions (Anzahl von Ausführungen) angezeigt. Im Beispiel werden die Daten eines Quartals bzw. von drei Monaten angezeigt. Jeder Monat verfügt über eine eigene Partition, und es gibt drei Ausführungen zur Abfrage dieser Daten: eine Ausführung für jede Tabelle.

Abbildung 14: Anzahl von Ausführungen

Wie in Abbildung 15 gezeigt wird, schließt SQL Server alle überflüssigen Partitionen aus und wählt nur jene aus, die tatsächlich zutreffende Daten enthalten. Werfen Sie im Argumentabschnitt einen Blick auf PARTITION ID:([PtnIds1017]), um die Art der Auswertung zu verstehen. Sie wundern sich möglicherweise über die Herkunft des Ausdrucks "PtnIds1017". Es handelt sich um eine logische Darstellung der von dieser Abfrage betroffenen Partitionen. Wenn Sie ganz oben in der Übersicht auf "Constant Scan" zeigen, wird ein Argument in der Form VALUES(((21)), ((22)), ((23))) angezeigt. Dieses Argument stellt die Partitionsnummern dar.

Abbildung 15: Ausschließen von Partitionen

Zum Überprüfen, welche Daten sich in den jeweiligen Partitionen befinden, und ausschließlich in diesen Partitionen, können Sie eine geringfügig geänderte Version der vorher eingesetzten Abfrage verwenden, um auf die neu integrierten Systemfunktionen für Partitionen zuzugreifen:

SELECT $partition.TwoYearDateRangePFN(o.OrderDate) 
         AS [Partition Number]
   , min(o.OrderDate) AS [Min Order Date]
   , max(o.OrderDate) AS [Max Order Date]
   , count(*) AS [Rows In Partition]
FROM dbo.Orders AS o
WHERE $partition.TwoYearDateRangePFN(o.OrderDate) IN (21, 22, 23)
GROUP BY $partition.TwoYearDateRangePFN(o.OrderDate)
ORDER BY [Partition Number]
GO

An dieser Stelle wird das Ausschließen von Partitionen grafisch dargestellt. Eine weitere Optimierungstechnik kann für partitionierte Tabellen und Indizes verwendet werden, insbesondere wenn diese mit den Tabellen ausgerichtet sind, mit denen Sie eine Verknüpfung durchführen. SQL Server führt möglicherweise mehrere Verknüpfungen durch, indem zunächst die jeweiligen Partitionen verknüpft werden.

Vorverknüpfen von ausgerichteten Tabellen

Innerhalb einer einzelnen Abfrage schließt SQL Server nicht nur Partitionen aus, sondern führt gleichzeitig die Verknüpfungen zwischen den verbleibenden Partitionen einzeln durch. Beachten Sie neben der Anzahl der Ausführungen für jeden Tabellenzugriff die Informationen zur Zusammenführungsverknüpfung. Wenn Sie mit dem Mauszeiger auf die Zusammenführungsverknüpfung zeigen, wird angezeigt, dass diese dreimal ausgeführt wurde.

Abbildung 16: Verknüpfen von partitionierten Tabellen

Beachten Sie in Abbildung 16 die in einer geschachtelten Schleife zusätzlich ausgeführte Verknüpfung. Es hat den Anschein, dass diese nach der Zusammenführungsverknüpfung durchgeführt wurde, tatsächlich wurden die Partitions-IDs jedoch an jede Tabellensuche bzw. jeden Tabellen-Scan weitergeleitet. Die abschließende Verknüpfung fügt lediglich die beiden Teildatenmengen zusammen und stellt dabei sicher, dass jeder Teil der beim Start (im "Constant Scan"-Ausdruck) definierten Partitions-ID entspricht.

Szenario mit änderbarem Ausschnitt

Sind die Daten des nächsten Monats (im Beispiel also Oktober 2004) verfügbar, sind einige Schritte in einer bestimmten Reihenfolge durchzuführen, um die bestehenden Dateigruppen zu verwenden und die Daten einzufügen oder auszulagern. In diesem Vertriebsszenario stammen die aktuell in FG1 befindlichen Daten vom Oktober 2002. Nachdem nun die Daten von Oktober 2004 verfügbar sind, gibt es zwei Möglichkeiten, je nach verfügbarem Speicherplatz und den Archivierungsanforderungen. Bedenken Sie: Um eine Partition schnell in eine Tabelle einzufügen oder auszulagern, dürfen bei diesem Vorgang lediglich Metadaten geändert werden. Insbesondere muss die neue Tabelle (die Quelle oder das Ziel, d. h. eigentlich eine Partition) in derselben Dateigruppe erstellt werden, die eingefügt oder ausgelagert werden soll. Wenn Sie dieselben Dateigruppen (in diesem Beispiel FG1) weiter verwenden möchten, müssen Sie das Umsetzen von Speicherplatz- und Archivierungsanforderungen festlegen. Um die Zeitspanne möglichst gering zu halten, in der die Tabelle nicht die vollständigen Daten von zwei Jahren enthält, können Sie, sofern Sie über den Speicherplatz verfügen, die aktuellen Daten (Oktober 2004) in die Dateigruppe FG1 laden, ohne die zu archivierenden Daten (Oktober 2002) zu entfernen. Wenn Sie jedoch nicht über ausreichenden Speicherplatz für den aktuellen Monat und den zu archivierenden Monat verfügen, müssen Sie zunächst die alte Partition auslagern (und entfernen).

Davon unabhängig ist ein Archivieren sehr einfach und wahrscheinlich bereits geschehen. Eine bewährte Methode zum Archivieren besteht aus dem Sichern der Dateigruppe unmittelbar nach dem Laden und Einfügen der neuen Partition, statt kurz vor dem Auslagerungsvorgang. Tritt beispielsweise im RAID-Array ein Fehler auf, kann die Dateigruppe wiederhergestellt werden, ohne die Daten erneut erstellen oder laden zu müssen. In diesem speziellen Fall könnten Sie nach dem Etablieren der Partitionsstruktur eine vollständige Sicherung durchführen, da die Datenbank erst kürzlich partitioniert wurde. Eine vollständige Datenbanksicherung ist nicht die einzige Option. Es gibt eine Vielzahl von Sicherungsstrategien, die in SQL Server 2005 implementiert werden können. Einige davon bieten eine bessere Granularität für die Sicherung und Wiederherstellung. Da sich die meisten Daten nicht ändern, können Sie die einzelnen Dateigruppen nach ihrem Laden sichern. Tatsächlich sollte dies Bestandteil Ihrer fortlaufenden Partitionierungstrategie werden. Weitere Informationen finden Sie in der SQL Server-Onlinedokumentation im Abschnitt "Administrieren von SQL Server" unter "Sichern von Dateien und Dateigruppen".

Nachdem die Strategie entwickelt ist, sollten Sie den genauen Ablauf und die Syntax verstehen. Syntax und Anzahl der Schritte erscheinen möglicherweise kompliziert, der Ablauf ist jedoch in jedem Monat gleich. Durch die dynamische SQL-Ausführung können Sie den Vorgang einfach automatisieren, indem Sie die folgenden Schritte durchführen:

• Verwalten der Stagingtabelle für die einzufügende Partition

• Verwalten der zweiten Stagingtabelle für die auszulagernde Partition

• Auslagern der alten Daten und Einfügen der neuen Daten in die partitionierte Tabelle

• Entfernen der Stagingtabellen

• Sichern der Dateigruppen

Für jeden einzelnen Schritt werden in den folgenden Abschnitten die Syntax und bewährte Methoden ausführlich beschrieben. Zusätzliche Anmerkungen helfen Ihnen bei der Automatisierung durch dynamische SQL-Ausführung.

Verwalten der Stagingtabelle für die einzufügende Partition

1. Erstellen Sie die Stagingtabelle, bei der es sich tatsächlich um eine zukünftige Partition handelt. Die Stagingtabelle muss über eine Einschränkung verfügen, mit der die Daten auf ausschließlich für die zu erstellende Partition gültige Daten eingeschränkt werden. Zur Leistungssteigerung sollten Sie die Daten ohne Einschränkungen in einen nicht indizierten Heap laden und anschließend vor dem Einfügen in die partitionierte Tabelle die Einschränkung WITH CHECK hinzufügen (siehe Schritt 3).

   CREATE TABLE SalesDB.[dbo].[OrdersOctober2004]  
   (
   [OrderID] [int] NOT NULL,
   [EmployeeID] [int] NULL,
   [VendorID] [int] NULL,
   [TaxAmt] [money] NULL,
   [Freight] [money] NULL,
   [SubTotal] [money] NULL,
   [Status] [tinyint] NOT NULL,
   [RevisionNumber] [tinyint] NULL,
   [ModifiedDate] [datetime] NULL,
   [ShipMethodID] [tinyint] NULL,
   [ShipDate] [datetime] NOT NULL, 
   [OrderDate] [datetime] NOT NULL, 
   [TotalDue] [money] NULL
   ) ON [FG1]
   GO
   

   Automatisierung: Diese Tabelle lässt sich einfach erstellen, da es sich immer um den aktuellsten Monat handelt. Zum jeweiligen Ausführungszeitpunkt kann der Monat mit integrierten Funktionen leicht festgestellt werden, bspw. mit DATENAME(m, getdate()). Da die Tabellenstruktur mit der existierenden übereinstimmen muss, besteht die wesentliche Abweichung für den einzelnen Monat im Tabellennamen. Sie können jedoch immer den identischen Namen verwenden, da die Tabelle nach dem Hinzufügen zur Partition nicht weiter benötigt wird. Die Tabelle ist nach dem Einfügen der Daten in die partitionierte Tabelle weiter vorhanden, Sie können die Stagingtabelle nach dem Abschluss des Vorgangs jedoch entfernen. Darüber hinaus muss der Datumsbereich geändert werden. Da Sie mit datetime-Daten arbeiten und in Bezug auf die Speicherung von Zeit Rundungsprobleme existieren, müssen Sie in der Lage sein, die korrekte Millisekundenangabe programmgesteuert festzustellen. Der einfachste Weg zum Feststellen des letzten datetime-Werts für das jeweilige Monatsende besteht aus dem Addieren eines Monats und anschließendem Abziehen von 2 oder 3 Millisekunden. Eine einzige Millisekunde können Sie nicht abziehen, da 59,999 auf 0,000 aufgerundet wird, dies steht jedoch für den ersten Tag des Folgemonats. 2 oder 3 Millisekunden können Sie jedoch subtrahieren, da die Subtraktion von 2 Millisekunden zur Abrundung auf 997 nach dem Komma führt und 3 Millisekunden einem Wert von 0,997 entsprechen. Daher ist ein Nachkommawert von 997 ein gültiger Wert, der gespeichert werden kann. So erhalten Sie den korrekten Endwert für Ihren datetime-Bereich:

   DECLARE @Month            nchar(2),
         @Year            nchar(4),
       @StagingDateRange      nchar(10)
   SELECT @Month = N'11', @Year = N'2004'
   SELECT @StagingDateRange = @Year + @Month + N'01'
   SELECT dateadd(ms, -2, @StagingDateRange)
   

   Die Tabelle wird für jeden Monat neu erstellt, da sie in der Dateigruppe vorliegen muss, in die Daten eingefügt oder ausgelagert werden. Um die zum Arbeiten richtige Dateigruppe zu ermitteln, verwenden Sie die folgende Systemtabellenabfrage in Kombination mit der bereits dargestellten $partition-Funktion. Geben Sie jedes Datum für den zu bearbeitenden Bereich an. In dieser Partition und Dateigruppe werden alle Arbeiten ausgeführt. Die unterstrichenen Abschnitte müssen für Ihre spezielle Tabelle, Partitionierungsfunktion und das jeweilige Datum angepasst werden.

   SELECT ps.name AS PSName, 
         dds.destination_id AS PartitionNumber, 
       fg.name AS FileGroupName
   FROM (((sys.tables AS t 
   INNER JOIN sys.indexes AS i 
         ON (t.object_id = i.object_id))
   INNER JOIN sys.partition_schemes AS ps 
         ON (i.data_space_id = ps.data_space_id))
   INNER JOIN sys.destination_data_spaces AS dds 
         ON (ps.data_space_id = dds.partition_scheme_id))
   INNER JOIN sys.filegroups AS fg
         ON dds.data_space_id = fg.data_space_id
   WHERE (t.name = 'Orders') AND (i.index_id IN (0,1)) AND
   dds.destination_id = $partition.TwoYearDateRangePFN('20021001')
   

2. Füllen Sie die Stagingtabelle mit Daten. Sind die Dateien konsistent, kann dieser Vorgang mit BULK INSERT-Anweisungen durchgeführt werden.

   Automatisierung: Dieser Vorgang ist am schwierigsten zu automatisieren. Sie müssen sicherstellen, dass alle Dateien geladen wurden. Überlegenswert ist ein paralleles Laden dieser Dateien. Eine Tabelle unterstützt diesen Vorgang, indem Sie die bereits geladenen Dateien und deren Speicherorte protokolliert. Sie können einen SQL Agent-Job erstellen, der in Minutenintervallen das Vorhandensein von Dateien prüft, die neuen Dateien lädt und anschließend mehrere BULK INSERT-Anweisungen ausführt.

3. Nachdem die Daten geladen wurden, können Sie die Einschränkung hinzufügen. Um die Vertrauenswürdigkeit der Daten zu gewährleisten, muss die Einschränkung mit WITH CHECK eingesetzt werden. Die WITH CHECK-Einstellung entspricht der Standardeinstellung und müsste daher nicht unbedingt angegeben werden, es ist jedoch sehr wichtig, nicht WITH NOCHECK zu verwenden.

4. Indizieren Sie die Stagingtabelle. Sie muss denselben gruppierten Index wie die Tabelle aufweisen, in der sie eine Partition wird.

   ALTER TABLE [OrdersOctober2004]
   ADD CONSTRAINT OrdersOctober2004PK 
   PRIMARY KEY CLUSTERED (OrderDate, OrderID)
   ON [FG1]
   GO
   

   Automatisierung: Das ist ein einfacher Schritt. Mit der Monatsangabe und den Dateigruppeninformationen aus dem ersten Schritt können Sie diesen gruppierten Index erstellen.

   ALTER TABLE SalesDB.[dbo].[OrdersOctober2004]  
   WITH CHECK
   ADD CONSTRAINT OrdersRangeYearCK
   CHECK ([OrderDate] >= '20041001' 
       AND [OrderDate] <= '20041031 23:59:59.997')
   GO
   

Verwalten einer zweiten Stagingtabelle für die auszulagernde Partition

1. Erstellen Sie eine zweite Stagingtabelle. Es handelt sich um eine leere Tabelle, die beim Auslagern die Daten der Partition enthalten wird.

   CREATE TABLE SalesDB.[dbo].[OrdersOctober2002]  
   (
   [OrderID] [int] NOT NULL,
   [EmployeeID] [int] NULL,
   [VendorID] [int] NULL,
   [TaxAmt] [money] NULL,
   [Freight] [money] NULL,
   [SubTotal] [money] NULL,
   [Status] [tinyint] NOT NULL,
   [RevisionNumber] [tinyint] NULL,
   [ModifiedDate] [datetime] NULL,
   [ShipMethodID] [tinyint] NULL,
   [ShipDate] [datetime] NOT NULL, 
   [OrderDate] [datetime] NOT NULL, 
   [TotalDue] [money] NULL
   ) ON [FG1]
   GO
   

2. Indizieren Sie die Stagingtabelle. Sie muss denselben gruppierten Index wie die Tabelle aufweisen, in der sie eine Partition wird (die Partition wird aus dieser Tabelle entstehen).

   ALTER TABLE [OrdersOctober2002]
   ADD CONSTRAINT OrdersOctober2002PK 
   PRIMARY KEY CLUSTERED (OrderDate, OrderID)
   ON [FG1]
   GO
   

Auslagern der alten Daten und Einfügen der neuen Daten in die partitionierte Tabelle

1. Lagern Sie die alten Daten in die zweite Stagingtabelle aus.

   ALTER TABLE Orders
   SWITCH PARTITION 1
   TO OrdersOctober2002
   GO
   

2. Ändern Sie die Partitionsfunktion, um die Bereichsgrenze für Oktober 2002 zu entfernen.

   ALTER PARTITION FUNCTION TwoYearDateRangePFN()
   MERGE RANGE ('20021031 23:59:59.997')
   GO
   

   • Dadurch wird die Verbindung zwischen der Dateigruppe und dem Partitionsschema ebenfalls entfernt. Insbesondere ist FG1 nicht mehr Bestandteil des Partitionsschemas. Wenn Sie die neuen Daten über dieselben bestehenden 24 Partitionen bereitstellen, müssen Sie FG1 zur "nächsten verwendeten" Partition machen, die für eine Teilung verwendet wird.

     ALTER PARTITION SCHEME TwoYearDateRangePScheme 
                   NEXT USED [FG1]
     GO
     

3. Ändern Sie die Partitionsfunktion, um die neue Bereichsgrenze für Oktober 2004 hinzuzufügen.

   ALTER PARTITION FUNCTION TwoYearDateRangePFN() 
   SPLIT RANGE ('20041031 23:59:59.997')
   GO
   

4. Ändern Sie die Definition der Einschränkung für die Basistabelle (sofern vorhanden), um den neuen Datenbereich zuzulassen. Da ein Hinzufügen von Einschränkungen zeitintensiv sein kann (Überprüfung der Daten), können Sie als bewährte Methode die Datumsangabe erweitern, statt die Einschränkung zu entfernen und neu zu erstellen. Im Moment existiert lediglich eine Einschränkung (OrdersRangeYearCK), in der Zukunft werden jedoch zwei vorhanden sein.

   ALTER TABLE Orders
   ADD CONSTRAINT OrdersRangeMaxOctober2004
   CHECK ([OrderDate] < '20041101')
   GO
   ALTER TABLE Orders
   ADD CONSTRAINT OrdersRangeMinNovember2002
   CHECK ([OrderDate] >= '20021101')
   GO
       ALTER TABLE Orders
       DROP CONSTRAINT OrdersRangeYearCK
       GO
   

5. Fügen Sie die neuen Daten aus der ersten Stagingtabelle ein.

   ALTER TABLE OrdersOctober2004
   SWITCH TO Orders PARTITION 24
   GO
   

Entfernen der Stagingtabellen

Alle Daten werden im nächsten (und letzten) Schritt archiviert, daher werden die Stagingdaten nicht benötigt. Eine DROP-Anweisung ist der kürzeste Weg zum Entfernen dieser Tabellen.

DROP TABLE dbo.OrdersOctober2002
GO
DROP TABLE dbo.OrdersOctober2004
GO

Sichern der Dateigruppe

Das Auswählen der Sicherungsmethode im letzten Schritt ist von Ihrer Sicherungsstrategie abhängig. Wenn eine Sicherungsstrategie auf Basis von Dateien bzw. Dateigruppen gewählt wurde, sollte eine Datei- oder Dateigruppensicherung durchgeführt werden. Ist als Sicherungsstrategie eine vollständige Datenbanksicherung gewählt, sollte diese bzw. eine differenzielle Sicherung durchgeführt werden.

BACKUP DATABASE SalesDB 
   FILEGROUP = 'FG1' 
TO DISK = 'C:\SalesDB\SalesDB.bak'
GO

Listenpartitionierung: Regionale Daten

Wenn Ihre Tabelle Daten aus verschiedenen Regionen enthält und Analysen oft nur für eine Region ausgeführt werden, oder Sie regelmäßig Daten für einzelne Regionen erhalten, sollten Sie überlegen, definierte Bereichspartitionen in Form einer Liste einzusetzen. Mit anderen Worten: Sie verwenden eine Funktion, die jede Partition explizit als Wert für eine Region definiert. Stellen Sie sich beispielsweise ein spanisches Unternehmen vor, das Niederlassungen in Spanien, Frankreich, Deutschland, Italien und Großbritannien hat. Die Umsatzzahlen werden immer pro Land analysiert. Ihre Tabelle könnte dann fünf Partitionen enthalten, jeweils eine Partition für ein Land.

Das Erstellen dieser Listenpartition ist nahezu identisch zum Erstellen der Datumsbereichspartition, mit Ausnahme der Tatsache, dass die Bereichsgrenzen keine anderen Werte außerhalb des aktuellen Partitionsschlüssels enthalten. Es handelt sich stattdessen um eine Liste, nicht um Bereiche. Obwohl es sich um eine Liste handelt, müssen die Grenzbedingungen eine äußerste linke und äußerste rechte Bedingung enthalten. Zum Erstellen von fünf Partitionen geben Sie in der Partitionsfunktion lediglich vier an. Die Werte müssen nicht sortiert sein (SQL Server sortiert sie intern), der logischste Weg zum Festlegen der richtigen Anzahl von Partitionen besteht aus dem Sortieren der Partitionswerte und Auslassen des höchsten Werts für die letzte Partition (wenn diese als LEFT-Partitionsfunktion definiert wurde), oder dem Sortieren der Partitionierungswerte und Beginnen mit dem zweitniedrigsten Wert (für RIGHT).

Da fünf Partitionen vorhanden sind, müssen Sie fünf Dateigruppen verwenden. In diesem Fall werden die Dateigruppen nach den in ihnen gespeicherten Daten benannt. Das Skript RegionalRangeCaseStudyFilegroups.sql enthält die vollständige Syntax. Jede Dateigruppe wird mit denselben Einstellungen erstellt. Das ist jedoch nur der Fall, wenn die Daten ausgewogen sind. Nur die Dateigruppe und die Datei für Spanien werden angezeigt, die vier zusätzlichen Dateigruppen und Dateien weisen dieselben Parameter auf, befinden sich jedoch auf anderen Laufwerken und haben den speziellen Namen der Länderpartition.

ALTER DATABASE SalesDB
ADD FILEGROUP [Spain]
GO
ALTER DATABASE SalesDB    
ADD FILE      
    (NAME = N'SalesDBSpain',
        FILENAME = N'C:\SalesDB\SalesDBSpain.ndf',
        SIZE = 1MB,
        MAXSIZE = 100MB,
        FILEGROWTH = 5MB) 
TO FILEGROUP [Spain]
GO

Der nächste Schritt besteht aus dem Erstellen der Funktion, die nur vier Partitionen definiert, indem sie LEFT als Grenzbedingung verwendet. Im Beispiel wird die Liste alle Länder mit Ausnahme von Großbritannien enthalten, da dies in alphabetischer Sortierung (engl.: UK) am Ende steht.

CREATE PARTITION FUNCTION CustomersCountryPFN(char(7))
AS 
RANGE LEFT FOR VALUES ('France', 'Germany', 'Italy', 'Spain')
GO

Damit die Daten in die richtige Dateigruppe gelangen, wird das Partitionsschema alphabetisch geordnet. Alle fünf Dateigruppen müssen in der Syntax des Partitionierungsschemas angegeben werden.

CREATE PARTITION SCHEME [CustomersCountryPScheme]
AS 
PARTITION CustomersCountryPFN 
   TO ([France], [Germany], [Italy], [Spain], [UK])
GO

Abschließend kann die Tabelle Customers (Kunden) auf Basis des neuen Schemas CustomersCountryPScheme erstellt werden.

CREATE TABLE [dbo].[Customers](
   [CustomerID] [nchar](5) NOT NULL,
   [CompanyName] [nvarchar](40) NOT NULL,
   [ContactName] [nvarchar](30) NULL,
   [ContactTitle] [nvarchar](30) NULL,
   [Address] [nvarchar](60) NULL,
   [City] [nvarchar](15) NULL,
   [Region] [nvarchar](15) NULL,
   [PostalCode] [nvarchar](10) NULL,
   [Country] [char](7) NOT NULL,
   [Phone] [nvarchar](24) NULL,
   [Fax] [nvarchar](24) NULL
) ON CustomersCountryPScheme (Country)
GO

Obwohl Bereichspartitionen ihrer Definition entsprechend lediglich Bereiche unterstützen, bieten sie gleichzeitig Möglichkeiten für andere Partitionstypen, bspw. Listenpartitionen.

 

Schlussbemerkung

SQL Server 2005 bietet durch Partitionierung einen einfachen und einheitlichen Weg zum Verwalten großer Tabellen und Indizes, damit Sie Teilmengen der Daten außerhalb der aktiven Tabelle verwalten können. Als Folge ergeben sich vereinfachte Verwaltung, erhöhte Leistung und eine abstrakte Anwendungslogik, da das Partitionierungsschema für die Anwendung völlig transparent ist. Wählen Sie die Bereichspartitionierung, wenn Ihre Daten logische Gruppierungen (Bereiche oder Listen) enthalten und größere Abfragen die Daten innerhalb dieser vordefinierten und konsistenten Bereiche analysieren sowie ein- und ausgehende Daten innerhalb derselben vordefinierten Bereiche verwalten. Enthalten Analysen große Datenmengen ohne bestimmte verwendbare Bereichsangaben oder greifen Abfragen auf nahezu alle Daten zu, ist das Verwenden von mehreren Dateigruppen ohne besondere Positionierungstechniken eine einfachere Lösung, die trotzdem Leistungsgewinne erzielt.

Skripts zu diesem Artikel

Die in den Codebeispielen verwendeten Skripts sind in der Datei SQLServer2005PartitionedTables.zip enthalten. Im Folgenden wird jedes in der Datei enthaltene Skript beschrieben.

RangeCaseStudyScript1-Filegroups.sql: Enthält die Syntax zum Erstellen der Dateigruppen und Dateien, die in der Fallstudie der nach Bereichen partitionierten Tabelle verwendet werden. Das Skript kann von Ihnen angepasst werden, damit das Beispiel mit weniger Laufwerken und kleineren Dateien (MB statt GB) nachvollzogen werden kann. Es enthält ebenfalls Code zum Importieren von Daten mittels INSERT...SELECT-Anweisungen. Damit können Sie nachvollziehen, an welchen Orten die Daten von den jeweiligen Partitionierungsfunktionen abgelegt werden.

RangeCaseStudyScript2-PartitionedTable.sql: Enthält die Syntax zum Erstellen der Partitionsfunktion, des Partitionsschemas und der nach Bereichen partitionierten Tabellen für die entsprechende Fallstudie. Dieses Skript enthält darüber hinaus die Einschränkungen und Indizes.

RangeCaseStudyScript3-JoiningAlignedTables.sql: Enthält Abfragen, die die verschiedenen durch SQL Server bereitgestellten Verknüpfungsstrategien für partitionierte Tabellen demonstrieren.

RangeCaseStudyScript4-SlidingWindow.sql: Enthält die Syntax und Abläufe in Bezug auf die monatliche Verwaltung der nach Bereichen partitionierten Tabelle der Fallstudie. In diesem Skript werden Daten in die Tabelle Orders eingefügt bzw. aus der Tabelle ausgelagert. Optional können Sie den gleichen Vorgang erstellen, um Daten in die Tabelle OrderDetails einzufügen bzw. aus der Tabelle auszulagern. Tipp: Betrachten Sie das in RangeCaseStudyScript2 für die Tabelle verwendete INSERT und passen Sie die Datenspalten zum Einfügen der Bestelldetails an.

RegionalRangeCaseStudyFilegroups.sql: Enthält die Syntax zum Erstellen der Dateigruppen und Dateien, die in der Fallstudie zur nach Regionen partitionierten Tabelle verwendet werden. Tatsächlich handelt es sich um eine Bereichspartition, die ein Listenpartitionsschema simuliert.

RegionalRangeCaseStudyPartitionedTable.sql: Enthält die Syntax zum Erstellen der Partitionsfunktion, des Partitionsschemas und der nach Regionen partitionierten Tabelle für die entsprechende Fallstudie.