Tables et index partitionnés

SQL Server prend en charge le partitionnement de table et d’index. Les données des tables et des index partitionnés sont divisées en unités qui peuvent être réparties entre plusieurs groupes de fichiers d’une base de données. Les données sont partitionnée horizontalement, afin que les groupes de lignes soient mappés dans 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é comme une entité logique unique lorsque des requêtes ou des mises à jour sont effectuées sur les données. Les tables et index partitionnés ne sont pas disponibles dans chaque édition de MicrosoftSQL Server. Pour obtenir la liste des fonctionnalités prises en charge par les éditions de SQL Server, consultez Fonctionnalités prises en charge par les éditions de SQL Server 2014.

Avantages du partitionnement

Le partitionnement de tables ou d’index volumineux peut avoir les avantages suivants en matière de gestion et de performances.

• Vous pouvez transférer ou accéder à des sous-ensembles de données rapidement 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 ne prend que quelques 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 sur une ou plusieurs partitions plus rapidement. Les opérations sont plus efficaces, car elles ciblent uniquement ces sous-ensembles de données, au lieu de la table entière. Par exemple, vous pouvez choisir de compresser des données dans une ou plusieurs partitions ou de reconstruire une ou plusieurs partitions d’un index.

• Vous pouvez améliorer les performances des requêtes, en fonction des types de requêtes que vous exécutez fréquemment et de votre configuration matérielle. Par exemple, l’optimiseur de requête peut traiter les requêtes d’équi-jointure entre deux tables partitionnées plus rapidement lorsque les colonnes de partitionnement des tables sont identiques, car les partitions elles-mêmes peuvent être jointes.

  Lorsque SQL Server effectue le tri des données pour les opérations d’E/S, il trie d’abord les données par partition. SQL Server accède à un lecteur à la fois, ce qui peut réduire les performances. Pour améliorer les performances de tri des données, répartissez les fichiers de données de vos partitions sur plusieurs disques en configurant un RAID. De cette façon, même si SQL Server trie toujours les données par partition, il peut accéder à tous les lecteurs de chaque partition en même temps.

  En outre, 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 la contention de verrou sur la table.

Composants et concepts

Les termes suivants s’appliquent au partitionnement de table et d’index.

Fonction de partition
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 de certaines colonnes, appelées colonne de partitionnement. Autrement dit, la fonction de partition définit le nombre de partitions que la table aura et la façon dont les limites des partitions sont définies. Par exemple, étant donné une table qui contient des données de commande client, vous pouvez partitionner la table en douze partitions (mensuelles) en fonction d’une colonne telle qu’une datetime date de vente.

Schéma de partition
Objet de base de données qui mappe les partitions d’une fonction de partition à un ensemble de groupes de fichiers. La principale raison de placer vos partitions sur des groupes de fichiers distincts consiste à vous assurer que vous pouvez effectuer des opérations de sauvegarde indépendamment sur des partitions. Cela est dû au fait que vous pouvez effectuer des sauvegardes sur des groupes de fichiers individuels.

Colonne de partitionnement
Colonne d’une table ou d’un index qu’une fonction de partition utilise pour partitionner la table ou l’index. Les colonnes calculées qui participent à une fonction de partition doivent être marquées explicitement comme PERSISTED. Tous les types de données valides pour une utilisation en tant que colonnes d’index peuvent être utilisés comme colonne de partitionnement, à l’exception timestampde . Les types de données ntext, text, image, xml, varchar(max), nvarchar(max) ou varbinary(max) ne peuvent pas être spécifiés. Les colonnes de type défini par l’utilisateur ou de type de données alias de la Common Language Runtime (CLR) du Microsoft .NET Framework ne peuvent être spécifiées.

Index aligné
Index basé sur le même schéma de partition que sa table correspondante. Lorsqu’une table et ses index sont alignés, SQL Server peut changer de partitions rapidement et efficacement 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 identiques, dans ce 1) les arguments des fonctions de partition ont le même type de données, 2) ils définissent le même nombre de partitions et 3) ils définissent les mêmes valeurs de limites pour les partitions.

Index non aligné
Index partitionné indépendamment de sa table correspondante. Autrement dit, l’index a un schéma de partition différent ou est placé sur un groupe de fichiers distinct 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 participe à des jointures colocalisées avec d'autres 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 répondre aux critères de filtre de la requête.

Recommandations en matière de performances

La nouvelle limite supérieure de 15 000 partitions affecte 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’augmentation du nombre de partitions supérieures à 1 000 et fournit des solutions de contournement si nécessaire. Avec la limite du nombre maximal de partitions augmentée à 15 000, vous pouvez stocker des données pendant une durée plus longue. 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 recommandations en matière de 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 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 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é de SQL Server à 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 appliqué à celui-ci.

Opérations d’index partitionnés

Les limitations de mémoire peuvent affecter les performances ou la capacité de SQL Server à générer un index partitionné. Ceci est particulièrement le cas avec des index non alignés. La création et la reconstruction des 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 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 SQL Server effectue le tri pour générer des index partitionnés, il génère d’abord 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. Chaque table de tri nécessite une quantité minimale de mémoire à générer. 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. Par conséquent, il doit y avoir suffisamment de mémoire pour gérer ces tris simultanés. Plus le nombre de partitions est élevé, plus la mémoire est nécessaire. La taille minimale de chaque table de tri, pour chaque partition, est de 40 pages, avec 8 kilo-octets par page. Par exemple, un index partitionné non aligné avec 100 partitions nécessite une mémoire suffisante pour trier en série 4 000 pages (40 * 100) en même temps. Si cette mémoire est disponible, l’opération de génération réussit, mais les performances peuvent souffrir. Si cette mémoire n’est pas disponible, l’opération de génération échoue. Par ailleurs, 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, l’exigence de mémoire peut être supérieure si SQL Server applique des degrés de parallélisme à l’opération de génération sur un ordinateur multiprocesseur. Cela est dû au fait que plus le degré de parallélisme est élevé, plus la mémoire est requise. Par exemple, si SQL Server définit des degrés de parallélisme 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 réduite à 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 peuvent prendre plus de temps à s’exécuter à mesure que le nombre de partitions augmente.

Requêtes

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 comporte 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 avec une clause WHERE filtrant la colonne A effectuera l’élimination d'une partition et analysera 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 avec un filtrage de clause WHERE 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.

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

À compter de SQL Server 2012, 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, vous pouvez remarquer 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.

Tâches associées

Contenu associé

Vous trouverez peut-être les livres blancs suivants sur la table partitionnée et les stratégies d’index et les implémentations utiles.

• Stratégies de table et d’index partitionnés à l’aide de SQL Server 2008

• Comment implémenter une fenêtre glissante automatique

• Chargement en bloc dans une table partitionnée

• Améliorations du traitement des requêtes sur les tables et index partitionnés

• Meilleures pratiques pour la création d’un entrepôt de données relationnelles à grande échelle