Tables et index partitionnés

  • Article

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

SQL Server, Azure SQL Database et Azure SQL Managed Instance prennent en charge la table et le partitionnement d’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. En savoir plus sur le 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 de données d’un OLTP vers un système OLAP prend seulement des secondes, au lieu des minutes et heures pendant lesquelles l’opération prend lorsque les données ne sont pas partitionnés.

  • Vous pouvez effectuer des opérations de maintenance ou de rétention 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 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 d’une table et 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 les requêtes équijoines entre deux tables partitionnés 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 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é une table qui contient des données de commande 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 (LEFT ou RIGHT) spécifie la façon dont 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 sera incluse dans une partition.
  • Une plage RIGHT spécifie que la valeur de limite appartient au côté droit 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 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, une pour chaque mois de la valeur 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 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 de 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 de 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 élimination précise de partition 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.

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 un 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 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 la conception de fichiers et de groupes de fichiers s’appliquent aux objets partitionnés que les objets non partitionnés.

Remarque

Le partitionnement n’est pas 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 Database, toutes les partitions doivent être placées sur le PRIMARY groupe de fichiers.

Recherchez un exemple de code pour 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 valides pour une utilisation en tant que colonnes clés d’index peuvent être utilisées comme colonne de partitionnement, à l’exception de l’horodatage.
  • 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.
  • Les colonnes de type défini par l’utilisateur et de type de données alias microsoft .NET Framework common language runtime (CLR) ne peuvent pas être spécifiées.

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 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 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 basculer les partitions dans ou hors de la table rapidement et efficacement tout en conservant la structure de partition de la table et de ses index. Un index n’a pas à participer à la même fonction de partition nommée à aligner sur 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, 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 vous assurer 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 s’il est déjà présent 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 distinct ou un ensemble de groupes de fichiers 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 ne contient pas 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.

En savoir plus sur l’élimination des partitions et les concepts connexes dans les 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 LA valeur NULL est spécifiée en tant que première valeur de limite et QUE RANGE RIGHT est spécifiée 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 en fonction des besoins.

Avec jusqu’à 15 000 partitions autorisées 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 tant qu’elles sont nécessaires et maintenir un équilibre entre les performances et le nombre de partitions.

Utilisation et instructions de la mémoire

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 suffisamment 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 mémoire insuffisante. 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 avez plus de 16 Go de mémoire, 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é. Cela est particulièrement le cas lorsque l’index n’est pas aligné avec 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 mémoire de création d’index. 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é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.

La création et la reconstruction d’index alignés peuvent prendre plus de temps à s’exécuter à mesure que le nombre de partitions augmente. Nous vous recommandons de ne pas exécuter plusieurs commandes de création et de reconstruction d’index en même temps que vous risquez d’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é aligné sur sa table de base, les tables de tri sont générées une par une, en utilisant moins de mémoire. Toutefois, lorsque vous générez un index partitionné non aligné, les tables de tri sont générées 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 génération échoue. Sinon, un index partitionné aligné avec 100 partitions nécessite uniquement une mémoire suffisante pour trier 40 pages, car les tris ne sont pas effectués en même temps.

Pour les index alignés et non alignés, la mémoire requise peut être supérieure 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. Cela est dû au fait que plus le degré de parallélisme (DOP), plus la mémoire requise est élevée. 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 une mémoire suffisante pour quatre processeurs pour trier 4 000 pages en même temps 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, les commandes DBCC telles que DBCC CHECKDB et DBCC CHECKTABLE peuvent prendre plus de temps à s’exécuter à 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 prendre plus de temps à s’exécuter à 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 il 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 un équijoin entre deux tables partitionnée ou plus, leurs colonnes de partitionnement doivent être identiques aux colonnes sur 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 différentes fonctions de partition qui sont essentiellement les mêmes, dans ce qui suit :

  • 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 ne sont pas colocalisées ou ne sont pas partitionnée sur le champ de jointure, la présence de partitions peut ralentir le traitement des requêtes au lieu de l’accélérer.

Vous pouvez trouver utile d’utiliser $PARTITION 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 partitionnés et les index.

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 remarquerez peut-être 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.

Contenu connexe

En savoir plus sur les tables partitionnée et les stratégies d’index dans les articles suivants :