Utilisation d'index columnstore cluster
Tâches d’utilisation d’index columnstore en cluster dans SQL Server.
Pour une présentation des index columnstore, consultez Columnstore Indexes Described.
Pour plus d'informations sur les index columnstore cluster, consultez Using Clustered Columnstore Indexes.
Contenu
Créer un index columnstore cluster
Pour créer un index columnstore cluster, créez d’abord une table rowstore sous forme de tas ou d’index cluster, puis utilisez l’instruction CREATE CLUSTERED COLUMNSTORE INDEX (Transact-SQL) pour convertir la table en index columnstore cluster. Si vous souhaitez que l'index columnstore cluster ait le même nom que l'index cluster, utilisez l'option DROP_EXISTING.
Cet exemple crée une table en tant que segment puis la convertit en un index columnstore cluster nommé cci_Simple. Cela modifie le stockage de la table entière qui change 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 cluster
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 un segment de mémoire rowstore.
Charger des données dans un index columnstore cluster
Ajoutez 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 peut tous charger des données dans un index columnstore cluster.
Les index columnstore cluster tirent parti du deltastore pour éviter la fragmentation de segments de colonne dans le columnstore.
Chargement dans une table partitionnée
Pour les données partitionnées, SQL Server affecte d'abord chaque ligne à une partition, puis effectue les opérations de columnstore sur les données dans la partition. Chaque partition a ses propres rowgroups et au moins un deltastore.
Scénarios de chargement de deltastore
Les lignes s'accumulent dans le deltastore tant que le nombre de lignes n'atteint pas 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 « FERMÉ ». 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 cluster il peut y avoir plusieurs deltastores.
Si un deltastore est verrouillé, SQL Server essaiera d’obtenir un verrou sur un autre deltastore. S’il n’y a pas de deltastores disponibles, SQL Server créez un deltastore.
Pour une table partitionnée, il peut y avoir un ou plusieurs deltastores pour chaque partition.
Pour les index columnstore cluster uniquement, les scénarios suivants décrivent à quel moment les lignes chargées sont directement placées dans le columnstore ou dans le deltastore.
Dans l'exemple, chaque rowgroup peut avoir 102 400-1 048 576 lignes par rowgroup.
| Lignes à charger en masse | Lignes ajoutées au columnstore | Lignes ajoutées au deltastore |
|---|---|---|
| 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 partition. Les résultats indiquent un rowgroup COMPRESSÉ dans le columnstore (comme segments de colonne compressés), et 1 ligne dans le deltastore.
SELECT * FROM sys.column_store_row_groups;
Changer des données dans un index columnstore cluster
Les index columnstore cluster prennent en charge les opérations DML d'insertion, mise à jour et 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 logiquement supprimée, 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 logiquement supprimée, 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 cluster columnstore
Utilisez CREATE CLUSTERED COLUMNSTORE INDEX (Transact-SQL) ou ALTER INDEX (Transact-SQL) pour effectuer une reconstruction complète d’un index columnstore en cluster existant. En outre, vous pouvez utiliser ALTER INDEX ... REBUILD pour reconstruire une partition spécifique.
Processus de reconstruction
Pour reconstruire un index columnstore cluster, SQL Server :
Acquiert un verrou exclusif sur la table ou la partition lorsque la reconstruction se produit. Les données sont « hors connexion » et indisponibles pendant la reconstruction.
Défragmente le columnstore en supprimant physiquement les lignes qui ont été logiquement supprimées de la table ; les octets supprimés sont récupérés sur le support physique.
Fusionne les données rowstore dans le deltastore, avec les données du columnstore avant de reconstruire l'index. Lorsque la reconstruction est terminée, toutes les données sont stockées au format columnstore, puis le deltastore est vide.
Recompresse toutes les données dans le columnstore. Il existe deux copies de l'index columnstore pendant la reconstruction. Une fois la reconstruction terminée, SQL Server supprime l’index columnstore d’origine.
Recommandations pour reconstruire un index columnstore cluster
Reconstruire un index columnstore cluster est utile pour supprimer une fragmentation, et pour déplacer toutes les lignes dans le columnstore. Tenez compte des recommandations suivantes :
Reconstruisez une partition au lieu de la table entière.
Reconstruire un index columnstore cluster entier prend beaucoup de temps si l'index est volumineux, et cela nécessite suffisamment d'espace disque pour stocker une copie supplémentaire de l'index pendant la reconstruction. Généralement il est nécessaire de reconstruire que la dernière partition utilisée.
Pour les tables partitionnées, vous n'avez pas besoin de reconstruire l'index columnstore entier, car la fragmentation se produira probablement uniquement dans les partitions modifiées récemment. Les tables de faits et de dimension volumineuses sont généralement partitionnées pour exécuter des opérations de sauvegarde et de gestion sur les segments de la table.
Reconstruisez une partition après des opérations DML lourdes.
Reconstruire une partition la défragmentera et réduira la mémoire sur disque. La reconstruction supprimera toutes les lignes du columnstore marquées pour suppression, et déplacera toutes les lignes du deltastore dans le columnstore.
Reconstruisez une partition après le chargement des données.
Cela garantit que toutes les données sont stockées dans le columnstore. Si plusieurs chargements se produisent simultanément, chaque partition peut disposer de plusieurs deltastores. La reconstruction déplacera toutes les lignes du deltastore dans le columnstore.
Réorganiser un index cluster columnstore
La réorganisation des index columnstore 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 requise pour déplacer les rowgroups fermés dans le columnstore. Le processus de déplacement de tuple recherche tous les rowgroups CLOSED et les déplace. Toutefois, le processus de déplacement de tuple est monothread et peut ne pas déplacer les rowgroups suffisamment rapidement pour votre charge de travail.
Recommandations pour la réorganisation
Quand réorganiser un index cluster columnstore :
- Réorganisez un index columnstore cluster après un ou plusieurs chargements de données pour obtenir des gains de performance des requêtes 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 s'améliorent.