Partager via

Nouveautés des index columnstore

  • Article

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) Base de données SQL dans Microsoft Fabric

Découvrez les fonctionnalités columnstore disponibles pour chaque version de SQL Server et les dernières versions de SQL Database, Azure Synapse Analytics et Analytics Platform System (PDW).

Synthèse des fonctionnalités pour les versions du produit

Ce tableau récapitule les principales fonctionnalités des index columnstore et des produits dans lesquels ils sont disponibles.

Fonctionnalité d’index columnstore SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x)1 SQL Server 2017 (14.x) SQL Server 2019 (15.x) SQL Server 2022 (16.x) Base de données SQL1 Pool SQL dédié Azure Synapse Analytics
Exécution en mode batch pour les requêtes multithread2 Oui oui oui oui oui oui oui Oui
Exécution en mode batch pour les requêtes monothread Oui oui oui oui oui Oui
Option de compression de l’archivage Oui oui oui oui oui oui Oui
Isolement de capture instantanée et isolement de capture instantanée Read Committed Oui oui oui oui oui Oui
Spécifier l’index columnstore lors de la création d’une table Oui oui oui oui oui Oui
Always On prend en charge les index columnstore Oui oui oui oui oui oui oui Oui
Le secondaire accessible en lecture Always On prend en charge les index columnstore non-cluster en lecture seule Oui oui oui oui oui oui oui Oui
Le secondaire accessible en lecture Always On prend en charge les index columnstore actualisables Oui oui oui Oui
Index columnstore non-cluster en lecture seule sur segment de mémoire ou arbre B (B-tree) Oui Oui oui 3 oui 3 oui 3 oui 3 oui 3 oui 3
Index columnstore non-cluster actualisable sur segment de mémoire ou arbre B (B-tree) Oui oui oui oui oui Oui
Index B-tree supplémentaires autorisés sur un segment de mémoire ou arbre B (B-tree) ayant un index columnstore non-cluster Oui oui oui oui oui oui oui Oui
Index columnstore cluster actualisable Oui oui oui oui oui oui Oui
Index B-tree sur un index columnstore cluster Oui oui oui oui oui Oui
Index columnstore sur une table optimisée en mémoire Oui oui oui oui oui Oui
Définition d’index columnstore non-cluster prenant en charge l’utilisation d’une condition filtrée Oui oui oui oui oui Oui
Option de temporisation de la compression pour les index columnstore dans CREATE TABLE et ALTER TABLE Oui oui oui oui oui Oui
Prise en charge du type nvarchar(max) Oui oui oui Oui non 4
Un index columnstore peut avoir une colonne calculée non persistante Oui oui Oui
Prise en charge de la fusion en arrière-plan du moteur de tuple Oui oui oui Oui
Index columnstore en cluster ordonnés Oui oui Oui
Index columnstore non cluster ordonnés Oui

1 Pour SQL Database, les index columnstore sont disponibles dans les niveaux DTU Premium d'Azure SQL Database, les niveaux DTU Standard (S3 et supérieur) et tous les niveaux vCore. Pour SQL Server 2016 (13.x) SP1 et versions ultérieures, les index columnstore sont disponibles dans toutes les éditions. Pour SQL Server 2016 (13.x) (antérieur à SP1), les index columnstore ne sont disponibles que dans l'édition Entreprise.

2 Le degré de parallélisme (DOP) pour les opérations en mode batch est limité à 2 pour SQL Server Édition Standard, et à 1 pour les éditions SQL Server Web et Express. Cette limitation fait référence aux index columnstore créés sur des tables basées sur des disques et des tables à mémoire optimisée.

3 Pour créer un index columnstore non-cluster en lecture seule, stockez l'index sur un groupe de fichiers en lecture seule.

4 Non pris en charge dans les pools SQL dédiés, mais est pris en charge dans le pool SQL serverless.

SQL Server 2022 (16.x)

SQL Server 2022 (16.x) a ajouté ces fonctionnalités.

  • Les index columnstore en cluster ordonnés améliorent les performances des requêtes en fonction des prédicats de colonne ordonnés. Les index columnstore ordonnés peuvent améliorer les performances en ignorant complètement les segments de données. Cela peut réduire considérablement les E/S nécessaires pour effectuer des requêtes sur les données columnstore. Pour en savoir plus, consultez l'élimination des segments. Les index columnstore en cluster ordonnés sont disponibles dans SQL Server 2022 (16.x). Pour plus d’informations, consultez CREATE COLUMNSTORE INDEX and Performance tuning with ordered clustered columnstore indexes.

  • Le pushdown de prédicat avec l'élimination de rowgroup columnstore en cluster des chaînes utilise des valeurs de limites pour optimiser les recherches de chaînes. Tous les index columnstore bénéficient de l’élimination améliorée des segments par type de données. À compter de SQL Server 2022 (16.x), les fonctionnalités d'élimination des segments s'étendent aux types de données chaîne, binaire, identificateur unique et datetimeoffset pour une mise à l'échelle supérieure à deux. Auparavant, l'élimination des segments columnstore ne s'appliquait qu'aux types de données numériques, date et heure, ainsi qu'au type de données datetimeoffset dont l'échelle est inférieure ou égale à deux. Après la mise à niveau vers une version de SQL Server qui prend en charge l'élimination de segment min/max de chaîne (SQL Server 2022 (16.x) et versions ultérieures), l'index columnstore ne bénéficiera pas de cette fonctionnalité tant qu'il n'aura pas été reconstruit à l'aide d'un REBUILD ou d'un DROP/CREATE.

  • Pour plus d’informations sur les fonctionnalités ajoutées, consultez Nouveautés de SQL Server 2022.

SQL Server 2019 (15.x)

SQL Server 2019 (15.x) ajoute ces nouvelles fonctionnalités :

Fonctionnelle

À partir de SQL Server 2019 (15.x), le moteur de tuple est aidé par une tâche de fusion en arrière-plan qui compresse automatiquement les rowgroups delta OPEN plus petits qui existent depuis un certain temps, tel que déterminé par un seuil interne, ou qui fusionne les rowgroups COMPRESSED à partir desquels un grand nombre de lignes a été supprimé. Auparavant, une opération de réorganisation d’index était nécessaire pour fusionner les rowgroups avec des données partiellement supprimées. Cela améliore la qualité de l’index columnstore dans le temps.

SQL Server 2017 (14.x)

SQL Server 2017 (14.x) ajoute ces nouvelles fonctionnalités.

Fonctionnelle

  • SQL Server 2017 (14.x) prend en charge les colonnes calculées non persistantes dans les index columnstore en cluster. Les colonnes calculées non persistantes ne sont pas prises en charge dans les index columnstore cluster. Vous ne pouvez pas créer un index non-cluster sur un index columnstore qui comporte une colonne calculée.

SQL Server 2016 (13.x)

SQL Server 2016 (13.x) ajoute des améliorations clés pour améliorer les performances et la flexibilité des index columnstore. Ces améliorations touchent les scénarios d’entreposage de données et permettent l’analytique opérationnelle en temps réel.

Fonctionnelle

  • Une table rowstore peut avoir un index columnstore non cluster actualisable. Auparavant, l’index columnstore non cluster était en lecture seule.

  • Pour définir des index columnstore non-cluster, vous pouvez utiliser une condition filtrée. Pour minimiser l’impact sur les performances de l’ajout d’un index columnstore sur une table OLTP, utilisez une condition filtrée pour créer un index columnstore non cluster uniquement sur les données brutes de votre charge de travail opérationnelle.

  • Une table en mémoire peut avoir un index columnstore. Vous pouvez le créer lors de la création de la table ou l'ajouter ultérieurement avec ALTER TABLE (Transact-SQL). Auparavant, une table sur disque pouvait avoir un index columnstore.

  • Un index columnstore cluster peut avoir un ou plusieurs index rowstore non cluster. Auparavant, l’index columnstore ne prenait pas en charge les index non cluster. SQL Server gère automatiquement les index non cluster pour les opérations DML.

  • Prise en charge des clés primaires et étrangères à l’aide d’un index B-tree pour appliquer ces contraintes sur un index columnstore cluster.

  • Les index columnstore ont une option de délai de compression qui réduit l’impact de la charge de travail transactionnelle sur l’analytique opérationnelle en temps réel. Cette option permet de modifier fréquemment les lignes pour les stabiliser avant leur compression dans le columnstore. Pour plus d'informations, consultez CREATE COLUMNSTORE INDEX (Transact-SQL) et Bien démarrer avec columnstore pour l'analytique opérationnelle en temps réel.

Performances pour les niveaux de compatibilité de base de données 120 ou 130

  • Les index columnstore prennent en charge l’isolement de capture instantanée Read Committed (RCSI) et l’isolement de capture instantanée (SI). Cela permet d’effectuer des requêtes analytiques cohérentes d’un point de vue transactionnel sans verrou.

  • L’index columnstore prend en charge la défragmentation de l’index en éliminant les lignes supprimées sans nécessité de reconstruire explicitement l’index. L’instruction ALTER INDEX ... REORGANIZE élimine les lignes supprimées de l’index columnstore selon une stratégie définie en interne, comme une opération en ligne

  • Les index columnstore sont accessibles sur un réplica secondaire lisible Always On. Vous pouvez améliorer les performances de l’analytique opérationnelle en déchargeant les requêtes analytiques sur un réplica secondaire Always On.

  • Le pushdown d’agrégation calcule les fonctions d’agrégation MIN, MAX, SUM, COUNT et AVG pendant les analyses de table quand le type de données n’utilise pas plus de 8 octets et n’a pas un type de données chaîne. Le pushdown d’agrégation est pris en charge avec ou sans la clause GROUP BY pour les index columnstore cluster et non-cluster. Sur SQL Server, cette amélioration est réservée à l'édition Enterprise.

  • Le pushdown de prédicats de chaîne accélère les requêtes qui comparent des chaînes de type VARCHAR/CHAR ou NVARCHAR/NCHAR. Cela s’applique aux opérateurs de comparaison courants, et inclut des opérateurs comme LIKE qui utilisent des filtres bitmap. Ceci fonctionne avec tous les classements pris en charge. Sur SQL Server, cette amélioration est réservée à l'édition Enterprise.

  • Améliorations des opérations en mode batch en tirant parti des fonctionnalités matérielles basées sur des vecteurs. Le moteur de base de données détecte le niveau de prise en charge du processeur pour les extensions matérielles AVX 2 (Advanced Vector Extensions) et SSE 4 (Streaming SIMD Extensions 4), et les utilise si elles sont prises en charge. Sur SQL Server, cette amélioration est réservée à l'édition Enterprise.

Performances pour le niveau de compatibilité de base de données 130

  • Nouvelle prise en charge de l’exécution en mode batch pour les requêtes utilisant l’une des opérations suivantes :

    • SORT
    • Agrégats avec plusieurs fonctions distinctes. Quelques exemples : COUNT/COUNT, AVG/SUM, CHECKSUM_AGG, STDEV/STDEVP
    • Fonctions d’agrégation de fenêtre : COUNT, COUNT_BIG, SUM, AVG, MIN, MAX et CLR
    • Agrégats de fenêtre définis par l’utilisateur : CHECKSUM_AGG, STDEV, STDEVP, VAR, VARP et GROUPING
    • Fonctions analytiques d’agrégation de fenêtre : LAG, LEAD, FIRST_VALUE, LAST_VALUE, PERCENTILE_CONT, PERCENTILE_DISC, CUME_DIST et PERCENT_RANK

  • Les requêtes à thread unique s’exécutant sous MAXDOP 1 ou avec un plan de requête série s’exécutent en mode batch. Auparavant, seules les requêtes multithread s'exécutaient en mode batch.

  • Les requêtes de table à mémoire optimisée peuvent avoir des plans parallèles en mode SQL InterOp lors de l’accès aux données dans un index rowstore ou un index columnstore.

Prise en charge

Ces vues système sont nouvelles pour columnstore :

sys.column_store_row_groups (Transact-SQL)

sys.dm_column_store_object_pool (Transact-SQL)

sys.dm_db_column_store_row_group_operational_stats (Transact-SQL)

sys.dm_db_column_store_row_group_physical_stats (Transact-SQL)

sys.dm_db_index_operational_stats (Transact-SQL)

sys.dm_db_index_physical_stats (Transact-SQL)

sys.internal_partitions (Transact-SQL)

Ces DMV basées sur OLTP en mémoire contiennent des mises à jour pour columnstore :

sys.dm_db_xtp_hash_index_stats (Transact-SQL)

sys.dm_db_xtp_index_stats (Transact-SQL)

sys.dm_db_xtp_memory_consumers (Transact-SQL)

sys.dm_db_xtp_nonclustered_index_stats (Transact-SQL)

sys.dm_db_xtp_object_stats (Transact-SQL)

sys.dm_db_xtp_table_memory_stats (Transact-SQL)

Limites

  • Pour les tables en mémoire, un index columnstore doit inclure toutes les colonnes. L’index columnstore ne peut pas avoir de condition filtrée.
  • Pour les tables en mémoire, les requêtes sur les index columnstore s’exécutent uniquement en mode InterOP et non en mode natif en mémoire. L’exécution en parallèle est prise en charge.

Problèmes connus

S'applique à : SQL Server, Azure SQL Database, Azure SQL Managed Instance, pools SQL dédiés Azure Synapse Analytics

  • Actuellement, les colonnes LOB (varbinary(max), varchar(max) et nvarchar(max)) dans les segments columnstore compressés ne sont pas affectées par DBCC SHRINKDATABASE et DBCC SHRINKFILE.

SQL Server 2014 (12.x)

SQL Server 2014 (12.x) a introduit l'index columnstore en cluster en tant que format de stockage principal. Cela autorise des charges régulières ainsi que des opérations de mise à jour, de suppression et d’insertion.

  • La table peut utiliser un index columnstore en cluster en tant que stockage de table primaire. Aucun autre index n'est autorisé sur la table mais, l'index columnstore en cluster étant actualisable, vous pouvez effectuer des chargements réguliers et apporter des modifications à des lignes individuelles.
  • L'index columnstore non-cluster conserve les mêmes fonctionnalités que dans SQL Server 2012 (11.x), à l'exception des opérateurs supplémentaires qui peuvent désormais être exécutés en mode batch. Il n’est toujours pas actualisable, sauf par reconstruction et par basculement de partition. L’index columnstore non cluster est pris en charge uniquement sur les tables sur disque, pas sur les tables en mémoire.
  • Les index columnstore en cluster et non-cluster disposent d'une option de compression d'archivage qui compresse davantage les données. L’option d’archivage est utile pour réduire la taille des données en mémoire et sur disque, mais elle ralentit les performances des requêtes. Elle fonctionne bien pour les données rarement utilisées.
  • Les index columnstore cluster et non cluster fonctionnent d’une manière très similaire. Ils utilisent le même format de stockage en colonnes, le même moteur de traitement des requêtes et le même jeu de vues de gestion dynamique. La différence a trait aux types d’index (primaire et secondaire), et au fait que l’index columnstore non cluster est en lecture seule.
  • Les opérateurs suivants s’exécutent en mode batch pour les requêtes multithread : scan, filter, project, join, group by et union all.

SQL Server 2012 (11.x)

SQL Server 2012 (11.x) a introduit l'index columnstore non-cluster en tant qu'autre type d'index sur les tables rowstore, et le traitement par lots pour les requêtes sur des données columnstore.

  • Une table rowstore peut avoir un index columnstore non cluster.
  • L’index columnstore est en lecture seule. Après avoir créé l’index columnstore, vous ne pouvez pas mettre à jour la table à l’aide d’opérations INSERT, DELETE et UPDATE. Pour effectuer ces opérations, vous devez supprimer l’index, mettre à jour la table, puis regénérer l’index columnstore. Vous pouvez charger des données supplémentaires dans la table à l’aide d’un basculement de partition. L’avantage du basculement de partition est que vous pouvez charger des données sans devoir supprimer et reconstruire l’index columnstore.
  • L'index columnstore requiert toujours un stockage supplémentaire, généralement supérieur de 10 % à celui du rowstore, car il stocke une copie des données.
  • Le traitement par lots offre des performances de requête au moins deux fois supérieures, mais il est disponible uniquement pour l’exécution de requêtes parallèles.

Contenu connexe