05.12.2013

SQL Server 2014 inMemory Tabellen

Technical Value

Der Sql Server 2014 bietet im Rahmen seiner neuen und sehr stark verbesserten Datenbank Engine XTP: „Extreme Transactional Processing“ (Hekaton) nun erstmalig die Möglichkeit Tabellen teilweise oder sogar komplett im Arbeitsspeicher („inMemory“) abzubilden.

Was bedeutet dies im Detail?

-Reduzierung IO Last der Datenlaufwerke

-Geringfügig geänderte Syntax bei der Erstellung des neuen Tabellentyps

-Weiterhin uneingeschränkte Datensicherheit

Reduzierung der IO Last

Aufgrund des neuen „inMemory“ Ansatzes werden die Daten im Optimalfall komplett im Arbeitsspeicher gehalten und nur dort geändert.

Um die Datensicherheit nach einen Ausfall, oder Neustart des Datenbankservers zu gewährleisten werden die Datenbankinhalte, und Transaktionsinformationen nach der erfolgreichen Ausführung einer Transaktion in stark optimierter Form in ein Transaction log geschrieben. Diese Schreibzugriffe erfolgen nun im Gegensatz zur alten Vorgehensweise sehr kompakt, zum Teil als Filesstream und komplett sequenziell.

Durch das sequenzielle Schreiben der Daten werden auch klassische Festplatten basierte Raidsysteme besser ausgelastet. Generell gilt aber nach wie vor die Empfehlung die Transactionlogs auf den schnellstmöglichen Datenspeicher zu speichern (ideal SSD / FusionIO etc.).

Insgesamt wurde die „RandomIO“ drastisch reduziert, aber beim Transaktionslog befindet sich, auch nach allen deutlich spürbaren Optimierungen, daß Performance Nadelöhr (siehe weiter unten folgende Tests).

Geänderte Syntax

Nachfolgend ein Minimalbeispiel für die Erstellung einer „inMemory“ Tabelle: 1

  1. CREATE TABLE [Test] ( [PK] [int] PRIMARY KEY NONCLUSTERED NOT NULL, [Test] [char](50) )WITH ( MEMORY_OPTIMIZED = ON ) GO

Bis auf den Zusatz „WITH ( MEMORY_OPTIMIZED = ON )“ gibt es hier eigentlich nichts Neues. Wenn man allerdings dieses Statement gegen eine normale Datenbank ausführt erhält man folgende Quittung: „The MEMORY_OPTIMIZED_DATA filegroup does not exist or is empty. Memory optimized tables cannot be created for a database until it has one MEMORY_OPTIMIZED_DATA filegroup that is not empty.“

Was bedeutet, das man zuerst die Datenbank um eine „MEMORY_OPTIMIZED_DATA“ Dateigruppe erweitern muß um die „inMemory“ Features nutzen zu können. Dies erreicht man durch die folgenden Befehle: 2

  1. USE [master] GO ALTER DATABASE [test] ADD FILEGROUP [imfg] CONTAINS MEMORY_OPTIMIZED_DATA GO ALTER DATABASE [test] ADD FILE ( NAME = N'IM.mdf', FILENAME = N'C:\DATA\IM' ) TO FILEGROUP [imfg] GO

Uneingeschränkte Datensicherheit

Wie werden die Daten persistiert, wenn sie im nur „inMemory“ im Arbeitsspeicher vorgehalten und verarbeitet werden? Und was passiert nach einem Neustart des Servers?

Die Daten bleiben standardmäßig nach einem Neustart des Datenbankservers erhalten, da permanent nach jeder Transaktion das Transaktionslog fortgeschrieben wird. Zusätzlich wird zyklisch der Datenbankinhalt in die „MEMORY_OPTIMIZED_DATA“ Dateigruppe geschrieben. Falls zwischen diesen Schreibvorgängen ein Neustart durchgeführt wird stellt der SQL Serverdienst das entsprechende Delta aus dem Transaktionslog wieder her und lädt die Datenbank in den Arbeitsspeicher.

Nach einem unvorhergesehenen Neustart kann es einige Zeit dauern bis die Datenbank wieder zur Verfügung steht, da diese zuerst wieder vollständig in den Arbeitsspeicher geladen werden muß. Diese Sicherungsvorkehrungen sind für Temporäre Tabellen, die ausschließlich Laufzeitdaten erhalten manchmal überflüssig und erzeugen damit unerwünschten IO Overhead. Aus Performanzgründen kann deshalb bei Tabellen mit wiederherstellbarem Inhalten der Parameter „DURABILITY = SCHEMA_ONLY“ bei der Tabellenerstellung verwendet werden. Durch die Verwendung dieses Parameters werden die Transaktionsprotokollierung und die zyklische Speicherung der Tabelleninhalte gestoppt. Nach einem Neustart sind alle Inhalte dieser Tabelle gelöscht.

Stark erhöhte Performanz und reduzierte IO Belastung

Durch die Verwendung der neuen XTP Engine in Verbindung mit „inMemory“ Tabellen lassen sich enorme Geschwindigkeitssteigerungen realisieren.

Folgend eine Übersicht der verschiedenen Tabellen Optionen auf identischer Hardware: 3

  1. DECLARE @d date = getdate() DECLARE @i int = 0 WHILE @i < 1000000 BEGIN SET @i = @i + 1 insert into [imoltp]..[Test] values (@i, 'Testvorname', 'Testnachname', 'Teststrasse 1', 'Duesseldorf', 'Deutschland', @d) END DECLARE @d2 date = getdate() DECLARE @i2 int = 0 WHILE @i2 < 1000000 BEGIN SET @i2 = @i2 + 12 UPDATE [imoltp]..[Test] SET [City] = 'Düsseldorf', [Birth_Date] = @d2 WHERE [PK] = @i2 END

Normale Tabelle ohne „inMemory“ Optimierung:

Ausführungszeit: 16:10 Minuten.

Tabelle mit „inMemory“ Optimierung: Ausführungszeit: 2:37 Minuten

Tabelle mit „inMemory“ Optimierung / Transaktionslog -> Ramdisk: Ausführungszeit: 1:30 Minuten

Tabelle mit „inMemory“ Optimierung /„DURABILITY = SCHEMA_ONLY“: Ausführungszeit: 0:21 Minuten

4

Abschließend können wir beobachten, das der SQL Server 2014 enorm von der XTP Engine und „inMemory“ Technik profitiert, aber nach wie vor einen hohen IOPS Durchsatz für das Transaction Log erfordert. Bei temporären Tabellen ohne Transaction Log Funktion und Datenpersistierung ist der Durchsatz verglichen zu einer klassischen Tabelle ohne „inMemory“ Technik überragend hoch.

Teilen auf

Newsletter Anmeldung

Abonnieren Sie unseren Newsletter!
Lassen Sie sich regelmäßig über alle Neuigkeiten rundum ORAYLIS und die BI- & Big-Data-Branche informieren.

Jetzt anmelden