Tables et index partitionnés

  • Article

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance

SQL Server, Azure SQL Database et Azure SQL Managed Instance prennent en charge le partitionnement des tables et des index. Les données des tables et index partitionnés sont divisées en unités qui peuvent être réparties sur plusieurs groupes de fichiers d’une base de données ou stockées dans un même groupe de fichiers. Quand plusieurs fichiers existent dans un groupe de fichiers, les données sont réparties entre les fichiers à l’aide de l’algorithme de remplissage proportionnel. Les données sont partitionnées horizontalement, de sorte que les groupes de lignes sont mappés à des partitions individuelles. Toutes les partitions d'un index ou d'une table unique doivent résider dans la même base de données. La table ou l'index est traité en tant qu'entité logique unique lorsque des requêtes ou des mises à jour sont effectuées sur les données.

Avant SQL Server 2016 (13.x) SP1, les tables et index partitionnés n’étaient pas disponibles dans chaque édition de SQL Server. Pour obtenir la liste des fonctionnalités prises en charge par les éditions de SQL Server, consultez Éditions et fonctionnalités prises en charge de SQL Server 2022. Les tables et index partitionnés sont disponibles dans tous les niveaux de service d’Azure SQL Database et d’Azure SQL Managed Instance.

Le partitionnement de tables est également disponible dans les pools SQL dédiés dans Azure Synapse Analytics, avec certaines différences de syntaxe. Pour en savoir plus, consultez Partitionnement de tables dans un pool SQL dédié.

Important

Le moteur de base de données prend en charge jusqu’à 15 000 partitions par défaut. Dans les versions antérieures à SQL Server 2012 (11.x), le nombre de partitions était limité à 1 000 par défaut.

Avantages du partitionnement

Le partitionnement des tables ou des index peut offrir les avantages suivants en matière de gestion et de performances.

  • Vous pouvez créer des sous-ensembles de données et y accéder facilement et efficacement, tout en conservant l'intégrité d'une collection de données. Par exemple, une opération telle que le chargement des données d’un système OLTP vers un système OLAP ne prend que quelques secondes au lieu des minutes et des heures qu’elle exige lorsque les données ne sont pas partitionnées.

  • Vous pouvez effectuer des opérations de maintenance ou de conservation des données sur une ou plusieurs partitions plus rapidement. Les opérations sont plus efficaces car elles ne ciblent que ces sous-ensembles de données, au lieu de la totalité de la table. Par exemple, vous pouvez choisir de compresser les données dans une ou plusieurs partitions de reconstruire une ou plusieurs partitions d’un index ou de tronquer les données d’une partition unique. Vous pouvez également extraire des partitions individuelles d’une table et les envoyer dans une table d’archive.

  • Vous pouvez améliorer les performances des requêtes en fonction des types de requêtes que vous exécutez fréquemment. Par exemple, l’optimiseur de requête peut traiter des requêtes d’équi-jointure entre deux ou plusieurs tables partitionnées plus rapidement lorsque les colonnes de partitionnement sont identiques aux colonnes sur lesquelles les tables sont jointes. Consultez Requêtes ci-dessous pour plus d’informations.

Vous pouvez améliorer les performances en activant l’escalade de verrous au niveau de la partition plutôt qu’au niveau de la table entière. Cela peut réduire les conflits de verrouillage de la table. Pour réduire les contentions de verrou en autorisant l’escalade de verrous sur la partition, définissez l’option LOCK_ESCALATION de l’instruction ALTER TABLE avec la valeur AUTO.

Composants et concepts

Les termes suivants s'appliquent aux partitionnement de table et d'index.

Fonction de partition

Une fonction de partition est un objet de base de données qui définit comment les lignes d’une table ou d’un index sont mappées à un ensemble de partitions en fonction des valeurs d’une certaine colonne, appelée colonne de partitionnement. Chaque valeur de la colonne de partitionnement est une entrée de la fonction de partitionnement, qui retourne une valeur de partition.

La fonction de partition définit le nombre de partitions et les limites de partition qu’utilise la table. Prenons l’exemple d’une table qui contient des données de commande client : vous pouvez partitionner la table en 12 partitions (mensuellement) en fonction d’une colonne datetime, telle qu’une date de vente.

Un type de plage (LEFT ou RIGHT) spécifie la façon dont les valeurs limites de la fonction de partition sont placées dans les partitions obtenues :

  • Une plage LEFT spécifie que la valeur limite appartient au côté gauche de l’intervalle de valeur limite quand les valeurs de l’intervalle sont triées par le moteur de base de données dans l’ordre croissant, de la gauche vers la droite. En d’autres termes, la valeur englobante la plus élevée sera incluse dans une partition.
  • Une plage RIGHT spécifie que la valeur limite appartient au côté droit de l’intervalle de valeur limite quand les valeurs de l’intervalle sont triées par le moteur de base de données dans l’ordre croissant, de la gauche vers la droite. En d’autres termes, la valeur englobante la plus basse sera incluse dans chaque partition.

Si LEFT ou RIGHT n’est pas spécifié, la plage LEFT est la valeur par défaut.

Par exemple, la fonction de partition suivante partitionne une table ou un index en 12 partitions, à raison d’une partition pour chaque mois de valeurs d’une année dans une colonne datetime. Une plage RIGHT est utilisée, indiquant que les valeurs limites serviront de valeurs englobantes inférieures dans chaque partition. Les plages RIGHT sont souvent plus simples à utiliser lors du partitionnement d’une table en fonction d’une colonne de types de données datetime ou datetime2, car les lignes avec une valeur « minuit » sont stockées dans la même partition que les lignes avec des valeurs ultérieures le même jour. De même, si vous utilisez le type de données date et les partitions d’un mois ou plus, une plage RIGHT conserve le premier jour du mois dans la même partition que les jours suivants dans ce mois. Cela permet une éliminationde partition précise lors de l’interrogation de la valeur de données d’une journée entière.

CREATE PARTITION FUNCTION [myDateRangePF1] (datetime)  
AS RANGE RIGHT FOR VALUES ('2022-02-01', '2022-03-01', '2022-04-01',  
               '2022-05-01', '2022-06-01', '2022-07-01', '2022-08-01',   
               '2022-09-01', '2022-10-01', '2022-11-01', '2022-12-01');

Le tableau suivant illustre le partitionnement d’une table ou d’un index dans lequel cette fonction de partition est appliquée à la colonne de partitionnement datecol. Le 1er février est le premier point de limite défini dans la fonction. Il agit donc comme la limite inférieure de la partition 2.

Partition 1 2 ... 11 12
Valeurs datecol<2022-02-01 12:00AM datecol>= 2022-02-01 12:00AM AND datecol<2022-03-01 12:00AM datecol>= 2022-11-01 12:00AM AND col1<2022-12-01 12:00AM datecol>= 2022-12-01 12:00AM

Pour RANGE LEFT et RANGE RIGHT, la partition la plus à gauche a la valeur minimale du type de données comme limite inférieure, et la partition la plus à droite a la valeur maximale du type de données comme limite supérieure.

Pour plus d’exemples de fonctions de partition LEFT et RIGHT, consultez CREATE PARTITION FUNCTION (Transact-SQL).

Schéma de partition

Un schéma de partition est un objet de base de données qui mappe les partitions d’une fonction de partition à un groupe de fichiers ou à plusieurs groupes de fichiers.

Vous trouverez un exemple de syntaxe pour créer des schémas de partition dans CREATE PARTITION SCHEME (Transact-SQL).

Groupes de fichiers

La principale raison de placer des partitions sur des groupes de fichiers distincts est de s’assurer que vous pouvez réaliser des opérations de sauvegarde et de restauration indépendantes sur les partitions. En effet, vous pouvez réaliser des sauvegardes sur des groupes de fichiers spécifiques. Lorsque vous utilisez un stockage hiérarchisé, l’utilisation de plusieurs groupes de fichiers vous permet d’attribuer des partitions spécifiques à des niveaux de stockage spécifiques, par exemple pour placer des partitions plus anciennes et moins fréquemment sollicitées sur un stockage plus lent et moins coûteux. Tous les autres avantages liés au partitionnement s’appliquent indépendamment du nombre de groupes de fichiers utilisés ou du placement des partitions sur des groupes de fichiers spécifiques.

La gestion des fichiers et des groupes de fichiers pour les tables partitionnée peut ajouter une complexité significative aux tâches administratives au fil du temps. Si vos procédures de sauvegarde et de restauration ne bénéficient pas de l’utilisation de plusieurs groupes de fichiers, un seul groupe de fichiers pour toutes les partitions est recommandé. Les mêmes règles pour concevoir des fichiers et des groupes de fichiers s’appliquent aux objets partitionnés ainsi qu’aux objets non partitionnés.

Remarque

Le partitionnement n’est pas entièrement pris en charge dans Azure SQL Database. Étant donné que seul le groupe de fichiers PRIMARY est pris en charge dans Azure SQL Database, toutes les partitions doivent être placées sur le groupe de fichiers PRIMARY.

Vous trouverez un exemple de code pour créer des groupes de fichiers pour SQL Server et Azure SQL Managed Instance dans Options de fichiers et de groupes de fichiers ALTER DATABASE (Transact-SQL).

Colonne de partitionnement

Colonne d'une table ou d'un index utilisée par une fonction de partition pour partitionner la table ou l'index. Les considérations suivantes s’appliquent lors de la sélection d’une colonne de partitionnement :

  • Les colonnes calculées qui font partie d’une fonction de partition doivent être créées explicitement avec l’attribut PERSISTED.
    • Étant donné qu’une seule colonne peut être utilisée comme colonne de partition, dans certains cas, la concaténation de plusieurs colonnes avec une colonne calculée peut s’avérer utile.
  • Les colonnes de tous les types de données autorisés dans les colonnes clés d’index peuvent être utilisées dans la colonne de partitionnement, sauf timestamp.
  • Les colonnes des types de données LOB (Large OBject), telles que ntext, text, image, xml, varchar(max), nvarchar(max) et varbinary(max) ne peuvent pas être spécifiées.
  • Le type défini par l’utilisateur CLR (Common Langage Runtime) Microsoft .NET Framework et les colonnes de type de données alias ne peuvent pas être non plus spécifiés.

Pour partitionner un objet, spécifiez le schéma de partition et la colonne de partitionnement dans les instructions CREATE TABLE (Transact-SQL), ALTER TABLE (Transact-SQL) et CREATE INDEX (Transact-SQL).

Lors de la création d’un index non cluster, si partition_scheme_name ou filegroup n’est pas spécifié et que la table est partitionnée, l’index est placé dans le même schéma de partition que la table sous-jacente, en utilisant la même colonne de partitionnement. Pour modifier la façon dont un index existant est partitionné, utilisez CREATE INDEX avec la clause DROP_EXISTING. Cela vous permet de partitionner un index non partitionné, de créer un index partitionné non partitionné ou de modifier le schéma de partition de l’index.

Index aligné

Index créé sur le même schéma de partition que la table qui lui correspond. Lorsqu’une table et ses index sont alignés, le moteur de base de données peut commuter rapidement et efficacement les partitions pour les insérer ou les extraire, tout en préservant leur structure aussi bien dans la table que dans les index. Un index n’a pas besoin de participer à la même fonction de partition nommée pour être aligné avec sa table de base. Toutefois, la fonction de partition de l’index et la table de base doivent être essentiellement les mêmes en ceci :

  • Les arguments des fonctions de partition ont le même type de données.
  • Ils définissent le même nombre de partitions.
  • Ils définissent les mêmes valeurs limites pour les partitions.

Partitionnement des index cluster

Lors du partitionnement d'un index cluster, la clé de clustering doit contenir la colonne de partitionnement. Lors du partitionnement d’un index cluster non unique, tandis que la colonne de partitionnement n’est pas spécifiée explicitement dans la clé de clustering, le moteur de base de données ajoute cette colonne par défaut à la liste des clés d’index cluster. Si l'index cluster est unique, vous devez spécifier explicitement que la clé d'index cluster contient la colonne de partitionnement. Pour plus d’informations sur les index cluster et l’architecture des index, consultez Instructions de conception d’index cluster.

Partitionnement des index non cluster

Lors du partitionnement d'un index non-cluster unique, la clé d'index doit contenir la colonne de partitionnement. Lors du partitionnement d’un index non cluster non unique, le moteur de base de données ajoute la colonne de partitionnement par défaut comme colonne non-clé (incluse) de l’index pour garantir que l’index est aligné avec la table de base. Le moteur de base de données n’ajoute pas la colonne de partitionnement à l’index si elle y figure déjà. Pour plus d’informations sur les index non cluster et l’architecture des index, consultez Instructions de conception d’index non cluster.

Index non aligné

Un index non aligné est partitionné différemment de sa table correspondante. Autrement dit, l’index a un schéma de partition différent qui le place dans un groupe de fichiers ou ensemble de groupe de fichiers différent de la table de base. La conception d’un index partitionné non aligné peut être utile dans les cas suivants :

  • La table de base n’a pas été partitionnée.
  • La clé d’index est unique et elle ne doit pas contenir la colonne de partitionnement de la table.
  • vous souhaitez que la table de base soit impliquée dans des jointures communes à plusieurs tables en utilisant différentes colonnes de jointure.

Élimination de partition

Processus par lequel l'optimiseur de requête accède uniquement aux partitions pertinentes pour satisfaire les critères de la requête.

Pour en savoir plus sur l’élimination des partitions et les concepts connexes, consultez Améliorations du traitement des requêtes sur les tables et les index partitionnés.

Limites

  • L'étendue d'une fonction de partition et d'un schéma est limitée à la base de données dans laquelle ils ont été créés. Dans la base de données, les fonctions de partition résident dans un espace de noms indépendant des autres fonctions.

  • Si des lignes d’une table partitionnée ont des valeurs NULL dans la colonne de partitionnement, ces lignes sont placées sur la partition la plus à gauche. Toutefois, si NULL est spécifié comme première valeur limite et que RIGHT RANGE est indiqué dans la définition de la fonction de partition, la partition la plus à gauche reste vide et les valeurs NULL sont placées dans la deuxième partition.

Recommandations sur les performances

Le moteur de base de données prend en charge jusqu’à 15 000 partitions par table ou index. Cependant, l’utilisation de plus de 1 000 partitions a des conséquences sur la mémoire, les opérations d’index partitionné, les commandes DBCC et les requêtes. Cette section décrit les implications en matière de performances en cas d’utilisation de plus de 1 000 partitions et fournit des solutions de contournement si nécessaire.

En autorisant jusqu’à 15 000 partitions par table ou index partitionnés, vous pouvez stocker des données pendant de longues durées dans une seule table. Toutefois, vous devez conserver les données uniquement pendant la durée nécessaire et obtenir un compromis entre les performances et le nombre de partitions.

Utilisation de la mémoire et recommandations

Nous vous recommandons d'utiliser au moins 16 Go de RAM si un grand nombre de partitions sont en cours d'utilisation. Si le système n’a pas assez de mémoire, les instructions DML (Data Manipulation Language), les instructions DDL (Data Definition Language) et d’autres opérations peuvent échouer en raison d’une insuffisance de mémoire. Les systèmes avec 16 Go de RAM qui exécutent un grand nombre de processus nécessitant beaucoup de mémoire risque de ne pas disposer de suffisamment de mémoire lors des opérations qui s'exécutent sur un grand nombre de partitions. Par conséquent, plus vous disposez de mémoire au-delà de 16 Go, moins vous risquez de rencontrer des problèmes de performances et de mémoire.

Les limitations de mémoire peuvent affecter les performances du moteur de base de données ou sa capacité à créer un index partitionné. C’est le cas notamment lorsque l’index n’est pas aligné avec sa table de base ou son index cluster, si la table dispose déjà d’un index cluster.

Dans SQL Server et Azure SQL Managed Instance, vous pouvez augmenter l’option de configuration de serveur index create memory (KB). Pour plus d’informations, consultez Configurer l’option de configuration Création d’index en mémoire. Pour Azure SQL Database, envisagez d’augmenter temporairement ou définitivement l’objectif de niveau de service de la base de données dans le Portail Azure pour allouer plus de mémoire.

Opérations d’index partitionné

La création et la reconstruction d’index non alignés sur une table contenant plus de 1 000 partitions sont possibles, mais ne sont pas prises en charge. Ces opérations peuvent entraîner une dégradation des performances ou une consommation de mémoire excessive.

La création et la reconstruction d’index alignés peuvent exiger davantage de temps à mesure que le nombre de partitions augmente. Nous vous recommandons de ne pas exécuter simultanément plusieurs commandes de création et de reconstruction d’index, car vous risquez de rencontrer des problèmes de performances et de mémoire.

Lorsque le moteur de base de données effectue un tri pour créer des index partitionnés, il commence par créer une table de tri pour chaque partition. Ensuite, il produit les tables de tri soit dans le groupe de fichiers de chaque partition, soit dans tempdb, si l’option d’index SORT_IN_TEMPDB est spécifiée. La création de chaque table de tri nécessite une quantité minimale de mémoire. Lorsque vous créez un index partitionné qui est aligné avec sa table de base, les tables de tri sont créées une par une, ce qui utilise moins de mémoire. Toutefois, lorsque vous créez un index partitionné non aligné, les tables de tri sont produites en même temps. De ce fait, il doit y avoir assez de mémoire pour gérer ces tri simultanés. Plus il y a de partitions, plus il faut de mémoire. La taille minimale pour chaque table de tri, pour chaque partition, est de 40 pages, à raison de 8 kilo-octets par page. Par exemple, un index partitionné non aligné avec 100 partitions nécessite une quantité de mémoire suffisante pour trier en série 4 000 (40 * 100) pages à la fois. Si cette mémoire est disponible, l'opération de création réussit, mais les performances risquent d'en pâtir. Si cette mémoire n’est pas disponible, l’opération de création échoue. À l’inverse, un index partitionné aligné avec 100 partitions n’a besoin que de la mémoire suffisante pour trier 40 pages, parce que les tris ne sont pas effectués en même temps.

Pour les deux types d’index, alignés et non alignés, la mémoire requise peut être beaucoup plus importante si le moteur de base de données utlise le parallélisme des requêtes lors de l’opération de création sur un ordinateur multiprocesseur. En effet, plus le degré de parallélisme est élevé, plus les besoins en mémoire sont importants. Par exemple, si le moteur de base de données définit les degrés de parallélisme avec la valeur 4, un index partitionné non aligné avec 100 partitions a besoin d’une quantité de mémoire suffisante pour quatre processeurs pour trier 4 000 pages à la fois, ou 16 000 pages. Si l'index partitionné est aligné, la mémoire requise est moins importante puisqu'il en faut pour quatre processeurs triant 40 pages ou 160 (4 * 40) pages. Vous pouvez utiliser l’option d’index MAXDOP pour réduire manuellement les degrés de parallélisme.

Commandes DBCC

Avec un plus grand nombre de partitions, l’exécution des commandes DBCC, telles que DBCC CHECKDB et DBCC CHECKTABLE, peut exiger davantage de temps à mesure que le nombre de partitions augmente.

Requêtes

Après avoir partitionné une table ou un index,les requêtes qui utilisent l’élimination de partition peuvent présenter des performances comparables ou meilleures avec un plus grand nombre de partitions. Les requêtes qui n’utilisent pas l’élimination de partition peuvent être plus longues à mesure que le nombre de partitions augmente.

Par exemple, supposons qu'une table a 100 millions de lignes et de colonnes A, Bet C.

  • Dans le scénario 1, la table est divisée en 1 000 partitions sur la colonne A.
  • Dans le scénario 2, la table est divisée en 10 000 partitions sur la colonne A.

Une requête sur la table qui contient une clause WHERE filtrant sur la colonne A effectue une élimination de partition et analyse une partition. Il se peut que cette même requête s'exécute plus rapidement dans le scénario 2 car il y a moins de lignes à analyser dans une partition. Une requête qui contient une clause WHERE filtrant sur la colonne B analyse toutes les partitions. Il se peut que cette requête s'exécute plus rapidement dans le scénario 1 que dans le scénario 2 car il y a moins de partitions à analyser.

Les requêtes qui utilisent des opérateurs tels que TOP ou MAX/MIN sur des colonnes autres que la colonne de partitionnement peuvent enregistrer une baisse des performances lors du partitionnement, du fait que toutes les partitions doivent être évaluées.

De même, une requête qui effectue une recherche à une seule ligne ou une analyse de petite plage prend plus de temps sur une table partitionnée que sur une table non partitionnée si le prédicat de requête n’inclut pas la colonne de partitionnement, car elle devra effectuer autant de recherches ou d’analyses qu’il existe de partitions. Pour cette raison, le partitionnement améliore rarement les performances dans les systèmes OLTP où ces requêtes sont courantes.

Si vous exécutez fréquemment des requêtes qui impliquent une équi-jointure entre au moins deux tables partitionnées, leurs colonnes de partitionnement doivent être les mêmes que celles par lesquelles les tables sont jointes. En outre, les tables, ou leurs index, doivent subir une colocation. Cela signifie qu’ils utilisent la même fonction de partition nommée ou des fonctions de partition nommée différentes mais fondamentalement similaires en ce sens qu’elles :

  • possèdent le même nombre de paramètres utilisés pour le partitionnement et que les types de données des paramètres correspondants sont les mêmes ;
  • définissent le même nombre de partitions ;
  • définissent les mêmes valeurs limites pour les partitions.

Ainsi, l’optimiseur de requête peut traiter la jointure plus rapidement car les partitions elles-mêmes peuvent être jointes. Si une requête joint deux tables qui n’ont pas subi de colocation ou qui ne sont pas partitionnées sur le champ de jointure, la présence de partitions peut réellement ralentir le traitement des requêtes au lieu de l’accélérer.

Vous pouvez trouver utile d’utiliser $PARTITION dans certaines requêtes. En savoir plus dans $PARTITION (Transact-SQL).

Pour plus d’informations sur la gestion des partitions dans le traitement des requêtes, notamment la stratégie d’exécution de requête parallèle pour les tables et index partitionnés et les meilleures pratiques supplémentaires, consultez Améliorations du traitement des requêtes sur les tables et les index partitionnés.

Changements de comportement dans le calcul des statistiques pour les opérations d’index partitionnés

Dans Azure SQL Database, Azure SQL Managed Instance et SQL Server 2012 (11.x) et versions ultérieures, les statistiques ne sont pas créées en analysant toutes les lignes de la table lorsqu’un index partitionné est créé ou reconstruit. Au lieu de cela, l'optimiseur de requête utilise l'algorithme d'échantillonnage par défaut pour générer des statistiques.

Après la mise à niveau d’une base de données avec des index partitionnés à partir d’une version de SQL Server antérieure à 2012 (11.x), vous remarquerez peut-être une différence dans les données d’histogramme pour ces index. Cette modification du comportement peut ne pas affecter les performances des requêtes. Pour obtenir des statistiques sur les index partitionnés en analysant toutes les lignes de la table, utilisez CREATE STATISTICS ou UPDATE STATISTICS avec la clause FULLSCAN.

Contenu connexe

Découvrez les stratégies de tables et d’idex partitionnés dans les articles suivants :