Tables et index partitionnés

  • Article
  • 19 minutes de lecture

S’applique à : base de données SQL Server Azure SQL 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 des 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 seul groupe de fichiers. Lorsque 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.

Dans les versions antérieures à SQL Server 2016 (13.x)SP1, les tables et les index partitionnés n’étaient pas disponibles dans toutes les éditions 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 2016. Les tables et les index partitionnés sont disponibles dans tous les niveaux de service de Azure SQL Base de données et Azure SQL Managed Instance.

Le partitionnement de table est également disponible dans les pools SQL dédiés dans Azure Synapse Analytics, avec certaines différences de syntaxe. Pour plus d’informations, consultez Partitionnement des 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 plus rapidement des opérations de maintenance ou de rétention des données sur une ou plusieurs partitions. 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 des données dans une ou plusieurs partitions, de reconstruire une ou plusieurs partitions d’un index ou de tronquer des données dans une seule partition. Vous pouvez également basculer des partitions individuelles à partir d’une table et dans une table d’archivage.

  • 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 plusieurs tables partitionnées plus rapidement lorsque les colonnes de partitionnement sont identiques que les 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 des verrous au niveau de la partition au lieu d’une 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 la façon dont 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. Par exemple, étant donné qu’une table contient des données de commandes client, vous pouvez partitionner la table en 12 partitions (mensuelles) en fonction d’une colonne datetime telle qu’une date de vente.

Un type de plage (GAUCHE ou DROITE) spécifie comment les valeurs de limite de la fonction de partition seront placées dans les partitions résultantes :

  • Une plage LEFT spécifie que la valeur de limite appartient au côté gauche de l’intervalle de valeur de limite lorsque les valeurs d’intervalle sont triées par le moteur de base de données dans l’ordre croissant de gauche à droite. En d’autres termes, la valeur englobante la plus élevée est incluse dans une partition.
  • Une plage RIGHT spécifie que la valeur de limite appartient au côté droit de l’intervalle de valeurs limites lorsque les valeurs d’intervalle sont triées par le moteur de base de données dans l’ordre croissant de gauche à droite. En d’autres termes, la valeur englobante la plus basse est 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, une pour chaque mois d’une année de valeurs dans une colonne datetime . Une plage RIGHT est utilisée, indiquant que les valeurs de limite serviront de valeurs englobantes inférieures dans chaque partition. Les plages DROITE 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 dont la valeur est minuit seront 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 que vous utilisez des partitions d’un mois ou plus, une plage RIGHT conserve le premier jour du mois dans la même partition que les derniers jours de ce mois. Cela facilite l’élimination précise de la partition lors de l’interrogation d’une journée entière de données.

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 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.

Recherchez 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 le stockage hiérarchisé, l’utilisation de plusieurs groupes de fichiers vous permet d’affecter 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 compliquer considérablement les tâches d’administration 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 est recommandé pour toutes les partitions. Les mêmes règles pour la conception de fichiers et de groupes de fichiers s’appliquent aux objets partitionnés que pour les objets non partitionnés.

Notes

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

Recherchez un exemple de code permettant de créer des groupes de fichiers pour SQL Server et Azure SQL Managed Instance dans les options de fichier et de groupe 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 participent à une fonction de partition doivent être créées explicitement en tant que 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 être utile.
  • Les colonnes de tous les types de données qui sont valides pour être utilisées comme colonnes de clé d’index peuvent être utilisées comme colonne de partitionnement, à l’exception de timestamp.
  • Les colonnes de types de données objet volumineux (LOB), telles que ntext, text, image, xml, varchar(max), nvarchar(max), et varbinary(max), ne peuvent pas être spécifiées.
  • Impossible de spécifier des colonnes de type de données définies par l’utilisateur et de type de données d’alias du Common Language Runtime (CLR) microsoft .NET Framework.

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 le groupe de fichiers n’est pas spécifié et que la table est partitionnée, l’index est placé dans le même schéma de partition, à l’aide de la même colonne de partitionnement, que la table sous-jacente. 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 rendre 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 basculer rapidement et efficacement les partitions dans ou hors de la table tout en conservant la structure de partition de la table et de ses 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 d’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 et que la colonne de partitionnement n’est pas explicitement spécifiée dans la clé de clustering, le moteur de base de données ajoute la colonne de partitionnement 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 d’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 unique et non cluster, le moteur de base de données ajoute la colonne de partitionnement par défaut en tant que colonne non clé (incluse) de l’index pour s’assurer que l’index est aligné sur la table de base. Le moteur de base de données n’ajoute pas la colonne de partitionnement à l’index si elle est déjà présente dans l’index. 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 sur un groupe de fichiers ou un ensemble de groupes de fichiers distincts 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.

Apprenez-en davantage sur l’élimination des partitions et les concepts associés dans 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 de limite et QUE RANGE RIGHT est spécifié 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. Toutefois, l’utilisation de plus de 1 000 partitions a des implications sur la mémoire, les opérations d’index partitionnés, les commandes DBCC et les requêtes. Cette section décrit les implications en matière de performances de l’utilisation de plus de 1 000 partitions et fournit des solutions de contournement si nécessaire.

Avec jusqu’à 15 000 partitions autorisées par table ou index partitionné, vous pouvez stocker des données pendant de longues durées dans une table unique. Toutefois, vous devez conserver les données uniquement aussi longtemps que nécessaire et maintenir un équilibre 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 ou la capacité du moteur de base de données à générer un index partitionné. C’est particulièrement le cas lorsque l’index n’est pas aligné sur sa table de base ou n’est pas aligné avec son index cluster, si la table a déjà un index cluster.

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

Opérations d’index partitionnés

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

L’exécution de la création et de la reconstruction d’index alignés peut prendre plus 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 le tri pour générer des index partitionnés, il génère d’abord 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. Sinon, la 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 index alignés et non alignés, les besoins en mémoire peuvent être plus importants si le moteur de base de données utilise le parallélisme des requêtes pour l’opération de génération sur un ordinateur multiprocesseur. En effet, plus le degré de parallélisme (DOP) est élevé, plus la mémoire requise est importante. Par exemple, si le moteur de base de données définit DOP sur 4, un index partitionné non aligné avec 100 partitions nécessite suffisamment de mémoire pour que quatre processeurs trient 4 000 pages en même temps, soit 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 prendre plus de temps à mesure que le nombre de partitions augmente.

Requêtes

Après le partitionnement d’une table ou d’un index, les requêtes qui utilisent l’élimination de partition peuvent avoir des performances comparables ou améliorées 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 doit effectuer autant de recherches ou d’analyses que de partitions. Pour cette raison, le partitionnement améliore rarement les performances dans les systèmes OLTP où de telles requêtes sont courantes.

Si vous exécutez fréquemment des requêtes qui impliquent une équijointure 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 qu’ils utilisent des fonctions de partition différentes qui sont essentiellement les mêmes, en ce 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 trouverez peut-être utile d’utiliser $PARTITION dans certaines requêtes. Pour plus d’informations, consultez $PARTITION (Transact-SQL).

Pour plus d’informations sur la gestion des partitions dans le traitement des requêtes, y compris la stratégie d’exécution de requête parallèle pour les tables et les index partitionnés, ainsi que 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 inférieure à 2012 (11.x), vous pouvez remarquer une différence dans les données d’histogramme pour ces index. Ce changement de comportement peut 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.

Étapes suivantes

Pour en savoir plus sur les tables partitionnées et les stratégies d’index, consultez les articles suivants :