Columnstore-Indizes

Artikel
10/31/2013

Ein Speicheroptimierter xVelocity-columnstore-Index gruppiert und speichert Daten für jede Spalte und verknüpft dann alle Spalten, um den Gesamtindex zu erstellen. Diese Vorgehensweise unterscheidet sich von herkömmlichen Indizes, bei denen Daten für jede Zeile gruppiert und gespeichert und dann alle Zeilen verknüpft werden, um den Gesamtindex zu erstellen. Für einige Typen von Abfragen kann der SQL Server-Abfrageprozessor das columnstore-Layout nutzen, um die Ausführungsdauer von Abfragen erheblich zu verringern. Die sprunghafte Zunahme von Anwendungen in den Bereichen Data Warehousing, Entscheidungsunterstützung und BI hat für eine hohe Nachfrage beim schnellen und exakten Lesen großer Datasets und bei deren Verarbeitung zu nützlichen Informationen und Wissensdatenbanken gesorgt. Wachsende Datenvolumen und immer weiter steigende Erwartungen können mithilfe von inkrementellen Leistungsverbesserungen nur sehr schwer verwaltet und erfüllt werden. Die columnstore-Indextechnologie von SQL Server ist besonders gut für typische Data Warehousing-Datasets geeignet. Columnstore-Indizes können die Data Warehousing-Erfahrung für Benutzer transformieren, indem sie eine schnellere Leistung für allgemeine Data Warehousing-Abfragen ermöglichen, z. B. Abfragen in den Bereichen Filterung, Aggregierung, Gruppierung und Sternverknüpfungen.

Inhalt

Grundlagen

Grundlagen: Beschreibung von columnstore-Indizes
Grundlagen: Einschränkungen von columnstore-Indizes
Demobeispiel: Columnstore-Indizes mit einer partitionierten Tabelle
Grundlagen: Typische columnstore-Indexszenarien
Grundlagen: Bitmapfilteroptimierungen

Bewährte Methoden

Bewährte Methoden: Aktualisieren von Daten in einem columnstore-Index
Bewährte Methoden: Auswählen von Spalten für einen Spaltenspeicherindex
Bewährte Methoden: Partitionierte Tabellen

Vorgehensweise

Vorgehensweise: Erstellen eines Spaltenspeicherindexes
Vorgehensweise: Bestimmen der Größe eines columnstore-Indexes
Vorgehensweise: Problembehandlung der Leistung eines Spaltenspeicherindexes

Grundlagen: Beschreibung von columnstore-Indizes

Columnstore-Indizes in SQL Server Database Engine (Datenbankmodul) können verwendet werden, um die Verarbeitungszeit häufiger Data Warehousing-Abfragen erheblich zu verkürzen. Typische Data Warehousing-Arbeitsauslastungen beinhalten das Summieren großer Datenmengen. Die Verfahren, die in Data Warehousing- und Entscheidungsunterstützungs-Systemen normalerweise zur Verbesserung der Leistung eingesetzt werden, umfassen vorausberechnete Zusammenfassungstabellen, indizierte Sichten, OLAP-Cubes usw. Obwohl die Abfrageverarbeitung damit stark beschleunigt werden kann, können diese Verfahren unflexibel und schwierig zu verwalten sein und müssen für jedes Abfrageproblem speziell entworfen werden.

Angenommen, Sie verwenden eine Faktentabelle F1 mit den Dimensionsschlüsselspalten dk1 und dk2. M steht für eine Aggregatfunktion, z. B. SUM. Anstatt jedes Mal, wenn eine auf "M(dk1)" verweisende Abfrage ausgeführt wird, M über die Spalte dk1 zu berechnen, kann eine Zusammenfassungstabelle "F2(dk1, M)" erstellt und verwendet werden. Auf diese Weise kann das Ergebnis vorausberechnet und die Abfrage schneller ausgeführt werden. Falls jedoch eine neue Abfrage benötigt wird, die auf "M(dk2)" verweist, muss eine neue Zusammenfassungstabelle "F3(dk2, M)" mit diesen Informationen erstellt werden. Wenn sich die Anzahl der Spalten in einer Tabelle erhöht und darin ggf. viele Funktionen enthalten sind, wird die Verwaltung bei diesem Ansatz erschwert, und es ist nicht einfach, alle erforderlichen Abfragen abzudecken.

Dies kann für Benutzer einen erheblichen Aufwand darstellen. Mithilfe der columnstore-Indizes von SQL Server können Benutzer den Arbeitsaufwand der anderen Lösungen reduzieren. Außerdem ermöglichen columnstore-Indizes für Abfragen eine so schnelle Berechnung der Ergebnisse, dass keine Vorabberechnung erforderlich ist.

Die Hauptmerkmale der Spaltenspeichertechnologie von SQL Server sind:

Spaltenförmiges Datenformat – Im Gegensatz zur herkömmlichen zeilenbasierten Organisation von Daten (als rowstore-Format bezeichnet) werden Daten in spaltenbasierten Datenbanksystemen, wie SQL Server mit columnstore-Indizes, Spalte für Spalte gruppiert und gespeichert. Bei der SQL Server-Abfrageverarbeitung kann das neue Datenlayout genutzt und die Ausführungsdauer für Abfragen deutlich optimiert werden.
Schnellere Abfrageergebnisse – Columnstore-Indizes können aus den folgenden Gründen schneller Ergebnisse liefern:
- Nur die benötigten Spalten müssen gelesen werden. Daher werden weniger Daten vom Datenträger in den Arbeitsspeicher ausgelesen und anschließend aus dem Arbeitsspeicher in den Prozessorcache verschoben.
- Spalten werden stark komprimiert. So wird die Anzahl von Bytes reduziert, die gelesen und verschoben werden müssen.
- Bei den meisten Abfragen wird nicht auf alle Spalten der Tabelle zugegriffen. Viele Spalten gelangen also nie in den Arbeitsspeicher. Diese Vorgehensweise in Verbindung mit einer bestmöglichen Komprimierung führt zu einer Optimierung der Pufferpoolauslastung, was wiederum zu einer Verringerung des E/A-Aufwands führt.
- Mithilfe der modernen Technologie zur Ausführung von Abfragen werden Gruppen von Spalten, die als Batches bezeichnet werden, auf ökonomische Weise verarbeitet, sodass sich die CPU-Auslastung reduziert.
Schlüsselspalten – Bei columnstore-Indizes wird das Konzept der Schlüsselspalten nicht verwendet, sodass die Einschränkung in Bezug auf die Anzahl der Schlüsselspalten in einem Index (16) für columnstore-Indizes nicht gilt.
Schlüssel des gruppierten Indexes – Wenn eine Basistabelle ein gruppierter Index ist, müssen alle Spalten im Gruppierungsschlüssel im nicht gruppierten columnstore-Index vorhanden sein. Falls eine Spalte im Gruppierungsschlüssel nicht in der CREATE INDEX-Anweisung aufgeführt ist, wird sie automatisch dem columnstore-Index hinzugefügt.
Partitionierung – Columnstore-Indizes verwenden die Tabellenpartitionierung. Es sind keine Änderungen an der Tabellenpartitionierungssyntax erforderlich. Ein columnstore-Index einer partitionierten Tabelle muss über eine Partitionsausrichtung mit der Basistabelle verfügen. Daher kann ein nicht gruppierter columnstore-Index nur in einer partitionierten Tabelle erstellt werden, wenn die Partitionierungsspalte eine der Spalten im columnstore-Index ist.
Datensatzgröße – Die Einschränkung für die Datensatzgröße des Indexschlüssels von 900 Byte gilt für columnstore-Indizes ebenfalls nicht.
Abfrageverarbeitung – Neben dem columnstore-Index wird mit SQL Server auch die Batchverarbeitung eingeführt, um die spaltenförmige Ausrichtung der Daten zu nutzen. Sowohl die columnstore-Struktur als auch die Batchverarbeitung tragen zur Leistungsoptimierung bei, aber die Untersuchung von Leistungsproblemen kann komplexer sein, als wenn nur ein Faktor beteiligt ist.
Tabelle kann nicht aktualisiert werden – Unter SQL Server 2012 kann eine Tabelle mit einem columnstore-Index nicht aktualisiert werden. Informationen zu Problemumgehungen finden Sie unter Bewährte Methoden: Aktualisieren von Daten in einem Columnstore-Index

Informationen zur Syntax für die Erstellung eines Columnstore-Indexes finden Sie unter CREATE COLUMNSTORE INDEX (Transact-SQL).

Datentypen

Die allgemeinen Geschäftsdatentypen können in einem columnstore-Index enthalten sein. Die folgenden Datentypen können in einem columnstore-Index enthalten sein.

char und varchar
nchar und nvarchar (außer varchar(max) und nvarchar(max))
decimal (und numeric), außer mit einer höheren Genauigkeit als 18 Stellen
int, bigint, smallint und tinyint
float (und real)
bit
money und smallmoney
Alle Datums- und Uhrzeitdatentypen (außer datetimeoffset mit mehr Dezimalstellen als 2)

Die folgenden Datentypen können nicht in einem columnstore-Index enthalten sein:

binary und varbinary
ntext, text und image
varchar(max) und nvarchar(max)
uniqueidentifier
rowversion (und timestamp)
sql_variant
decimal (und numeric) mit einer höheren Genauigkeit als 18 Stellen
datetimeoffset mit mehr Dezimalstellen als 2
CLR-Typen (hierarchyid und räumliche Typen)
xml

Potenzielle schlechte Leistung

Die Abfrageleistung bei der Entscheidungsunterstützung wird häufig verbessert, wenn columnstore-Indizes für große Tabellen verwendet werden, aber bei einigen Abfragen und sogar ganzen Arbeitsauslastungen kann es zu einer schlechteren Leistung kommen. Bei Verwendung eines kostenbasierten Ansatzes entscheidet sich der Abfrageoptimierer in der Regel nur dann für die Verwendung eines columnstore-Indexes, wenn sich dadurch die Gesamtleistung der Abfrage verbessert. Die vom Optimierer verwendeten Kostenmodelle basieren jedoch auf Annäherungen, und ein einigen Fällen entscheidet sich der Optimierer für die Verwendung des columnstore-Indexes für eine Tabelle, wenn es vorteilhafter gewesen wäre, für den Zugriff auf die Tabelle einen Zeilenspeicher zu verwenden (B-Struktur oder Heap). Falls dies eintritt, sollten Sie den Abfragehinweis IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX verwenden oder einen Indexhinweis nutzen, um den Optimierer auf einen Zeilenspeicherindex zu verweisen. Es kann sein, dass der Optimierer trotzdem noch einige Informationen aus dem columnstore-Index einbezieht. In seltenen Fällen kann es daher vorkommen, dass das Leistungsproblem mithilfe dieser Option nicht behoben werden kann. Falls die Arbeitsauslastungsleistung durch den Einsatz eines columnstore-Indexes nicht verbessert werden kann und Sie zum Beheben des Problems keine Indexhinweise verwenden können, sollten Sie den columnstore-Index löschen, um zur Zeilenspeicherverarbeitung zurückzukehren.

Problembereiche

Columnstore-Indizes von SQL Server und die spaltenbasierte Abfrageverarbeitung sind für typische Data Warehousing-Abfragen optimiert, die eine große Faktentabelle aufweisen und bei denen mittlere bis kleine Dimensionstabellen in einer Sternschemakonfiguration verwendet und dann gruppiert und aggregiert werden. Obwohl die Anzahl der Zeilen in der Faktentabelle groß ist, geben diese Abfragen in der Regel ein verhältnismäßig kleines Resultset zurück, da die Daten aggregiert werden. Die Leistung von Abfragen, für die der columnstore-Index verwendet wird, kann gering sein, falls mindestens eine der folgenden Bedingungen erfüllt ist.

Das Resultset ist groß, da die Daten nicht aggregiert werden. (Die Rückgabe eines großen Resultsets erfolgt naturgemäß langsamer als bei einem kleinen Resultset.)
Es findet kein Verknüpfungsvorgang, keine Filterung und keine Aggregation statt. In diesem Fall wird keine Batchverarbeitung durchgeführt. Daher beschränkt sich der Vorteil durch den columnstore-Index auf die Vorteile, die sich aus der Komprimierung und dem Lesen von weniger Spalten ergeben.
Zwei große Tabellen müssen so verknüpft werden, dass große Hashtabellen erstellt werden, die nicht in den Arbeitsspeicher passen und auf den Datenträger überlaufen müssen.
Es werden viele Spalten zurückgegeben, was dazu führt, dass ein größerer Anteil des columnstore-Index abgerufen wird.
Eine Verknüpfungsbedingung für eine Tabelle mit columnstore-Index enthält mehr als eine Spalte.

Sie können die weiter oben in diesem Abschnitt beschriebenen Methoden zur Umgehung der langsamen Verarbeitung per columnstore verwenden, falls dies aus einem der genannten Gründe auftritt.

Nach oben

Grundlagen: Einschränkungen von columnstore-Indizes

Grundlegende Einschränkungen

Für einen columnstore-Index gilt:

Kann nicht mehr als 1024 Spalten enthalten.
Kann nicht gruppiert werden. Nur nicht gruppierte columnstore-Indizes sind verfügbar.
Kann kein eindeutiger Index sein.
Kann nicht für eine Sicht oder indizierte Sicht erstellt werden.
Kann keine Spalte mit geringer Dichte enthalten.
Kann nicht als Primärschlüssel oder Fremdschlüssel dienen.
Kann nicht mit der ALTER INDEX-Anweisung geändert werden. Stattdessen sollte der Columnstore-Index gelöscht und neu erstellt werden. (Sie können einen Columnstore-Index mithilfe von ALTER INDEX deaktivieren und neu erstellen.)
Kann nicht mit dem INCLUDE-Schlüsselwort erstellt werden.
Kann nicht das ASC-Schlüsselwort oder das DESC-Schlüsselwort zum Sortieren des Index enthalten. Columnstore-Indizes werden gemäß den Komprimierungsalgorithmen sortiert. Die Sortierung ist im Index nicht möglich. Von einem columnstore-Index ausgewählte Werte könnten nach dem Suchalgorithmus sortiert werden, aber Sie müssen die Sortierung eines Resultsets mithilfe der ORDER BY-Klausel garantieren.
Verwendet oder behält die Statistiken nicht in der Art und Weise eines traditionellen Index.
Kann keine Spalte mit einem FILESTREAM-Attribut enthalten. Andere Spalten in der Tabelle, die im Index nicht verwendet werden, können das FILESTREAM-Attribut enthalten.

Eine Tabelle mit einem columnstore-Index kann nicht aktualisiert werden.

Informationen zur Umgehung dieses Problems finden Sie unter Bewährte Methoden: Aktualisieren von Daten in einem columnstore-Index.

Auswirkungen bei eingeschränktem Arbeitsspeicher

Die Spaltenspeicherverarbeitung ist für die Verarbeitung im Arbeitsspeicher optimiert. SQL Server implementiert Mechanismen, bei denen Daten und die meisten Datenstrukturen auf den Datenträger überlaufen können, falls kein ausreichender Arbeitsspeicher verfügbar ist. Falls starke Arbeitsspeichereinschränkungen gelten, wird für die Verarbeitung der Zeilenspeicher verwendet. Es kann Fälle geben, in denen der columnstore-Index als Zugriffsmethode ausgewählt wird, der Arbeitsspeicher aber nicht ausreicht, um die erforderlichen Datenstrukturen zu erstellen. Indem der Vorgang als columnstore-Vorgang gestartet und dann automatisch ein langsamerer Codepfad verwendet wird, kann es bei Abfragen, bei denen eine starke Arbeitsspeichereinschränkung herrscht, zu einem Abfall der Leistung kommen. Es hängt jeweils von der Abfrage ab, welche Anforderung an einen effektiven Arbeitsspeicher besteht. Für die Erstellung eines columnstore-Indexes sind ungefähr 8 MB multipliziert mit der Anzahl der Spalten im Index multipliziert mit dem Grad an Parallelität erforderlich. Im Allgemeinen nehmen die Arbeitsspeicheranforderungen mit dem Anteil der Spalten zu, bei denen es sich um Zeichenfolgen handelt. Indem Sie den Grad an Parallelität verringern, können Sie daher die Arbeitsspeicheranforderungen zum Erstellen des columnstore-Indexes reduzieren.

Auswertung einiger Ausdrücke wird mehr als für andere Ausdrücke beschleunigt

Einige allgemeine Ausdrücke werden mithilfe eines Batchmodus ausgewertet, anstatt für jede Zeile einzeln, wenn der columnstore-Index verwendet wird. Der Batchmodus bietet über die Vorteile bei Verwendung eines columnstore-Indexes hinaus eine zusätzliche Abfragebeschleunigung. Nicht jeder Abfrageausführungsoperator ist für die Batchmodusverarbeitung aktiviert.

Columnstore-Index unterstützt SEEK nicht

Wenn für die Abfrage erwartet wird, dass nur ein Bruchteil der Zeilen zurückgegeben wird, ist es unwahrscheinlich, dass der Optimierer den columnstore-Index auswählt (Beispiel: Abfragen vom Typ Nadel im Heuhaufen). Wenn der Tabellenhinweis FORCESEEK verwendet wird, zieht der Optimierer den columnstore-Index nicht in Erwägung.

Columnstore-Indizes können nicht mit den folgenden Funktionen kombiniert werden:

Seiten- und Zeilenkomprimierung und vardecimal-Speicherformat (ein Columnstore-Index ist bereits in einem anderen Format komprimiert)
Replikation
Änderungsnachverfolgung
Change Data Capture
Filestream

Nach oben

Demobeispiel: Columnstore-Indizes mit einer partitionierten Tabelle

In den Beispielen in diesem Thema wird eine partitionierte Tabelle mit dem Namen FactResellerSalesPtnd verwendet, die in der Beispieldatenbank AdventureWorksDW2012 erstellt wurde. Um in einer partitionierten Tabelle einen columnstore-Index zu testen, stellen Sie eine Verbindung mit der Datenbank AdventureWorksDW2012 her und führen den folgenden Code zum Erstellen einer partitionierten Version der Faktentabelle aus.

Hinweis
Informationen zu den Beispieldatenbanken und Anweisungen zum Herunterladen der Datenbank finden Sie unter AdventureWorks-Beispieldatenbanken.

Erstellen der FactResellerSalesPtnd-Tabelle

Führen Sie den folgenden Code aus, um eine partitionierte Version der FactResellerSales-Tabelle mit dem Namen FactResellerSalesPtnd zu erstellen.

USE AdventureWorksDW2012;
GO

CREATE PARTITION FUNCTION [ByOrderDateMonthPF](int) AS RANGE RIGHT 
FOR VALUES (
    20050701, 20050801, 20050901, 20051001, 20051101, 20051201, 
    20060101, 20060201, 20060301, 20060401, 20060501, 20060601, 
    20060701, 20060801, 20060901, 20061001, 20061101, 20061201, 
    20070101, 20070201, 20070301, 20070401, 20070501, 20070601, 
    20070701, 20070801, 20070901, 20071001, 20071101, 20071201, 
    20080101, 20080201, 20080301, 20080401, 20080501, 20080601, 
    20080701, 20080801, 20080901, 20081001, 20081101, 20081201
) 
GO

CREATE PARTITION SCHEME [ByOrderDateMonthRange] 
AS PARTITION [ByOrderDateMonthPF] 
ALL TO ([PRIMARY]) 
GO

-- Create a partitioned version of the FactResellerSales table
CREATE TABLE [dbo].[FactResellerSalesPtnd]( 
    [ProductKey] [int] NOT NULL, 
    [OrderDateKey] [int] NOT NULL, 
    [DueDateKey] [int] NOT NULL, 
    [ShipDateKey] [int] NOT NULL, 
    [CustomerKey] [int] NOT NULL, 
    [EmployeeKey] [int] NOT NULL, 
    [PromotionKey] [int] NOT NULL, 
    [CurrencyKey] [int] NOT NULL, 
    [SalesTerritoryKey] [int] NOT NULL, 
    [SalesOrderNumber] [nvarchar](20) NOT NULL, 
    [SalesOrderLineNumber] [tinyint] NOT NULL, 
    [RevisionNumber] [tinyint] NULL, 
    [OrderQuantity] [smallint] NULL, 
    [UnitPrice] [money] NULL, 
    [ExtendedAmount] [money] NULL, 
    [UnitPriceDiscountPct] [float] NULL, 
    [DiscountAmount] [float] NULL, 
    [ProductStandardCost] [money] NULL, 
    [TotalProductCost] [money] NULL, 
    [SalesAmount] [money] NULL, 
    [TaxAmt] [money] NULL, 
    [Freight] [money] NULL, 
    [CarrierTrackingNumber] [nvarchar](25) NULL, 
    [CustomerPONumber] [nvarchar](25) NULL,
    OrderDate [datetime] NULL,
    DueDate [datetime] NULL,
    ShipDate [datetime] NULL
) ON ByOrderDateMonthRange(OrderDateKey);
GO

-- Using simple or bulk logged recovery mode, and then the TABLOCK 
-- hint on the target table of the INSERT…SELECT is a best practice
-- because it causes minimal logging and is therefore much faster.
ALTER DATABASE AdventureWorksDW2012 SET RECOVERY SIMPLE;
GO

-- Copy the data from the FactResellerSales into the new table
INSERT INTO dbo.FactResellerSalesPtnd WITH(TABLOCK)
SELECT * FROM dbo.FactResellerSales;
GO

-- Create the columnstore index
CREATE NONCLUSTERED COLUMNSTORE INDEX [csindx_FactResellerSalesPtnd]
ON [FactResellerSalesPtnd]
( 
    [ProductKey], 
    [OrderDateKey], 
    [DueDateKey], 
    [ShipDateKey], 
    [CustomerKey], 
    [EmployeeKey], 
    [PromotionKey], 
    [CurrencyKey], 
    [SalesTerritoryKey], 
    [SalesOrderNumber], 
    [SalesOrderLineNumber], 
    [RevisionNumber], 
    [OrderQuantity], 
    [UnitPrice], 
    [ExtendedAmount], 
    [UnitPriceDiscountPct], 
    [DiscountAmount], 
    [ProductStandardCost], 
    [TotalProductCost], 
    [SalesAmount], 
    [TaxAmt], 
    [Freight], 
    [CarrierTrackingNumber], 
    [CustomerPONumber], 
    [OrderDate],
    [DueDate],
    [ShipDate]
);

Führen Sie nun eine Abfrage aus, die vom columnstore-Index profitieren kann, und vergewissern Sie sich, dass der columnstore-Index verwendet wird.

Testen des columnstore-Indexes

Drücken Sie STRG+M, oder wählen Sie im Menü Abfrage die Option Tatsächlichen Ausführungsplan einschließen. Dadurch wird eine grafische Darstellung des tatsächlichen Ausführungsplans aktiviert, der von SQL Server Management Studio verwendet wird.
Führen Sie im Abfrage-Editor-Fenster die folgende Abfrage aus.
```
SELECT SalesTerritoryKey, SUM(ExtendedAmount) AS SalesByTerritory
FROM FactResellerSalesPtnd
GROUP BY SalesTerritoryKey;
```
Stellen Sie im Fenster Ergebnisse auf der Registerkarte Ausführungsplan sicher, dass der Abfrageplan einen Indexscan des nicht gruppierten csindx_FactResellerSalesPtnd-Indexes ausgewählt hat.

Hinweis

Weitere Informationen zu Symbolen des grafischen Showplans finden Sie unter Referenz zu logischen und physischen Showplanoperatoren.

Nach oben

Hinweis
Weitere Informationen zu Symbolen des grafischen Showplans finden Sie unter Referenz zu logischen und physischen Showplanoperatoren.

Grundlagen: Typische columnstore-Indexszenarien

Stern- und Schneeflocken-Datenbankschemas werden normalerweise in Data Warehouses mit Dimensionsdaten und Data Marts verwendet, in denen die Geschwindigkeit des Datenabrufs wichtiger als die Effizienz von Datenbearbeitungen ist. Die Spaltenspeichertechnologie in SQL Server 2012 kann Abfragen erkennen und beschleunigen, die auf Stern- und Schneeflockenschemas abzielen.

Beispiele:

Hinweis
In den folgenden Beispielen wird die Batchverarbeitung ggf. nicht verwendet, weil die Tabellen nicht groß genug sind. Der Batchausführungsmodus, z. B. die Parallelverarbeitung, wird nur für aufwändigere Abfragen verwendet.

A: Aggregatabfrage, die zwei Tabellen verknüpft

Angenommen, eine Sternverknüpfungsabfrage berechnet die von Produkt 215 pro Quartal verkaufte Menge. Die Faktentabelle mit dem Namen FactResellerSalesPtnd wird für die Spalte OrderDateKey partitioniert. Eine der Dimensionstabellen mit dem Namen DimDate ist über eine Primär-/Fremdschlüssel-Beziehung im Datumsschlüssel mit der Faktentabelle verknüpft.
```
SELECT d.CalendarYear,
    d.CalendarQuarter,
    COUNT(*) AS NumberSold
FROM dbo.FactResellerSalesPtnd AS f
    JOIN dbo.DimDate AS d
    ON f.OrderDateKey = d.DateKey
WHERE ProductKey = 215
GROUP BY d.CalendarYear, d.CalendarQuarter
ORDER BY d.CalendarYear, d.CalendarQuarter;
```

Darüber hinaus gibt es Abfragen, die möglicherweise nur auf eine einzelne Tabelle abzielen. In solchen Fällen versucht SQL Server die Vorteile der Batchausführung und der Spaltenspeichertechnologie zu nutzen, um auch die Abfrageausführung zu beschleunigen.

B: Einfache Aggregatabfrage für eine einzelne Tabelle

Die Faktentabelle ist FactResellerSalesPtnd, und diese Tabelle wird für die Spalte OrderDateKey partitioniert. Die folgende Abfrage gibt die Anzahl der Zeilen und die Anzahl der Bestellungen zurück.
```
SELECT COUNT(*) AS NumberOfRows, 
    COUNT(DISTINCT(f.SalesOrderNumber)) AS NumberOfOrders
FROM dbo.FactResellerSalesPtnd AS f;
```
Für Abfragen in einem typischen Data Warehousing-Szenario kann normalerweise eine Geschwindigkeitssteigerung um den Faktor 1,5 bis 10 erzielt werden, wenn während der Abfrageausführung columnstore-Indizes eingesetzt werden und der Batchausführungsmodus verwendet wird. Für einige Sternverknüpfungsabfragen fällt die Beschleunigungssteigerung noch deutlich höher aus.

Grundlagen: Bitmapfilteroptimierungen

Zusätzlich zum Layout der Daten im Spaltenformat verwendet SQL Server Bitmapfilter, die zum Verbessern der Leistung während der Abfrageausführung an das Speichermodul übergeben werden. Die Bitmapfilter erhöhen die Geschwindigkeit der Abfrageausführung, indem die Anzahl der einbezogenen Zeilen reduziert wird, bevor Joins implementiert werden. Auf diese Weise verringert sich die Anzahl der Zeilen, die vom Joinoperator verarbeitet werden. Die Bitmap wird auf der Erstellungsseite eines Hashjoins erstellt, aber die eigentlichen Bitmapüberprüfungen werden auf der Überprüfungsseite des Hashjoins ausgeführt. Sie können die Verwendung von Bitmapfiltern entweder mithilfe des grafischen Ausführungsplans oder des XML-Ausführungsplans verfolgen.

Bewährte Methoden: Aktualisieren von Daten in einem columnstore-Index

Tabellen, die über einen columnstore-Index verfügen, können nicht aktualisiert werden. Es gibt drei Möglichkeiten, dieses Problem zu umgehen.

Um eine Tabelle mit einem columnstore-Index zu aktualisieren, löschen Sie den columnstore-Index, führen die erforderlichen INSERT-, DELETE-, UPDATE- oder MERGE-Vorgänge aus und erstellen den columnstore-Index dann neu.
Partitionieren Sie die Tabelle, und wechseln Sie die Partitionen. Fügen Sie Daten für eine Masseneinfügung in eine Stagingtabelle ein, erstellen Sie für die Stagingtabelle einen columnstore-Index, und wechseln Sie für die Stagingtabelle dann zu einer leeren Partition. Falls Sie andere Updates durchführen möchten, wechseln Sie für eine Partition aus der Haupttabelle in eine Stagingtabelle, deaktivieren oder löschen Sie den columnstore-Index der Stagingtabelle, führen Sie die Updateschritte aus, erstellen Sie den columnstore-Index für die Stagingtabelle neu, und wechseln Sie für die Stagingtabelle dann zurück in die Haupttabelle.
Fügen Sie statische Daten in eine Haupttabelle mit einem columnstore-Index ein, und ordnen Sie neue Daten und jüngere Daten, die sich mit hoher Wahrscheinlichkeit ändern werden, in einer separaten Tabelle mit dem gleichen Schema an, das nicht über einen columnstore-Index verfügt. Wenden Sie die Updates auf die Tabelle mit den neuesten Daten an. Zum Abfragen der Daten schreiben Sie die Abfrage als zwei Abfragen neu, eine für jede Tabelle. Kombinieren Sie die beiden Resultsets dann mit UNION ALL. Die Unterabfrage für die große Haupttabelle profitiert vom columnstore-Index. Falls die aktualisierbare Tabelle deutlich kleiner ist, wirkt sich das Fehlen des columnstore-Indexes weniger stark auf die Leistung aus. Es ist zwar auch möglich, eine Sicht abzufragen, die basierend auf UNION ALL für zwei Tabellen erstellt wurde, aber unter Umständen ist in diesem Fall kein deutlicher Leistungsvorteil zu beobachten. Die Leistung hängt vom Abfrageplan ab, der wiederum von der Abfrage, den Daten und den Kardinalitätsschätzungen abhängt. Der Vorteil bei Verwendung einer Sicht besteht darin, dass ein INSTEAD OF-Trigger der Sicht Updates an die Tabelle umleiten kann, die nicht über einen columnstore-Index verfügt. Der Sichtmechanismus wäre für Benutzer und Anwendungen dann transparent. Wenn Sie einen dieser Ansätze mit UNION ALL verwenden, sollten Sie die Leistung an typischen Abfragen testen und dann die Entscheidung treffen, ob die Vorteile einen etwaigen Leistungsabfall wettmachen.

Hinweis
Erstellen Sie keinen columnstore-Index als Mechanismus zum Herstellen des Schreibschutzes für eine Tabelle. Die Einschränkung in Bezug auf das Aktualisieren von Tabellen mit columnstore-Index kann für zukünftige Versionen nicht garantiert werden. Wenn schreibgeschütztes Verhalten erforderlich ist, sollten Sie dies umsetzen, indem Sie eine schreibgeschützte Dateigruppe erstellen und die Tabelle in diese Dateigruppe verschieben.

Erstellen Sie keinen columnstore-Index als Mechanismus zum Herstellen des Schreibschutzes für eine Tabelle. Die Einschränkung in Bezug auf das Aktualisieren von Tabellen mit columnstore-Index kann für zukünftige Versionen nicht garantiert werden. Wenn schreibgeschütztes Verhalten erforderlich ist, sollten Sie dies umsetzen, indem Sie eine schreibgeschützte Dateigruppe erstellen und die Tabelle in diese Dateigruppe verschieben.

Bewährte Methoden: Auswählen von Spalten für einen Spaltenspeicherindex

Ein Teil des Leistungsvorteils eines columnstore-Indexes beruht auf den Komprimierungsverfahren, bei denen die Anzahl der Datenseiten reduziert wird, die zum Verarbeiten der Abfrage gelesen und verarbeitet werden müssen. Die Komprimierung funktioniert am besten für Spalten mit Buchstaben oder Zahlen, die einen hohen Grad an Duplizität aufweisen. Dimensionstabellen können z. B. Spalten für Postleitzahlen, Orte und Verkaufsbereiche aufweisen. Falls jeder Ort über viele Postleitzahlen verfügt und jeder Verkaufsbereich viele Orte umfasst, wird die Spalte mit dem Verkaufsbereich am stärksten komprimiert, die Spalte mit dem Ort etwas weniger und die Spalte mit der Postleitzahl am wenigsten. Obwohl alle Spalten gute Kandidaten für einen columnstore-Index sind, erzielen Sie mit dem Hinzufügen der Spalte mit dem Verkaufsbereichscode zum columnstore-Index in Bezug auf die columnstore-Komprimierung den größten Vorteil, während sich für den Postleitzahlencode der geringste Vorteil ergibt.

Nach oben

Bewährte Methoden: Partitionierte Tabellen

Columnstore-Indizes sind dafür ausgelegt, Abfragen in sehr großen Data Warehouse-Szenarien zu unterstützen, bei denen die Partitionierung häufig verwendet wird. Die Partitionierung wird empfohlen, falls die Daten in einer Tabelle mit einem columnstore-Index in regelmäßigen Abständen aktualisiert werden müssen. Weitere Informationen zum Update von Partitionen eines columnstore-Indexes finden Sie im vorherigen Abschnitt Bewährte Methoden: Aktualisieren von Daten in einem columnstore-Index.

Nach oben

Vorgehensweise: Erstellen eines columnstore-Indexes

Das Erstellen eines columnstore-Indexes gleicht der Erstellung anderer Indizes. Sie können einen columnstore-Index mithilfe von Transact-SQL oder mit den grafischen Tools von SQL Server Management Studio erstellen.

Erstellen eines columnstore-Indexes mit Transact-SQL

Führen Sie im Abfrage-Editor-Fenster die CREATE COLUMNSTORE INDEX-Anweisung aus. Ein Beispiel finden Sie oben unter Erstellen der FactResellerSalesPtnd-Tabelle. Weitere Informationen finden Sie unter CREATE COLUMNSTORE INDEX (Transact-SQL).

Erstellen eines columnstore-Indexes mit SQL Server Management Studio

Verwenden Sie in Management Studio den Objekt-Explorer, um eine Verbindung mit einer Instanz von SQL Server Database Engine (Datenbankmodul) herzustellen.
Erweitern Sie im Objekt-Explorer die Instanz von SQL Server, erweitern Sie Datenbanken, erweitern Sie eine Datenbank, erweitern Sie eine Tabelle, klicken Sie mit der rechten Maustaste auf eine Tabelle, zeigen Sie auf Neuer Index, und klicken Sie dann auf die Option für den nicht gruppierten columnstore-Index.
Geben Sie im Dialogfeld Indexname auf der Registerkarte Allgemein einen Namen für den neuen Index ein, und klicken Sie dann auf Hinzufügen.
Wählen Sie im Dialogfeld Spalten auswählen die Spalten aus, die am columnstore-Index beteiligt sein sollen, und klicken Sie zweimal auf OK, um den Index zu erstellen.

Vorgehensweise: Bestimmen der Größe eines columnstore-Indexes

Ein columnstore-Index besteht sowohl aus Segmenten als auch aus Wörterbüchern. Im folgenden Beispiel wird veranschaulicht, wie die Gesamtgröße eines columnstore-Indexes (in der Tabelle FactResellerSalesPtnd) bestimmt wird, indem die on_disk_size-Spalten von sys.column_store_segments und sys.column_store_dictionaries kombiniert werden.

SELECT SUM(on_disk_size_MB) AS TotalSizeInMB
FROM
(
   (SELECT SUM(css.on_disk_size)/(1024.0*1024.0) on_disk_size_MB
    FROM sys.indexes AS i
    JOIN sys.partitions AS p
        ON i.object_id = p.object_id 
    JOIN sys.column_store_segments AS css
        ON css.hobt_id = p.hobt_id
    WHERE i.object_id = object_id('FactResellerSalesPtnd') 
    AND i.type_desc = 'NONCLUSTERED COLUMNSTORE') 
  UNION ALL
   (SELECT SUM(csd.on_disk_size)/(1024.0*1024.0) on_disk_size_MB
    FROM sys.indexes AS i
    JOIN sys.partitions AS p
        ON i.object_id = p.object_id 
    JOIN sys.column_store_dictionaries AS csd
        ON csd.hobt_id = p.hobt_id
    WHERE i.object_id = object_id('FactResellerSalesPtnd') 
    AND i.type_desc = 'NONCLUSTERED COLUMNSTORE') 
) AS SegmentsPlusDictionary

Vorgehensweise: Problembehandlung der Leistung eines Spaltenspeicherindexes

Untersuchen Sie den Abfrageausführungsplan, um zu bestimmen, ob ein columnstore-Index verwendet wird. Falls der maximale Vorteil erzielt wird, sind drei Elemente vorhanden.

Der columnstore-Index befindet sich im Abfrageausführungsplan.

columnstore-Index Scan-Operator (Symbol)

Falls der columnstore-Index nicht verwendet wird und Sie der Meinung sind, dass der columnstore-Index für die Abfrage einen Vorteil darstellen würde, werten Sie die Abfrageleistung aus, während Sie die Verwendung des columnstore-Indexes mit dem WITH (INDEX(<indexname>))-Hinweis erzwingen. Das folgende Beispiel zeigt eine Abfrage mit einem Indexhinweis.
```
SELECT d.CalendarYear,
    d.CalendarQuarter,
    COUNT(*) AS NumberSold
FROM dbo.FactResellerSalesPtnd AS f WITH (INDEX(csindx_FactResellerSalesPtnd))
    JOIN dbo.DimDate AS d
    ON f.OrderDateKey = d.DateKey
WHERE ProductKey = 215
GROUP BY d.CalendarYear, d.CalendarQuarter
ORDER BY d.CalendarYear, d.CalendarQuarter;
```
Wenn Sie den Zeiger im grafischen Abfrageplan über das columnstore-Indexsymbol bewegen, wird als tatsächlicher Ausführungsmodus anstelle von "Zeile" der Modus "Batch" angegeben.
Im grafischen Ausführungsplan ist ein Symbol für den physischen Operator "Bitmap" enthalten, das angibt, dass die Anzahl der Zeilen vor einem Verknüpfungsvorgang per Bitmapfilter reduziert wird.

Bitmap-Operator (Symbol)

Nach oben

Columnstore-Indizes

Inhalt

Grundlagen: Beschreibung von columnstore-Indizes

Datentypen

Potenzielle schlechte Leistung

Grundlagen: Einschränkungen von columnstore-Indizes

Demobeispiel: Columnstore-Indizes mit einer partitionierten Tabelle

Erstellen der FactResellerSalesPtnd-Tabelle

Testen des columnstore-Indexes

Grundlagen: Typische columnstore-Indexszenarien

Beispiele:

A: Aggregatabfrage, die zwei Tabellen verknüpft

B: Einfache Aggregatabfrage für eine einzelne Tabelle

Grundlagen: Bitmapfilteroptimierungen

Bewährte Methoden: Aktualisieren von Daten in einem columnstore-Index

Bewährte Methoden: Auswählen von Spalten für einen Spaltenspeicherindex

Bewährte Methoden: Partitionierte Tabellen

Vorgehensweise: Erstellen eines columnstore-Indexes

Erstellen eines columnstore-Indexes mit Transact-SQL

Erstellen eines columnstore-Indexes mit SQL Server Management Studio

Vorgehensweise: Bestimmen der Größe eines columnstore-Indexes

Vorgehensweise: Problembehandlung der Leistung eines Spaltenspeicherindexes

Verwandte Tasks

Verwandte Inhalte

Zusätzliche Ressourcen