SQL Server 2014 – In-Memory
Der neue SQL Server 2014 steht in den Startlöchern und wird am 1. April released. Mit ihm fallen uns viele neue Features anheim, wie z.B. In-Memory Tabellen.
Was ist In-Memory?
Mit In-Memory besteht die Möglichkeit „Memory Optimized Tables“ zu erstellen. Diese beschleunigen das Lesen und Schreiben der Tabelle, indem sie sich nach dem Start des SQL-Servers sofort im Arbeitsspeicher befinden und nicht erst von der Festplatte gelesen werden müssen. Das bringt schon einmal einen enormen Fortschritt bei der Optimierung der Datenbank: umso weniger Lese- und Schreibvorgänge von der Festplatte, umso besser. Nun stellt sich die Frage: Ist es denn so tragisch, wenn man einen Lesevorgang mehr hat?
Naja das kommt nun auf die Lebenszeit der Seiten im Arbeitsspeicher an.
Wenn nun eine Tabelle mit einem Select-Statement aufgerufen wird, werden alle Seiten der Tabelle von der Festplatte in den Arbeitsspeicher geladen. Nun fallen aber die Seiten der Abfrage nicht sofort wieder aus dem Arbeitsspeicher, es sei denn der Arbeitsspeicher ist dementsprechend voll und eine andere Abfrage braucht die Speicher.
Wenn nun die In-Memory Variante benutzt wird, fallen die „Memory-Optimized Tables“-Seiten nie aus dem Speicher, da sie sich dauerhaft im Speicher halten. Letztendlich bedeutet das, dass die I\O bei In-Memory gegen Null geht.
Dieses Feature erinnert stark an die frühere Variante Tabellen im Speicher zu halten. Die Rede ist von „DBCC PINTABLE“. Mit diesem Statement wurden Tabellen als fixiert markiert und somit waren sie dauerhaft im Arbeitsspeicher. Allerdings wurde diese Funktionalität sehr früh wieder entfernt, da unerwünschte Nebeneffekte auftraten. Dazu zählte die Beschädigung des Pufferpools. In-Memory soll komplett neu entworfen worden sein und keine negativen Gemeinsamkeiten mit DBCC PINTABLE aufweisen.
Datenbank vorbereiten
Bevor man In-Memory Tabellen nutzen will, muss man seine Datenbank dementsprechend erstellen oder anpassen.
create database HKTest
on
primary (Name = [HKTest data],
filename = 'C:\data\HKTest.mdf'),
filegroup [IM_fg] contains memory_optimized_data
(name = [HKTest_mod_dir],
filename = 'C:\data\HKTest_fg')
log on (name = [HKTest_log], filename = 'C:\data\HKTest_log.ldf')
collate Latin1_general_100_bin2
Eine Filestream-Dateigruppe muss erstellt werden und die Collation auf Latin1_General_100_bin2 gesetzt werden.
Bestehende Datenbank anpassen:
alter database HKTest
add filegroup IM_fg contains memory_optimized_data;
go
alter database HKTest
add file (Name='hk_mod', filename= 'C:\data\HKTest_fg')
to filgroup hk_mod;
go
alter database [HKTest] collate Latin1_General_100_BIN2
go
SQL Server Neustart, wo sind meine Daten?
Gehen wir davon aus, man benutzt In-Memory Tabellen und der SQL Server entscheidet sich aus irgendwelchen Gründen den Dienst zu quittieren. Was passiert mit den Daten in den Memory-Optimized Tabellen? Schließlich liegen diese vollständig im Arbeitsspeicher und RAM ist flüchtig.
Glücklicherweise werden die Daten einer Memory-Optimized Tabelle in einer extra definierten Filestream-Dateigruppe gespeichert, sodass nach dem SQL Server Neustart die Daten aus der Dateigruppe ohne unser Zutun wiederhergestellt werden. Außerdem werden Operationen mit „Memory-optimized Tables“ ins gleiche Transaktionslog geschrieben wie „disk-based“ Tabellen.
Die Protokollierung der Memory-Optimized fällt äußerst gering aus, im Gegensatz zu disk-based Tabellen. Bei einer Ermittlung der Größe des Transaktionsprotokolls kam bei Memory-Optimized-Tabellen eine Größe von 42MB zustande, bei einem Einfügevorgang von einer Million Zeilen. Die nicht speicheroptimierte Tabelle schnitt nicht so gut ab. Bei gleicher Tabellenstruktur und derselben Anzahl der Datensätze kam die Protokolldatei auf 4,1GB. Genauso sieht es mit der Ausführungszeit des Insert-Statements aus. Hier kann ebenfalls wieder die speicheroptimierte Tabelle punkten, mit einer Ausführungszeit von ca. zwei Sekunden sticht sie die disk-based Tabelle, die mit ca. vier Minuten unterwegs ist, aus. Der Grund des enormen Unterschieds besteht darin, dass die disk-based Tabelle die neuen Datensätze in die jeweilige Datendatei schreiben muss, was natürlich IO verursacht. Da die speicheroptimierte Tabelle vollständig im Speicher liegt, entsteht somit keine IO.
Wenn eine Tabelle mit der Option „Schema_Only“ erstellt wird, ist sie nicht dauerhaft und wird auch nicht mitgeloggt. Das bedeutet diese Art der Tabelle befindet sich im Speicher und benötigt somit keine IO. Die Schema_Only-Variante setzt beim Punkt Ausführungszeit noch einen drauf und ist noch schneller als die „normale“ memory-optimized Tabelle. Nachteil: nach einem Serverneustart oder einem Failover sind die Daten verloren, doch die Tabellen werden neu erstellt. Diese Art von Tabellen eignen sich ideal um eventuell Abfrageergebnisse zwischenzuspeichern. Diese Neuerung bietet eine gute Alternative für Temporäre Tabellen und Tabellenvariablen.
Vorteile gegenüber der Temporären Tabelle/Tabellenvariable:
Bei einer Temporären Tabelle entsteht IO sobald Daten in die Tabelle geschrieben werden.
Eine Tabellenvariable hat ebenfalls ein Problem, und zwar können die Zeilen in der Variable nicht geschätzt werden. Beides trifft auf eine Memory-Optimized Table nicht zu.
CREATE TABLE dbo.Ord
(OrdNo INT not null primary key nonclustered hash with (bucket_count=1000000),
OrdDate datetime not null,
CustCode nvarchar(5) not null)
with (memory_optimized=on, Durability = Schema_and_Data)
go
Mit dieser Syntax wird eine Memory-Optimized Tabelle erstellt.
CREATE TABLE dbo.Ord
(OrdNo INT not null primary key nonclustered hash with (bucket_count=1000000),
OrdDate datetime not null,
CustCode nvarchar(5) not null)
with (memory_optimized=on, Durability = Schema_Only)
go
Gleiche Tabelle mit Schema_Only gekennzeichnet.
Wichtig zu wissen ist, dass mindestens ein Index auf der Tabelle vorhanden sein muss, und zwar ein Hash-Index wie im oberen Beispiel oder ein Range Index. Zu beachten ist lediglich die Größe des bucket_counts, man sollte nämlich ca. das 2-fache des eindeutigen Werts nehmen. Was ist der bucket_count? Der Hash-Index besteht aus einem Array von Zeigern.
Jedes Element des Arrays ist ein sogenannter bucket_count. Wie lässt sich nun die richtige Größe des bucket_counts bestimmen? Idealerweise sollte man sich hier an der Anzahl der eindeutigen Indexzeile orientieren.
create procedure dbo.OrderInsert
with native_compilation, schemabinding, execute as owner
as
begin atomic with
(transaction isolation level = snapshot,
language = N'English')
declare @i int = 0
while @i <= 1000000
begin
declare @OrdDate datetime = getdate();
insert into dbo.Ord (OrdNo, CustCode, OrdDate) values (@i, 'ALFKI', getdate());
set @i += 1
end
end
Hier wird eine natively compiled procedure erstellt, in der eine Million Datensätze in die Memory-optimized Tabelle eingefügt werden. Bei einer Million Einträgen habe ich deshalb den bucket_count auf 1000000 gesetzt.
Allerdings wird der angegebene bucket_count aufgerundet und zwar Bit-weise.
Bucket_count von 10000 ist nicht gleich 10000
Bucket_count von 10000 ist gleich 16384 ergibt 2^14
Problem bei zu viel Bucket_count: Umso höher der bucket_count, umso mehr Speicher verbraucht die speicheroptimierte Tabelle.
Beispiel in den Test:
Bei meinen Tests ist mir einmal der Fehler unterlaufen, dass ich mich beim bucket_count um eine 0 vertan habe, daraus resultierte, dass mein SQL Server-Dienst den gesamten Speicher der Virtuellen Maschine vertilgte.
Problem bei zu wenig Bucket_count: Bei zu geringer Größe des bucket_counts ist die Leistungssteigerung dahin.
Beispiel bei zu wenig Bucket_count:
Der Einfüge-Vorgang dauerte bei einem Bucket_count von 1024 und einer Million Datensätze achtmal solange.
Wenn man den Bucket_count nicht bestimmen kann, sollte man den Range Index benutzen, doch dieser wird erst in der CTP2 des SQL Server 2014 vorgestellt. Mal sehen, was dieser mit sich bringt.
Natively Compiled Procedures
Diese neuartige Stored Procedure funktioniert nur in Verbindung mit einer Memory-optimized Tabelle. Sie hat einen entscheidenden Vorteil, sie wird kompiliert und nicht interpretiert. In-Memory Tabellen sind schnell, wer aber noch schneller unterwegs sein will, benutzt sie in Verbindung mit natively Compiled Procedures. Allerdings gibt es eine sehr lange Liste zu Statements, die nicht in einer natively Compiled Procedure benutzt werden dürfen. Hier der Link: http://technet.microsoft.com/en-us/library/dn246937%28v=sql.120%29.aspx
Mit einer natively Compiled Procedure lässt sich zusätzlich das Lesen beschleunigen. Bei einem Lesevorgang von 10.000.000.000 Datensätzen legt die In-memory Tabelle eine Zeit von 28 Sekunden vor. Die Schema_Only Tabelle braucht dieselbe Zeit wie die Schema_and_data, da sie sich beide im Speicher befinden. Die gleiche Anzahl der Einträge mit einer disk-based Tabelle abzufragen wäre Irrsinn. Deshalb habe ich die Anzahl der abgefragten Datensätze ein bisschen nach unten geschraubt und zwar auf 1 Million. Bereits hier dauert die Abfrage 58 Sekunden.
Fazit
Mit In-Memory hat Microsoft wieder einmal einen Fortschritt in Richtung Geschwindigkeit gemacht: bei Einfügevorgängen eine Leistungssteigerung um den Faktor 30 und bei den Schema_Only Tabellen sogar um den Faktor 60. Allerdings ist Vorsicht geraten bei In-Memory-Tabellen, bei meinen Tests musste ich ca. zehnmal meinen SQL Server-Dienst beenden, da durch den Speicherverbrauch der SQL Server kein RAM mehr zur Verfügung hatte. Früher war Arbeitsspeicher noch teuer, heute nicht mehr, der Preis des Arbeitsspeichers ist rapide gesunken. Beim heutigen Stand kann man einen Server mit 32 Kernen und 1 TB RAM schon für unter 50000€ erstehen. Allerdings sollte nicht der Irrglaube entstehen, dass mehr Arbeitsspeicher alle Probleme löst. Mehr Arbeitsspeicher reduziert zwar die Wartezeit für Lesezugriffe, wirkt sich aber nicht auf die Wartezeit aus, die benötigt wird um Schreibvorgänge auf die Festplatte zu tätigen. Außerdem muss sich die Datenbank nach dem Neustart eine Weile wiederherstellen, um die Daten aus der Filestream-Dateigruppe wieder in den Arbeitsspeicher zu laden.
Möchtest du mehr zu SQL wissen, dann schau mal hier: http://www.ppedv.de/SQL