Nouveautés des index columnstore
S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)
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) ajoute ces nouvelles fonctionnalités. Pour plus d'informations, consultez Nouveautés de SQL Server 2022 (16.x).
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 et Réglage des performances avec un index columnstore en cluster ordonné.
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.
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 ligneLes 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
etAVG
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 clauseGROUP 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
etCLR
- Agrégats de fenêtre définis par l’utilisateur :
CHECKSUM_AGG
,STDEV
,STDEVP
,VAR
,VARP
etGROUPING
- Fonctions analytiques d’agrégation de fenêtre :
LAG
,LEAD
,FIRST_VALUE
,LAST_VALUE
,PERCENTILE_CONT
,PERCENTILE_DISC
,CUME_DIST
etPERCENT_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 :
Ces DMV basées sur OLTP en mémoire contiennent des mises à jour pour columnstore :
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
etUPDATE
. 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
- Index columnstore - Guide de conception
- Index columnstore - Conseils en matière de chargement de données
- Index columnstore - Performances des requêtes
- Bien démarrer avec columnstore pour l’analytique opérationnelle en temps réel
- Index columnstore – Data Warehouse
- Optimiser la maintenance des index pour améliorer les performances des requêtes et réduire la consommation des ressources