SQL Server
Vereinfachen der Datenbankwartung mit Tabellenpartitionen
Noah Gomez
Kurz zusammengefasst:
- Erstellen partitionierter Tabellen
- Hinzufügen und Zusammenführen von Tabellen
- Erstellen und Verwalten partitionierter Indexe
Laden Sie den Code für diesen Artikel herunter: GomezPartitioning2007_03.exe (156KB)
In der Vergangenheit waren Datenbankadministratoren, die Tabellen mit Millionen von Datenzeilen verwalten, gezwungen, mehrere Tabellen zu erstellen. Nach dem Partitionieren dieser Tabellen musste der Administrator
die Tabellen während der Ausführung von vielen Abfragen wieder verknüpfen. Beim Verknüpfen von Partitionen musste eine partitionierte Ansicht oder ein in einem Wrapper gespeichertes Verfahren erstellt werden, das erkannte, wo sich die Daten befanden, und das ein weiteres gespeichertes Verfahren ausführte, das nur auf die zur Rückgabe des Dataset erforderlichen Partitionen abzielte.
Obwohl diese Methoden funktionierten, waren sie aufwändig. Die Verwaltung mehrerer Tabellen und ihrer Indexe sowie die Methoden zum erneuten Verknüpfen der Tabellen verursachten oft Verwaltungs- und Wartungsprobleme. Außerdem führte das Erstellen mehrerer Tabellen zum Partitionieren von Daten zu einer gewissen Inflexibilität, da die Art der Partitionierung für die gespeicherten Verfahren, Wartungsaufträge, DTS-Aufträge (Data Transformation Services), Anwendungscode und anderen Prozesse verständlich sein musste. Damit der Benutzer diese Quasipartitionen ohne Änderung des Codes hinzufügen oder löschen konnte, wurden diese Elemente in der Regel auf nicht dynamische Weise erstellt und waren daher ineffizient.
Mit den Enterprise- und Developer-Editionen von SQL Server™ 2005 können Sie große Datenmengen in einer einzelnen Tabelle in mehrere kleinere Partitionen partitionieren, die sich effektiver verwalten und warten lassen. Die Möglichkeit, Datensegmente zu erstellen, auf die über einen einzelnen Ausgangspunkt zugegriffen wird, verringert viele der früheren Verwaltungsprobleme. Die Verwendung eines einzelnen Ausgangspunkts (Tabellen- oder Indexname) verbirgt die Datensegmente vor dem Anwendungscode und ermöglicht es dem Administrator oder Entwickler, die Partitionen bei Bedarf zu ändern, ohne die Codebasis anzupassen.
Sie können also mehrere Partitionen erstellen, diese Partitionen verschieben, alte Partitionen löschen und sogar die Art und Weise ändern, wie Daten partitioniert werden, ohne den Code in Ihrer Anwendung anzupassen. Ihr Anwendungscode ruft einfach weiterhin dieselbe Basistabelle oder denselben Indexnamen auf. Sie können auch die in einzelnen Indexen enthaltenen Daten verringern, wodurch wiederum die Wartungszeiten für diese Indexe reduziert werden, und Sie können die Geschwindigkeit beim Laden von Daten erhöhen, indem Sie sie in leere Partitionen laden.
Technisch gesehen wird jede SQL Server 2005-Tabelle partitioniert, d. h. jede Tabelle hat mindestens eine Partition. SQL Server 2005 ermöglicht es Datenbankadministratoren, zusätzliche Partitionen in jeder Tabelle zu erstellen. Bei Tabellen- und Indexpartitionen handelt es sich um fest definierte Partitionen auf Zeilenebene (das Partitionieren nach Spalten ist nicht zulässig), die einen einzelnen Ausgangspunkt (Tabellen- oder Indexname) zulassen, ohne dass dem Anwendungscode die dem Ausgangspunkt zugrunde liegende Anzahl der Partitionen bekannt sein muss. Partitionen können in der Basistabelle sowie in den der betreffenden Tabelle zugeordneten Indexen vorhanden sein.
Erstellen partitionierter Tabellen
Partitionsfunktionen und Partitionsschemas werden zum Erstellen einer Tabelle verwendet, die über die einzelne Standardpartition hinauswachsen kann. Mithilfe dieser Objekte können Sie Daten in bestimmte Segmente unterteilen und steuern, wo sich diese Datensegmente in Ihrem Speicherentwurf befinden. Sie können Daten beispielsweise auf Grundlage ihres Alters oder mithilfe anderer gemeinsamer Unterscheidungsmerkmale über mehrere Laufwerkarrays verteilen. Beachten Sie, dass eine Tabelle auf der Grundlage einer Tabellenspalte partitioniert werden kann, und jede Partition muss Daten enthalten, die nicht in die anderen Partitionen gestellt werden können.
Partitionsfunktionen. Beim Partitionieren einer Tabelle müssen Sie als erstes entscheiden, wie Sie die Daten in verschiedene Segmente unterteilen wollen. Eine Partitionsfunktion dient zur Zuordnung der einzelnen Datenzeilen zu verschiedenen Partitionen. Die Zuordnung dieser einzelnen Datenzeilen kann über beliebige Spalten mit der Ausnahme von text, ntext, image, xml, timestamp, varchar(max), nvarchar(max), varbinary(max), Aliasdatentypen oder benutzerdefinierten CLR-Datentypen (Common Language Runtime) erfolgen. Die Partitionierungsfunktion muss jedoch in der Lage sein, eine Datenzeile in nur eine Tabellenpartition zu stellen. Es ist nicht zulässig, dass eine Datenzeile gleichzeitig zu mehreren Partitionen gehört.
Zum Partitionieren einer Tabelle müssen Sie die Partitionierungsspalte in der Zieltabelle erstellen. Diese Partitionierungsspalte kann im Tabellenschema vorhanden sein, wenn die Tabelle erstellt wird. Sie können die Tabelle auch ändern und die Spalte zu einem späteren Zeitpunkt hinzufügen. Die Spalte kann NULL-Werte annehmen, aber alle Zeilen, die NULL-Werte enthalten, werden standardmäßig ganz links in die Tabellenpartition gestellt. Dies lässt sich vermeiden, indem Sie angeben, dass NULL-Werte ganz rechts in die Tabellenpartition gestellt werden sollen, wenn Sie die Partitionierungsfunktion erstellen. Die Verwendung der linken oder rechten Partition ist eine wichtige Entwurfsentscheidung, wenn Sie Ihr Partitionierungsschema ändern und mehr Partitionen hinzufügen oder vorhandene Partitionen löschen.
Beim Erstellen einer Partitionierungsfunktion können Sie eine LEFT- oder RIGHT-Partitionsfunktion auswählen. Der Unterschied zwischen LEFT- und RIGHT-Partitionen besteht darin, wo die Begrenzungswerte in das Partitionierungsschema gestellt werden. LEFT-Partitionen (dies ist Standardpartition) umfassen den Begrenzungswert in der Partition, während RIGHT-Partitionen den Begrenzungswert in die nächste Partition stellen.
Sehen Sie sich zum besseren Verständnis dieses Konzepts diese einfachen LEFT- und RIGHT-Partitionen an:
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
CREATE PARTITION FUNCTION Right_Partition (int) AS RANGE RIGHT
FOR VALUES (1,10,100)
In der ersten Funktion (Left_Partition) werden die Werte 1, 10 und 100 in die erste, zweite beziehungsweise dritte Partition gestellt. In der zweiten Funktion (Right_Partition) werden die Werte in die zweite, dritte und vierte Partition gestellt.
Beim Erstellen einer partitionierten Tabelle ist es wichtig, die Partitionen möglichst gleichmäßig zu erstellen. Dadurch werden Sie besser verstehen, wie viel Platz für eine Partition erforderlich ist. Durch die Verwendung einer LEFT- und RIGHT-Partition wird festgelegt, wo Daten hingestellt werden, und dies legt wiederum die Größe der Partition und die Größe der Indexe fest, die in dieser Partition erstellt werden.
Sie können, wie hier dargestellt, mithilfe der $PARTITION-Funktion die Partitionsnummer festlegen, in die ein Datenwert gestellt wird:
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Right_Partition (10)
In der ersten SELECT-Anweisung ist das Ergebnis 2. Die zweite SELECT-Anweisung gibt 3 zurück.
Partitionsschemas. Wenn Sie die Funktion erstellt und entschieden haben, wie Ihre Daten unterteilt werden, müssen Sie entscheiden, wo die einzelnen Partitionen auf Ihrem Datenträgersubsystem erstellt werden. Zum Erstellen dieses Datenträgerlayouts verwenden Sie Partitionsschemas. Partitionsschemas verwalten den Plattenspeicher einzelner Partitionen durch die Verwendung von Dateigruppen, um jede Partition an einen Speicherort des Datenträgersubsystems zu stellen. Sie können Partitionsschemas so konfigurieren, dass alle Partitionen in eine einzelne Dateigruppe oder alle Partitionen in verschiedene Dateigruppen gestellt werden oder Dateigruppen von mehreren Partitionen gemeinsam genutzt werden. Die letztgenannte Methode bietet dem Datenbankadministrator viel Flexibilität beim Verteilen der Datenträger-E/A-Vorgänge.
Abbildung 1 zeigt einige Möglichkeiten beim Zuweisen einer Dateigruppe oder mehrerer Dateigruppen zu einem Partitionsschema. Sie sollten beachten, dass die von Ihrem Partitionsschema verwendeten Dateigruppen bereits in der Datenbank vorhanden sein müssen, bevor Sie Ihr Partitionierungsschema erstellen.
Figure 1 Zuweisen von Dateigruppen zu einem Partitionsschema
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
--Place all partitions into the different filegroups
CREATE PARTITION SCHEME Different_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
TO (Filegroup1, Filegroup2, Filegroup3, Filegroup4)
--Filegroups must currently exist in database
--Place multiple partitions into the different filegroups
CREATE PARTITION SCHEME Multiple_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
TO (Filegroup1, Filegroup2, Filegroup1, Filegroup2)
--Filegroups must currently exist in database
Wenn Sie die Beispielpartitionsfunktionen wie in Abbildung 1 dargestellt erstellen und das Partitionsschema zum Erstellen einer Tabelle verwenden, können Sie festlegen, wohin einzelne Datenzeilen in Ihren neu partitionierten Tabellen gestellt werden. Dann können Sie die Verteilung dieser Datenzeilen anzeigen, nachdem sie in Ihre partitionierte Tabelle eingefügt wurden. Der Code dazu sieht etwa wie der Code in Abbildung 2 aus.
Figure 2 Platzieren von Datenzeilen und Anzeigen der Verteilung
--Prepare database
IF OBJECT_ID('Partitioned_Table') IS NOT NULL
DROP TABLE Partitioned_Table
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Primary_Left_Scheme')
DROP PARTITION SCHEME Primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Left_Partition')
DROP PARTITION FUNCTION Left_Partition
GO
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
ALL TO ([PRIMARY])
CREATE TABLE Partitioned_Table
(
col1 INT
,col2 VARCHAR(15)
) ON Primary_Left_Scheme (col1)
--Determine where values will be placed (this is not required)
--You should try to do this before executing the code
SELECT $PARTITION.Left_Partition (1)
SELECT $PARTITION.Left_Partition (2)
SELECT $PARTITION.Left_Partition (3)
SELECT $PARTITION.Left_Partition (4)
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Left_Partition (11)
SELECT $PARTITION.Left_Partition (12)
SELECT $PARTITION.Left_Partition (13)
SELECT $PARTITION.Left_Partition (14)
SELECT $PARTITION.Left_Partition (100)
SELECT $PARTITION.Left_Partition (101)
SELECT $PARTITION.Left_Partition (102)
SELECT $PARTITION.Left_Partition (103)
SELECT $PARTITION.Left_Partition (104)
--Insert data into partitioned table
INSERT INTO Partitioned_Table VALUES (1,'Description')
INSERT INTO Partitioned_Table VALUES (2,'Description')
INSERT INTO Partitioned_Table VALUES (3,'Description')
INSERT INTO Partitioned_Table VALUES (4,'Description')
INSERT INTO Partitioned_Table VALUES (10,'Description')
INSERT INTO Partitioned_Table VALUES (11,'Description')
INSERT INTO Partitioned_Table VALUES (12,'Description')
INSERT INTO Partitioned_Table VALUES (13,'Description')
INSERT INTO Partitioned_Table VALUES (14,'Description')
INSERT INTO Partitioned_Table VALUES (100,'Description')
INSERT INTO Partitioned_Table VALUES (101,'Description')
INSERT INTO Partitioned_Table VALUES (102,'Description')
INSERT INTO Partitioned_Table VALUES (103,'Description')
INSERT INTO Partitioned_Table VALUES (104,'Description')
--View the distribution of data in the partitions
SELECT ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('Partitioned_Table')
Ändern partitionierter Tabellen
Trotz sorgfältiger Vorausplanung ist es manchmal erforderlich, Ihre partitionierten Tabellen anzupassen, nachdem sie erstellt und gefüllt wurden. Ihr Partitionsschema mag wie beabsichtigt funktionieren, doch möglicherweise müssen Sie neue Partitionen hinzufügen, wenn sich neue Daten ansammeln, oder vielleicht müssen Sie große Mengen partitionierter Daten gleichzeitig löschen. Glücklicherweise lassen partitionierte Tabellen und die zugrunde liegenden Partitionierungsstrukturen Änderungen zu, nachdem die Tabelle veröffentlicht und mit Daten gefüllt wurde.
Hinzufügen von Partitionen. Viele Partitionierungspläne bieten die Möglichkeit, in der Zukunft eine neue Partition hinzuzufügen. Dieser Zeitpunkt kann ein bestimmtes Datum sein oder von einem Wert in einer inkrementellen Identitätsspalte abhängen. Wenn Sie dies jedoch nicht im Voraus eingeplant haben, können Sie einer partitionierten Tabelle später immer noch neue Partitionen hinzufügen. Sehen Sie sich die in Abbildung 2 erstellte Tabelle an. Dieser Tabelle können Sie eine neue Partition, die höhere Werte als 500 enthalten soll, folgendermaßen hinzufügen:
--Determine where values live before new partition
SELECT $PARTITION.Left_Partition (501) --should return a value of 4
--Create new partition
ALTER PARTITION FUNCTION Left_Partition ()
SPLIT RANGE(500)
--Determine where values live after new partition
SELECT $PARTITION.Left_Partition (501) --should return a value of 5
Die Möglichkeit, Partitionen hinzuzufügen, bietet viel Flexibilität. Abbildung 3 zeigt, wie Sie links von der Funktion eine Partition hinzufügen können. In diesem Fall müssen Sie das Partitionierungsschema anweisen, wohin die neue Partition gestellt werden soll, da Sie alle Dateigruppen aufgebraucht haben, die Sie bei der ursprünglichen Erstellung des Partitionierungsschemas erstellt haben. Obwohl Sie die PRIMARY-Dateigruppe für alle Ihre Partitionen verwenden, müssen Sie das Partitionierungsschema dennoch anweisen, die PRIMARY-Dateigruppe für die neue Partition wiederzuverwenden.
Figure 3 Hinzufügen einer Partition zur linken Seite der Funktion
--Determine where values live after new partition
SELECT $PARTITION.Left_Partition (5) --should return a value of 2
SELECT $PARTITION.Left_Partition (1) --should return a value of 1
SELECT $PARTITION.Left_Partition (10) --should return a value of 2
--Add new filegroups to the partitioning scheme
ALTER PARTITION SCHEME Primary_Left_Scheme
NEXT USED [PRIMARY]
--Create new partition
ALTER PARTITION FUNCTION Left_Partition ()
SPLIT RANGE(5)
--Determine where values live after new partition
SELECT $PARTITION.Left_Partition (5) --should return a value of 2
SELECT $PARTITION.Left_Partition (1) --should return a value of 1
SELECT $PARTITION.Left_Partition (10) --should return a value of 3
Zusammenführen zweier Partitionen. SQL Server ermöglicht das Löschen einzelner Partitionen aus einer Tabelle, wobei die Daten erhalten bleiben. Dies kann zum Zusammenführen älterer aktiver Daten in archivierten Daten oder zur Verringerung der Anzahl vorhandener Partitionen genutzt werden, sodass die Verwaltung der partitionierten Tabelle erleichtert wird. Sie können mit dieser Funktion auch Partitionen zusammenführen, indem Sie Daten von einer Dateigruppe in eine andere verschieben, sodass Speicherplatz auf bestimmtem Laufwerkarrays frei wird. Der Code in Abbildung 4 zeigt, wie Sie Daten von einer Partition in eine andere Partition in derselben Dateigruppe verschieben können.
Figure 4 Verschieben von Daten von einer Partition in eine andere
--Prepare database
IF OBJECT_ID('multiple_partition') IS NOT NULL
DROP TABLE multiple_partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Primary_Left_Scheme')
DROP PARTITION SCHEME Primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Left_Partition')
DROP PARTITION FUNCTION Left_Partition
GO
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE multiple_partition
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Primary_Left_Scheme (col1)
INSERT INTO multiple_partition VALUES (1,'Description')
INSERT INTO multiple_partition VALUES (2,'Description')
INSERT INTO multiple_partition VALUES (3,'Description')
INSERT INTO multiple_partition VALUES (4,'Description')
INSERT INTO multiple_partition VALUES (10,'Description')
INSERT INTO multiple_partition VALUES (11,'Description')
INSERT INTO multiple_partition VALUES (12,'Description')
INSERT INTO multiple_partition VALUES (13,'Description')
INSERT INTO multiple_partition VALUES (14,'Description')
INSERT INTO multiple_partition VALUES (100,'Description')
INSERT INTO multiple_partition VALUES (101,'Description')
INSERT INTO multiple_partition VALUES (102,'Description')
INSERT INTO multiple_partition VALUES (103,'Description')
INSERT INTO multiple_partition VALUES (104,'Description')
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
--Check where data would be placed
SELECT $PARTITION.Left_Partition (1)
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Left_Partition (100)
SELECT $PARTITION.Left_Partition (101)
--Merge two partitions
ALTER PARTITION FUNCTION Left_Partition()
MERGE RANGE (10)
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
--Check where data would be placed
SELECT $PARTITION.Left_Partition (1)
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Left_Partition (100)
SELECT $PARTITION.Left_Partition (101)
Verschieben einer Einzelpartitionstabelle in eine partitionierte Tabelle. Bei Laderoutinen müssen oft große Datenmengen in die Datenbank geladen und dann geändert oder aggregiert werden, bevor sie in die eigentliche Datentabelle verschoben werden. Die Partitionsfunktionen von SQL Server 2005 ermöglichen das Verschieben einer Einzelpartitionstabelle in eine Tabelle mit mehreren Partitionen. So können Sie Daten in eine einzelne Ladetabelle laden, diese Daten ändern und dann die ganze Tabelle in eine vorhandene Tabelle verschieben, ohne jede einzelne Datenzeile verschieben zu müssen. Bei dieser Schicht der Partitionierung müssen die zugrunde liegenden Partitionierungsstrukturen nicht geändert werden. Vielmehr wird die partitionierte Tabelle geändert. Der Code in Abbildung 5 zeigt, wie es geht.
Figure 5 Verschieben einer ganzen Tabelle in eine vorhandene Tabelle
--Prepare database
IF OBJECT_ID(‘multiple_partition’) IS NOT NULL
DROP TABLE multiple_partition
GO
IF OBJECT_ID(‘single_partition’) IS NOT NULL
DROP TABLE single_partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = ‘Primary_Left_Scheme’)
DROP PARTITION SCHEME Primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = ‘Left_Partition’)
DROP PARTITION FUNCTION Left_Partition
GO
--Create single partition table
CREATE TABLE single_partition
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
)
--Table must have a CHECK Constraint
ALTER TABLE single_partition
WITH CHECK
ADD CONSTRAINT CK_single_partition
CHECK (col1 > 100)
INSERT INTO single_partition VALUES (101,’Description’)
INSERT INTO single_partition VALUES (102,’Description’)
INSERT INTO single_partition VALUES (103,’Description’)
INSERT INTO single_partition VALUES (104,’Description’)
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE multiple_partition
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Primary_Left_Scheme (col1)
INSERT INTO multiple_partition VALUES (1,’Description’)
INSERT INTO multiple_partition VALUES (2,’Description’)
INSERT INTO multiple_partition VALUES (3,’Description’)
INSERT INTO multiple_partition VALUES (4,’Description’)
INSERT INTO multiple_partition VALUES (10,’Description’)
INSERT INTO multiple_partition VALUES (11,’Description’)
INSERT INTO multiple_partition VALUES (12,’Description’)
INSERT INTO multiple_partition VALUES (13,’Description’)
INSERT INTO multiple_partition VALUES (14,’Description’)
INSERT INTO multiple_partition VALUES (100,’Description’)
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID(‘multiple_partition’), OBJECT_
ID(‘single_partition’))
--Move the single table into the partitioned table
ALTER TABLE single_partition SWITCH TO multiple_partition PARTITION 4
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID(‘multiple_partition’), OBJECT_ID(‘single_partition’))
Verschieben einer Partition aus einer Tabelle in eine andere. Eine häufige administrative Aufgabe besteht im Verschieben älterer Daten in separate Archivtabellen. Beim Archivierungsprozess ist normalerweise eine Reihe von Anweisungen beteiligt, was zu zusätzlicher Ressourcennutzung in Ihren Transaktionsprotokollen führen kann. Den Besitz einer Partition von einer Tabelle auf eine andere umzustellen, ist jedoch eine einfach Möglichkeit, große Datenmengen ohne den üblichen Transaktionsprotokollaufwand zu archivieren. Dieses Feature ermöglicht es dem Datenbankadministrator, Segmente älterer Daten aus seinen aktiven Tabellen in archivierte Tabellen zu verschieben. Doch da die Daten nicht wirklich verschoben werden, kann der Zeitaufwand sehr viel geringer als beim Verschieben einzelner Datenzeilen sein. Abbildung 6 zeigt, wie es geht.
Figure 6 Verschieben älterer Daten in archivierte Tabellen
--Prepare database
IF OBJECT_ID('active_data') IS NOT NULL
DROP TABLE active_data
GO
IF OBJECT_ID('archive_data') IS NOT NULL
DROP TABLE archive_data
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Active_Scheme')
DROP PARTITION SCHEME Active_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Active_Partition')
DROP PARTITION FUNCTION Active_Partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Archive_Scheme')
DROP PARTITION SCHEME Archive_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Archive_Partition')
DROP PARTITION FUNCTION Archive_Partition
GO
--Create active function
CREATE PARTITION FUNCTION Active_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Create archive function
CREATE PARTITION FUNCTION Archive_Partition (int) AS RANGE LEFT
FOR VALUES (100,200,300)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Active_Scheme
AS PARTITION Active_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Archive_Scheme
AS PARTITION Archive_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE active_data
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Active_Scheme (col1)
CREATE TABLE archive_data
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Archive_Scheme (col1)
INSERT INTO active_data VALUES (1,'Description')
INSERT INTO active_data VALUES (2,'Description')
INSERT INTO active_data VALUES (3,'Description')
INSERT INTO active_data VALUES (4,'Description')
INSERT INTO active_data VALUES (10,'Description')
INSERT INTO active_data VALUES (11,'Description')
INSERT INTO active_data VALUES (12,'Description')
INSERT INTO active_data VALUES (13,'Description')
INSERT INTO active_data VALUES (14,'Description')
INSERT INTO active_data VALUES (100,'Description')
INSERT INTO archive_data VALUES (200,'Description')
INSERT INTO archive_data VALUES (300,'Description')
INSERT INTO archive_data VALUES (400,'Description')
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
data'))
--Switch ownership of partition to another table
ALTER TABLE active_data SWITCH PARTITION 3 TO archive_data PARTITION 1
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
data'))
Verwenden einer Einzelpartition zum Erstellen einer neuen Tabelle. Sie können eine Einzelpartition aus einer vorhandenen partitionierten Tabelle in eine leere, nicht partitionierte Tabelle verschieben. Auf diese Weise kann der Datenbankadministrator die Indexwartung auf der Einzelpartition durchführen oder leicht große Datenmengen löschen, ohne dass der Löschprozess protokolliert wird. Das Beispiel in Abbildung 7 zeigt, wie eine Partition in eine leere Tabelle verschoben und die neue Tabelle dann zum Löschen der Daten verwendet wird.
Figure 7 Verschieben und Löschen von Daten
--Prepare database
IF OBJECT_ID('active_data') IS NOT NULL
DROP TABLE active_data
GO
IF OBJECT_ID('archive_data') IS NOT NULL
DROP TABLE archive_data
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Active_Scheme')
DROP PARTITION SCHEME Active_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Active_Partition')
DROP PARTITION FUNCTION Active_Partition
GO
--Create active function
CREATE PARTITION FUNCTION Active_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Active_Scheme
AS PARTITION Active_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE active_data
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Active_Scheme (col1)
CREATE TABLE archive_data
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
)
INSERT INTO active_data VALUES (1,'Description')
INSERT INTO active_data VALUES (2,'Description')
INSERT INTO active_data VALUES (3,'Description')
INSERT INTO active_data VALUES (4,'Description')
INSERT INTO active_data VALUES (10,'Description')
INSERT INTO active_data VALUES (11,'Description')
INSERT INTO active_data VALUES (12,'Description')
INSERT INTO active_data VALUES (13,'Description')
INSERT INTO active_data VALUES (14,'Description')
INSERT INTO active_data VALUES (100,'Description')
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
data'))
--Switch ownership of partition to another table
ALTER TABLE active_data SWITCH PARTITION 3 TO archive_data
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
data'))
--Drop all archive data without logging
DROP TABLE archive_data
GO
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
data'))
Partitionierte Indexe
Mit der Möglichkeit zum Partitionieren der Daten einer Tabelle bietet sich die Möglichkeit, partitionierte Indexe zu erstellen. So kann der Datenbankadministrator die Indexstruktur auf Grundlage der geteilten Daten statt auf Grundlage der Daten der gesamten Tabelle entwerfen. Das Erstellen partitionierter Indexe führt zu einzelnen B-Strukturen in den partitionierten Indexen. Durch das Teilen der Indexe entstehen kleinere Indexe, die beim Ändern, Hinzufügen und Löschen von Daten durch das Speichermodul leichter gewartet werden können. Diese kleineren Indexe können auch einzeln vom Datenbankadministrator verwaltet werden, was eine bessere Indexwartung bei großen Datasets ermöglicht.
Erstellen partitionierter Indexe. Beim Erstellen partitionierter Indexe können Sie ausgerichtete oder nicht ausgerichtete Indexe erstellen. Bei ausgerichteten Indexen erstellen Sie den Index mit einer direkten Beziehung zu den partitionierten Daten. (Bei nicht ausgerichteten Indexen wählen Sie ein anderes Partitionierungsschema.)
Ausgerichtete Indexe sind die bevorzugte Methode, die automatisch ausgeführt wird, wenn Sie die Partitionstabelle und dann die Indexe erstellen, ohne ein anderes Partitionierungsschema anzugeben. Die Verwendung ausgerichteter Indexe bietet Ihnen die Flexibilität zum Erstellen zusätzlicher Partitionen in der Tabelle und die Möglichkeit, den Besitz einer Partition auf eine andere Tabelle umzustellen. Diese Funktionen sind oft der Grund, warum Datenbankadministratoren überhaupt partitionierte Tabellen erstellen. Mit der Verwendung des Partitionierungsschemas der Tabelle für Ihre Indexe werden Sie Ihre Ziele bei der Partitionierung wahrscheinlich erreichen.
Sie können Indexe für Tabellen erstellen, bei denen die Daten im Index nicht an den Daten in der Tabelle ausgerichtet sind. Wenn sich die Daten in einer partitionierten Tabelle befinden, können Sie die Daten auf unterschiedliche Weise verknüpfen. (Partitionierte Daten können mithilfe des Abfrageoptimierers effizient mit anderen partitionierten Daten verknüpft werden.) Sie können dies auch mit einer nicht partitionierten Tabelle tun, sodass Sie einen partitionierten Index (für die Einzelpartitionstabelle) erstellen können, was die Indexwartung erleichtert.
Der Code in Abbildung 8 erstellt einen partitionierten, nicht gruppierten Index in einer partitionierten Tabelle. Der nicht gruppierte Index wird an der Tabelle ausgerichtet und nutzt die Partitionierungsspalte der Tabelle als nicht gruppierten Indexschlüssel.
Figure 8 Partitionierter, nicht gruppierter Index in einer partitionierten Tabelle
--Prepare database
IF OBJECT_ID('multiple_partition') IS NOT NULL
DROP TABLE multiple_partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Primary_Left_Scheme')
DROP PARTITION SCHEME Primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Left_Partition')
DROP PARTITION FUNCTION Left_Partition
GO
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE multiple_partition
(
col1 INT
,col2 VARCHAR(15)
) ON Primary_Left_Scheme (col1)
--Create partitioned non-clustered index
CREATE NONCLUSTERED INDEX cl_multiple_partition ON multiple_
partition(col1)
INSERT INTO multiple_partition VALUES (1,'Description')
INSERT INTO multiple_partition VALUES (2,'Description')
INSERT INTO multiple_partition VALUES (3,'Description')
INSERT INTO multiple_partition VALUES (4,'Description')
INSERT INTO multiple_partition VALUES (10,'Description')
INSERT INTO multiple_partition VALUES (11,'Description')
INSERT INTO multiple_partition VALUES (12,'Description')
INSERT INTO multiple_partition VALUES (13,'Description')
INSERT INTO multiple_partition VALUES (14,'Description')
INSERT INTO multiple_partition VALUES (100,'Description')
INSERT INTO multiple_partition VALUES (101,'Description')
INSERT INTO multiple_partition VALUES (102,'Description')
INSERT INTO multiple_partition VALUES (103,'Description')
INSERT INTO multiple_partition VALUES (104,'Description')
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
--Verify index partitions
SELECT partition_id, index_id FROM sys.partitions pt
WHERE pt.[object_id] = OBJECT_ID('multiple_partition')
Der Code in Abbildung 9 erstellt einen nicht ausgerichteten, nicht gruppierten Index in einer partitionierten Tabelle. Dieser nicht gruppierte Index verwendet andere Spalten für seinen Indexschlüssel, der in sortierten Verknüpfungen mit anderen partitionierten Tabellen verwendet werden kann.
Figure 9 Nicht ausgerichteter, nicht gruppierter Index in einer partitionierten Tabelle
--Prepare database
IF OBJECT_ID('multiple_partition') IS NOT NULL
DROP TABLE multiple_partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Primary_Left_Scheme')
DROP PARTITION SCHEME Primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Index_primary_Left_Scheme')
DROP PARTITION SCHEME Index_primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Left_Partition')
DROP PARTITION FUNCTION Left_Partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Index_Left_Partition')
DROP PARTITION FUNCTION Index_Left_Partition
GO
--Create partitioned index function
CREATE PARTITION FUNCTION Index_Left_Partition (int) AS RANGE LEFT
FOR VALUES (10,50,100)
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all index partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Index_primary_Left_Scheme
AS PARTITION Index_Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE multiple_partition
(
col1 INT
,col2 INT
) ON Primary_Left_Scheme (col1)
--Create non-aligned partitioned nonclustered index
CREATE NONCLUSTERED INDEX cl_multiple_partition ON multiple_
partition(col2)
ON Index_primary_Left_Scheme (col2)
INSERT INTO multiple_partition VALUES (1,10)
INSERT INTO multiple_partition VALUES (2,10)
INSERT INTO multiple_partition VALUES (3,10)
INSERT INTO multiple_partition VALUES (4,10)
INSERT INTO multiple_partition VALUES (10,50)
INSERT INTO multiple_partition VALUES (11,50)
INSERT INTO multiple_partition VALUES (12,50)
INSERT INTO multiple_partition VALUES (13,50)
INSERT INTO multiple_partition VALUES (14,50)
INSERT INTO multiple_partition VALUES (100,100)
INSERT INTO multiple_partition VALUES (101,100)
INSERT INTO multiple_partition VALUES (102,100)
INSERT INTO multiple_partition VALUES (103,100)
INSERT INTO multiple_partition VALUES (104,100)
--Verify row count on partitioned data
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
AND p.index_id = 0
--Verify row count on partitioned index
--Row counts will not match those found in the data partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
AND p.index_id <> 0
Verwalten partitionierter Indexe. In der Vergangenheit dauerte die Indexwartung bei großen Tabellen mit Millionen oder sogar Milliarden von Datenzeilen oft länger als den Datenbankadministratoren an Zeit zur Verfügung stand. Diese Wartung wurde oft nicht durchgeführt, da die Daten während der Erstellung des neuen Index gesperrt waren. Mit SQL Server 2005 kann der Datenbankadministrator die Indexwartung online durchführen, ohne die zugrunde liegende Tabelle über längere Zeit zu sperren. Doch selbst dieser Ansatz, bei dem Sie die Indexwartung durchführen, während Benutzer auf die Daten zugreifen, kann Ihr System aufgrund der Ressourcennutzung verlangsamen. Ein besserer Ansatz ist die Partitionierung von Indexen in kleinere Segmente und die anschließende Durchführung der Indexwartung für diese kleineren Partitionen. Um beispielsweise eine Indexwartung bei einer Indexpartition durchzuführen, könnten Sie einfach den nachstehenden Codeausschnitt an das Ende des in Abbildung 8 dargestellten Codes anfügen.
ALTER INDEX cl_multiple_partition
ON multiple_partition
REBUILD Partition = 2
Beachten Sie, dass die Indexwartung bei einzelnen Indexpartitionen offline durchgeführt werden muss und zu einer Sperrung der Tabelle während der Indexwartung führen kann. Um dies zu verhindern, können Sie die Einzelpartition in eine separate Partition verschieben, die Indexwartung durchführen und die Partition dann wieder in die Haupttabelle verschieben. Dieser Prozess verursacht einige Leistungsprobleme, wenn die Partition wieder in die Tabelle verschoben und der gruppierte Index aktualisiert wird, doch dies ist weniger problematisch als das Sperren der ganzen Tabelle. Zudem sind weniger Systemressourcen erforderlich.
Zusammenfassung
Wie Sie sehen, bietet die SQL Server 2005-Tabellenpartitionierung deutlich erhöhte Flexibilität beim Speichern und Warten von Daten in großen Tabellen, ohne den Anwendungscode oder SQL Server-Prozesse neu ausarbeiten zu müssen. Mit diesen Möglichkeiten erweist sich SQL Server als geeignete Plattform für wichtige Unternehmensdatenbanken.
Noah Gomez ist Senior SQL Server Development DBA für Verizon und hat sich auf VLDBs (Very Large Databases – sehr große Datenbanken) und große Anwendungen spezialisiert. Er ist Mitglied der Professional Association for SQL Server (PASS) und war Mitglied des DBA-Teams von Verizon, das an VLDBs mit mehreren Terabyte gearbeitet hat, die 2003 Top Ten Grand Prize-Auszeichnungen von Winter Corp. erhielten.
© 2008 Microsoft Corporation und CMP Media, LLC. Alle Rechte vorbehalten. Die nicht genehmigte teilweise oder vollständige Vervielfältigung ist nicht zulässig.