Columnstore Indexes DescribedColumnstore Indexes Described

Le SQL ServerSQL Server les index columnstore en mémoire stocke et gère les données à l’aide de stockage de données en colonnes et de traitement de requête fondée sur une colonne.The SQL ServerSQL Serverin-memory columnstore index stores and manages data by using column-based data storage and column-based query processing. Les index columnstore fonctionnent bien pour les charges de travail de stockage de données qui effectuent principalement des chargements en masse et des requêtes en lecture seule.Columnstore indexes work well for data warehousing workloads that primarily perform bulk loads and read-only queries. Utilisez l'index columnstore pour atteindre des gains de performances des requêtes pouvant être multipliés par 10 par rapport au stockage orienté lignes traditionnel, et une compression de données multipliée par 7 par rapport à la taille des données non compressées.Use the columnstore index to achieve up to 10x query performance gains over traditional row-oriented storage, and up to 7x data compression over the uncompressed data size.

Note

Nous considérons l'index columnstore cluster comme étant la norme pour le stockage de grandes tables de faits de stockage des données, et nous pensons qu'il va être utilisé dans la plupart des scénarios de stockage des données.We view the clustered columnstore index as the standard for storing large data warehousing fact tables, and expect it will be used in most data warehousing scenarios. Étant donné que l'index columnstore cluster est modifiable, votre charge de travail peut exécuter un grand nombre d'insertions, mises à jour, et suppressions.Since the clustered columnstore index is updateable, your workload can perform a large number of insert, update, and delete operations.

SommaireContents

Principes de baseBasics

Un columnstore index est une technologie permettant de stocker, extraire et gérer les données à l'aide d'un format de données en colonnes, appelé columnstore.A columnstore index is a technology for storing, retrieving and managing data by using a columnar data format, called a columnstore. SQL ServerSQL Server prend en charge les index columnstore en cluster et non-cluster. supports both clustered and nonclustered columnstore indexes. Les deux utilisent la même technologie columnstore en mémoire, mais sont différents en ce qui concerne leur but et les fonctionnalités qu'ils prennent en charge.Both use the same in-memory columnstore technology, but they do have differences in purpose and in features they support.

AvantagesBenefits

Les index columnstore fonctionnent bien pour la plupart des requêtes en lecture seule qui effectuent des analyses sur des ensembles de données volumineux.Columnstore indexes work well for mostly read-only queries that perform analysis on large data sets. Bien souvent, il s'agit de requêtes destinées à des charges de travail de stockage de données.Often, these are queries for data warehousing workloads. Les index columnstore offrent des gains de performances importants pour les requêtes qui utilisent des analyses de table complètes, et ne conviennent pas pour les requêtes qui effectuent des opérations de recherche de données, notamment qui recherchent une valeur particulière.Columnstore indexes give high performance gains for queries that use full table scans, and are not well-suited for queries that seek into the data, searching for a particular value.

Avantages de l'index columnstore :Columnstore Index benefits:

  • Les colonnes contenant souvent des données similaires, les taux de compression sont élevés.Columns often have similar data, which results in high compression rates.

  • Les taux de compression élevés améliorent les performances des requêtes en utilisant un plus faible encombrement en mémoire.High compression rates improve query performance by using a smaller in-memory footprint. En conséquence, les performances des requêtes sont améliorées, car SQL ServerSQL Server peut exécuter davantage d'opérations de requêtes et sur les données en mémoire.In turn, query performance can improve because SQL ServerSQL Server can perform more query and data operations in-memory.

  • Un nouveau mécanisme d'exécution de requête, appelé « exécution en mode batch » a été ajouté à SQL Server pour réduire considérablement l'utilisation de l'UC.A new query execution mechanism called batch-mode execution has been added to SQL Server that reduces CPU usage by a large amount. L'exécution en mode batch est étroitement intégrée avec (et optimisée pour) le format de stockage columnstore.Batch-mode execution is closely integrated with, and optimized around, the columnstore storage format. L'exécution en mode batch est parfois appelée « exécution vectorielle ou vectorisée ».Batch-mode execution is sometimes known as vector-based or vectorized execution.

  • Les requêtes sélectionnent souvent seulement quelques colonnes d'une table, ce qui réduit les E/S totales à partir du support physique.Queries often select only a few columns from a table, which reduces total I/O from the physical media.

Versions de columnstoreColumnstore Versions

SQL Server 2012, SQL Server 2012 Parallel Data Warehouse, et SQL Server 2014 utilisent tous des index columnstore pour accélérer les requêtes d'entrepôts de données communs.SQL Server 2012, SQL Server 2012 Parallel Data Warehouse, and SQL Server 2014 all use columnstore indexes to accelerate common data warehouse queries. SQL Server 2012 introduit deux nouvelles fonctionnalités : un index columnstore non cluster et une fonction d'exécution de requête vectorielle qui traite les données dans des unités appelées « lots ».SQL Server 2012 introduced two new features: a nonclustered columnstore index and a vector-based query execution capability that processes data in units called "batches." SQL Server 2014 contient les fonctionnalités de SQL Server 2012 plus des index columnstore cluster pouvant être mis à jour.SQL Server 2014 has the features of SQL Server 2012 plus updateable clustered columnstore indexes.

Caractéristiques clésKey Characteristics

||
|-|
|S'applique à: SQL Server 2014SQL Server 2014 jusqu'à SQL Server 2017SQL Server 2017.Applies to: SQL Server 2014SQL Server 2014 through SQL Server 2017SQL Server 2017.|

Dans SQL ServerSQL Server, un index cluster columnstore :In SQL ServerSQL Server, a clustered columnstore index:

  • Est disponible dans les éditions Enterprise, Developer et Evaluation.Is available in Enterprise, Developer, and Evaluation editions.

  • Peut être mis à jour.Is updateable.

  • Est la méthode de stockage principale de la table entière.Is the primary storage method for the entire table.

  • N'a pas de colonnes clés.Has no key columns. Toutes les colonnes sont des colonnes incluses.All columns are included columns.

  • Est le seul index sur la table.Is the only index on the table. Ne peut pas être associé à d'autres index.It cannot be combined with any other indexes.

  • Peut être configuré pour utiliser columnstore ou la compression d'archivage columnstore.Can be configured to use columnstore or columnstore archival compression.

  • Ne stocke pas physiquement les colonnes dans un ordre de tri.Does not physically store columns in a sorted order. Au lieu de cela, il stocke les données pour améliorer la compression et les performances.Instead, it stores data to improve compression and performance.

||
|-|
|S'applique à: SQL Server 2012SQL Server 2012 jusqu'à SQL Server 2017SQL Server 2017.Applies to: SQL Server 2012SQL Server 2012 through SQL Server 2017SQL Server 2017.|

Dans SQL ServerSQL Server, un index non cluster columnstore :In SQL ServerSQL Server, a nonclustered columnstore index:

  • Peut indexer un sous-ensemble de colonnes dans l'index cluster ou le segment de mémoire.Can index a subset of columns in the clustered index or heap. Par exemple, il peut indexer les colonnes utilisées fréquemment.For example, it can index the frequently used columns.

  • Nécessite un stockage supplémentaire pour stocker une copie des colonnes dans l'index.Requires extra storage to store a copy of the columns in the index.

  • Est mis à jour par la reconstruction de l'index ou le basculement de partitions. Il ne peut pas être mis à jour en utilisant des opérations DML telles que l'insertion, la mise à jour et la suppression.Is updated by rebuilding the index or switching partitions in and out. It is not updateable by using the DML operations such as insert, update, and delete.

  • Peut être associé à d'autres index sur la table.Can be combined with other indexes on the table.

  • Peut être configuré pour utiliser columnstore ou la compression d'archivage columnstore.Can be configured to use columnstore or columnstore archival compression.

  • Ne stocke pas physiquement les colonnes dans un ordre de tri.Does not physically store columns in a sorted order. Au lieu de cela, il stocke les données pour améliorer la compression et les performances.Instead, it stores data to improve compression and performance. Le prétri des données avant de créer l'index columnstore n'est pas requis, mais cela peut améliorer la compression columnstore.Pre-sorting the data before creating the columnstore index is not required, but can improve columnstore compression.

Termes et Concepts clésKey Concepts and Terms

Les termes et concepts clés suivants sont associés aux index columnstore.The following key terms and concepts are associated with columnstore indexes.

columnstore indexcolumnstore index
Un columnstore index est une technologie permettant de stocker, extraire et gérer les données à l'aide d'un format de données en colonnes, appelé columnstore.A columnstore index is a technology for storing, retrieving and managing data by using a columnar data format, called a columnstore. SQL ServerSQL Server prend en charge les index columnstore en cluster et non-cluster. supports both clustered and nonclustered columnstore indexes. Les deux utilisent la même technologie columnstore en mémoire, mais sont différents en ce qui concerne leur but et les fonctionnalités qu'ils prennent en charge.Both use the same in-memory columnstore technology, but they do have differences in purpose and in features they support.

columnstorecolumnstore
Un columnstore représente des données qui sont organisées logiquement sous la forme d’une table avec des lignes et des colonnes, et stockées physiquement dans un format de données selon les colonnes.A columnstore is data that is logically organized as a table with rows and columns, and physically stored in a column-wise data format.

rowstorerowstore
Un rowstore représente des données qui sont organisées logiquement sous la forme d’une table avec des lignes et des colonnes, puis stockées physiquement dans un format de données selon les lignes.A rowstore is data that is logically organized as a table with rows and columns, and then physically stored in a row-wise data format. Il s'agit de la méthode traditionnelle de stockage des données de table relationnelles.This has been the traditional way to store relational table data.

rowgroups et segments de colonnerowgroups and column segments
Pour une haute performance et des taux de compression élevés, l'index columnstore découpe la table en groupes de lignes, appelés rowgroups, puis comprime chaque groupe de lignes selon les colonnes.For high performance and high compression rates, the columnstore index slices the table into groups of rows, called row groups, and then compresses each row group in a column-wise manner. Le nombre de lignes dans le groupe de lignes doit être assez grand pour améliorer le taux de compression, et assez petit tirer parti des opérations en mémoire.The number of rows in the row group must be large enough to improve compression rates, and small enough to benefit from in-memory operations.

groupe de lignesrow group
Un rowgroup est un groupe de lignes qui sont compressées au format columnstore en même temps.A rowgroup is a group of rows that are compressed into columnstore format at the same time.

segment de colonnecolumn segment
Un segment de colonne est une colonne de données au sein d'un rowgroup.A column segment is a column of data from within the rowgroup.

  • Un rowgroup contient généralement le nombre maximal de lignes par rowgroup qui est de 1 048 576 lignes.A rowgroup usually contains the maximum number of rows per rowgroup which is 1,048,576 rows.

  • Chaque rowgroup contient un segment de colonne pour chaque colonne dans la table.Each rowgroup contains one column segment for every column in the table.

  • Chaque segment de colonne est compressé et stocké sur un support physique.Each column segment is compressed together and stored on physical media.

    Column segmentColumn segment

    index columnstore non clusternonclustered columnstore index
    Un nonclustered columnstore index est un index en lecture seule créé sur un index cluster existant ou une table de segments de mémoire.A nonclustered columnstore index is a read-only index created on an existing clustered index or heap table. Il contient une copie d'un sous-ensemble de colonnes, et peut contenir toutes les colonnes de la table.It contains a copy of a subset of columns, up to and including all of the columns in the table.. La table est en lecture seule alors qu'elle contient un index columnstore non cluster.The table is read-only while it contains a non-clustered columnstore index.

    Un index columnstore non cluster permet d'avoir un index columnstore pour exécuter les requêtes d'analyse tout en exécutant des opérations en lecture seule sur la table d'origine.A nonclustered columnstore index provides a way to have a columnstore index for running analysis queries while at the same time performing read-only operations on the original table.

    Index non cluster columnstoreNonclustered columnstore index

    index columnstore clusterclustered columnstore index
    Un clustered columnstore index est le stockage physique de la totalité de la table, et est le seul index de la table.A clustered columnstore index is the physical storage for the entire table and is the only index for the table. L'index cluster peut être mis à jour.The clustered index is updateable. Effectuez des opérations d'insertion, suppression, et mise à jour sur l'index et chargez en masse des données dans l'index.You can perform insert, delete, and update operations on the index and you can bulk load data into the index.

    Clustered Columnstore IndexClustered Columnstore Index

    Pour réduire la fragmentation des segments de colonne et améliorer les performances, l'index columnstore peut stocker des données temporaires dans une table rowstore, appelée un deltastore, plus un arbre B d'ID pour les lignes supprimées.To reduce fragmentation of the column segments and improve performance, the columnstore index might store some data temporarily into a rowstore table, called a deltastore, plus a B-Tree of IDs for deleted rows. Les opérations deltastore sont effectuées en coulisse.The deltastore operations are handled behind the scenes. Pour retourner des résultats de requête corrects, l'index columnstore cluster associe les résultats de columnstore et de deltastore.To return the correct query results, the clustered columnstore index combines query results from both the columnstore and the deltastore.

    deltastoredeltastore
    Utilisé seulement avec des index columnstore cluster, un deltastore est une table rowstore contenant des lignes jusqu'à ce que le nombre de lignes soit suffisamment important pour être inséré dans le columnstore.Used with clustered columnstore indexes only, a deltastore is a rowstore table that stores rows until the number of rows is large enough to be moved into the columnstore. Un deltastore est utilisé avec des index columnstore cluster pour améliorer les performances du chargement et d'autres opérations DML.A deltastore is used with clustered columnstore indexes to improve performance for loading and other DML operations.

    Lors d'un chargement en masse important, la plupart des lignes sont directement placées dans le columnstore sans passer par le deltastore.During a large bulk load, most of the rows go directly to the columnstore without passing through the deltastore. Certaines lignes à la fin du chargement en masse peuvent être en trop petit nombre pour atteindre la taille minimale d'un rowgroup, qui est de 102 400 lignes.Some rows at the end of the bulk load might be too few in number to meet the minimum size of a rowgroup which is 102,400 rows. Dans ce cas, les lignes finales sont placées dans le deltastore plutôt que dans le columnstore.When this happens, the final rows go to the deltastore instead of the columnstore. Pour les petits chargements en masse de taille inférieure à 102 400 lignes, toutes les lignes vont directement au deltastore.For small bulk loads with less than 102,400 rows, all of the rows go directly to the deltastore.

    Lorsque le deltastore atteint le nombre maximal de lignes, il est fermé.When the deltastore reaches the maximum number of rows, it becomes closed. Un processus de déplacement de tuple vérifie les groupes de lignes fermés.A tuple-move process checks for closed row groups. Lorsqu'il trouve un rowgroup fermé, il le compresse et le stocke dans le columnstore.When it finds the closed rowgroup, it compresses it and stores it into the columnstore.

Chargement des donnéesLoading Data

Chargement des données dans un Index Columnstore non clusterLoading Data into a Nonclustered Columnstore Index

Pour charger des données dans un index non cluster, première charger des données dans une table rowstore traditionnelle stockée comme un segment de mémoire ou en cluster d’index et puis créer l’index columnstore non cluster avec CREATE COLUMNSTORE INDEX ().To load data into a nonclustered columnstore index, first load data into a traditional rowstore table stored as a heap or clustered index, and then create the nonclustered columnstore index with CREATE COLUMNSTORE INDEX (Transact-SQL).

Chargement des données dans un index columnstoreLoading data into a columnstore index

Une table avec un index columnstore non cluster est en lecture seule jusqu'à ce que l'index soit désactivé ou supprimé.A table with a nonclustered columnstore index is read-only until the index is dropped or disabled. Pour mettre à jour la table et l'index columnstore non cluster, basculez des partitions. Vous pouvez également désactiver l'index, mettre à jour la table, puis reconstruire l'index.To update the table and the nonclustered columnstore index you can switch partitions in and out. You can also disable the index, update the table, and then rebuild the index.

Pour plus d’informations, consultez Using Nonclustered Columnstore IndexesFor more information see Using Nonclustered Columnstore Indexes

Chargement des données dans un Index cluster ColumnstoreLoading Data into a Clustered Columnstore Index

Chargement dans un index columnstore clusterLoading into a clustered columnstore index

Comme le suggère le diagramme, pour charger des données dans un index cluster columnstore, SQL ServerSQL Server:As the diagram suggests, to load data into a clustered columnstore index, SQL ServerSQL Server:

  1. Insère les rowgroups de taille maximale directement dans le columnstore.Inserts maximum-size rowgroups directly into the columnstore. Lorsque les données sont chargées, SQL ServerSQL Server affecte les lignes de données dans l'ordre premier arrivé premier servi dans un rowgroup ouvert.As the data is loaded, SQL ServerSQL Server assigns the data rows in a first-come first-serve order into an open rowgroup.

  2. Pour chaque rowgroup, après qu'il a atteint la taille maximale, SQL ServerSQL Server :For each rowgroup, after it reaches the maximum size, SQL ServerSQL Server:

    1. Marque le rowgroup comme étant CLOSED.Marks the rowgroup as CLOSED.

    2. Ignore le deltastore.Bypasses the deltastore.

    3. Compresse chaque segment de colonne avec le rowgroup à l'aide de la compression columnstore.Compresses each column segment with the rowgroup with columnstore compression.

    4. Stocke physiquement chaque segment de colonne compressé dans le columnstore.Physically stores each compressed column segment into the columnstore.

  3. Insère les lignes restantes dans le columnstore ou le deltastore comme suit :Inserts the remaining rows into the columnstore or the deltastore as follows:

    1. Si le nombre de lignes répond à l'exigence de nombre de lignes minimal par rowgroup, les lignes sont ajoutées au columnstore.If the number of rows meets the minimum rows per rowgroup requirement, the rows are added to the columnstore.

    2. Si le nombre de lignes est inférieur au nombre de lignes minimal par rowgroup, les lignes sont ajoutées au deltastore.If the number of rows is less than the minimum rows per rowgroup, the rows are added to the deltastore.

    Pour plus d’informations sur les processus et les tâches de deltastore, consultez Using Clustered Columnstore IndexesFor more information about deltastore tasks and processes, see Using Clustered Columnstore Indexes

Conseils sur les performancesPerformance Tips

Planifiez suffisamment de mémoire pour créer des index columnstore en parallèlePlan for enough memory to create columnstore indexes in parallel

La création d'un index columnstore par défaut est une opération parallèle tant que la mémoire est contrainte.Creating a columnstore index is by default a parallel operation unless memory is constrained. La création de l'index en parallèle requiert plus de mémoire que la création de l'index en série.Creating the index in parallel requires more memory than creating the index serially. Lorsqu'il y a suffisamment de mémoire, la création d'un index columnstore prend 1,5 fois plus de temps que créer un arbre B sur les mêmes colonnes.When there is ample memory, creating a columnstore index takes on the order of 1.5 times as long as building a B-tree on the same columns.

La mémoire requise pour créer un index columnstore dépend du nombre de colonnes, du nombre de colonnes de chaîne, du degré de parallélisme (DOP), et des caractéristiques des données.The memory required for creating a columnstore index depends on the number of columns, the number of string columns, the degree of parallelism (DOP), and the characteristics of the data. Par exemple, si la table a moins d'un million de lignes, SQL Server utilisera un seul thread pour créer l'index columnstore.For example, if your table has fewer than one million rows, SQL Server will use only one thread to create the columnstore index.

Si votre table a plus d'un million de lignes, mais SQL Server ne peut pas obtenir suffisamment d'allocation de mémoire pour créer l'index à l'aide de MAXDOP, il réduira automatiquement MAXDOP de sorte qu'il tienne dans l'allocation de mémoire disponible.If your table has more than one million rows, but SQL Server cannot get a large enough memory grant to create the index using MAXDOP, SQL Server will automatically decrease MAXDOP as needed to fit into the available memory grant. Dans certains cas, le DOP doit être réduit à un pour pouvoir créer l'index sous une mémoire contrainte.In some cases, DOP must be decreased to one in order to build the index under constrained memory.

Index columnstore non clusterNonclustered Columnstore Indexes

Pour les tâches courantes, consultez Using Nonclustered Columnstore Indexes.For common tasks, see Using Nonclustered Columnstore Indexes.

Index columnstore clusterClustered Columnstore Indexes

Pour les tâches courantes, consultez Using Clustered Columnstore Indexes.For common tasks, see Using Clustered Columnstore Indexes.

MétadonnéesMetadata

Toutes les colonnes dans un index columnstore sont stockées dans les métadonnées en tant que colonnes incluses.All of the columns in a columnstore index are stored in the metadata as included columns. L'index columnstore n'a pas de colonnes clés.The columnstore index does not have key columns.