Index columnstore - Conseils en matière de chargement de données

  • Article

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Options et recommandations pour le chargement de données dans un index columnstore en utilisant le chargement en masse SQL standard et des méthodes d’insertion segmentée. Le chargement de données dans un index columnstore est une partie essentielle de tout processus d’entreposage de données, car il transfère des données dans l’index en préparation de l’analyse.

Vous ne connaissez pas les index columnstore ? Consultez Index columnstore - Présentation et Index columnstore - Architecture.

Qu’est-ce que le chargement en masse ?

Le chargement en masse fait référence à la façon dont un grand nombre de lignes sont ajoutées à un magasin de données. Il s’agit du moyen le plus performant de déplacer des données dans un index columnstore, car il fonctionne sur des lots de lignes. Le chargement en masse remplit des rowgroups à leur capacité maximale et les compresse directement dans le columnstore. Seules les lignes situées à la fin d’un chargement qui ne respectent pas la valeur minimale de 102 400 lignes par rowgroup sont placées dans le deltastore.

Pour effectuer un chargement en masse, utilisez l’utilitaire bcp ou les services d’intégration, ou bien sélectionnez des lignes dans une table de mise en lots.

Screenshot showing loading into a clustered columnstore index.

Comme l’indique le diagramme :

  • Ne présort pas les données. Les données sont insérées dans des rowgroups dans l’ordre dans lequel elles sont reçues.
  • Si la taille du lot est >= 102400, les lignes sont directement chargées dans les rowgroups compressés. Vous devez choisir une taille >de lot =102400 pour une importation en bloc efficace, car vous pouvez éviter de déplacer des lignes de données vers des rowgroups delta avant que les lignes ne soient finalement déplacées vers des rowgroups compressés par un thread d’arrière-plan, le mover Tuple (TM).
  • Si la taille < du lot est 102 400 ou si les lignes restantes sont < de 102 400, les lignes sont chargées dans des rowgroups delta.

Remarque

Sur une table rowstore avec des données d’index columnstore non cluster, SQL Server insère toujours des données dans la table de base. Les données ne sont jamais insérées directement dans l’index columnstore.

Le chargement en masse dispose des fonctions d’optimisation des performances intégrées suivantes :

  • Chargements parallèles : vous pouvez avoir plusieurs charges en bloc simultanées (bcp ou insertion en bloc) qui chargent chacun un fichier de données distinct. Contrairement aux chargements en bloc rowstore dans SQL Server, vous n’avez pas besoin de spécifier TABLOCK , car chaque thread d’importation en bloc charge les données exclusivement dans des rowgroups distincts (rowgroups compressés ou delta) avec un verrou exclusif sur celui-ci.

  • Journalisation réduite : les données directement chargées dans des groupes de lignes compressés entraînent une réduction significative de la taille du journal. Par exemple, si les données ont été compressées 10x, le journal des transactions correspondant est d’environ 10x plus petit sans nécessiter de modèle de récupération tablock ou journalisé en bloc/simple. Toutes les données qui sont placées dans un rowgroup delta sont entièrement journalisées. Cela inclut toutes les tailles de lot inférieures à 102 400 lignes. La meilleure pratique consiste à utiliser batchsize >= 102400. Étant donné qu’il n’y a pas besoin de TABLOCK, vous pouvez charger les données en parallèle.

  • Journalisation minimale : vous pouvez obtenir une réduction supplémentaire de la journalisation si vous suivez les conditions préalables à la journalisation minimale. Toutefois, contrairement au chargement de données dans un rowstore, TABLOCK entraîne un verrou X sur la table plutôt qu’un verrou BU (mise à jour en bloc) et, par conséquent, la charge de données parallèle ne peut pas être effectuée. Pour plus d’informations sur le verrouillage, consultez Verrouillage et contrôle de version de ligne.

  • Optimisation du verrouillage : le verrou X d’un groupe de lignes est automatiquement acquis lors du chargement de données dans un groupe de lignes compressé. Toutefois, lors du chargement en bloc dans un rowgroup delta, un verrou X est acquis au niveau du rowgroup, mais SQL Server verrouille toujours la PAGE/EXTENT, car le verrou X rowgroup ne fait pas partie de la hiérarchie de verrouillage.

Si vous avez un index B-tree non cluster sur un index columnstore, il n’existe aucune optimisation de verrouillage ou de journalisation pour l’index lui-même, mais les optimisations sur l’index columnstore cluster, comme décrit précédemment, sont applicables.

La modification des données (insertion, suppression, mise à jour) n’est pas une opération en mode batch, car elle n’est pas parallèle.

Planifier les tailles de chargement en masse pour réduire les rowgroups delta

Les index columnstore fonctionnent de manière optimale quand la plupart des lignes sont compressées dans le columnstore et qu’elles ne se trouvent pas dans des rowgroups delta. Il est préférable de dimensionner vos chargements afin que les lignes soient directement placées dans le columnstore et d’ignorer autant que possible le deltastore.

Les scénarios suivants décrivent à quel moment les lignes chargées sont directement insérées dans le columnstore ou quand elles sont placées dans le deltastore. Dans l'exemple, chaque rowgroup peut avoir 102 400-1 048 576 lignes par rowgroup. Dans la pratique, la taille maximale d’un rowgroup peut être inférieure à 1 048 576 lignes en cas de pression de mémoire.

Lignes à charger en bloc Lignes ajoutées au rowgroup compressé Lignes ajoutées au rowgroup delta
102 000 0 102 000
145 000 145 000

Taille de rowgroup : 145 000		 0
1 048 577 1,048,576

Taille de rowgroup : 1 048 576		 1
2 252 152 2 252 152

Tailles de rowgroup : 1 048 576, 1 048 576, 155 000.		 0

L’exemple suivant montre les résultats du chargement de 1 048 577 lignes dans une table. Les résultats indiquent un rowgroup COMPRESSÉ dans le columnstore (comme segments de colonne compressés), et 1 ligne dans le deltastore.

SELECT object_id, index_id, partition_number, row_group_id, delta_store_hobt_id,
    state, state_desc, total_rows, deleted_rows, size_in_bytes
FROM sys.dm_db_column_store_row_group_physical_stats;

Screenshot showing rowgroup and deltastore for a batch load.

Utiliser une table en préproduction pour améliorer les performances

Si vous chargez des données uniquement pour l’exécuter avant d’exécuter plus de transformations, le chargement de la table dans la table de tas est beaucoup plus rapide que le chargement des données dans une table columnstore en cluster. De plus, le chargement de données dans une table temporaire [Temporary] s’effectue également beaucoup plus rapidement que le chargement d’une table dans un stockage permanent.

Un modèle courant pour le chargement des données consiste à charger les données dans une table intermédiaire, à effectuer une transformation, puis à la charger dans la table cible à l’aide de la commande suivante :

INSERT INTO [<columnstore index>]
SELECT col1 /* include actual list of columns in place of col1*/
FROM [<Staging Table>]

Cette commande charge les données dans l’index columnstore de manière similaire à bcp ou à insérer en bloc, mais dans un seul lot. Si le nombre de lignes dans la table < intermédiaire 102400, les lignes sont chargées dans un rowgroup delta sinon les lignes sont directement chargées dans un rowgroup compressé. Limitation importante : cette opération INSERT était à thread unique. Pour charger des données en parallèle, vous pouviez créer plusieurs tables de mise en lots ou envoyer des instructions INSERT/SELECT sans chevauchement des plages de lignes de la table de mise en lots. Cette limitation disparaît avec SQL Server 2016 (13.x). La commande suivante charge les données de la table intermédiaire en parallèle, mais vous devez spécifier TABLOCK. Cela peut sembler contradictoire avec ce qui a été dit précédemment concernant le chargement en masse, mais la principale différence réside dans le fait que la charge de données en parallèle de la table de mise en lots est exécutée dans le cadre de la même transaction.

INSERT INTO [<columnstore index>] WITH (TABLOCK)
SELECT col1 /* include actual list of columns in place of col1*/
FROM [<Staging Table>]

Les optimisations suivantes sont disponibles lors du chargement dans un index columnstore cluster à partir de la table intermédiaire :

  • Optimisation des journaux : réduction de la journalisation lorsque les données sont chargées dans un rowgroup compressé.
  • Optimisation du verrouillage : lors du chargement de données dans un rowgroup compressé, le verrou X sur rowgroup est acquis. Toutefois, avec le rowgroup delta, un verrou X est acquis au niveau du rowgroup, mais SQL Server verrouille toujours les verrous PAGE/EXTENT, car le verrou X rowgroup ne fait pas partie de la hiérarchie de verrouillage.

Si vous avez un ou plusieurs index non cluster, il n’existe aucune optimisation de verrouillage ou de journalisation pour l’index lui-même, mais les optimisations sur l’index columnstore cluster comme décrit précédemment sont toujours là.

Qu’est-ce que l’insertion segmentée ?

L’insertion segmentée fait référence à la façon dont des lignes individuelles sont déplacées vers l’index columnstore. Les insertions segmentées utilisent l’instruction INSERT INTO. Avec l’insertion segmentée, toutes les lignes sont placées dans le deltastore. Cette fonction est utile pour les lignes en petit nombre, mais peu pratique pour les chargements volumineux.

INSERT INTO [<table-name>] VALUES ('some value' /*replace with actual set of values*/)

Remarque

Les threads simultanés qui utilisent l’instruction INSERT INTO pour insérer des valeurs dans un index columnstore cluster peuvent insérer des lignes dans le même rowgroup deltastore.

Une fois que le rowgroup contient 1 048 576 lignes, le rowgroup delta que nous avons marqué fermé, mais il est toujours disponible pour les requêtes et les opérations de mise à jour/suppression, mais les lignes nouvellement insérées entrent dans un rowgroup deltastore existant ou nouvellement créé. Il existe un tuple Mover (TM) de thread d’arrière-plan qui compresse régulièrement les rowgroups delta fermés toutes les 5 minutes. Vous pouvez appeler explicitement la commande suivante pour compresser le rowgroup delta fermé.

ALTER INDEX [<index-name>] on [<table-name>] REORGANIZE

Si vous souhaitez forcer un rowgroup delta fermé et compressé, vous pouvez exécuter la commande suivante. Vous pouvez exécuter cette commande si vous avez terminé de charger les lignes et ne vous attendez pas à de nouvelles lignes. En fermant et compressant explicitement le rowgroup delta, vous pouvez augmenter la capacité de stockage et améliorer les performances de requête d’analyse. Il est recommandé d’appeler cette commande si vous ne prévoyez pas l’insertion de nouvelles lignes.

ALTER INDEX [<index-name>] on [<table-name>] REORGANIZE with (COMPRESS_ALL_ROW_GROUPS = ON)

Fonctionnement du chargement dans une table partitionnée

Pour les données partitionnés, SQL Server affecte d’abord chaque ligne à une partition, puis effectue des opérations columnstore sur les données de la partition. Chaque partition a ses propres rowgroups et au moins un rowgroup delta.

Étapes suivantes