Wir haben als große Neuerung des SQL Server 2014 bereits die InMemory-Fähigkeiten kurz beleuchtet. Nun wollen wir einen Schritt weiter gehen und die neue Technologie mit einem alten Thema „verheiraten“.

Beim Cube-Writeback werden üblicherweise große Datenmengen in die Datenbank geschrieben. Im Mehrbenutzerfall kann es hierbei zu spürbaren Wartezeiten durch Sperren von Datenbankobjekten kommen. Die neue InMemory-Technologie des SQL Server 2014 scheint wie geschaffen, um dieses Problem zu verringern. Das Ziel dieses Beitrags soll deshalb sein, den Writeback in eine InMemory-Tabelle durchzuführen.

Schritt 1: Ein Cube muss her

Mein Kollege Sébastien Eparvier hat kürzlich detailliert dargestellt, wie man vorgeht, um Writeback in einen OLAP Cube zu ermöglichen. Diese Schritte wollen wir deshalb hier als gegeben voraussetzen. Beginnen wir also mit einem einfachen Cube auf der AdventureWorks2012DW-Datenbank. Die Struktur des Cubes ist in Abbildung 1 dargestellt.

 

Abbildung 1: Struktur unseres Beispiels-Cubes.

 

Diesem Cube fügen wir nun eine Writeback-Partition hinzu, die in die Tabelle WriteTable_Fact_Sales_Quota schreibt:

 

Abbildung 2: Hinzufügen der Writeback-Partition.

 

Wenn wir den Cube verarbeiten und deployen, befindet sich in unserer Datenbank eine Tabelle mit der folgenden Definition:

CREATE TABLE [dbo].[WriteTable_Fact_Sales_Quota](

    [SalesAmountQuota_0] [float] NULL,

    [DateKey_1] [int] NULL,

    [EmployeeKey_2] [int] NULL,

    [MS_AUDIT_TIME_3] [datetime] NULL,

    [MS_AUDIT_USER_4] [nvarchar](255) NULL

) ON [PRIMARY]

 

Diese Tabelle können wir wie gehabt aus Excel mittels Cube Writeback („Was wäre wenn“-Analyse) befüllen:

 

Abbildung 3: Cube Writeback aus Excel.

 

Schritt 2: Feststellen des Ausgangszustands

Nun möchten wir beobachten, was datenbankseitig passiert, wenn wir in diese Tabelle schreiben. Dafür melden wir uns auf dem Server an, der die Datenbank hostet (in meinem Fall eine lokale VM) und starten den Performance Monitor:

 

Abbildung 4: Erstellen eines Variablen-Satzes für die Performance-Messung.

 

Dem eben erstellten Collector-Set fügen wir nun folgende Kennzahlen hinzu:

• Bulk Copy Throughput/sec (für die Adventureworks2012-Datenbank),
• Latch Waits/sec,
• Lock Requests/sec (total) und
• Transactions/sec (für die Adventureworks2012-Datenbank).

Wenn wir nun einen Wert auf oberster Ebene unseres Cubes manipulieren (Beispielsweise Zelle B5 in Abbildung 3) und die Änderungen persistieren, dann können wir im Performance Monitor die entsprechenden Kennzahlen beobachten. Das Resultat für die Durchführung ist in Abbildung 5 zu sehen.

 

Abbildung 5: Performance-Monitor für Einfügevorgang ohne Manipulationen.

 

Wie wir sehen, kommt es schon bei einem nicht konkurrierenden Writeback-Vorgang zu Latch-Waits. Da das XTP-Design frei von Latches ist, könnte eine Migration der Writeback-Tabelle in die Memory-Optimized Filegroup hier einen Performance-Gewinn bringen. Richten wir uns nun eine Profiler-Session ein, um den Einfügevorgang zu beobachten, so stoßen wir auf die in Abbildung 6 gezeigte Zeile:

 

 

Abbildung 6: Der Batch-Insert-Vorgang

 

Hier erkennen wir nicht nur eine vierstellige Anzahl von Reads und Writes sondern auch eine Gesamtlaufzeit von 1861 ms.

Schritt 3: In den Arbeitsspeicher

Nun möchten wir versuchen, die Zieltabelle WriteTable_Fact_Sales_Quota in die Memory-Optimized Filegroup zu transferieren. Wir können dafür versuchen, den Memory Optimization Advisor zu verwenden.

 

Abbildung 7: Starten des Memory Optimization Advisors.

 

Mit der ursprünglichen Tabellendefinition schlägt der Transfer der Tabelle allerdings fehl, da in der Tabelle alle Spalten Null-Werte zulassen, was bei InMemory-Tabellen nicht erlaubt ist:

 

Abbildung 8: Der automatische Transfer mit dem Memory Optimization Advisor schlägt fehl.

 

Deshalb erstellen wir die Tabelle neu und verwenden dabei folgende Definition:

CREATE TABLE [dbo].[WriteTable_Fact_Sales_Quota](

    [SalesAmountQuota_0] [float] not NULL,

    [DateKey_1] [int] not NULL,

    [EmployeeKey_2] [int] not NULL,

    [MS_AUDIT_TIME_3] [datetime] not NULL,

    [MS_AUDIT_USER_4] [nvarchar](255) not NULL

) ON [PRIMARY]

 

Mit dieser angepassten Definition bestehen wir alle Punkte der Prüfung:

 

Abbildung 9: Mit angepasster Tabellendefinition ist der Check erfolgreich.

 

Da es für InMemory-Tabellen zwingend erforderlich ist, einen Index anzulegen, ist es im Migrationsprozess nun noch erforderlich, diesen Index zu definieren:

Abbildung 10: Definition eines Index auf der neuen InMemory-Tabelle.

 

Wenn wir nun versuchen, aus Excel ein Writeback durchzuführen, schlägt dieser Vorgang fehl. Der Grund dafür ist, dass die SQL Server Analysis Services beim Cube Writeback aus Sicherheitsgründen automatisch versuchen über den Table Hint „with (tablock)“ die Tabelle zu sperren. Dieser Tabellenhinweis ist für Memory Optimized Tabellen allerdings nicht erlaubt. Abbildung 11 zeigt die Fehlermeldung, die Excel uns anzeigt.

 

Abbildung 11: Fehlermeldung beim Writeback in die Memory Optimized Tabelle.

 

Schritt 4: Den Tablock beim Cube-Writeback entfernen

Wir müssen also den Tabellenhinweis „with (tablock)“ für den Writeback entfernen. Das ist nicht ganz einfach, da wir keinen direkten Zugriff auf die SQL Statements haben, die Excel an den SQL Server sendet. Wir können allerdings die Parameter für das SQL beeinflussen. Diese werden in den sogenannten „Cartridges“ gesetzt. Diese befinden sich im Ordner OLAP\bin\Cartridges des Installationsverzeichnisses unseres Servers, wie in Abbildung 12 gezeigt.

 

Abbildung 12: Das Cartridge-Verzeichnis und die Cartridges.

 

Wir müssen nun die Cartridge „sql90.xslt“ bearbeiten. In der Datei finden wir einen Tag, der den Tablock-Tabellenhinweis setzt:

 

Abbildung 13: Der Tablock-Tabellenhinweis im Cartridge.

 

Wenn wir diesen Hinweis entfernen, müssen wir noch die Analysis Services neu starten, um die Änderung zu aktivieren.

 

Schritt 5: Writeback in Memory Optimized Tabelle

Nun sind wir endlich in der Lage, aus Excel ein Writeback in die Tabelle im Arbeitsspeicher durch zu führen. Wir führen wieder ein Writeback auf oberster Ebene unseres Cubes durch und lassen dabei den Performance Monitor und den Profiler mitlaufen.

Im Profiler sehen wir nun, dass das Bulk Insert-Statement, das beim Writeback abgesetzt wird, nun bei verwenden der Option „durability schema only“ der InMemory-Tabelle keine Reads und keine Writes mehr hat. Das ist das erwartete und erwünschte Verhalten, denn da alle Daten im Arbeitsspeicher liegen, ist kein Lesen und kein Schreiben auf der Festplatte mehr nötig. Außerdem ist der Schreibvorgang in die InMemory-Tabelle um etwa 40% schneller als wenn die Tabelle auf der Festplatte liegt. Dieses Verhältnis dürfte bei größeren produktiven Cubes und mehreren schreibenden Benutzern sogar noch deutlicher zugunsten der InMemory-Lösung ausfallen, als bei unserem kleinen AdventureWorks-Beispielcube.

Abbildung 14 zeigt die Aktivität, die der Performance Monitor beim Schreiben in die InMemory-Tabelle registriert. Wie erwartet steigt dabei der XTP-Speicherbedarf, da ja neue Daten im Arbeitsspeicher abgelegt werden, die Latch-Waits sind ebenfalls wie erwartet nicht mehr sichtbar. Das erklärt den beobachteten Performance-Gewinn beim Writeback-Vorgang.

 

Abbildung 14: Performance Monitor beim Writeback in InMemory-Tabelle

 

Abbildung 15 zeigt einen Ausschnitt aus der Profiler-Session, an der wir erkennen, dass tatsächlich keine Reads und keine Writes mehr stattfinden und der Flaschenhals „Festplattenzugriff“ beim Writeback in InMemory-Tabellen somit erfolgreich eliminiert wurde.

 

 

Abbildung 15: Beim Writeback in InMemory-Tabellen erfolgen keine Reads und Writes auf der Festplatte

 

Fazit

Wir haben gezeigt, wie InMemory-Tabellen für Cube-Writeback genutzt werden können und dass das Nutzen von InMemory-Tabellen einen Performance-Gewinn erwarten lässt. Bereits für kleine Cubes wie den AdventureWorks-Cube, den wir als Beispiel konstruiert hatten lässt sich eine Beschleunigung beim Schreibvorgang feststellen.