Partager via

Tables et index partitionnés

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

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 ces derniers à 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.

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 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 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 déplacer des partitions individuelles d'une table vers 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 plusieurs tables partitionnées plus rapidement lorsque les colonnes de partitionnement sont identiques que les colonnes sur lesquelles les tables sont jointes. Pour plus d’informations, consultez la section sur les requêtes.

Il est possible d’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.

Partition, fonction

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. Par exemple, compte tenu d’une table qui contient des données de commande, 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 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 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 limite 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 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 limite 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 limites les plus basses 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 élimination de partition précise 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 la limite inférieure de la partition 2.

Partition 1 2 ... 11 12
Values datecol<2022-02-01 12:00AM datecol>= 2022-02-01 12:00AMET datecol<2022-03-01 12:00AM datecol>= 2022-11-01 12:00AM ET 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.

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 ou à plusieurs groupe de fichiers.

Recherchez un exemple de syntaxe pour créer des schémas de partition dans CREATE PARTITION SCHEME.

Filegroups

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. Si 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 consultées moins fréquemment 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, il vaut mieux utiliser un seul groupe de fichiers pour toutes les partitions. Les mêmes règles de conception de fichiers et de groupes de fichiers s’appliquent aux objets partitionnés, comme s’appliquent aux objets nonpartitionnés.

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 observations 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 explicitement être créées comme 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 qui peuvent être utilisées comme colonnes de clés d’index peuvent être utilisées comme colonnes de partitionnement, à l’exception de 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.
  • Les colonnes de type défini par l’utilisateur et de type de données alias du CLR (common language runtime) du Microsoft .NET Framework 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, ALTER TABLE et CREATE INDEX .

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 nonpartitionné, de créer un index partitionné nonpartitionné 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 rapidement et efficacement activer ou désactiver des partitions dans 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 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, alors 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 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-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 un 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.

Limitations

  • 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, de base de données SQL dans Fabric et d’Azure SQL Managed Instance.

    • Dans Azure SQL Database et SQL Database dans Fabric, toutes les partitions doivent être placées sur le PRIMARY groupe de fichiers, car le PRIMARY groupe de fichiers est le seul disponible.

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

  • 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, elles 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, alors la partition la plus à gauche reste vide et les valeurs NULL sont placées dans la deuxième partition.

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

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és, 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 selon les besoins.

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 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 de nombreux processus gourmands en mémoire peuvent manquer de mémoire sur les 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 possède déjà 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 Configuration du serveur : index create memory.

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 exiger davantage de temps à 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 un tri pour créer des index partitionnés, il commence par créer une table de tri pour chaque partition. Il génère ensuite les tables de tri dans le groupe de fichiers respectif de chaque partition ou 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 génération réussit, mais les performances peuvent souffrir. 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, 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. En effet, plus il y a de degrés de parallélisme (DOP), plus il faut de mémoire. Par exemple, si le moteur de base de données affecte aux DOP la valeur 4, un index partitionné non aligné avec 100 partitions a besoin d’une quantité de mémoire suffisante pour que quatre processeurs puissent trier 4 000 pages à la fois, 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 exiger davantage de temps à mesure que le nombre de partitions augmente.

Queries

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, B et 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. Cette même requête peut s’exécuter 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. La requête peut s’exécuter 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 rencontrer des performances réduites avec le partitionnement, car 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 nonpartitionné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 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 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 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 pourriez trouver utile d’utiliser $PARTITION dans certaines requêtes. En savoir plus dans $PARTITION.

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 les index partitionnés, ainsi que des meilleures pratiques supplémentaires, consultez Améliorations du traitement des requêtes sur les tables et index partitionnés.

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

Dans Azure SQL Database, SQL Database dans Fabric, 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. Cette modification du 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

  • Last updated on