Columnstore-Indizes: AbfrageleistungColumnstore indexes - Query performance

Anwendungsbereich:Applies to: JaSQL ServerSQL Server (alle unterstützten Versionen) yesSQL ServerSQL Server (all supported versions) JaAzure SQL-DatenbankAzure SQL DatabaseYesAzure SQL-DatenbankAzure SQL Database JaVerwaltete Azure SQL-InstanzAzure SQL Managed InstanceYesVerwaltete Azure SQL-InstanzAzure SQL Managed Instance JaAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics JaParallel Data WarehouseParallel Data WarehouseyesParallel Data WarehouseParallel Data WarehouseAnwendungsbereich:Applies to: JaSQL ServerSQL Server (alle unterstützten Versionen) yesSQL ServerSQL Server (all supported versions) JaAzure SQL-DatenbankAzure SQL DatabaseYesAzure SQL-DatenbankAzure SQL Database JaVerwaltete Azure SQL-InstanzAzure SQL Managed InstanceYesVerwaltete Azure SQL-InstanzAzure SQL Managed Instance JaAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics JaParallel Data WarehouseParallel Data WarehouseyesParallel Data WarehouseParallel Data Warehouse

Empfehlungen zum Erreichen der sehr schnellen Abfrageleistung, die Columnstore-Indizes vom Design her bereitstellen können sollten.Recommendations for achieving the very fast query performance that columnstore indexes are designed to provide.

Columnstore-Indizes können eine bis zu 100-fach bessere Leistung für Analyse- und Data Warehousing-Arbeitsauslastungen und eine bis zu 10-fach bessere Datenkomprimierung als herkömmliche Rowstore-Indizes erreichen.Columnstore indexes can achieve up to 100x better performance on analytics and data warehousing workloads and up to 10x better data compression than traditional rowstore indexes. Diese Empfehlungen unterstützen Sie beim Erzielen der schnellen Abfrageleistung, für die Columnstore-Indizes entwickelt wurden.These recommendations help your queries achieve the very fast query performance that columnstore indexes are designed to provide. Weitere Erläuterungen zur Columnstore-Leistung finden Sie am Ende.Further explanations about columnstore performance are at the end.

Empfehlungen zur Verbesserung der AbfrageleistungRecommendations for improving query performance

Im Folgenden finden Sie einige Empfehlungen zum Erreichen der hohen Leistung, für die Columnstore-Indizes entwickelt wurden.Here are some recommendations for achieving the high-performance columnstore indexes are designed to provide.

1. Organisieren Sie Daten, um weitere Zeilengruppen im Rahmen eines vollständigen Tabellenscans zu beseitigen.1. Organize data to eliminate more rowgroups from a full table scan

  • Nutzen Sie die Einfügereihenfolge.Leverage insert order. Im Allgemeinen werden in einem herkömmlichen Data Warehouse die Daten in zeitlicher Reihenfolge eingefügt. Die Analyse erfolgt in der Zeitdimension.In common case in traditional data warehouse, the data is indeed inserted in time order and analytics is done in time dimension. Beispiel: Analyse der Umsätze nach Quartal.For example, analyzing sales by quarter. Für diese Art der Arbeitsauslastung wird das Löschen der Zeilengruppe automatisch durchgeführt.For this kind of workload, the rowgroup elimination happens automatically. In SQL Server 2016 (13.x)SQL Server 2016 (13.x) können Sie die Anzahl der Zeilengruppen ermitteln, die während der Abfrageverarbeitung ausgelassen werden.In SQL Server 2016 (13.x)SQL Server 2016 (13.x), you can find out number rowgroups skipped as part of query processing.

  • Nutzen Sie den gruppierten Rowstore-Index.Leverage the rowstore clustered index. Wenn das allgemeine Abfrageprädikat für eine Spalte (z. B. C1) gilt, die nicht mit der Einfügereihenfolge der Zeile verbunden ist, können Sie einen gruppierten Rowstore-Index für die Spalte C1 erstellen und durch das Löschen des gruppierten Rowstore-Indexes einen gruppierten Columnstore-Index erstellen.If the common query predicate is on a column (for example, C1) that is unrelated to the insert order of the row, you can create a rowstore clustered index on columns C1 and then create clustered columnstore index by dropping the rowstore clustered index. Wenn Sie bei der Erstellung des gruppierten Columnstore-Indexes explizit MAXDOP = 1 verwenden, wird der resultierende gruppierte Columnstore-Index nach Spalte C1 sortiert.if you create the clustered columnstore index explicitly using MAXDOP = 1, the resulting clustered columnstore index is perfectly ordered on column C1. Bei Angabe von MAXDOP = 8 können Sie eine Überlappung der Werte über acht Zeilengruppen beobachten.If you specify MAXDOP = 8, then you will see overlap of values across eight rowgroups. Ein häufiges Szenario für diese Strategie, wenn Sie zu Beginn einen Columnstore-Index mit großer Datenmenge erstellen.A common case of this strategy when you initially create columnstore index with large set of data. Beachten Sie bei einem nicht gruppierten Columnstore-Index (NCCI), dass die grundlegende Rowstore-Tabelle über einen gruppierten Index verfügt. Die Zeilen sind bereits sortiert.Note, for nonclustered columnstore index (NCCI), if the base rowstore table has a clustered index, the rows are already ordered. In diesem Fall wird der resultierende Columnstore-Index automatisch sortiert.In this case, the resultant nonclustered columnstore index will automatically be ordered. Beachten Sie unbedingt Folgendes: Der Columnstore-Index behält die Reihenfolge der Zeilen nicht inhärent bei.One important point to note is that columnstore index does not inherently maintain the order of rows. Wenn neue Zeilen eingefügt oder ältere Zeilen aktualisiert werden, müssen Sie den Vorgang ggf. wiederholen, da die Abfrageleistung der Analyse abnehmen kann.As new rows are inserted or older rows are updated, you may need to repeat the process as the analytics query performance may deteriorate

  • Nutzen Sie die Tabellenpartitionierung.Leverage table partitioning. Sie können den Columnstore-Index partitionieren und dann durch Entfernen der Partition die Anzahl der zu scannenden Zeilengruppen reduzieren.You can partition the columnstore index and then use partition elimination to reduce number of rowgroups to scan. In einer Faktentabelle werden beispielsweise die Einkäufe von Kunden gespeichert, und anhand eines allgemeinen Abfragemusters lassen sich die Einkäufe eines bestimmten Kunden in einem Quartal ermitteln. In diesem Fall können Sie die Einfügereihenfolge mit der Partitionierung für die Spalte „Customer“ kombinieren.For example, a fact table stores purchases made by customers and a common query pattern is to find quarterly purchases done by a specific customer, you can combine the insert order with partitioning on customer column. Jede Partition enthält zeitlich sortierte Zeilen für einen bestimmten Kunden.Each partition will contain rows in time order for specific customer. Sie sollten auch die Verwendung von Tabellenpartitionierung in Betracht ziehen, wenn Daten aus dem Columnstore entfernt werden müssen.Also, consider using table partitioning if there's a need to remove data from the columnstore. Das Auslagern und Abschneiden von Partitionen, die nicht mehr benötigt werden, ist eine effiziente Strategie zum Löschen von Daten, ohne Fragmentierung zu generieren, die durch kleinere Zeilengruppen eingeführt wird.Switching out and truncating partitions that are not needed anymore is an efficient strategy to delete data without generating fragmentation introduced by having smaller rowgroups.

  • Vermeiden Sie das Löschen großer Datenmengen.Avoid deleting large amounts of data. Das Entfernen von komprimierten Zeilen aus einer Zeilengruppe ist kein synchroner Vorgang.Removing compressed rows from a rowgroup is not a synchronous operation. Es wäre aufwendig, eine Zeilengruppe zu dekomprimieren, die Zeile zu löschen und sie dann erneut zu komprimieren.It would be expensive to uncompress a rowgroup, delete the row, and then recompress it. Wenn Sie also Daten aus komprimierten Zeilengruppen löschen, werden diese Zeilen Gruppen weiterhin überprüft, auch wenn sie weniger Zeilen zurückgeben.Therefore, if you delete data from compressed rowgroups, these rowgroups will still be scanned even though they return fewer rows. Wenn die Anzahl der gelöschten Zeilen für mehrere Zeilengruppen groß genug ist, damit diese in weniger Zeilengruppen zusammengeführt werden können, erhöht die Neuorganisation des Columnstore die Qualität des Indexes, und die Abfrageleistung wird verbessert.If the number of deleted rows for several rowgroups is large enough for these to be merged into fewer rowgroups, then reorganizing the columnstore increases the quality of the index and query performance improves. Wenn bei der Datenlöschung normalerweise ganze Zeilengruppen entfallen, sollten Sie die Verwendung von Tabellenpartitionierung, das Auslagern von Partitionen, die nicht mehr benötigt werden, und das Abschneiden von Zeilen anstelle einer Löschung in Betracht ziehen.If your data deletion process usually empties entire rowgroups, consider using table partitioning, switch out partitions that are not needed anymore, and truncate them instead of deleting rows.

    Hinweis

    Ab SQL Server 2019 (15.x)SQL Server 2019 (15.x) wird der Tupelverschiebungsvorgang von einem Mergetask im Hintergrund unterstützt, der automatisch kleinere OPEN-Deltazeilengruppen komprimiert, die für einen bestimmten Zeitraum vorhanden waren (wie durch einen internen Schwellenwert festgelegt), oder COMPRESSED-Zeilengruppen mergt, aus denen eine große Anzahl von Zeilen gelöscht wurde.Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x), the tuple-mover is helped by a background merge task that automatically compresses smaller OPEN delta rowgroups that have existed for some time as determined by an internal threshold, or merges COMPRESSED rowgroups from where a large number of rows has been deleted. Dies verbessert die Qualität des Columnstore-Index im Lauf der Zeit.This improves the columnstore index quality over time.
    Wenn das Löschen von großen Datenmengen aus dem Columnstore-Index erforderlich ist, empfiehlt es sich, diesen Vorgang im Lauf der Zeit in kleinere Löschbatches aufzuteilen. Dies ermöglicht es dem Mergetask im Hintergrund, kleinere Zeilengruppen zu mergen und die Indexqualität zu verbessern, sodass nach dem Löschen von Daten keine Wartungsfenster für Indexumstrukturierung vorgesehen werden müssen.If deleting large amounts of data from the columnstore index is required, consider spliting that operation into smaller delete batches over time, allowing the background merge task to handle the task of merging smaller rowgroups and improve index quality, eliminating the need to schedule index reorganization maintenance windows after data deletion.
    Weitere Informationen zu Columnstore-Begriffen und -Konzepten finden Sie unter Columnstore-Indizes: Übersicht.For more information about columnstore terms and concepts, see Columnstore indexes: Overview.

2. Planen Sie ausreichend Arbeitsspeicher für eine parallele Erstellung von Columnstore-Indizes ein2. Plan for enough memory to create columnstore indexes in parallel

Bei der Erstellung eines Columnstore-Indexes handelt es sich standardmäßig um einen parallel ausgeführten Vorgang, sofern der verfügbare Arbeitsspeicher nicht eingeschränkt ist.Creating a columnstore index is by default a parallel operation unless memory is constrained. Die parallele Indexerstellung erfordert mehr Arbeitsspeicher als die serielle Erstellung des Index.Creating the index in parallel requires more memory than creating the index serially. Wenn ausreichend Arbeitsspeicher verfügbar ist, dauert das Erstellen eines Columnstore-Indexes 1,5-mal so lange wie das Erstellen einer B-Struktur für die gleichen Spalten.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.

Der Speicherplatz, der für das Erstellen eines Columnstore-Indexes erforderlich ist, hängt von der Anzahl von Spalten, der Anzahl der Zeichenfolgenspalten, dem Grad der Parallelität (Degree of Parallelism, DOP) und von den Eigenschaften der Daten ab.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. Wenn die Tabelle beispielsweise weniger als eine Million Zeilen aufweist, verwendet SQL ServerSQL Server nur einen Thread, um den Columnstore-Index zu erstellen.For example, if your table has fewer than one million rows, SQL ServerSQL Server will use only one thread to create the columnstore index.

Wenn die Tabelle mehr als eine Million Zeilen aufweist, SQL ServerSQL Server aber keine ausreichend dimensionierte Arbeitsspeicherzuweisung abrufen kann, um den Index mit MAXDOP zu erstellen, verringert SQL ServerSQL ServerMAXDOP automatisch nach Bedarf, um es auf den verfügbaren Arbeitsspeicher zu beschränken.If your table has more than one million rows, but SQL ServerSQL Server cannot get a large enough memory grant to create the index using MAXDOP, SQL ServerSQL Server will automatically decrease MAXDOP as needed to fit into the available memory grant. In bestimmten Fällen muss DOP auf eins verringert werden, um den Index mit eingeschränktem Arbeitsspeicher zu erstellen.In some cases, DOP must be decreased to one in order to build the index under constrained memory.

Ab SQL SQL Server 2016 (13.x)SQL Server 2016 (13.x) wird die Abfrage immer im Batchmodus ausgeführt.Beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x), the query will always operate in batch mode. In früheren Versionen wird die Batchausführung nur verwendet, wenn DOP größer als 1 ist.In previous releases, batch execution is only used when DOP is greater than one.

Erläuterungen zur Columnstore-LeistungColumnstore Performance Explained

Columnstore-Indizes erzielen eine hohe Abfrageleistung durch die Kombination der Hochgeschwindigkeits-In-Memory-Modusbatchverarbeitung mit Techniken, die E/A-Anforderungen erheblich reduzieren.Columnstore indexes achieve high query performance by combining high-speed in-memory batch mode processing with techniques that greatly reduce I/O requirements. Da bei analytischen Abfragen eine große Anzahl von Zeilen gescannt werden, sind diese in der Regel E/A-gebunden, sodass eine E/A-Reduzierung während der Abfrageausführung maßgeblich für das Design der Columnstore-Indizes ist.Since analytics queries scan large numbers of rows, they are typically IO-bound, and therefore reducing I/O during query execution is critical to the design of columnstore indexes. Sobald die Daten in den Arbeitsspeicher gelesen wurden, ist es wichtig, dass die Anzahl der In-Memory-Vorgänge reduziert wird.Once data has been read into memory, it is critical to reduce the number of in-memory operations.

Columnstore-Indizes reduzieren die E/A-Vorgänge und optimieren die In-Memory-Vorgänge mithilfe einer hohen Datenkomprimierung sowie der Columnstore-Löschung, der Löschung von Zeilengruppen und der Batchverarbeitung.Columnstore indexes reduce I/O and optimize in-memory operations through high data compression, columnstore elimination, rowgroup elimination, and batch processing.

DatenkomprimierungData compression

Columnstore-Indizes erzielen eine bis zu 10 Mal höhere Datenkomprimierung als Rowstore-Indizes.Columnstore indexes achieve up to 10x greater data compression than rowstore indexes. Dadurch werden die zum Ausführen der Analyseabfragen erforderlichen E/A-Vorgänge erheblich verringert, wodurch die Abfrageleistung verbessert wird.This greatly reduces the I/O required to execute analytics queries and therefore improves query performance.

  • Columnstore-Indizes lesen komprimierte Daten vom Datenträger, was bedeutet, dass weniger Datenbytes in den Arbeitsspeicher gelesen werden müssen.Columnstore indexes read compressed data from disk, which means fewer bytes of data need to be read into memory.

  • Columnstore-Indizes speichern Daten in komprimierter Form im Arbeitsspeicher, wodurch E/A-Vorgänge reduziert werden, da die Häufigkeit der Lesevorgänge der gleichen Daten in den Arbeitsspeicher verringert wird.Columnstore indexes store data in compressed form in memory, which reduces I/O by reducing the number of times the same data is read into memory. Beispielsweise können Columnstore-Indizes bei zehnfacher Komprimierung zehnmal mehr Daten im Arbeitsspeicher aufbewahren als bei der Speicherung der Daten in unkomprimierter Form.For example, with 10x compression, columnstore indexes can keep 10x more data in memory compared to storing the data in uncompressed form. Wenn sich mehr Daten im Arbeitsspeicher befinden, ist es wahrscheinlicher, dass der Columnstore-Index die benötigten Daten im Arbeitsspeicher findet, ohne dass zusätzliche Lesevorgänge vom Datenträger anfallen.With more data in memory, it is more likely that the columnstore index will find the data it needs in memory without incurring additional reads from disk.

  • Columnstore-Indizes komprimieren Daten nach Spalten anstatt nach Zeilen, wodurch hohe Komprimierungsraten erzielt und die Größe der auf dem Datenträger gespeicherten Daten reduziert werden.Columnstore indexes compress data by columns instead of by rows, achieving high compression rates and reducing the size of the data stored on disk. Jede Spalte wird separat komprimiert und gespeichert.Each column is compressed and stored independently. Daten in einer Spalte haben immer den gleichen Datentyp und tendenziell auch ähnliche Werte.Data within a column always has the same data type and tends to have similar values. Die Datenkomprimierungstechniken sind sehr gut, um höhere Komprimierungsraten zu erreichen, wenn die Werte ähnlich sind.Data compression techniques are very good at achieving higher compression rates when values are similar.

  • Wenn eine Faktentabelle beispielsweise Kundenadressen und eine Spalte für das Land enthält, beträgt die Gesamtzahl der möglichen Werte weniger als 200.For example, if a fact table stores customer addresses and has a column for country, the total number of possible values is fewer than 200. Einige dieser Werte werden mehrmals wiederholt.Some of those values will be repeated many times. Wenn die Faktentabelle 100 Millionen Zeilen enthält, lässt sich die Länderspalte problemlos komprimieren und erfordert nur sehr wenig Speicherplatz.If the fact table has 100 million rows, the country column will compress easily and require very little storage. Eine zeilenweise Komprimierung kann auf diese Weise nicht von der Ähnlichkeit der Spaltenwerte profitieren. Daher werden hierbei mehr Bytes verwendet, um die Werte in der Länderspalte zu komprimieren.Row-by-row compression is not able to capitalize on the similarity of column values in this way and will use more bytes to compress the values in the country column.

SpaltenlöschungColumn elimination

Columnstore-Indizes überspringen den Lesevorgang von Spalten, die für das Ergebnis der Abfrage nicht erforderlich sind.Columnstore indexes skip reading in columns that are not required for the query result. Durch die sogenannte Spaltenlöschung werden die E/A-Vorgänge für die Abfrageausführung weiter reduziert, wodurch die Abfrageleistung verbessert wird.This ability, called column elimination, further reduces I/O for query execution and therefore improves query performance.

  • Die Spaltenlöschung ist möglich, da die Daten nach Spalten organisiert und komprimiert sind.Column elimination is possible because the data is organized and compressed column by column. Im Gegensatz dazu werden beim zeilenweisen Speichern von Daten die Werte der einzelnen Zeilen physisch zusammen gespeichert und können nicht problemlos getrennt werden.In contrast, when data is stored row-by-row, the column values in each row are physically stored together and cannot be easily separated. Der Abfrageprozessor muss eine ganze Zeile einlesen, um bestimmte Spaltenwerte abrufen zu können, wodurch die E/A-Vorgänge erhöht werden, da zusätzliche Daten unnötigerweise in den Arbeitsspeicher gelesen werden.The Query Processor needs to read in an entire row to retrieve specific column values, which increases I/O because extra data is unnecessarily read into memory.

  • Wenn eine Tabelle beispielsweise 50 Spalten hat und die Abfrage nur 5 dieser Spalten verwendet, ruft der Columnstore-Index nur die 5 Spalten vom Datenträger ab.For example, if a table has 50 columns and the query only uses 5 of those columns, the columnstore index only fetches the 5 columns from disk. Das Einlesen der anderen 45 Spalten wird übersprungen.It skips reading in the other 45 columns. Dadurch werden die E/A-Vorgänge um weitere 90 % reduziert, vorausgesetzt, dass alle Spalten eine ähnliche Größe aufweisen.This reduces I/O by another 90% assuming all columns are of similar size. Wenn die gleichen Daten in einer Zeilengruppe gespeichert sind, muss der Abfrageprozessor die zusätzlichen 45 Spalten lesen.If the same data are stored in a rowstore, the query processor needs to read the additional 45 columns.

ZeilengruppenlöschungRowgroup elimination

Bei vollständigen Tabellenscans entspricht ein großer Prozentsatz der Daten in der Regel nicht den Abfrageprädikatskriterien.For full table scans, a large percentage of the data usually does not match the query predicate criteria. Mithilfe von Metadaten kann der Columnstore-Index das Einlesen der Zeilengruppen überspringen, die keine für das Abfrageergebnis erforderlichen Daten enthalten. Dabei werden keine tatsächlichen E/A-Vorgänge durchgeführt.By using metadata, the columnstore index is able to skip reading in the rowgroups that do not contain data required for the query result, all without actual I/O. Durch die sogenannte Zeilengruppenlöschung werden die E/A-Vorgänge für vollständige Tabellenscans weiter reduziert, wodurch die Abfrageleistung verbessert wird.This ability, called rowgroup elimination, reduces I/O for full table scans and therefore improves query performance.

Wann muss ein Columnstore-Index einen vollständigen Tabellenscan durchführen?When does a columnstore index need to perform a full table scan?

Ab SQL Server 2016 (13.x)SQL Server 2016 (13.x) können Sie einen oder mehrere reguläre nicht gruppierte B-Strukturindizes für einen gruppierten Columnstore-Index genauso wie für einen Rowstore-Heap erstellen.Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), you can create one or more regular nonclustered B-tree indexes on a clustered columnstore index just like you can on a rowstore heap. Die nicht gruppierten B-Strukturindizes können eine Abfrage mit einem Gleichheitsprädikat oder einem Prädikat mit einem kleinen Wertebereich beschleunigen.The nonclustered B-tree indexes can speed up a query that has an equality predicate or a predicate with a small range of values. Bei komplexeren Prädikaten kann der Abfrageoptimierer sich für einen vollständigen Tabellenscan entscheiden.For more complicated predicates, the query optimizer might choose a full table scan. Ohne die Fähigkeit, Zeilengruppen zu überspringen, wäre ein vollständiger Tabellenscan sehr zeitaufwändig, insbesondere bei großen Tabellen.Without the ability to skip rowgroups, a full table scan would be very time-consuming, especially for large tables.

Wann profitiert eine Analyseabfrage von einer Zeilengruppenlöschung für einen vollständigen Tabellenscan?When does an analytics query benefit from rowgroup elimination for a full-table scan?

Ein Einzelhandelsunternehmen hat beispielsweise seine Vertriebsdaten mithilfe einer Faktentabelle mit gruppiertem Columnstore-Index modelliert.For example, a retail business has modeled their sales data using a fact table with clustered columnstore index. Bei jedem neuen Verkauf werden verschiedene Attribute der Transaktion gespeichert, einschließlich des Verkaufsdatums eines Produkts.Each new sale stores various attributes of the transaction including the date a product was sold. Obwohl Columnstore-Indizes keine sortierte Reihenfolge sicherstellen, werden die Zeilen in dieser Tabelle interessanterweise nach Datum sortiert geladen.Interestingly, even though columnstore indexes do not guarantee a sorted order, the rows in this table will be loaded in a date-sorted order. Mit der Zeit wächst diese Tabelle.Over time this table will grow. Auch wenn das Einzelhandelsunternehmen Verkaufsdaten der letzten 10 Jahre speichert, muss bei einer Analyseabfrage nur ein Aggregat für das letzte Quartal berechnet werden.Although the retail business might keep sales data for the last 10 years, an analytics query might only need to compute an aggregate for last quarter. Mit Columnstore-Indizes können Sie es vermeiden, auf die Daten der vorherigen 39 Quartale zuzugreifen, indem nur die Metadaten für die Datumsspalte untersucht werden.Columnstore indexes can eliminate accessing the data for the previous 39 quarters by just looking at the metadata for the date column. Dies entspricht einer zusätzlichen Reduzierung der Datenmenge, die in den Arbeitsspeicher gelesen und verarbeitet wird, von 97 %.This is an additional 97% reduction in the amount of data that is read into memory and processed.

Welche Zeilengruppen werden bei einem vollständigen Tabellenscan übersprungen?Which rowgroups are skipped in a full table scan?

Um zu bestimmen, welche Zeilengruppen gelöscht werden sollen, verwendet der Columnstore-Index Metadaten, um die Mindest- und Maximalwerte jedes Spaltensegments für jede Zeilengruppe zu speichern.To determine which rows groups to eliminate, the columnstore index uses metadata to store the minimum and maximum values of each column segment for each rowgroup. Wenn keiner der Spaltensegmentbereiche die Kriterien für Abfrageprädikate erfüllt, wird die gesamte Zeilengruppe übersprungen, ohne tatsächliche E/A-Vorgänge auszuführen.When none of the column segment ranges meet the query predicate criteria, the entire rowgroup is skipped without doing any actual IO. Dies funktioniert, da die Daten in der Regel in sortierter Reihenfolge geladen werden, und auch wenn eine Sortierung der Zeilen nicht garantiert werden kann, befinden sich ähnliche Datenwerte häufig innerhalb derselben Zeilengruppe oder in einer benachbarten Zeilengruppe.This works because the data is usually loaded in a sorted order and although rows are not guaranteed to be sorted, similar data values are often located within the same rowgroup or a neighboring rowgroup.

Weitere Informationen zu Zeilengruppen finden Sie unter Entwurfsleitfäden für Columnstore-Indizes.For more details about rowgroups, see Columnstore Index Design Guidelines.

BatchmodusausführungBatch Mode Execution

Bei der Batchmodusausführung handelt es sich um die gemeinsame Verarbeitung eines Rowsets, in der Regel bis zu 900 Zeilen, aus Gründen der Ausführungseffizienz.Batch mode execution refers to processing a set of rows, typically up to 900 rows, together for execution efficiency. Die Abfrage SELECT SUM (Sales) FROM SalesData aggregiert beispielsweise den Gesamtumsatz aus der Tabelle „SalesData“.For example, the query SELECT SUM (Sales) FROM SalesData aggregates the total sales from the table SalesData. Bei der Batchmodusausführung berechnet die Abfrageausführungs-Engine das Aggregat in Gruppen von 900 Werten.In batch mode execution, the query execution engine computes the aggregate in group of 900 values. Dadurch werden Metadaten, Zugriffskosten und andere Aufwandsarten auf alle Zeilen in einem Batch verteilt, anstatt die Kosten für jede Zeile zu zahlen, wodurch der Codepfad erheblich reduziert wird.This spreads metadata the access costs and other types of overhead over all the rows in a batch, rather than paying the cost for each row thereby significantly reducing the code path. Bei der Batchmodusverarbeitung kommen, sofern möglich, komprimierte Daten zum Einsatz. Zugleich werden einige der Exchange-Operatoren beseitigt, die bei der Zeilenmodusverarbeitung verwendet werden.Batch mode processing operates on compressed data when possible and eliminates some of the exchange operators used by row mode processing. Dies beschleunigt die Ausführung von Analyseabfragen in beträchtlichem Maße.This speeds up execution of analytics queries by orders of magnitude.

Nicht alle Abfrageausführungsoperatoren können im Batchmodus ausgeführt werden.Not all query execution operators can be executed in batch mode. DML-Vorgänge, wie z. B. Einfügen, Löschen oder Aktualisieren, werden z. B. Zeile für Zeile ausgeführt.For example, DML operations such as Insert, Delete or Update are executed row at a time. Batchmodusoperatoren richten sich an Operatoren, um die Abfrageleistung wie Scan, Join, Aggregate, Sort und so weiter zu beschleunigen.Batch mode operators target operators for speeding up query performance such as Scan, Join, Aggregate, sort and so on. Da der Columnstore-Index in SQL Server 2012 (11.x)SQL Server 2012 (11.x) eingeführt wurde, gibt es eine nachhaltige Initiative, um die Operatoren zu erweitern, die im Batchmodus ausgeführt werden können.Since the columnstore index was introduced in SQL Server 2012 (11.x)SQL Server 2012 (11.x), there is a sustained effort to expand the operators that can be executed in the batch mode. Die folgende Tabelle führt die Operatoren auf, die im Batchmodus gemäß der Produktversion ausgeführt werden.The table below shows the operators that run in batch mode according to the product version.

BatchmodusoperatorenBatch Mode Operators EinsatzWhen is this used? SQL Server 2012 (11.x)SQL Server 2012 (11.x) SQL Server 2014 (12.x)SQL Server 2014 (12.x) SQL Server 2016 (13.x)SQL Server 2016 (13.x) und SQL-DatenbankSQL Database1and SQL-DatenbankSQL Database1 KommentareComments
DML-Vorgänge (Insert, Delete, Update, Merge)DML operations (insert, delete, update, merge) neinno neinno neinno DML ist kein Batchmodusvorgang, da es nicht parallel ist.DML is not a batch mode operation because it is not parallel. Auch wenn die serielle Batchmodusverarbeitung aktiviert wird, kommt es zu keiner maßgeblichen Leistungssteigerung, wenn DML im Batchmodus verarbeitet wird.Even when we enable serial mode batch processing, we don't see significant gains by allowing DML to be processed in batch mode.
Columnstore Index Scancolumnstore index scan SCANSCAN Nicht verfügbarNA jayes jayes Bei Columnstore-Indizes kann das Prädikat mittels Push an den SCAN-Knoten übertragen werden.For columnstore indexes, we can push the predicate to the SCAN node.
Columnstore Index Scan (nicht gruppiert)columnstore Index Scan (nonclustered) SCANSCAN jayes jayes jayes jayes
Index Seekindex seek Nicht verfügbarNA Nicht verfügbarNA neinno Es wird ein Suchvorgang im Zeilenmodus durch einen nicht gruppierten B-Strukturindex durchgeführt.We perform a seek operation through a nonclustered B-tree index in rowmode.
Compute Scalarcompute scalar Ausdruck, dessen Auswertung einen Skalarwert ergibt.Expression that evaluates to a scalar value. jayes jayes jayes Es gibt jedoch einige Einschränkungen hinsichtlich des Datentyps.There are some restrictions on data type. Dies gilt für alle Batchmodusoperatoren.This is true for all batch mode operators.
Verkettungconcatenation UNION und UNION ALLUNION and UNION ALL neinno jayes jayes
filterfilter Anwenden von PrädikatenApplying predicates jayes jayes jayes
Hash Matchhash match Hashbasierte Aggregatfunktionen, äußerer Hashjoin, rechter Hashjoin, linker Hashjoin, rechter innerer Join, linker innerer JoinHash-based aggregate functions, outer hash join, right hash join, left hash join, right inner join, left inner join jayes jayes jayes Einschränkungen für die Aggregation: keine Mindest-/Maximalwerte für Zeichenfolgen.Restrictions for aggregation: no min/max for strings. Verfügbare Aggregationsfunktionen: sum/count/avg/min/max.Aggregation functions available are sum/count/avg/min/max.
Einschränkungen für Join: keine nicht übereinstimmenden Typ-Joins für nicht ganzzahlige Typen.Restrictions for join: no mismatched type joins on non-integer types.
Merge Joinmerge join neinno neinno neinno
Multithread-Abfragenmulti-threaded queries jayes jayes jayes
Nested Loopsnested loops neinno neinno neinno
Singlethread-Abfragen unter MAXDOP 1single-threaded queries running under MAXDOP 1 neinno neinno jayes
Singlethread-Abfragen mit einem seriellen Abfrageplansingle-threaded queries with a serial query plan neinno neinno jayes
sortsort Order by-Klausel für SCAN mit Columnstore-IndexOrder by clause on SCAN with columnstore index. neinno neinno jayes
Top Sorttop sort neinno neinno jayes
Window Aggregateswindow aggregates Nicht verfügbarNA Nicht verfügbarNA jayes Neuer Operator in SQL Server 2016 (13.x)SQL Server 2016 (13.x).New operator in SQL Server 2016 (13.x)SQL Server 2016 (13.x).

1 Gilt für SQL Server 2016 (13.x)SQL Server 2016 (13.x), SQL-DatenbankSQL Database Premium-Tarife, Standard-Tarife (S3 und höher), alle vCore-Tarife sowie Parallel Data WarehouseParallel Data Warehouse.1 Applies to SQL Server 2016 (13.x)SQL Server 2016 (13.x), SQL-DatenbankSQL Database Premium tiers, Standard tiers - S3 and above, and all vCore tiers, and Parallel Data WarehouseParallel Data Warehouse

Weitere Informationen finden Sie im Handbuch zur Architektur der Abfrageverarbeitung.For more information, see the Query Processing Architecture Guide.

AggregatweitergabeAggregate Pushdown

Ein normaler Ausführungspfad zur Aggregatberechnung, um die qualifizierenden Zeilen aus dem SCAN-Knoten abzurufen und die Werte im Batchmodus zu aggregieren.A normal execution path for aggregate computation to fetch the qualifying rows from the SCAN node and aggregate the values in Batch Mode. Dadurch wird eine gute Leistung bereitgestellt. Ab SQL Server 2016 (13.x)SQL Server 2016 (13.x) kann der Aggregatvorgang jedoch mittels Push an den SCAN-Knoten weitergegeben werden, um die Leistung der Aggregatberechnung in erheblichem Maße zusätzlich zur Ausführung im Batchmodus zu verbessern, sofern folgende Bedingungen erfüllt sind:While this delivers good performance, but with SQL Server 2016 (13.x)SQL Server 2016 (13.x), the aggregate operation can be pushed to the SCAN node to improve the performance of aggregate computation by orders of magnitude on top of Batch Mode execution provided the following conditions are met:

  • Bei den Aggregaten handelt es sich um MIN, MAX, SUM, COUNT und COUNT(*).The aggregates are MIN, MAX, SUM, COUNT and COUNT(*).
  • Der Aggregatoperator muss sich über dem SCAN-Knoten oder über dem SCAN-Knoten mit GROUP BY befinden.Aggregate operator must be on top of SCAN node or SCAN node with GROUP BY.
  • Dieses Aggregat ist kein eindeutiges Aggregat.This aggregate is not a distinct aggregate.
  • Die Aggregatspalte ist keine Zeichenfolgenspalte.The aggregate column is not a string column.
  • Die Aggregatspalte ist keine virtuelle Spalte.The aggregate column is not a virtual column.
  • Der Datentyp für die Eingabe und Ausgabe muss einer der folgenden sein und in 64 Bit passen.The input and output datatype must be one of the following and must fit within 64 bits.
    • tinyint, int, bigint, smallint, bittinyint, int, bigint, smallint, bit
    • smallmoney, money, decimal und numeric mit einer Genauigkeit von <= 18smallmoney, money, decimal and numeric with precision <= 18
    • smalldate, date, datetime, datetime2, timesmalldate, date, datetime, datetime2, time

Aggregatweitergabe wird durch effiziente Aggregation von komprimierten/codierten Daten bei Cache-entlastender Ausführung und durch Nutzung von SIMD weiter beschleunigt.Aggregate push down is further accelerated by efficient Aggregation on compressed/encoded data in cache-friendly execution and by leveraging SIMD

Aggregatweitergabeaggregate pushdown

Die Aggregatweitergabe wird beispielsweise in den beiden folgenden Abfragen durchgeführt:For example, aggregate pushdown is done in both of the queries below:

SELECT  productkey, SUM(TotalProductCost)    
FROM FactResellerSalesXL_CCI    
GROUP BY productkey;    
    
SELECT  SUM(TotalProductCost)    
FROM FactResellerSalesXL_CCI;    

ZeichenfolgenprädikatweitergabeString predicate pushdown

Beim Entwurf eines Data Warehouse-Schemas besteht die empfohlene Schemamodellierung darin, ein Sternschema oder Schneeflockenschema zu verwenden, das aus einer oder mehreren Faktentabellen und vielen Dimensionstabellen besteht.When designing a data warehouse schema, the recommended schema modeling is to use star-schema or snowflake schema consisting of one or more fact tables and many dimension tables. Die Faktentabelle speichert die Unternehmensmessungen oder -transaktionen, und die Dimensionstabelle speichert die Dimensionen, anhand derer die Fakten analysiert werden müssen.The fact table stores the business measurements or transactions and dimension table store the dimensions across which facts need to be analyzed.

Bei einem Fakt kann es sich beispielsweise um einen Datensatz für den Verkauf eines bestimmten Produkts in einer bestimmten Region handeln, während die Dimension eine Reihe von Regionen, Produkten usw. darstellt.For example, a fact can be a record representing a sale of a particular product in a specific region while the dimension represents a set of regions, products and so on. Die Fakten- und Dimensionstabellen sind über eine Primär-/Fremdschlüsselbeziehung miteinander verbunden.The fact and dimension tables are connected through a primary/foreign key relationship. Die am häufigsten verwendeten Abfragen verbinden eine oder mehrere Dimensionstabellen mit der Faktentabelle.Most commonly used analytics queries join one or more dimension tables with the fact table.

Betrachten Sie z.B. eine Dimensionstabelle namens Products.Let us consider a dimension table Products. Ein typischer Primärschlüssel wäre ProductCode, der in der Regel als Zeichenfolgendatentyp dargestellt wird.A typical primary key will be ProductCode which is commonly represented as string data type. Für die Abfrageleistung hat es sich bewährt, einen Ersatzschlüssel zu erstellen (in der Regel eine Spalte mit ganzen Zahlen), um aus der Faktentabelle auf die Zeile in der Dimensionstabelle zu verweisen.For performance of queries, it is a best practice to create surrogate key, typically an integer column, to refer to the row in the dimension table from the fact table.

Der Columnstore-Index führt die Analyseabfragen mit Joins/Prädikaten mit Schlüsseln auf numerischer Basis bzw. Ganzzahlbasis sehr effizient aus.The columnstore index runs analytics queries with joins/predicates involving numeric or integer-based keys very efficiently. Bei vielen Kundenworkloads wurden jedoch zeichenfolgenbasierte Spalten verwendet, die Fakt-/Dimensionstabellen verknüpfen. Dies führte dazu, dass die Abfrageleistung mit Columnstore-Indizes nicht gut war.However, in many customer workloads, we find the use to string-based columns linking fact/dimension tables and with the result the query performance with columnstore index was not as performing. SQL Server 2016 (13.x)SQL Server 2016 (13.x) verbessert die Leistung von Analyseabfragen mit zeichenfolgenbasierten Spalten erheblich, indem die Prädikate mit Zeichenfolgenspalten an den SCAN-Knoten weitergegeben werden.improves the performance of analytics queries with string based columns significantly by pushing down the predicates with string columns to the SCAN node.

Die Weitergabe von Zeichenfolgenprädikaten nutzt zur Verbesserung der Abfrageleistung das primäre bzw. sekundäre Wörterbuch, das für die Spalten erstellt wurde.String predicate pushdown leverages the primary/secondary dictionary created for column(s) to improve the query performance. Nehmen Sie z. B. ein Zeichenfolgenspaltensegment in einer Zeilengruppe, bestehend aus 100 unterschiedlichen Zeichenfolgenwerten.For example, let us consider string column segment within a rowgroup consisting of 100 distinct string values. Das bedeutet, dass bei einer Million Zeilen auf jeden einzelnen Zeichenfolgenwert durchschnittlich 10.000 Mal verwiesen wird.This means each distinct string value is referenced 10,000 times on average assuming 1 million rows.

Bei der Zeichenfolgenprädikatweitergabe berechnet die Abfrageausführung das Prädikat anhand der Werte im Wörterbuch. Wenn sich dies qualifiziert, werden alle Zeilen mit Bezug auf den Wörterbuchwert automatisch qualifiziert.With string predicate pushdown, the query execution computes the predicate against the values in the dictionary and if it qualifies, all rows referring to the dictionary value are automatically qualified. Dies verbessert die Leistung auf zwei Arten:This improves the performance in two ways:

  1. Es wird nur die qualifizierte Zeile zurückgegeben, wodurch die Anzahl der Zeilen reduziert wird, die aus dem SCAN-Knoten übertragen werden müssen.Only the qualified row is returned reducing number of the rows that need to flow out of SCAN node.

  2. Die Anzahl von Zeichenfolgenvergleichen wird maßgeblich verringert.The number of string comparisons are significantly reduced. In diesem Beispiel sind nur 100 Zeichenfolgenvergleiche gegenüber einer Millionen Vergleiche erforderlich.In this example, only 100 string comparisons are required as against 1 million comparisons. Wie im Folgenden beschrieben gibt es einige Einschränkungen:There are some limitations as described below:

    • Keine Zeichenfolgenprädikatweitergabe für Deltazeilengruppen.No string predicate pushdown for delta rowgroups. Es gibt kein Wörterbuch für Spalten in Deltazeilengruppen.There is no dictionary for columns in delta rowgroups.
    • Keine Weitergabe von Zeichenfolgenprädikaten, wenn die Einträge im Wörterbuch eine Größe von 64 KB überschreiten.No string predicate pushdown if dictionary exceeds 64 KB entries.
    • Ausdrücke, die als NULL-Werte ausgewertet werden, werden nicht unterstützt.Expression evaluating NULLs are not supported.

Weitere InformationenSee Also

Entwurfsleitfäden für Columnstore-Indizes Datenladeanleitungen für Columnstore-Indizes Columnstore Index Design Guidelines Columnstore Indexes Data Loading Guidance
Erste Schritte mit Columnstore für operative Echtzeitanalyse Get started with Columnstore for real time operational analytics
Columnstore-Indizes für Data Warehousing Columnstore Indexes for Data Warehousing
Neuorganisieren und Neuerstellen von Indizes Reorganize and Rebuild Indexes
Columnstore Index Architecture (Columnstore-Indizes: Architektur) Columnstore Index Architecture
CREATE INDEX (Transact-SQL) CREATE INDEX (Transact-SQL)
ALTER INDEX (Transact-SQL)ALTER INDEX (Transact-SQL)