© best_vector/Shutterstock.com
Adaptive Abfragenausführung mit dem SQL Server 2017

Das Performance-Perpetuum-Mobile


SQL Server 2017 brachte zahlreiche Neuerungen wie beispielsweise die Unterstützung verschiedener Linux-Derivate und Docker oder Verfügbarkeitsgruppen ohne Cluster mit. Neben diesen vielbeachteten Neuerungen hat sich auch einiges im Bereich der Performanceoptimierung getan: Im Vergleich zu den älteren Versionen gibt es nun einige Mechanismen, die eine automatische Optimierung der Abfragepläne versprechen.

Video: SQL Server 2017 (vNext)

In diesem Artikel soll sowohl auf die adaptive Abfrageausführung (Adaptive Query Processing) als auch auf das automatische Tuning im SQL Server 2017 eingegangen als auch die Funktionsweise dieser Optimierungsverfahren beschrieben werden [1]. Vorteilhaft für den SQL-Server-Entwickler bzw. -Administrator: Die vorgestellten Optimierungsverfahren laufen (sofern eingeschaltet, s. u.) transparent im Hintergrund ab, d. h. es ist keine weitere Konfiguration oder Ähnliches erforderlich.

Performanceoptimierung – die Sisyphosarbeit

Jeder, der sich schon einmal beruflich um einen SQL Server kümmern musste, kennt das Problem: Irgendwie laufen Abfragen langsam und die Umgebung erzielt nicht mehr die Performance, die sie ganz am Anfang gehabt hat. Das kann viele unterschiedliche Gründe haben. Neben der Tatsache, dass sich die Datenmengen im Vergleich zu den Anfangstagen der Applikation deutlich vergrößert haben – und das geht schnell, glauben Sie mir – greifen möglicherweise immer mehr Benutzer auf das System zu und verursachen so einen Einbruch der Systemleistung, der in bestimmten Fällen erheblich sein kann. Um die ursprüngliche Leistung des Systems wiederherzustellen, gibt es nun zwei Ansätze – einen einfachen und einen intelligenten. Der einfache Weg ist der, dass wir einfach Hardware auf das Problem schmeißen. Läuft der SQL Server nicht schnell genug, muss halt eine größere Kiste her: mehr Speicher, im Fall echter Hardware schnellere Platten, oder eine Umkonfiguration der VM. Dabei handelt es sich meiner Meinung nach um den „einfachen“ Weg, weil man nicht nachdenken muss, sondern einfach eine neue Maschine einkauft, den SQL Server umzieht und fertig ist. Allerdings führt er nicht immer zum Ziel, ist ggf. nur eine temporär erfolgreiche Lösung und mal ehrlich: Diese Art Lösung ist doch nicht der Grund dafür, dass wir uns alle mit IT beschäftigen.

Der intelligente Weg sieht so aus, dass wir uns mit dem eigentlichen Grund dafür beschäftigen, warum der SQL Server langsam ist, und sowohl Indizes als auch Abfragen optimieren. Zugegeben: die Optimierung von Abfragen ist nicht immer möglich. Besonders dann nicht, wenn es sich um eine Drittherstelleranwendung handelt, bei der die Abfragen nicht einfach umgeschrieben werden können. Aber auch hier kann man viel über das Anlegen zusätzlicher Indizes, die Aktualisierung der Statistiken oder die physikalische Neuaufteilung der Datenbankdateien erreichen.

Damit wir den intelligenten Weg beschreiten können, müssen wir uns zunächst einmal damit beschäftigen, wie Abfragen auf dem SQL Server ausgeführt werden (Abb. 1) und wie bei suboptimaler Abfrageausführung eine Performanceeinbuße auftreten kann.

geisler_performance_1.tif_fmt1.jpgAbb. 1: Verarbeitung von Abfragen im SQL Server

Hat man eine Abfrage eingegeben und lässt sie vom SQL Server ausführen, passiert Folgendes: Die Abfrage wird zunächst in die Task Queue eingereiht, in der alle Tasks warten, die noch nicht bearbeitet werden. Ein Worker-Prozess, der gerade nichts zu tun hat (idle), holt sich die Abfrage aus der Task Queue und führt sie aus. Hierbei wird die Abfrage zunächst geparst, dann kompiliert und anschließend optimiert. Hierbei entsteht ein Ausführungsplan, der im Cache für Ausführungspläne (Plan Cache) gespeichert wird. Wenn noch einmal dieselbe Abfrage geschickt wird, kann die Verarbeitung der Abfrage übersprungen und direkt der gecachte Plan verwendet werden. Das gilt übrigens insbesondere für Abfragen mit Parametern: Arbeitet man bei der Programmierung von SQL-Server-Anwendungen – so wie man sollte – mit Parametern, anstatt die Abfragen per String-Konkatenation zusammenzubauen, kann der SQL Server das erkennen und verwendet den schon erzeugten Abfrageplan einfach mit dem neuen Parameter.

Nach der Erzeugung des Abfrageplans wird die Abfrage ausgeführt und die zurückgelieferten Daten werden im Buffer Pool Cache zwischengespeichert. Die Ergebnisdatenmenge wird zurückgeliefert und der Worker Task kehrt wieder in den Idle-Modus zurück [2].

Den Hauptknackpunkt bei der Abfrageverarbeitung stellt die Erzeugung des Abfrageplans dar. Bei der Verarbeitung von Abfragen erzeugt der SQL Server unterschiedliche Abfragepläne, vergleicht sie miteinander und wählt den scheinbar optimalen Plan aus. Hierbei greift der Query Optimizer auch mal daneben und wählt einen Plan aus, der aufgrund der vorliegenden Situation nicht optimal ist und somit dazu führt, dass nicht die Performance bei der Ausführung der Abfrage erzielt werden kann, die theoretisch möglich wäre.

Um zu verstehen, wie die automatischen Optimierungen im SQL Server 2017 funktionieren, müssen wir zunächst verstehen, warum es dazu kommen kann, dass die Abfragepläne für Abfragen nicht optimal ausgewählt werden. Nachdem ein Abfrageplan erzeugt worden ist, führt der SQL Server für jeden Schritt, den es in diesem Plan gibt, eine Schätzung der Kardinalität durch [3]. Hierbei wird geschätzt, wie viele Datensätze bei jedem Schritt zurückgeliefert werden, und aufgrund dieser Schätzung werden bestimmte Operatoren ausgewählt, wird Speicher alloziert und abschließend auch der optimale Plan (auf Basis der geschätzten Kardinalität) für die Abfrage ausgewählt. Die Schätzung der Kardinalität beruht auf statistischen Verfahren und bestimmten Annahmen [4]. Sind die Daten, die zur Schätzung der Kardinalität verwendet werden, nicht gut genug, verschätzt sich der SQL Server und es wird nicht der optimale Plan zur Durchführung der Abfrage ausgewählt.

Für eine fehlerhafte Schätzung gibt es viele Gründe. Ein besonders häufig anzutreffender Grund ist das Fehlen von Statistiken oder die Verwendung veralteter Statistiken [5]. Der SQL Server führt zu jeder Tabelle Statistiken, die z. B. Informationen zur Anzahl der Datensätze innerhalb der Tabelle, zur Dichte der Daten (wie viele NULL-Werte sind enthalten) oder zur Verteilung der Daten enthalten. Die Statistiken werden vom Query Optimizer ausgewertet. Fehlen sie oder sind veraltet, basiert die Annahme des Query Optimizers auf fehlerhaften Daten. Ein einfaches Beispiel hierzu: Stellen Sie sich eine Tabelle vor, in deren (veralteter) Statistik steht, dass in der Tabelle ein Datensatz enthalten ist; in Wirklichkeit befinden sich aber eine Million Datensätze in der Tabelle. Es ist klar, dass der Query Optimizer in diesem Fall nicht optimal arbeiten kann. Ein weiterer Fall ist, wenn die Sampling-Rate für die Statistiken (also die Anzahl der Datensätze, die zur Ermittlung von Werten wie der Datendichte etc. herangezogen werden) zu gering ist. In diesem Fall stellt die Statistik ein falsches Bild der Daten dar. Weitere Probleme stellt sch...

Neugierig geworden? Wir haben diese Angebote für dich:

Angebote für Gewinner-Teams

Wir bieten Lizenz-Lösungen für Teams jeder Größe: Finden Sie heraus, welche Lösung am besten zu Ihnen passt.

Das Library-Modell:
IP-Zugang

Das Company-Modell:
Domain-Zugang