SQL Server 2014: Extended Buffer Pool und Clustered Columnstore Index erstellen

Mit SQL Server 2014 kann jetzt auch ein aktualisierbarer Clustered Columnstore Index erstellt werden. Wir zeigen, welche Schritte dafür nötig sind.

SQL Server 2014 wurde Anfang April in verschiedenen Versionen mit unterschiedlichem Funktionsumfang veröffentlicht. Neben einem allgemeinen Überblick über Skalierbarkeit und Hochverfügbarkeit sowie In-Memory-OLTP und Cloud-Integration widmet sich dieser Artikel den beiden neuen Features Extended Buffer Pool und Clustered Columnstore Indexes, ein zweiter Artikel erklärt den Umgang mit partitionierten Indizes und speicheroptimierten Tabellen.

So erstellen Sie Extended Buffer Pools in SQL 2014

Der Buffer Pool spielt beim Speichern von Daten-Pages und bei der I/O-Reduzierung eine entscheidende Rolle. Bei SQL Server 2014 können Sie den Buffer Pool auf permanentes Storage wie ein Solid-State Drive (SSD) oder ein SSD-Array erweitern. SQL Server schreibt nur saubere Pages auf diese Puffer-Erweiterung, um damit das Risiko eines Datenverlustes so gering wie möglich zu halten. Durch die Erweiterung des Buffer Pools dürften Sie einen Performance-Gewinn bei Microsofts In-Memory-Implementierung OLTP (Online Transcation Processing) bemerken, vor allem bei Lese-intensiven Workloads.

Um eine Buffer Memory Extension zu erstellen, müssen Sie einfach die Einstellung BUFFER POOL EXTENSION auf Server-Ebene der entsprechenden SQL-Server-Instanz modifizieren. Bevor Sie das erledigen, sollten Sie allerdings die derzeitige Konfiguration der sys.dm_os_buffer_pool_extension_configuration in Augenschein nehmen. Das erledigen Sie über den folgenden Befehl:

SELECT * FROM sys.dm_os_buffer_pool_extension_configuration

Diese Anweisung liefert Ihnen die gewünschten Details zur derzeitigen Konfiguration. Sie enthält die Spalte state_description, die den derzeitigen Status des Buffer Pools zeigt. Per Standard ist die Buffer Pool Extension deaktiviert, in diesem Fall zeigt der Status BUFFER POOL EXTENSION DISABLED an.

Sie können diesen Zustand allerdings recht einfach mithilfe des Statements ALTER SERVER CONFIGURATION ändern und die Einstellung BUFFER POOL EXTENSION auf ON setzen. Nachfolgende T-SQL-Anweisung erledigt das:

ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
(FILENAME='C:\BufferPool\cache.bpe', SIZE = 5 GB);

Ändern Sie diese Einstellung auf ON, müssen Sie Pfad und Dateiname der Cache-Datei hinterlegen, die mit der Erweiterung .bpe versehen sein muss. Weiterhin ist es notwendig, die Cache-Größe zu spezifizieren. Im obigen Beispiel habe ich einen Ordner auf meiner lokalen Festplatte benutzt. In der Praxis würden Sie hier eher eine SSD oder ein SSD-Array angeben.

Sobald Sie das Statement laufen lassen, kreiert der SQL Server eine-Cache-Datei am Ziel-Ort. Abbildung 1 zeigt die Datei cache.bpe auf meiner lokalen Festplatte.

Abbildung 1: So sieht die Cache-Datei einer Buffer Pool Extension aus.

So einfach erstellen Sie einen erweiterten Buffer Pool. Die Einstellungen können Sie überprüfen, indem Sie abermals die dynamische Management-Ansicht sys.dm_os_buffer_pool_extension_configuration konsultieren. Nun sollte die Spalte state_description den Wert als BUFFER POOL EXTENSION CLEAN PAGE CACHING ENABLED anzeigen. Sie bekommen nun auch Details über die Cache-Datei angezeigt.

Möchten Sie die Buffer Pool Extension abschalten, können Sie einfach das Statement ALTER SERVER CONFIGURATION abermals bemühen und die Einstellung auf OFFsetzen:

ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION OFF;

Nach dem Ausführen der Anweisung wird der SQL Server die Cache-Datei entfernen und es liegt kein erweiterter Buffer Pool mehr vor.

Geclusterte Columnstore Indexes in SQL 2014 erstellen

Microsoft hat Columnstore-Indizes in SQL Server 2012 eingeführt. Anders als typische B-Tree-Indizes verwendet ein Columnstore-Index ein Säulen-artiges Format und macht sich die Speicher-Management-Möglichkeiten und die fortgeschrittenen Komprimierungs-Algorithmen von xVelocity zu Nutze. Columnstore-Indizes sind vor allen Dingen bei in hohem Maße aggregierten Data-Warehouse-Abfragen nützlich. Allerdings wurden in SQL Server 2012 nur ungeclusterte Columnstore-Indizes unterstützt, die sich nicht aktualisieren ließen. Man konnte sie nur löschen und wieder neu erstellen.

SQL 2014 bringt nun eine neue Index-Art mit sich: einen geclusterten Columnstore-Index, der sich aktualisieren lässt, ohne dabei den Index verwerfen zu müssen. Dieser Index wird dabei genauso einfach erstellt, wie alle anderen Indizes auch. Nehmen wir an, dass wir die folgende Anweisung SELECT...INTO verwenden, um der Datenbank AdventureWorksDW2012 die Tabelle ResellerSales anzufügen.

USE AdventureWorksDW2012;
GO

SELECT *
INTO ResellerSales
FROM FactResellerSales;

Auf meinem System speichert das Statement 60.855 Reihen in der neuen Tabelle. Nun können wir einen  geclusterten Columnstore-Index in dieser Tabelle erstellen und benutzen. Dafür dient nachfolgende T-SQL-Anweisung:

CREATE CLUSTERED COLUMNSTORE INDEX csi_clustered
ON ResellerSales;

Sie sehen, dass wir die Schlüsselwörter CREATE CLUSTERED COLUMNSTORE INDEX und den Index-Namen (csi_clustered) spezifizieren. Danach folgt eine ON-Klausel, die auf die Ziel-Tabelle verweist. Nach dem Ausführen der Anweisung können wir die Erstellung des Index verifizieren, indem wir uns diesen mithilfe des Object Explorer im SQL Server Management Studio (SSMS) ansehen.

Abbildung 2: SQL Server 2014 unterstützt nun geclusterte Columnstore Indizes.

Auch wenn ein geclusterter Columnstore-Index einfach zu implementieren ist, müssen trotzdem einige Beschränkungen beachtet werden. Zum Beispiel kann die Tabelle keine ungeclusterten Indizes, Unique Constraints, Primary Keys oder Foreign Keys enthalten. Und natürlich dürfen Sie nur einen geclusterten Index definieren. Sie können aber eine Tabelle mit einem herkömmlichen geclustertem Index konvertieren, so dass diese danach einen geclusterten Columnstore Index enthält. Dazu verwenden Sie einfach das Statement CREATE CLUSTERED COLUMNSTORE INDEX, wie im nachfolgenden Beispiel zu sehen ist:

DROP INDEX ResellerSales.csi_clustered;

CREATE CLUSTERED INDEX ix_clustered
ON ResellerSales (SalesOrderNumber, SalesOrderLineNumber);

CREATE CLUSTERED COLUMNSTORE INDEX ix_clustered
ON ResellerSales
WITH (DROP_EXISTING = ON);

Um die Umgebung vorzubereiten, benutzen wir zunächst die Anweisung DROP INDEX. Damit löschen wir den geclusterten Columnstore Index, den wir im vorherigen Beispiel erstellt haben. Im Anschluss definieren wir mithilfe des Statements CREATE CLUSTERED INDEX einen regulären geclusterten Index, der auf den Spalten SalesOrderNumber und SalesOrderLineNumber basiert.

Danach verwenden wir das Statement CREATE CLUSTERED COLUMNSTORE INDEX, um den regulären geclusterten Index zu verwerfen und einen neuen Columstore-Index zu erstellen. Nachdem wir die Ziel-Tabelle in der ON-Klausel spezifiziert haben, verwenden wir zusätzlich eine WITH-Klausel. In dieser ist die Option DROP_EXISTING auf ON gesetzt. Daraus resultierend wird ein existierender geclusterter Index verworfen und dafür ein neuer geclusterter Columnstore Index erstellt. Beachten Sie allerdings, dass sich der Index-Name in der Anweisung CREATE CLUSTERED COLUMNSTORE INDEX auf den zu ersetzenden Index beziehen muss.

Wenden wir uns nun dem Aufbau des neuen geclusterten Columstore-Index zu. In SQL Server 2014 ist dies, wie bereits erwähnt, ohne verwerfen und neu anlegen möglich. Eine mögliche Option ist die Benutzung des Statements CREATE CLUSTERED COLUMNSTORE INDEX, das wir im vorigen Beispiel benutzt haben. SQL Server kann den Index auch mithilfe einer Anweisung vom Typ CREATE neu aufbauen.

Eine weitere Möglichkeit für einen so genannten Rebuild des Index ist die Verwendung der Anweisung ALTER INDEX, wie folgendes Beispiel zeigt.

ALTER INDEX ix_clustered
ON ResellerSales
REBUILD;

Wir spezifizieren entsprechend die Namen von Index und Tabelle und fügen gleichzeitig eine REBUILD-Klausel ein.

Ob Sie die Anweisungen CREATE CLUSTERED COLUMNSTORE INDEX oder ALTER INDEX für das Rebuild verwenden ist dabei egal. Der SQL Server aktualisiert den Index, ohne diesen explizit zu verwerfen und neu anzulegen. Wir können sogar testen, wie das funktioniert. Verwenden wir zunächst die Katalog-Ansicht sys.column_store_row_groups, um den Status des geclusterten Index abzufragen.

SELECT * FROM sys.column_store_row_groups
WHERE object_id = OBJECT_ID('ResellerSales');

Abbildung 3: Eine Columnstore Row Group zeigt einen komprimierten Status, wenn der Index auf dem aktuellen Stand ist.

Abbildung 3 zeigt das Ergebnis dieser Anweisung. Beachten Sie, dass die Spalte state_description den Status als COMPRESSED ausweist und die gesamte Anzahl der Reihen 60855 ist. Genau das erwarten wir in diesem Fall. Alle Reihen sind einbezogen und komprimiert und wir wissen, dass der Index aktuell ist. Eine Row Group ist dabei ein Index-Segment, in das SQL Server einen Columnstore Index aufteilt.

Im Anschluss fügen wir der Tabelle weitere Reihen hinzu. Das folgende INSERT-Statement erweitert die Tabelle ResellerSales um weitere 60.855 Reihen.

INSERT INTO ResellerSales
SELECT *
FROM FactResellerSales;

Abbildung 4: Eine Columnstore Row Group zeigt einen OPEN-Status an, wenn der Index nicht auf dem neuesten Stand ist.

Fragen wir nun die Katalog-Ansicht sys.column_store_row_groups abermals ab. Diesmal bekommen wir zwei Reihen als Ergebnis, wie Sie in Abbildung 4 sehen. Beachten Sie, dass die erste Reihe den Wert von state_description als OPEN anzeigt und der Wert der total_rows 60855 ist. Die Original-Reihe bleibt dagegen unverändert. Anders gesagt haben wir der Tabelle zusätzliche Daten spendiert, die sich aber noch nicht in unserem Columnstore-Index wiederspiegeln.

Im nächsten Schritt bauen wir den Index neu auf, damit auch die neuen Daten angemessen einbezogen sind:

ALTER INDEX ix_clustered
ON ResellerSales
REBUILD;

Wie Sie bereits gesehen haben, muss die Anweisung ALTER INDEX die Klausel REBUILD enthalten, um den Index zu aktualisieren. Fragen Sie nun die Kataglog-Ansicht sys.column_store_row_groups erneut ab, würden Sie eine einzige Reihe sehen. Der Wert für state_description sollte COMPRESSED anzeigen und der Wert für total_rows 121710.

Erfahren Sie mehr über Business-Software

- GOOGLE-ANZEIGEN

ComputerWeekly.de

Close