SQL Server In-Memory OLTP - max() vs. ident_current()
Während eines Performance Driven Development Advanced Trainings kam eine interessante Fragestellung auf.
Die Aufgabenstellung war einen einfachen lesenden Workload zu erzeugen, um die Geschwindigkeit von In-Memory OLTP zu testen. Folgender Code wurde vorgeschlagen:
declare @max int
select @max = max(id) from t1_old
declare @i int = rand()*@max
select * from t1_old
where id >= @i and id <= @i+rand()*250
Sehr unverdächtig!
Das offensichtliche Problem in der Where Clause lassen wir für den Moment mal unkommentiert. Das lösen wir weiter unten noch auf. Ich fand den Vorschlag ganz okay, da er echte Probleme der Qualität von T-SQL Code aufzeigte.
Die physikalische Struktur hinter t1_old ist ein Clustered Index mit der ID als Clusterkey.
Ein Test mit einer interaktiven Variante unseres Werkzeuges SQLPressure ergab, bei einer Steigerung von 20 Threads alle 5 Sekunden, dass sich der Workload bei 300 Threads mit einem Durchsatz von ungefähr 5000 einpendelt. Nicht schlecht für eine VM mit 4 virtuellen Kernen.
Dann kam der eigentliche spannende Test. Und zwar auf der In-Memory Struktur von t1. Es wurde natürlich mit einer deutlichen Steigerung des Durchsatzes gerechnet! by the way, auf id liegt der NONCLUSTERED index. Es gibt keinen HASH Index, da eine Bereichsabfrage geplant ist.
Das Ergebnis war eher enttäuschend. Was war passiert? Der Durchsatz lag sogar unter dem Ergebnis der diskbased Table t1_old. Die Tabellen wurden neu erstellt. Die Tests wiederholt. Ähnliche Ergebnisse. Der Glauben an In-Memory OLTP war zerstört.
Weitere Tests ergaben, dass gar nicht die eigentliche Query das Problem war, sondern die max(id) Abfrage. Dazu wurde die max Funktion durch eine statische Angabe ersetzt.
Als Nächstes wurde überlegt, wie man max() ersetzen könnte. Irgendwo gab es doch da Funktionen? Genau, es gibt u.a. ident_current(), um das letzte Autoincrement einer Tabelle abzufragen.
Im nächsten Test wurde entsprechend diese Funktion verwendet.
Und siehe da. Wir haben In-Memory OLTP da, wo wir unseren Workload vermuten würden. Zwar scheint auch der Einsatz von ident_current() nicht völlig transparent zu sein, dennoch scheint sie deutlich weniger auszubremsen als max().
Ein letzter Test sollte noch zeigen, ob ident_current() vielleicht auch beim disk-based Table noch etwas herausholen könnte.
Dem war aber nicht so! Hier macht die Verwendung von max() und ident_current() keinen wirklich messbaren Unterschied.
Eine kurze Betrachtung der Querypläne ergab, dass die Implementierung von max() deutlich abweicht.
Hier für die diskbased Variante:
Und hier für die In-Memory OLTP Variante:
Hier kann schon anhand der Unterstrukturkosten gesehen werden, dass die zweite Variante einfach ein wenig länger brauchen wird.
Dieses kleine Beispiel zeigt auf, dass eine einfache Portierung von Tabellen nach in-memory OLTP nicht immer sofort den gewünschten Geschwindigkeitsgewinn bringen muss.
So, kommen wir zum SARG Problem in der Where Clause.
where id >= @i and id <= @i+rand()*250
Da hat der Query Optimizer natürlich keine Chance mit dem zweiten Teil. Daher kam noch mein Vorschlag, dass wir den Teil aus der Where Clause entfernen und separat “ausrechnen”.
declare @max int
select @max = max(id) from t1_old
declare @i int = rand()*@max
declare @iend int = @i+rand()*250
select * from t1_old
where id >= @i and id <= @iend
Und dann kam der nächste Test. Erstmal mit der diskbased Engine.
Wow, da geht auf einmal ja richtig viel. Ja, die gute alte 8kb Seite hat es noch voll drauf. Zumindest, was das Lesen angeht. :)
Und dann nochmals mit der in-memory OLTP Engine. Das Resultat hat alle überrascht.
Es tat sich nicht wirklich viel. Was sagt uns das? Für den Moment, dass dieser rein lesende Workload in beiden Engines sich ähnlich verhält. Dem in-memory OLTP Ansatz mussten wir ebenso mit einer Code Änderung “über die Straße helfen” als auch dem diskbased T-SQL.
Nur was passiert, wenn wir anfangen Daten zu ändern? Das zeige ich demnächst mal. :)