SQL Server
Simplifiez la maintenance des bases de données avec les partitions de tables
Noah Gomez
Vue d'ensemble:
- Création de tables partitionnées
- Ajout et fusion de partitions
- Création et gestion d'index partitionnés
Télécharger le code de cet article: GomezPartitioning2007_03.exe (156KB)
Par le passé, les administrateurs de bases de données gérant des tables contenant des millions de lignes de données étaient forcés de créer plusieurs tables. Une fois ces tables partitionnées, l'administrateur devait relier
ces tables pendant l'exécution d'un grand nombre de leurs requêtes. La liaison des partitions impliquait la création d'une vue partitionnée ou d'une procédure de wrapper stockée qui découvrait l'emplacement des données et exécutait une autre procédure stockée pour atteindre uniquement les partitions nécessaires pour récupérer l'ensemble de données.
Ces méthodes fonctionnaient, mais elles étaient laborieuses. L'administration de plusieurs tables et de leurs index, de même que les méthodes utilisées pour relier les tables, provoquait souvent des problèmes d'administration et de maintenance. Par ailleurs, la création de tables multiples pour partitionner les données entraînait un certain degré de rigidité, car les procédures stockées, les tâches de maintenance, les tâches DTS (Data Transformation Services), le code d'application et d'autres processus devaient comprendre la nature du partitionnement. Donc, pour vous permettre d'ajouter ou de supprimer ces quasi-partitions sans modifier votre code, ces éléments étaient généralement créés d'une manière non-dynamique et étaient par conséquent inefficaces.
Les éditions Enterprise et Developer de SQL Server™ 2005 vous permettent de partitionner de grandes quantités de données contenues dans une seule table en plusieurs petites partitions pouvant être gérées plus efficacement. La possibilité de créer des segments de données consultés à partir d'un point d'entrée unique réduit un grand nombre des problèmes d'administration rencontrés avec les vieilles méthodes. L'utilisation d'un point d'entrée unique (nom de la table ou nom de l'index) masque les segments de données multiples pour le code d'application et permet à l'administrateur ou au développeur de modifier les partitions autant que nécessaire sans avoir à ajuster la base de code.
En bref, vous pouvez créer des partitions multiples, déplacer ces partitions, supprimer de vieilles partitions et même changer la façon dont les données sont partitionnées sans jamais avoir à ajuster le code dans votre application. Votre code d'application continue simplement à appeler le même nom de table ou d'index de base. Pendant ce temps, vous pouvez réduire la quantité des données contenues dans les index individuels, réduisant ainsi les temps de maintenance pour ces index, et vous pouvez augmenter la vitesse de chargement des données en les chargeant dans des partitions vides.
Techniquement, chaque table SQL Server 2005 est partitionnée ; chaque table contient au moins une partition. SQL Server 2005 permet aux administrateurs de bases de données de créer des partitions supplémentaires sur chaque table. Les partitions de tables et d'index sont des partitions au niveau des lignes (le partitionnement par colonne n'est pas autorisé) qui autorisent un point d'entrée unique (nom de table ou nom d'index) sans que le code d'application ait besoin de connaître le nombre de partitions derrière le point d'entrée. Les partitions peuvent exister sur la table de base, de même que les index associés à la table.
Création de tables partitionnées
Vous utilisez des fonctions de partition et des modèles de partition pour créer une table capable de croître au delà de la partition unique par défaut. Ces objets vous permettent de diviser les données dans des segments spécifiques et de contrôler l'emplacement de ces segments de données dans votre conception de stockage. Vous pouvez, par exemple, répartir les données sur plusieurs baies de lecteurs en fonction de l'âge des données ou en utilisant d'autres facteurs de différenciation courants. Notez qu'une table peut être partitionnée à partir d'une colonne de la table et que chaque partition doit contenir des données qui ne peuvent pas être placées dans d'autres partitions.
Fonctions de partition Lors du partitionnement d'une table, la première décision concerne la façon dont vous souhaitez diviser les données dans des segments différents. Une fonction de partition est utilisée pour mettre en correspondance les lignes de données individuelles dans les différentes partitions. Ces lignes de données individuelles peuvent être mises en correspondance avec n'importe quel type de colonne, à l'exception des types suivants : texte, ntext, image, xml, timestamp, varchar(max), nvarchar(max), varbinary(max), types de données alias et types de données définis par l'utilisateur CLR (Common Language Runtime). Cependant, la fonction de partitionnement doit pouvoir placer une ligne de données dans une seule partition de table. Elle ne peut pas permettre à une ligne de données d'appartenir à plusieurs partitions en même temps.
Afin de partitionner une table, vous devez créer la colonne de partitionnement dans la table ciblée. Cette colonne de partitionnement peut exister dans le modèle de table lors de la création de la table, ou vous pouvez modifier la table et ajouter la colonne à une date ultérieure. La colonne peut accepter des valeurs NULL, mais toutes les lignes contenant des valeurs NULL seront placées par défaut dans la partition la plus à gauche de la table. Vous pouvez éviter ceci en spécifiant que les valeurs NULL doivent être placées dans la partition la plus à droite de la table lorsque vous créez la fonction de partitionnement. L'utilisation de la partition gauche ou droite sera une décision de conception importante lorsque vous modifierez votre modèle de partitionnement et que vous ajouterez de nouvelles partitions ou supprimerez des partitions existantes.
Lors de la création d'une fonction de partitionnement, vous pouvez choisir une fonction de partition GAUCHE ou DROITE. La différence entre les partitions GAUCHE et DROITE concerne l'endroit où seront placées les valeurs de limite dans le modèle de partitionnement. Les partitions GAUCHES (par défaut) incluent la valeur de limite dans la partition tandis que les partitions DROITES placent la valeur de limite dans la partition suivante.
Pour comprendre ce concept un peu mieux, examinons des partitions GAUCHES et DROITES simples :
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)
Dans la première fonction (Left_Partition), les valeurs 1, 10, et 100 sont placées dans les première, deuxième, et troisième partitions, respectivement. Dans la deuxième fonction (Right_Partition), les valeurs sont placées dans les deuxième, troisième et quatrième partitions.
Lorsque vous créez une table partitionnée, il est important de faire en sorte que les partitions soient aussi homogènes que possible. Ceci vous aidera à comprendre l'espace nécessaire pour une partition. L'usage des partitions GAUCHES et DROITES déterminera l'emplacement des données, avant de déterminer la taille de la partition et la taille des éventuels index créés sur cette partition.
Vous pouvez déterminer le nombre de partitions dans lesquelles une valeur de données sera placée en utilisant la fonction $PARTITION, comme illustré ici :
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Right_Partition (10)
Dans la première instruction SELECT, le résultat sera 2. Le résultat de la seconde instruction SELECT sera 3.
Modèles de partition Après avoir créé la fonction et décidé de quelle façon vos données seront divisées, vous devez décider où les partitions individuelles seront créées sur votre sous-système de disque. Vous utilisez des modèles de partition pour créer cette disposition de disque. Les modèles de partition gèrent le stockage sur disque des partitions individuelles en utilisant des groupes de fichiers pour placer chaque partition dans un emplacement sur le sous-système de disque. Vous pouvez configurer les modèles de partition pour que toutes les partitions soient placées dans un seul groupe de fichiers, que toutes les partitions soient placées dans des groupes de fichiers différents ou que plusieurs partitions partagent des groupes de fichiers. Cette dernière méthode offre à l'administrateur de bases de données une grande flexibilité dans la répartition des E/S de disque.
La figure 1 montre certaines des façons dont vous pouvez attribuer un groupe de fichiers ou plusieurs groupes de fichiers à un modèle de partition. Vous devez être conscient que les groupes de fichiers utilisés par votre modèle de partition doivent déjà exister dans la base de données avant que vous ne créiez votre modèle de partitionnement.
Figure 1 Attribution de groupes de fichiers à un modèle de partition
--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
Si vous créez les exemples de fonctions de partition comme illustré à la figure 1 et que vous utilisez le modèle de partition pour créer une table, vous pouvez déterminer ensuite où les lignes de données individuelles sont placées dans les tables que vous venez de partitionner. Vous pouvez alors afficher la distribution de ces lignes de données après leur insertion dans votre table partitionnée. Le code qui vous permet de faire tout ceci ressemblera à celui de la figure 2.
Figure 2 Placement de lignes de données et affichage de la distribution
--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')
Modification de tables partitionnées
Même si vous planifiez minutieusement, vous devrez parfois ajuster vos tables partitionnées après leur création et leur remplissage. Votre modèle de partition peut fonctionner comme prévu, mais vous pouvez, par exemple, avoir besoin d'ajouter de nouvelles partitions lorsque de nouvelles données sont accumulées, à moins que vous n'ayez besoin de supprimer de grandes quantités de données partitionnées à un moment donné. Heureusement, les tables partitionnées et les structures de partitionnement sous-jacentes permettent d'effectuer des modifications après l'entrée en service de la table et l'insertion des données.
Ajout de partitions De nombreux projets de partitionnement incluent la possibilité d'ajouter une nouvelle partition à une date ultérieure. Ce point dans le temps peut être une date particulière ou peut dépendre d'une valeur dans une colonne d'identité incrémentielle. Cependant, si vous n'avez pas prévu cette éventualité, vous pouvez toujours intervenir à une date ultérieure et ajouter de nouvelles partitions à une table partitionnée. Examinez la table créée à la figure 2. Vous pouvez ajouter une nouvelle partition à cette table pour contenir des valeurs supérieures à 500, comme ceci :
--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
La capacité d'ajouter des partitions offre une grande flexibilité. La figure 3 montre comment ajouter une partition du côté gauche de la fonction. Dans ce cas, vous devez indiquer au modèle de partitionnement à quel endroit mettre la nouvelle partition puisque vous avez épuisé tous les groupes de fichiers créés au moment de la création du modèle de partitionnement. Bien que vous utilisiez le groupe de fichier PRIMARY pour toutes vos partitions, vous devez quand même dire au modèle de partitionnement de réutiliser le groupe de fichiers PRIMARY pour la nouvelle partition.
Figure 3 Ajout d'une partition du côté gauche de la fonction
--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
Fusionner deux partitions SQL Server vous permet de supprimer les partitions d'une table tout en conservant les données. Cette fonctionnalité peut servir pour fusionner des données actives plus anciennes dans des données archivées ou pour réduire le nombre de partitions que vous avez, ce qui facilitera l'administration de la table partitionnée. Vous pouvez également utiliser cette fonctionnalité pour fusionner des partitions, en déplaçant les données d'un groupe de fichiers à un autre pour libérer de l'espace disque sur certains groupes de lecteurs. Le code de la figure 4 montre comment déplacer des données d'une partition vers une autre partition sur le même groupe de fichiers.
Figure 4 Déplacement de données d'une partition vers une autre
--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)
Transférer une table à partition unique vers une table partitionnée Pendant les routines de chargement, de grandes quantités de données doivent souvent être chargées dans la base de données, puis modifiées ou agrégées avant leur déplacement vers la table de données. Le partitionnement SQL Server 2005 vous permet de transférer une table à partition unique vers une table à plusieurs partitions. Cela signifie que vous pouvez charger des données dans une seule table de chargement, modifier ces données, puis transférer la table entière vers une table existante sans avoir à déplacer chaque ligne de données individuelle. Cette couche de partitionnement n'implique pas la modification des structures de partitionnement sous-jacentes. Elle implique la modification de la table partitionnée. Le code de la figure 5 vous indique comment y parvenir.
Figure 5 Déplacement d'une table entière vers une table existante
--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’))
Déplacer une partition d'une table à une autre Le déplacement de données plus anciennes vers des tables d'archives séparées est une tâche administrative courante. Le processus d'archivage implique généralement une série d'instructions qui peuvent créer une utilisation de ressources supplémentaires dans vos journaux de transactions. Le transfert de propriété d'une partition d'une table à une autre, cependant, est un moyen facile d'archiver de grandes quantités de données sans les surcharges de journaux de transactions. Cette fonctionnalité permet à l'administrateur de base de données de déplacer des segments de données plus anciennes de leurs tables actives vers des tables archivées. Mais puisque les données ne sont en fait pas déplacées, le temps nécessaire peut être bien inférieur à celui du déplacement de lignes de données individuelles. La figure 6 vous indique comment y parvenir.
Figure 6 Déplacement de données plus anciennes vers des tables archivées
--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'))
Utiliser une seule partition pour créer une nouvelle table Vous pouvez déplacer une seule partition d'une table partitionnée existante vers une table non partitionnée vide. Ce faisant, un administrateur de base de données peut exécuter une maintenance d'index sur la partition unique ou supprimer aisément de grandes quantités de données sans que ce processus de suppression ne soit enregistré. L'exemple de la figure 7 montre comment déplacer une partition dans une table vide, puis utiliser cette nouvelle table pour supprimer les données.
Figure 7 Déplacement et suppression de données
--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'))
Index partitionnés
La possibilité de partitionner les données d'une table s'accompagne de la possibilité de créer des index partitionnés. Ceci permet à l'administrateur de base de données de concevoir la structure d'index en fonction des données divisées plutôt que des données de l'ensemble de la table. La création d'index partitionnés donne des arborescences B individuelles sur les index partitionnés. La division des index a pour effet de créer des index plus petits qui sont plus faciles à gérer pour le moteur de stockage pendant la modification, l'ajout et la suppression de données. Ces index plus petits peuvent également être gérés de façon individuelle par l'administrateur de la base de données, ce qui permet une meilleure gestion des index sur de grands ensembles de données.
Création d'index partitionnés. Lorsque vous créez des index partitionnés, vous pouvez créer soit des index alignés soit des index non alignés. Pour les index alignés, vous créez l'index avec une relation directe avec les données partitionnées. (Pour les index non alignés, vous choisissez un modèle de partitionnement différent).
La méthode des index alignés est préférable et sera adoptée automatiquement si vous créez la table de partition avant de créer les index sans spécifier un modèle de partitionnement différent. L'utilisation d'index alignés vous offre la flexibilité de créer des partitions supplémentaires sur la table et la possibilité de faire passer la propriété d'une partition vers une autre table. Ces fonctionnalités sont souvent la raison pour laquelle les administrateurs de bases de données créent des tables partitionnées et le simple fait d'utiliser le modèle de partitionnement de la table pour vos index vous permettra sans doute d'atteindre vos objectifs de partitionnement.
Vous pouvez créer des index par rapport à des tables avec données d'index non alignées sur les données de la table. Si les données se trouvent dans une table partitionnée, ceci vous permettra de joindre les données de différentes façons (les données partitionnées peuvent être efficacement jointes à d'autres données partitionnées par l'optimiseur de requête). Vous pouvez aussi faire cela avec une table non partitionnée, ce qui vous permettra de créer un index partitionné (par rapport à la table de partition unique) et facilitera la gestion de l'index.
Le code de la figure 8 créera un index partitionné, non mis en cluster sur une table partitionnée. L'index non mis en cluster sera aligné sur la table et utilisera la colonne de partitionnement de la table en tant que clé d'index non mis en cluster.
Figure 8 Index partitionné, non mis en cluster sur table partitionnée
--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')
Le code de la figure 9 créera un index non aligné, non mis en cluster sur une table partitionnée. Cet index non mis en cluster utilisera des colonnes différentes pour sa clé d'index, lesquelles peuvent être utilisées dans des jointures assemblées par rapport à d'autres tables partitionnées.
Figure 9 Index non aligné, non mis en cluster sur table partitionnée
--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
Gestion d'index partitionnés Par le passé, l'exécution de la maintenance des index par rapport à de grandes tables contenant des millions voire des milliards de lignes de données prenait souvent beaucoup trop de temps pour les administrateurs de bases de données. Cette maintenance était souvent omise en raison du verrouillage des données pendant la reconstruction de l'index. Avec SQL Server 2005, l'administrateur de base de données peut réaliser la maintenance des index en ligne sans verrouiller la table sous-jacente pendant une période prolongée. Mais même cette approche (qui vous oblige à réaliser la maintenance des index pendant que les utilisateurs accèdent aux données) peut ralentir votre système en raison de l'utilisation des ressources. Une meilleure approche consiste à partitionner les index en segments plus petits avant de réaliser la maintenance des index par rapport à ces partitions plus petites. Par exemple, pour réaliser une maintenance des index par rapport à une partition d'index, vous pourriez simplement ajouter l'extrait de code ci-dessous à la fin du code illustré à la figure 8.
ALTER INDEX cl_multiple_partition
ON multiple_partition
REBUILD Partition = 2
Notez que la maintenance des index par rapport à des partitions d'index uniques doit être réalisée hors ligne et peut entraîner le verrouillage de la table pendant la maintenance des index. Pour empêcher ceci, vous pouvez transférer la partition unique vers une partition séparée, réaliser la maintenance des index, puis ramener la partition dans la table principale. Ce processus causera des problèmes de performances aux moments où la partition sera replacée dans la table et l'index en cluster mis à jour, mais ceci est moins problématique que le verrouillage de l'ensemble de la table et nécessite moins de ressources système.
Résumé
Comme vous pouvez le constater, le partitionnement de tables SQL Server 2005 offre une flexibilité considérablement améliorée pour le stockage et la maintenance des données dans de grandes tables, sans avoir à retravailler le code d'application ou les processus SQL Server. Grâce à ces capacités, SQL Server s'affirme comme une plate-forme robuste pour les bases de données stratégiques d'entreprise.
Noah Gomez est administrateur de base de donnés de développement SQL Server senior pour Verizon, société spécialisée dans les bases de données VLDB et les applications à grande échelle. Il est membre de la Professional Association for SQL Server (PASS) et faisait partie de l'équipe d'administrateurs de bases de données Verizon qui s'est consacrée aux bases de données VLDB multi-téraoctets qui ont remporté le grand prix Winter Corp. Top Ten en 2003.
© 2008 Microsoft Corporation et CMP Media, LLC. Tous droits réservés. Toute reproduction, totale ou partielle, est interdite sans autorisation préalable.