Utilisation d’indexes columnstore en cluster

Tâches d’utilisation des index columnstore en cluster dans SQL Server.

Pour obtenir une vue d’ensemble des index columnstore, consultez Les index Columnstore décrits.

Pour plus d’informations sur les index columnstore clusterisés, consultez Utilisation des index columnstore clusterisés.

Contenu

• Créer un index Columnstore clusterisé

• Supprimer une index Columnstore clusterisé

• Charger des données dans un index Columnstore clusterisé

• Modifier des données dans un index Columnstore clusteré

• Reconstruire un index Columnstore cluster

• Réorganiser un index Columnstore regroupé

Créer un index columnstore clusterisé

Pour créer un index columnstore clusterisé, commencez par créer une table rowstore sous forme de tas ou d'index clusterisé, puis utilisez l’instruction CREATE CLUSTERED COLUMNSTORE INDEX (Transact-SQL) pour convertir la table en index columnstore clusterisé. Si vous souhaitez que l'index columnstore regroupé porte le même nom que l'index regroupé, utilisez l'option DROP_EXISTING.

Cet exemple crée une table sous forme de tas, puis la convertit en un index columnstore clusterisé nommé cci_Simple. Cela modifie le stockage de l’ensemble de la table de rowstore en columnstore.

CREATE TABLE T1(
    ProductKey [int] NOT NULL, 
    OrderDateKey [int] NOT NULL, 
    DueDateKey [int] NOT NULL, 
    ShipDateKey [int] NOT NULL);
GO
CREATE CLUSTERED COLUMNSTORE INDEX cci_T1 ON T1;
GO

Pour plus d’exemples, consultez la section Exemples dans CREATE CLUSTERED COLUMNSTORE INDEX (Transact-SQL).

Supprimer un index Columnstore regroupé

Utilisez l’instruction DROP INDEX (Transact-SQL) pour supprimer un index columnstore cluster. Cette opération supprime l’index et convertit la table columnstore en tas rowstore.

Charger les données dans un index Columnstore groupé

Vous pouvez ajouter des données à un index columnstore cluster existant à l’aide de l’une des méthodes de chargement standard. Par exemple, l’outil de chargement en bloc bcp, Integration Services et INSERT ... SELECT peuvent tous charger des données dans un index de données columnstore regroupées à cluster.

Les index columnstore en cluster tirent parti du deltastore pour empêcher la fragmentation des segments de colonne dans le columnstore.

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

Scénarios de chargement Deltastore

Les lignes s’accumulent dans le deltastore jusqu’à ce que le nombre de lignes soit le nombre maximal de lignes autorisées pour un rowgroup. Lorsque le deltastore contient le nombre maximal de lignes par rowgroup, SQL Server marque le rowgroup comme « CLOSED ». Un processus en arrière-plan, appelé « tuple-mover », recherche le rowgroup CLOSED et se déplace dans le columnstore, où le rowgroup est compressé en segments de colonne et les segments de colonne sont stockés dans le columnstore.

Pour chaque index columnstore clusterisé, il peut y avoir plusieurs deltastores.

• Si un deltastore est verrouillé, SQL Server tente d’obtenir un verrou sur un autre deltastore. S’il n’existe aucun deltastore disponible, SQL Server crée un deltastore.

• Pour une table partitionnée, il peut y avoir un ou plusieurs deltastores pour chaque partition.

Pour les index columnstore en cluster uniquement, les scénarios suivants décrivent quand les lignes chargées vont directement dans le columnstore ou quand elles vont dans le deltastore.

Dans l'exemple, chaque groupe de lignes peut avoir de 102 400 à 1 048 576 lignes.

L’exemple suivant montre les résultats du chargement de 1 048 577 lignes dans une partition. Les résultats indiquent qu'il y a un groupe de lignes COMPRESSÉ dans le columnstore (en tant que segments de colonne compressés) et 1 ligne dans le deltastore.

SELECT * FROM sys.column_store_row_groups;

Modifier des données dans un index Columnstore cluster

Les index columnstore en cluster prennent en charge les opérations DML d’insertion, de mise à jour et de suppression.

Utilisez INSERT (Transact-SQL) pour insérer une ligne. La ligne sera ajoutée au deltastore.

Utilisez DELETE (Transact-SQL) pour supprimer une ligne.

• Si la ligne se trouve dans le columnstore, SQL Server marque la ligne comme étant supprimée logiquement, mais ne récupère pas le stockage physique de la ligne tant que l’index n’est pas reconstruit.

• Si la ligne se trouve dans le deltastore, SQL Server supprime logiquement et physiquement la ligne.

Utilisez UPDATE (Transact-SQL) pour mettre à jour une ligne.

• Si la ligne se trouve dans le columnstore, SQL Server marque la ligne comme étant supprimée logiquement, puis insère la ligne mise à jour dans le deltastore.

• Si la ligne se trouve dans le deltastore, SQL Server met à jour la ligne dans le deltastore.

Reconstruire un index columnstore cluster

Utilisez CREATE CLUSTERED COLUMNSTORE INDEX (Transact-SQL) ou ALTER INDEX (Transact-SQL) pour effectuer une reconstruction complète d’un index columnstore cluster existant. En outre, vous pouvez utiliser ALTER INDEX ... REBUILD pour reconstruire une partition spécifique.

Reconstruire le processus

Pour reconstruire l'index columnstore groupé, SQL Server :

• Acquiert un verrou exclusif sur la table ou la partition pendant la reconstruction. Les données sont « hors connexion » et indisponibles pendant la reconstruction.

• Défragmente le columnstore en supprimant physiquement les lignes qui ont été supprimées logiquement de la table ; les octets supprimés sont récupérés sur le support physique.

• Fusionne les données du rowstore dans le deltastore avec les données du columnstore avant de reconstruire l’index. Une fois la reconstruction terminée, toutes les données sont stockées au format columnstore et le deltastore est vide.

• Compresse à nouveau toutes les données dans le columnstore. Deux copies de l’index columnstore existent pendant la reconstruction. Une fois la reconstruction terminée, SQL Server supprime l’index columnstore d’origine.

Recommandations pour la reconstruction d’un index Columnstore cluster

La reconstruction d'un index columnstore clusterisé est utile pour supprimer la fragmentation et déplacer toutes les lignes dans le columnstore. Nous avons les recommandations suivantes :

• Reconstruire une partition plutôt que la table entière.

  1. La reconstruction de la table entière prend beaucoup de temps si l’index est volumineux et nécessite suffisamment d’espace disque pour stocker une copie supplémentaire de l’index pendant la reconstruction. En règle générale, il est nécessaire de reconstruire la partition la plus récente utilisée.

  2. Pour les tables partitionnée, vous n’avez pas besoin de reconstruire l’index columnstore entier, car la fragmentation est susceptible de se produire uniquement dans les partitions qui ont été modifiées récemment. Les tables de faits et les tables de dimension volumineuses sont généralement partitionnées pour effectuer des opérations de sauvegarde et de gestion sur des segments de la table.

• Reconstruisez une partition après des opérations DML lourdes.

  La reconstruction d’une partition défragmente la partition et réduit le stockage sur disque. La reconstruction du columnstore supprimera toutes les lignes marquées pour suppression et déplacera toutes les lignes du deltastore vers le columnstore.

• Regénérer une partition après le chargement des données.

  Cela garantit que toutes les données sont stockées dans le columnstore. Si plusieurs charges se produisent en même temps, chaque partition peut avoir plusieurs deltastores. La reconstruction déplace toutes les lignes deltastore dans le columnstore.

Réorganiser un index de colonnes stockées regroupé

La réorganisation d'un index columnstore en cluster déplace tous les rowgroups fermés dans le columnstore. Pour effectuer une réorganisation, utilisez ALTER INDEX (Transact-SQL) avec l’option REORGANIZE.

La réorganisation n’est pas nécessaire pour déplacer des rowgroups CLOSED dans le columnstore. Le processus tuple-mover trouvera finalement tous les rowgroups FERMÉs et les déplace. Toutefois, le tuple-mover est monothread et risque de ne pas déplacer les rowgroups suffisamment rapidement pour votre charge de travail.

Recommandations pour la réorganisation

Quand faut-il réorganiser un index colonne magasin regroupé ?

• Réorganisez un index de magasin de colonnes en cluster après un ou plusieurs chargements de données pour obtenir des avantages de performances de requête aussi rapidement que possible. La réorganisation nécessite initialement des ressources processeur supplémentaires pour compresser les données, ce qui peut ralentir les performances globales du système. Toutefois, dès que les données sont compressées, les performances des requêtes peuvent améliorer.