Partitionnement de tables dans le pool SQL dédié
Recommandations et exemples relatifs à l’utilisation de partitions de tables dans un pool SQL dédié.
Qu’est-ce que les partitions de table ?
Les partitions de table permettent de diviser vos données en groupes de données plus petits. Dans la plupart des cas, les partitions de table sont créées sur une colonne de dates. Le partitionnement est pris en charge sur tous les types de tables de pools SQL dédié, notamment un columnstore en cluster, un index cluster et un segment de mémoire. Le partitionnement est également pris en charge sur tous les types de distribution, notamment le hachage ou le tourniquet (round robin) distribué.
Le partitionnement peut bénéficier de la maintenance des données et des performances des requêtes. Le fait qu’il bénéficie à ces deux éléments ou à un seul dépend de la façon dont les données sont chargées et du fait que la même colonne puisse être utilisée ou non dans les deux cas, étant donné que le partitionnement peut uniquement être effectué sur une colonne.
Avantages pour les charges
Le principal avantage du partitionnement dans un pool SQL dédié est d’améliorer l’efficacité et les performances du chargement de données en utilisant la suppression, le basculement et la fusion de partitions. Dans la plupart des cas, les données sont partitionnées sur une colonne de dates étroitement liée à l’ordre selon lequel les données sont chargées dans le pool SQL. L’utilisation de partitions à des fins de maintenance des données présente l’avantage d’éviter la journalisation des transactions. Bien que l’insertion, la mise à jour ou la suppression de données constituent l’approche la plus simple, avec un peu de réflexion et d’efforts, l’utilisation du partitionnement pendant votre processus de chargement peut considérablement améliorer les performances.
Le basculement de partitions peut servir à supprimer ou à remplacer rapidement une section d’une table. Par exemple, une table de faits des ventes peut contenir seulement des données pour les 36 derniers mois. À la fin de chaque mois, le mois de données de ventes le plus ancien est supprimé de la table. Ces données ont pu être supprimées à l’aide d’une instruction delete pour supprimer les données pour le mois plus ancien.
Toutefois, la suppression d’une grande quantité de données ligne par ligne avec une instruction DELETE peut prendre beaucoup de temps, ainsi que créer le risque de transactions volumineuses qui pourraient nécessiter un temps de restauration considérable en cas de problème. Une approche plus optimale consiste à supprimer la partition de données la plus ancienne. Alors que la suppression des lignes individuelle peut prendre des heures, la suppression d’une partition complète ne prend que secondes.
Avantages pour les requêtes
Le partitionnement peut également servir à améliorer les performances des requêtes. Une requête qui applique un filtre de données partitionnées peut limiter l’analyse aux seules partitions éligibles. Cette méthode de filtrage peut éviter l’analyse de table complète et n’analyser qu’un sous-ensemble de données moins conséquent. Avec l’introduction des index columnstore en cluster, les avantages des performances de l’élimination de prédicats sont moins notables, mais dans certains cas, il peut exister un avantage pour les requêtes.
Par exemple, si la table de faits Sales (Ventes) est partagée en 36 mois grâce au champ de date des ventes, les requêtes filtrées par date de vente peuvent ignorer la recherche dans les partitions qui ne correspondent pas au filtre.
Dimensionnement des partitions
Tandis que le partitionnement peut être utilisé pour améliorer les performances de certains scénarios, la création d’une table avec trop de partitions peut nuire aux performances dans certaines circonstances. Ces inquiétudes sont particulièrement avérées pour les tables columnstore en cluster.
Pour que le partitionnement soit utile, il est important de savoir quand utiliser le partitionnement et le nombre de partitions à créer. Il n’existe aucune règle absolue concernant le nombre de partitions ; cela dépend de vos données et du nombre de partitions que vous chargez simultanément. En règle générale, un schéma de partition réussi n’a qu’entre dix et cent partitions, pas mille.
Lorsque vous créez des partitions sur des tables columnstore en cluster, il est important de prendre en compte le nombre de lignes dans chaque partition. Pour une compression et des performances des tables columnstore en cluster optimales, un minimum de 1 million de lignes par partition et par distribution est nécessaire. Avant la création des partitions, le pool SQL dédié divise déjà chaque table en 60 distributions.
Tout partitionnement ajouté à une table est en plus des distributions créées en arrière-plan. Dans cet exemple, si la table de faits de ventes contient 36 partitions mensuelles, et étant donné qu’un pool SQL dédié comporte 60 distributions, la table de faits de ventes doit contenir 60 millions de lignes par mois, ou 2,1 milliards de lignes lorsque tous les mois sont remplis. Si une table contient moins de lignes que le nombre minimum recommandé par partition, envisagez d’utiliser moins de partitions pour augmenter le nombre de lignes par partition.
Pour plus d’informations, consultez l’article Indexation qui comporte des requêtes pouvant accéder à la qualité des index columnstore en cluster.
Différences de syntaxe par rapport à SQL Server
Le pool SQL dédié présente un mode de définition des partitions qui est plus simple que SQL Server. Les fonctions et les schémas de partitionnement ne sont pas utilisés dans le pool SQL dédié, car ils se trouvent dans SQL Server. À la place, il vous suffit d’identifier la colonne partitionnée et les points de limite.
Bien que la syntaxe de partitionnement puisse être légèrement différente de SQL Server, les concepts de base sont les mêmes. SQL Server et le pool SQL dédié prennent en charge une colonne de partition par table, qui peut être une partition par spécification de plages de valeurs. Pour en savoir plus sur le partitionnement, consultez Tables et index partitionnés.
L’exemple suivant utilise l’instruction CREATE TABLE pour partitionner la table FactInternetSales sur la colonne OrderDateKey :
CREATE TABLE [dbo].[FactInternetSales]
(
[ProductKey] int NOT NULL
, [OrderDateKey] int NOT NULL
, [CustomerKey] int NOT NULL
, [PromotionKey] int NOT NULL
, [SalesOrderNumber] nvarchar(20) NOT NULL
, [OrderQuantity] smallint NOT NULL
, [UnitPrice] money NOT NULL
, [SalesAmount] money NOT NULL
)
WITH
( CLUSTERED COLUMNSTORE INDEX
, DISTRIBUTION = HASH([ProductKey])
, PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES
(20000101,20010101,20020101
,20030101,20040101,20050101
)
)
);
Migrer des partitions à partir de SQL Server
Pour migrer les définitions de partitions SQL Server vers le pool SQL dédié, il vous suffit d’effectuer les opérations suivantes :
- Éliminez le schéma de partition SQL Server.
- Ajoutez la définition de la fonction de partition à votre instruction CREATE TABLE.
Si vous migrez une table partitionnée à partir d’une instance SQL Server, le SQL ci-dessous peut vous aider à déterminer le nombre de lignes se trouvant dans chaque partition. N’oubliez pas que si la même granularité de partitionnement est utilisée sur le pool SQL dédié, le nombre de lignes par partition diminue d’un facteur de 60.
-- Partition information for a SQL Server Database
SELECT s.[name] AS [schema_name]
, t.[name] AS [table_name]
, i.[name] AS [index_name]
, p.[partition_number] AS [partition_number]
, SUM(a.[used_pages]*8.0) AS [partition_size_kb]
, SUM(a.[used_pages]*8.0)/1024 AS [partition_size_mb]
, SUM(a.[used_pages]*8.0)/1048576 AS [partition_size_gb]
, p.[rows] AS [partition_row_count]
, rv.[value] AS [partition_boundary_value]
, p.[data_compression_desc] AS [partition_compression_desc]
FROM sys.schemas s
JOIN sys.tables t ON t.[schema_id] = s.[schema_id]
JOIN sys.partitions p ON p.[object_id] = t.[object_id]
JOIN sys.allocation_units a ON a.[container_id] = p.[partition_id]
JOIN sys.indexes i ON i.[object_id] = p.[object_id]
AND i.[index_id] = p.[index_id]
JOIN sys.data_spaces ds ON ds.[data_space_id] = i.[data_space_id]
LEFT JOIN sys.partition_schemes ps ON ps.[data_space_id] = ds.[data_space_id]
LEFT JOIN sys.partition_functions pf ON pf.[function_id] = ps.[function_id]
LEFT JOIN sys.partition_range_values rv ON rv.[function_id] = pf.[function_id]
AND rv.[boundary_id] = p.[partition_number]
WHERE p.[index_id] <=1
GROUP BY s.[name]
, t.[name]
, i.[name]
, p.[partition_number]
, p.[rows]
, rv.[value]
, p.[data_compression_desc];
Basculement de partitions
Le pool SQL dédié prend en charge le fractionnement, la fusion et le basculement de partition. Chacune de ces fonctions est exécutée à l’aide de l’instruction ALTER TABLE.
Pour faire basculer une partition d’une table à une autre, vous devez vous assurer que les partitions s’alignent sur leurs limites respectives et que les définitions de tables correspondent. Comme aucune contrainte de validation n’est disponible pour appliquer la plage de valeurs dans une table, la table source doit contenir les mêmes limites de partition que la table cible. Si les limites de partition sont différentes, le basculement de la partition échoue, car les métadonnées de celle-ci ne sont pas synchronisées.
Un fractionnement de partition nécessite que la partition respective (pas nécessairement la table entière) soit vide si la table a un index columnstore en cluster (CCI). D’autres partitions de la même table peuvent contenir des données. Une partition qui contient des données ne peut pas être fractionnée. elle génère une erreur : ALTER PARTITION statement failed because the partition is not empty. Only empty partitions can be split in when a columnstore index exists on the table. Consider disabling the columnstore index before issuing the ALTER PARTITION statement, then rebuilding the columnstore index after ALTER PARTITION is complete. Comme solution de contournement permettant de fractionner une partition contenant des données, consultez Fractionnement d’une partition contenant des données.
Fractionnement d’une partition contenant des données
Pour fractionner une partition qui contient déjà des données, la méthode la plus efficace consiste à utiliser une instruction CTAS . Si la table partitionnée est une table columnstore en cluster, la partition de table doit être vide pour pouvoir être fractionnée.
L’exemple suivant crée une table columnstore partitionnée. Il insère une ligne dans chaque partition :
CREATE TABLE [dbo].[FactInternetSales]
(
[ProductKey] int NOT NULL
, [OrderDateKey] int NOT NULL
, [CustomerKey] int NOT NULL
, [PromotionKey] int NOT NULL
, [SalesOrderNumber] nvarchar(20) NOT NULL
, [OrderQuantity] smallint NOT NULL
, [UnitPrice] money NOT NULL
, [SalesAmount] money NOT NULL
)
WITH
( CLUSTERED COLUMNSTORE INDEX
, DISTRIBUTION = HASH([ProductKey])
, PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES
(20000101
)
)
);
INSERT INTO dbo.FactInternetSales
VALUES (1,19990101,1,1,1,1,1,1);
INSERT INTO dbo.FactInternetSales
VALUES (1,20000101,1,1,1,1,1,1);
La requête suivante recherche le nombre de lignes à l’aide de la vue catalogue sys.partitions :
SELECT QUOTENAME(s.[name])+'.'+QUOTENAME(t.[name]) as Table_name
, i.[name] as Index_name
, p.partition_number as Partition_nmbr
, p.[rows] as Row_count
, p.[data_compression_desc] as Data_Compression_desc
FROM sys.partitions p
JOIN sys.tables t ON p.[object_id] = t.[object_id]
JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]
JOIN sys.indexes i ON p.[object_id] = i.[object_Id]
AND p.[index_Id] = i.[index_Id]
WHERE t.[name] = 'FactInternetSales';
La commande de fractionnement suivante génère un message d’erreur :
ALTER TABLE FactInternetSales SPLIT RANGE (20010101);
Msg 35346, Level 15, State 1, Line 44
SPLIT clause of ALTER PARTITION statement failed because the partition is not empty. Only empty partitions can be split in when a columnstore index exists on the table. Consider disabling the columnstore index before issuing the ALTER PARTITION statement, then rebuilding the columnstore index after ALTER PARTITION is complete.
Cependant, vous pouvez utiliser la commande CTAS pour créer une table afin de stocker les données.
CREATE TABLE dbo.FactInternetSales_20000101
WITH ( DISTRIBUTION = HASH(ProductKey)
, CLUSTERED COLUMNSTORE INDEX
, PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES
(20000101
)
)
)
AS
SELECT *
FROM FactInternetSales
WHERE 1=2;
Comme les limites de la partition sont alignées, le basculement est autorisé. La table source présente donc une partition vide, que vous pouvez par la suite fractionner.
ALTER TABLE FactInternetSales SWITCH PARTITION 2 TO FactInternetSales_20000101 PARTITION 2;
ALTER TABLE FactInternetSales SPLIT RANGE (20010101);
Il ne reste plus qu’à aligner les données en fonction des limites de la nouvelle partition, via la commande CTAS, et à faire basculer les données une nouvelle fois vers la table principale.
CREATE TABLE [dbo].[FactInternetSales_20000101_20010101]
WITH ( DISTRIBUTION = HASH([ProductKey])
, CLUSTERED COLUMNSTORE INDEX
, PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES
(20000101,20010101
)
)
)
AS
SELECT *
FROM [dbo].[FactInternetSales_20000101]
WHERE [OrderDateKey] >= 20000101
AND [OrderDateKey] < 20010101;
ALTER TABLE dbo.FactInternetSales_20000101_20010101 SWITCH PARTITION 2 TO dbo.FactInternetSales PARTITION 2;
Une fois le déplacement des données effectué, il est judicieux d’actualiser les statistiques sur la table cible. La mise à jour des statistiques permet de vérifier qu’elles reflètent de manière précise la nouvelle distribution des données sur leurs partitions respectives.
UPDATE STATISTICS [dbo].[FactInternetSales];
Enfin, dans le cas d’un commutateur de partition unique pour déplacer des données, vous pouvez supprimer les tables créées pour le commutateur de partition, FactInternetSales_20000101_20010101 et FactInternetSales_20000101. Vous pouvez également conserver des tables vides pour les commutateurs de partition réguliers et automatisés.
Charger de nouvelles données dans les partitions qui contiennent des données en une seule étape
Le chargement des données dans des partitions avec basculement de partition est un moyen pratique d’organiser de nouvelles données dans une table qui n’est pas visible aux utilisateurs. Sur les systèmes occupés, il peut être difficile de traiter la contention de verrouillage associée au basculement de partition.
Pour effacer les données existantes dans une partition, le paramètre ALTER TABLE était nécessaire pour extraire les données. Un autre paramètre ALTER TABLE était ensuite requis pour insérer les nouvelles données.
Dans le pool SQL dédié, l’option TRUNCATE_TARGET est prise en charge dans la commande ALTER TABLE. Avec TRUNCATE_TARGET, la commande ALTER TABLE remplace les données existantes de la partition par de nouvelles données. Voici un exemple qui illustre l’utilisation de CTAS pour créer une table avec les données existantes, l’insertion de nouvelles données, puis le basculement de toutes les données dans la table cible remplaçant ainsi les données existantes.
CREATE TABLE [dbo].[FactInternetSales_NewSales]
WITH ( DISTRIBUTION = HASH([ProductKey])
, CLUSTERED COLUMNSTORE INDEX
, PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES
(20000101,20010101
)
)
)
AS
SELECT *
FROM [dbo].[FactInternetSales]
WHERE [OrderDateKey] >= 20000101
AND [OrderDateKey] < 20010101
;
INSERT INTO dbo.FactInternetSales_NewSales
VALUES (1,20000101,2,2,2,2,2,2);
ALTER TABLE dbo.FactInternetSales_NewSales SWITCH PARTITION 2 TO dbo.FactInternetSales PARTITION 2 WITH (TRUNCATE_TARGET = ON);
Contrôle de code source dans le cadre du partitionnement d’une table
Notes
Si votre outil de contrôle de code source n’est pas configuré pour ignorer les schémas de partition, la modification du schéma d’une table en vue de mettre à jour les partitions peut entraîner la suppression et la recréation d’une table dans le cadre du déploiement, ce qui peut être irréalisable. Une solution personnalisée permettant d’implémenter une telle modification, comme décrit ci-dessous, peut s’avérer nécessaire. Vérifiez que votre outil d’intégration continue et livraison continue (CI/CD) le permet. Dans les Paramètres de publication avancés de SQL Server Data Tools (SSDT), recherchez « Ignorer les schémas de partition » pour éviter l’exécution d’un script généré qui entraîne la suppression d’une table et la recréation de celle-ci.
Cet exemple est utile lors de la mise à jour des schémas de partition d’une table vide. Pour déployer en continu des modifications de partition sur une table avec des données, suivez les étapes décrites dans Fractionnement d’une partition contenant des données avec le déploiement pour déplacer temporairement des données de chaque partition avant d’appliquer la plage de fractionnement de partition. Cela est nécessaire, car l’outil CI/CD ne tient pas compte des partitions qui contiennent des données.
Pour éviter la détérioration de la définition de votre table dans le système de contrôle du code source, vous pouvez envisager l’approche suivante :
Créez la table en tant que table partitionnée, mais sans ajouter de valeurs de partition.
CREATE TABLE [dbo].[FactInternetSales] ( [ProductKey] int NOT NULL , [OrderDateKey] int NOT NULL , [CustomerKey] int NOT NULL , [PromotionKey] int NOT NULL , [SalesOrderNumber] nvarchar(20) NOT NULL , [OrderQuantity] smallint NOT NULL , [UnitPrice] money NOT NULL , [SalesAmount] money NOT NULL ) WITH ( CLUSTERED COLUMNSTORE INDEX , DISTRIBUTION = HASH([ProductKey]) , PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES () ) );SPLITlors du processus de déploiement :-- Create a table containing the partition boundaries CREATE TABLE #partitions WITH ( LOCATION = USER_DB , DISTRIBUTION = HASH(ptn_no) ) AS SELECT ptn_no , ROW_NUMBER() OVER (ORDER BY (ptn_no)) as seq_no FROM ( SELECT CAST(20000101 AS INT) ptn_no UNION ALL SELECT CAST(20010101 AS INT) UNION ALL SELECT CAST(20020101 AS INT) UNION ALL SELECT CAST(20030101 AS INT) UNION ALL SELECT CAST(20040101 AS INT) ) a; -- Iterate over the partition boundaries and split the table DECLARE @c INT = (SELECT COUNT(*) FROM #partitions) , @i INT = 1 --iterator for while loop , @q NVARCHAR(4000) --query , @p NVARCHAR(20) = N'' --partition_number , @s NVARCHAR(128) = N'dbo' --schema , @t NVARCHAR(128) = N'FactInternetSales' --table; WHILE @i <= @c BEGIN SET @p = (SELECT ptn_no FROM #partitions WHERE seq_no = @i); SET @q = (SELECT N'ALTER TABLE '+@s+N'.'+@t+N' SPLIT RANGE ('+@p+N');'); -- PRINT @q; EXECUTE sp_executesql @q; SET @i+=1; END -- Code clean-up DROP TABLE #partitions;
Avec cette approche, le code dans le contrôle de code source reste statique. Quant aux valeurs limites du partitionnement, elles peuvent rester dynamiques et évoluer avec le pool SQL au fil du temps.
Étapes suivantes
Pour plus d’informations sur le développement des tables, consultez les articles sur Vue d’ensemble de la table.