SQL Server 2014: So aktualisieren Sie partitionierte Indizes

SQL Server 2014 bietet viele neue Funktionen. Wir zeigen Ihnen, welche Vorteile Ihnen partitionierte Indizes und speicheroptimierte Tabellen bringen.

Dies ist der zweite Teil einer Artikel-Serie, der wichtige Funktionen des kommenden SQL Server 2014 vorstellt. In diesem Beitrag konzentrieren wir uns  auf das Aktualisieren partitionierter Indizes und auf speicheroptimierte Tabellen. Im ersten Teil haben wir uns erweiterte Buffer Pools  und die Implementierung geclusterter ColumnStore-Indizes in SQL Server 2014 angesehen.

Microsoft SQL Server 2014 wurde Anfang April offiziell veröffentlicht und ist in verschiedenen Versionen mit unterschiedlichem Funktionsumfang erhältlich. SQL Server 2014 bietet zahlreiche Neuerungen bei In-Memory-OLTP und Cloud-Integration sowie bei Skalierbarkeit und Hochverfügbarkeit.

In diesem Artikel konzentriere ich aber mich auf zwei weitere Neuerungen, die SQL Server 2014 mit sich bringt: das Aktualisieren partitionierter Indizes und die Verwaltung von speicheroptimierten Tabellen.

Aktualisieren partitionierter Indizes in SQL Server 2014

Abbildung 1: Mit dem Create Partition Wizard lassen sich Grenzwerte erstellen.

Mit SQL 2014 können Sie also partitionierte Columnstore-Indizes neu aufbauen. Angenommen wir partitionieren die Tabelle ResellerSales. Um das zu realisieren, verwenden wir den Create Partition Wizard aus dem SQL Server Management Studio (SSMS). Auch wenn eine Schritt-für-Schritt-Anleitung durch den Assistenten den Rahmen dieses Artikels sprengen würde, möchte ich Ihnen dennoch einige Screenshots zeigen. So können sie sehen, wie die Tabelle auf meinem System partitioniert wurde. In Abbildung 1 sehen Sie das Dialog-Fenster Set Boundary Values, das Sie über die Seite Map Partitions des Assistenten erreichen. Ich habe die Partition basierend auf der Spalte OrderDate kreiert.

Abbildung 2: Sie können den Create Partition Wizard benutzen, um Partitionen zu mappen.

Nachdem ich die Werte im Dialog-Fenster Set Boundary Values gesetzt habe, ging es zurück zur Seite Map Partitions. Dort wurde eine Filegroup für jede Partition ausgewählt. In diesem Beispiel habe ich für alle Partitionen PRIMARY gesetzt. Im Anschluss habe ich auf die Schaltfläche Estimate storage gegklickt, um den Rest der Tabelle auszufüllen. Abbildung 2 zeigt die Ergebnisse auf meinem System.

Das soll nicht heißen, dass Sie Ihre Partitionen genauso setzen müssen. Allerdings sollte betont werden, dass sich die nachfolgenden Beispiele an dieser Konfiguration orientieren.

Nachdem Sie alle Partitionen erstellt haben, können Sie die Katalog-Ansicht sys.partitions verwenden. Damit erhalten Sie Informationen über die Partitionen, die Sie für die Tabelle ResellerSales erstellt haben. Sie realisieren das mit nachfolgender SELECT-Anweisung:

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

Abbildung 3: Die Katalog-Ansicht von sys.partitions gibt Einblicke in die Partitionen der Tabellen.

In Abbildung 3 sehen Sie das Ergebnis dieser Abfrage. Achten Sie darauf, dass es zwar fünf Partitionen gibt, aber nur die ersten vier davon Daten enthalten. Ebenso bemerkenswert ist die Spalte data_compression_desc, die für jede Reihe den Komprimierungs-Typ COLUMNSTORE anzeigt. Genau das wollen wir sehen.

Bei SQL Server 2014 können wir ein einzelnes T-SQL-Statement verwenden, um entweder alle partitionierten, geclusterten Columnstore-Indizes oder alle individuellen Indizes neu aufzubauen. Für ein Update aller Partitionen können wie das Statement ALTER INDEX zu Rate ziehen. In diesem Fall modifizieren wir allerdings den REBUILD-Abschnitt, um die Option PARTITION einzubeziehen. Hier ein mögliches Beispiel:

ALTER INDEX ix_clustered
ON ResellerSales
REBUILD PARTITION = ALL;

Wie Sie sehen, haben wir die Option PARTITION auf ALL gesetzt. Möchten Sie eine individuelle Partition aktualisieren, setzen Sie bei der Option eine spezielle Nummer ein. Sie können an dieser Stelle wieder die Katalog-Ansicht von sys.partitions verwenden, um die Partitions-Nummern zu erhalten. Zum Beispiel würde das folgende ALTER-INDEX-Statement die Partition 2 updaten:

ALTER INDEX ix_clustered
ON ResellerSales
REBUILD PARTITION = 2;

Die Möglichkeit, eine individuelle Partition in einem geclusterten Columnstore-Index wieder aufzubauen, ist natürlich bequem. Allerdings können Sie keine Live-Updates durchführen. So lange die Operation für den Wiederaufbau läuft, ist eine Abfrage oder eine Aktualisierung der Daten der dazugehörigen Tabelle nicht möglich.

Das gilt aber nicht für alle partitionierten Indizes in SQL Server 2014. Nehmen wir an, dass wir ein Statement SELECT...INTO verwenden, um die Tabelle ResellerSales2 zu erschaffen.

SELECT *
INTO ResellerSales2
FROM FactResellerSales;

Wie erwartet, fügt die Anweisung der neuen Tabelle 60.855 Zeilen hinzu. Im Anschluss partitionieren wir die Tabelle, wie wir das auch schon für ResellerSales getan haben. Danach verwenden wir das Statement CREATE CLUSTERED INDEX, um einen regulären (Nicht-Columnstore) geclusterten Index zu erstellen.

CREATE CLUSTERED INDEX ix_orderdate
ON ResellerSales2 (OrderDate);

Nun können wir die Indizes aktualisieren, die mit einer speziellen Partition assoziiert sind. Die Tabelle ist dabei weiterhin online. Um das zu realisieren, müssen wir das Statement ALTER INDEX modifizieren und um einen WITH-Abschnitt erweitern, wie das folgende Beispiel zeigt.

ALTER INDEX ix_orderdate
ON ResellerSales2
REBUILD PARTITION = 2
WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY
                (MAX_DURATION = 10, ABORT_AFTER_WAIT = BLOCKERS)));

Unsere WITH-Klausel muss die ONLINE-Option zunächst auf ON setzen. Danach werden die dazugehörigen Optionen gesetzt. In unserem Fall vermittelt die Option WAIT_AT_LOW_PRIORITY dem SQL Server, mit dem Aufbau des Indexes so lange zu warten, bis es keine blockierenden Operationen mehr in dieser Tabelle gibt. Die Option MAX_DURATION weist auf die Anzahl der Minuten hin, wie lange die Software auf diese blockierenden Operationen warten soll. Die Option ABORT_AFTER_WAIT bestimmt, was nach Ablauf der Wartezeit geschehen soll. In diesem Beispiel habe ich BLOCKERS spezifiziert. Damit wird dem SQL Server befohlen, sämtliche Transaktionen abzubrechen, die den Wiederaufbau des Indexes blockieren.

So erstellen Sie speicheroptimierte Tabellen

Die In-Memory OLTP Engine (Online Transaction Processing) mit Codenamen Hekaton ist in SQL Server 2014 eine absolute Neuheit. Die Storage-Engine ist nun in das Datenbank-Managementsystem integriert, verwendet aber fortgeschrittene In-Memory-Technologien, um groß angelegte OLTP-Workloads zu unterstützen. Um diese neue Funktion ausnützen zu können, muss die Datenbank speicheroptimierte („memory-optimized“) Dateigruppen  und Tabellen enthalten. Zum Glück ist der Prozess in SQL Server 2014 recht unkompliziert.

Um den Vorgang zu demonstrieren, habe ich eine Datenbank mit Namen TestHekaton erstellt. Im Anschluss wird dieser Datenbank eine speicheroptimierte Dateigruppe hinzugefügt. Der nachfolgende T-SQL-Code zeigt, wie das beispielsweise funktioniert:

USE master;
GO

CREATE DATABASE TestHekaton;
GO

ALTER DATABASE TestHekaton
ADD FILEGROUP HekatonFG CONTAINS MEMORY_OPTIMIZED_DATA;
GO

Abbildung 4: Die Datenbank-Eigenschaften zeigen die neue speicheroptimierte Dateigruppe.

Beachten Sie die Klausel ADD FILEGROUP im Statement ALTER DATABASE. Sie enthält den Namen der Dateigruppe (HekatonFG) und die Schlüsselwörter CONTAINS MEMORY_OPTIMIZED_DATA. Das weist den SQL Server an, die Art von Dateigruppe zu erstellen, die für die Verwendung der In-Memory-OLTP-Engine notwendig ist. Wollen Sie bestätigt haben, dass die Dateigruppe erschaffen wurde, können Sie die Filegroups-Sektion der Datenbank-Eigenschaften in SSMS konsultieren. Abbildung 4 zeigt ein entsprechendes Beispiel.

Im nächsten Schritt fügen wir der Dateigruppe eine Datei hinzu. Dies erreichen Sie mit einem weiteren Statement ALTER DATABASE. Die nachfolgende Anweisung fügt der HekatonFG-Dateigruppe eine neue Datei hinzu.

ALTER DATABASE TestHekaton
ADD FILE
(
NAME = 'HekatonFile',
FILENAME =
                'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSRV2014CTP1\MSSQL\DATA\HekatonFile'
)
TO FILEGROUP [HekatonFG];
GO

Abbildung 5: Die Datenbank-Eigenschaften geben Aufschluss über die der Dateigruppe angefügten Datei.

Sehen Sie sich die Klausel ADD FILE an. Wir haben hier der Übersichtlichkeit halber einen einfachen Namen plus Pfad und Datei-Namen angegeben. In der Sektion TO FILEGROUP spezifizieren wir den Namen unserer neuen Filegroup. Im Anschluss können wir die Files-Seite der Datenbank-Eigenschaften konsultieren, um uns das Ergebnis des getätigten Befehls anzusehen. Abbildung 5 visualisiert das.

Beachten Sie auch, dass wir auch die Datenbank-Eigenschaften für das Hinzufügen der Dateigruppe und Datei hätten verwenden können. Welche Methode Sie wählen ist Geschmackssache und bleibt Ihnen überlassen. Der Vorteil eines Scripts ist aber, dass Sie dieses speichern, modifizieren und merhmals ausführen können.

Nachdem wir nun der Datenbank die notwendigen Dateigruppen und Dateien hinzugefügt haben, geht es an die Erstellung speicheroptimierter Tabellen. Bei der Definition der Tabelle spezifizieren wir ihre „Durability“. Eine speicheroptimierte Tabelle kann entweder „durable“ (haltbar) oder „nondurable“ (nicht haltbar) sein. „Durable“ bedeutet, dass die Tabelle mit Daten im Arbeitsspeicher auch in die speicheroptimierte Dateigruppe gespeichert wird. Bei einer „nondurable“ Tabelle werden die Daten nur im Arbeitsspeicher vorgehalten. Sollte das System abstürzen oder ein Neustart fällig sein, sind die Daten verloren. „Durable“ Tabellen sind der Standard in SQL Server 2014. Das ist auch die Art von Tabellen, mit der Sie wahrscheinlicher arbeiten. Sehen wir uns an, wie wir eine erstellen können.

Bei der Definition einer „durable“, speicheroptimierten Tabelle müssen Sie auch einen Primärschlüssel definieren, der auf einem ungeclusterten Hash-Index basiert. Bei einem Hash-Index wird auf die Daten durch eine In-Memory-Hash-Tabelle zugegriffen und nicht durch Pages mit fester Größe. Hash-Indizes sind die einzige Art von Indizes, die in einer speicheroptimierten Tabelle unterstützt werden.

Bei der Definition der Tabelle müssen Sie nicht nur einen begleitenden Primärschlüssel definieren, sondern die Tabelle auch als speicheroptimiert deklarieren. Das nachfolgende Statement CREATE TABLE erläutert den Vorgang.

USE TestHekaton;
GO

CREATE TABLE Reseller
(
ResellerID INT NOT NULL
PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024),
ResellerName NVARCHAR(50) NOT NULL,
ResellerType NVARCHAR(20) NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

INSERT INTO Reseller
VALUES (1, 'A Bike Store', 'Value Added Reseller');

Wie Sie sehen, enthält die Spalte ResellerID den Primärschlüssel, der als ungeclusterter Hash-Wert definiert ist. Beachten Sie, dass Sie eine WITH-Klausel mit der Spezifikation der BUCKET_COUNT-Einstellung einfügen müssen. Das teilt dem SQL Server die Anzahl der Buckets mit, die im Hash-Index erstellt werden sollen. Ein Bucket ist ein Slot, der einen Satz an Schlüssel-Werte-Paaren vorhält. Microsoft empfiehlt einen Bucket Count, der ein bis zwei Mal so hoch wie die zu erwartende Anzahl der Unique Index Keys in dieser Tabelle ist.

Die Definition der Tabelle schließt mit einer zweiten WITH-Klausel ab. Hier setzen Sie die Option MEMORY_OPTIMIZED auf ON und die Option DURABILITY auf SCHEMA_AND_DATA. Letzteres definiert eine „durable“ Tabelle. Im Anschluss fügen wir eine Reihe in die Tabelle ein, um die Geschichte ausprobieren zu können.

Mehr müssen Sie im Grunde nicht tun, um eine speicheroptimierte Tabelle zu erstellen. Alles Weitere geschieht hinter den Kulissen. Behalten Sie aber im Hinterkopf, dass der SQL Server 2014 diese Tabelle mit einigen Limitierungen versieht. Zum Beispiel unterstützen sie keine Fremdschlüssel (Foreign Key) oder Prüf-Bedingungen (Check Constraints). Das gilt auch für IDENTITY-Spalten oder DML-Trigger (Data Manipulation Language). Besonders zu beachten ist auch, dass sämtliche Schreib-Aktivitäten gestoppt werden, sobald der zur Verfügung stehende Speicher nicht mehr ausreicht.

Auf der anderen Seite unterstützen speicheroptimierte Tabellen nativ kompilierte Stored Procedures, so lange diese ausschließlich auf speicheroptimierten Tabellen referenzieren. In solchen Fällen lassen sich Stored Procedures in nativen Code umwandeln, der in der Regel schneller ist und weniger Arbeitsspeicher als eine herkömmliche Stored Procedure braucht.

Zusätzlich müssen nativ kompilierte Stored Procedures Schema-gebunden sein und in einem spezifizierten Ausführungs-Kontext laufen. Weiterhin ist für nativ kompilierte Stored Procedures genau ein Atomic-Block zwingend notwendig.

Das folgende Statement CREATE PROCEDURE definiert eine nativ kompilierte Stored Procedure, die Daten aus der zuvor erstellten Reseller-Tabelle erhält.

CREATE PROCEDURE GetResellerType
(
@id INT
)
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN
ATOMIC WITH
(TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english')
SELECT ResellerName, ResellerType
FROM dbo.Reseller
WHERE ResellerID = @id
END;
GO

Nachdem wir unseren Parameter definiert haben, fügen wir eine WITH-Klausel ein, die die Option NATIVE_COMPILATION spezifiziert. Beachten Sie dabei, dass die Klausel ebenfalls die Optionen SCHEMABINDING und EXECUTE AS enthält, während OWNER den Ausführungs-Kontext definiert. Diese WITH-Klausel kümmert sich um drei der Anforderungen, die wir für die Implementierung einer nativ kompilierten Stored Procedure benötigen.

Um die Anforderungen des atomaren Blocks zu adressieren, spezifizieren wir ATOMIC nach dem Schlüsselwort BEGIN. Im Anschluss folgt eine weitere WITH-Klausel, die das Isolations-Niveau der Transaktion und die Sprache beinhaltet. Für Transaktionen in Bezug auf speicheroptimierte Tabellen können Sie SNAPSHOT, REPEATABLE READ und SERIALIZABLE als Isolations-Niveau verwenden. Als Sprache hinterlegen Sie einen der verfügbaren Aliasnamen der möglichen Sprache oder Sprachen.

Mehr brauchen Sie für die Definition einer Stored Procedure nicht tun. Sobald diese kreiert ist, können Sie sie mithilfe eines EXECUTE-Statements testen. Folgendes Beispiel erläutert das:

EXEC GetResellerType 1;

Wie erwartet liefert das Statement als Ergebnis Art und Name des Resellers. In unserem Fall ist das A Bike Store und Value Added Reseller.

Wie dieser und der erste Artikel gezeigt haben, ist das Arbeiten mit dem erweiterten Buffer Pool, geclusterten Columnstore-Indizes, partitionierten Indizes und speicheroptimierten Tabellen in SQL Server 2014 recht übersichtlich und angenehm. Allerdings haben wir diese Themen nur an der Oberfläche angekratzt. Bevor Sie diese Möglichkeiten einsetzen, empfehlen wir Ihnen einen genauen Blick in die Dokumentation von SQL Server 2014. Wie bei jeder neuen Version von SQL Server gibt es viele Änderungen, von denen einige auf der ersten Blick nicht sichtbar sind. Dazu gehören zum Beispiel die Verbesserungen in der Query Engine und der neue Ressourcen-Regulator für I/O. Mit unseren Ausführungen hier können Sie aber auf jeden Fall mit eigenen und ausführlichen Tests der neuen Funktionen beginnen. Eine kostenlose Test-Version ist über Microsoft TechNet erhältlich. Im Anschluss können Sie selbst entscheiden, ob SQL Server 2014 die richtige Software für Sie ist.

Über den Autor: Robert Sheldon ist technischer Berater und Autor mehrerer Bücher, Artikel und Schulungsmaterialien über Microsoft Windows, relationale Datenbank-Management- Systeme (DBMS) sowie Business-Intelligence- (BI-) Design und -Implementierung.

Folgen Sie SearchEnterpriseSoftware.de auch auf Facebook, Twitter und Google+!

Erfahren Sie mehr über Datenbanken

- GOOGLE-ANZEIGEN

ComputerWeekly.de

Close