SQL Server Performance Optimierung

• Die Herausforderung: Auf dem Testsystem funktionierte die Applikation einwandfrei und schnell, nun ist sie ausgerollt und die Benutzerinnen und Benutzer sind mit der Performance nicht zufrieden.

• Oft muss sich der Entwickler oder die Entwicklerin selbst darum kümmern.

• Dieser Blog soll einen Weg für den Softwareentwickler, die Softwareentwicklerin aufzeigen, wie der Datenzugriff einer Applikation performanter gemacht werden kann.

Wer kennt das Problem nicht? Auf dem Testsystem funktionierte die Applikation einwandfrei und schnell, nun ist sie ausgerollt und die Benutzerinnen und Benutzer sind mit der Performance nicht zufrieden. In kleineren Projekten gibt es keinen Datenbankadministrator der sich diesem Problem annehmen kann, so dass sich der Entwickler oder die Entwicklerin selbst darum kümmern muss. Dieser Blog soll einen Weg für den Softwareentwickler, die Softwareentwicklerin aufzeigen, wie der Datenzugriff einer Applikation performanter gemacht werden kann.

1. Als erstes muss die Performance gemessen werden. Dazu gehören zum Beispiel die Antwortszeiten oder die Anzahl der Lesezugriffe für einzelne Datenbankabfragen.
2. Im zweiten Schritt muss dann ein Flaschenhals identifiziert werden. Dabei ist darauf zu achten, dass der Flaschenhals beseitigt wird, der dem Benutzer am meisten bringt. Es nützt ihm wenig, wenn der monatliche Report doppelt so schnell erstellt ist, er aber in der täglichen Arbeit immer noch gebremst wird.
3. Nun kann genau dieser Flaschenhals behoben werden.
4. Der wichtigste Schritt im ganzen Prozess ist das Verifizieren der Optimierung. Es können nun die gleichen Messungen nochmals gemacht und mit den Resultaten von Schritt 1 verglichen werden. Wichtig ist hier, dass man sich nicht nur auf den optimierten Punkt beschränkt, sondern die ganze Applikation betrachtet. Das Ziel wäre verfehlt, wenn nur ein Teil schneller läuft, der Rest aber noch langsamer geworden ist.

Wo geht die Performance verloren?

Nun stellt sich die Frage, wo die Performance verloren geht. Es gibt vier Schichten, die wir einzelnen betrachten und untersuchen können:

• Zuunterst haben wir die Hardware. In diesen Bereich gehören vor allem die Disks, auf denen unsere Daten schlussendlich gespeichert sind. Aber auch die Maschine, auf der unsere SQL Server Instanz läuft, hat einen Einfluss auf die Performance.
• Zur physikalischen Datenbankstruktur gehören die Tabellen und die Stored Procedures, wie sie in der Datenbank vorhanden sind. 
• Die logische Datenbankstruktur beschäftigt sich mit den Entitäten, wie sie in der Applikation vorkommen. 
• Auf der obersten Schicht haben wir die Applikation und dessen Datenbankzugriff. 

• Auch die integrierten Performance Counters von Windows können uns Hinweis auf einen Flaschenhals geben. Besonders interessant sind die beiden Counters Avg. Disk Queue Length und SQL Server: Wait Statistics. Diese zeigen auf, wie fest die Harddisks ausgelastet (insbesondere jene Disks mit den Datenfiles) sind und auf was der SQL Server warten muss.

Mit den gemessenen Werten können wir uns auf die Suche nach der Ursache machen. Die einfachste Erklärung die es gibt, ist eine zu langsame Hardware. Auch wenn es sich nach einer einfachen Ausrede anhört, es kann vorkommen, dass die Hardware den Datenmengen nicht mehr gewachsen ist. Eine weitere Ursache ist, dass sich der SQL Server mit anderen Serverapplikationen um die Ressourcen streiten muss. Virenscanner, Web- und Applikationsserver, die neben dem Datenbankserver auf der gleichen Hardware betrieben werden, entziehen dem Datenbankserver wertvolle Ressourcen. Dasselbe gilt auch für die Datenbanken anderer Applikationen, die alle auf der gleichen SQL Server Instanz laufen. All diese Datenbanken buhlen um die Ressourcen des Servers. Die Harddisk kann ein weiterer Flaschenhals sein. Auch wenn dieser Punkt mit dem Aufkommen von SSDs im Serverbereich nicht mehr so von Bedeutung ist. Befinden sich alle Datenfiles, Logfiles die TempDB und die SystemDB auf der gleichen Disk, so ist diese nur noch am Positionieren des Lesekopfes. Dieser Umstand wird noch verschlimmert, wenn die Dateien stark fragmentiert auf der Disk abgelegt sind.

Wir haben nun verschiedene Ursachen gesehen. Der nächste Schritt ist diese zu beheben:

• Ideal ist natürlich, wenn unsere Applikationsdatenbank einen eigenen dedizierten Server für sich alleine erhält. Alle Services, die nicht benötigt werden, sind abgeschaltet und dem SQL Server stehen die ganzen Ressourcen zu.
• Sind mehrere Disks vorhanden, so macht es Sinn, die Daten- und Logfiles auf verschiedene Disks zu platzieren. Dies gilt auch für die System- und TempDB. Diese sollten auf einer anderen Disk abgelegt werden, als die Applikationsdaten. Zudem sollte darauf geachtet werden, dass die Dateien nicht fragmentiert sind.
• Eine weitere Möglichkeit um die Hardware zu entlasten, ist das Ablegen der Daten in komprimierter Form. Der SQL Server unterstützt das komprimierte Speichern von Tabellen und Indizes. So müssen weniger Daten gelesen werden, jedoch erhöht sich die CPU Auslastung. 

Bei großen Tabellen macht es eventuell auch Sinn diese zu partitionieren und auf mehreren Disks abzulegen. So können die Daten parallel geladen werden. 

Nachdem ein Flaschenhals beseitigt wurde, darf natürlich der letzte Schritt im Optimierungsprozess nicht vergessen werden, nämlich das Validieren der hoffentlich besseren Performance.
 

Der Executionplan kann auch bei einer Abfrage an eine SQL Azure Datenbank angezeigt werden. Im Management Portal lässt sich die Abfrage ausführen und auch gleich der Executionplan anzeigen. 

Tauchen im Executionplan Full Table Scans auf, so ist das ein Anzeichen, dass eventuell ein Index die Performance verbessern könnte. Das SQL Server Management Studio zeigt dies sogar als Tipp an. Der SQL Server speichert Informationen über Indizes, die er für die Abfragen hätte verwenden können, wenn sie denn da gewesen wären. Diese Information können wir auch direkt über die Tabelle sys.dm_db_missing_index_group_stats abfragen.

Der SQL Server macht in derselben Tabelle auch Vorschläge für die „included columns“. Dies sind Felder einer Tabelle, die nicht zum eigentlichen Index gehören, aber zusätzlich abgespeichert werden. So können bei einem Zugriff auf diese Felder unter der Verwendung des Index die Daten direkt zurückgegeben werden. Nicht nur fehlende Indizes können die Performance negativ beeinflussen; es können auch zu viele Indizes vorhanden sein. Der SQL Server muss diese bei jeder Datenänderung nachführen, hat sie aber noch nie für eine Abfrage verwendet. In der Tabelle sys.dm_db_index_usage_stats ist die Verwendungsstatistik der Indizes gespeichert. Ist die Zahl der Updates grösser als die Zahl der Zugriffe, so kann der Index möglicherweise gelöscht werden. Es muss aber immer der gesamte Workload betrachtet werden. Eventuell wird genau dieser Index für den monatlichen Report verwendet.

Ähnlich wie eine Harddisk können auch Indizes fragmentiert sein. Dies führt dazu, dass beim Zugriff auf die Daten (zu) viele Datapages gelesen werden müssen, was sich verständlicherweise negativ auf die Performance auswirkt. Mit Hilfe der Funktion sys.dm_db_index_physical_stats kann die Fragmentierung abgefragt werden. Ist dieser Wert grösser als 30% sollte der Index neu erstellt werden (ALTER INDEX REBUILD), ist er zwischen 5% bis 30% reicht es ihn neu zu organisieren (ALTER INDEX REORGANIZE). Kleinere Werte als 5% sollten nicht beachtet werden, da der Aufwand (das neu organisieren benötigt Ressourcen und belastet unseren Server) grösser ist als der Nutzen.

Eine Ursache für schlechte Executionplans können veraltete Statistiken sein. Der Query Optimizer verwendet die Statistiken, um zu entscheiden, wie er auf die Tabellen zugreifen und diese verknüpft werden sollen. Werden die Statistiken nicht aktualisiert, so entscheidet sich der Optimizer eventuell für die falsche Strategie. Die Statistiken sollten daher regelmässig aktualisiert werden. Dies kann manuell über den Befehl UPDATE STATISTICS gemacht werden. Der SQL Server kann dies jedoch auch automatisch machen. Dies wird bei den Optionen für jede einzelne Datenbank eingestellt.

Microsoft stellt zu den bereits erwähnten Werkzeugen zusätzlich den „Database Engine Tuning Advisor“ zur Verfügung. Dieser ist im SQ Server Management Studio unter dem Menupunkt Tool zu finden. In diesem Tool kann die Datenbank angegeben werden, die für einen bestimmten Workload analysiert werden soll. 
 

Nachdem die Analyse abgeschlossen ist, werden Vorschläge angezeigt, inklusive SQL Statement. Diese Vorschläge sollten jetzt nicht einfach blind umgesetzt werden. Je nach ausgewähltem Workload verbessern sie zwar die Performance, dies aber vielleicht nur in einem gewissen Bereich.

Eine solche Extended Event Session zeigt viele interessante Punkte. Alle Abfragen und Statements, die an den Server gestellt werden, sind hier sichtbar. Dies gibt einen guten Einblick, welche Abfragen wie häufig ausgeführt werden, wie viele Ressourcen (CPU, Reads, Writes) sie dazu benötigen und wie lange die Ausführungszeit ist. 

Seit SQL Server Version 2016 gibt es auch den Query Store. Ist dieser eingeschaltet, werden alle Abfragen mit den dazugehörenden Laufzeitstatistiken abgespeichert und können so zu einem späteren Zeitpunkt analysiert werden.  
 

Wo kann nun die Ursache für eine schlechte Performance liegen? Eine Ursache kann das zu „feste“ normalisieren der Daten sein. Zwar sollten in einer relationalen Datenbank die Daten nicht redundant abgespeichert sein, es gibt jedoch Situationen, bei denen das gezielte Einfügen von Redundanzen die Ausführungsgeschwindigkeit einer Abfrage enorm verbessern kann. Werden jedoch überwiegend Modifikationsoperationen (im Vergleich zu Select Statements) ausgeführt, kann das Ganze auch in die andere Richtung kippen. Die redundanten Daten müssen dann auch immer nachgeführt werden, was wiederum Ressourcen benötigt.
 

Die Applikation

Nicht immer ist der SQL Server Schuld an einer schlechten Performance. Manchmal ist es auch die Applikation schlechthin, welche den SQL Server in die Knie zwingt. Um Fehlern oder Fehlverhalten der Applikation auf die Schliche zu kommen, kommen nochmals zwei bereits bekannte Tools zum Einsatz. Zum einen ist dies der SQL Server Profiler, zum anderen die Performance Counters. 

Bei den Performance Counters interessiert uns dieses Mal die Anzahl User Connection und die Anzahl der Transaktionen (zu finden unter SQL Server: General Statistics). Steigt die Anzahl der Verbindungen mit jedem Klick in der Applikation an und geht erst beim Beenden wieder zurück, so deutet das auf ein falsches oder gar kein Connection Management in der Applikation hin. Die Verbindungen werden zwar geöffnet, danach aber nicht mehr geschlossen. Dasselbe gilt für die Transaktionen. Ist die Anzahl der offenen Transaktionen nur zunehmend, so wurde auf ein korrektes Transaktionsmanagement in der Applikation verzichtet. Abhilfe schafft in diesem Fall nur ein Review des Connection- und Transactionmanagements.

Der SQL Server Profiler gibt uns Aufschluss über die Abfragen, welche die Applikation an die Datenbank stellt. Hier gilt das Augenmerk bestimmten Patterns von Abfragen, welche wiederholt vorkommen. Werden zum Beispiel immer wieder die gleichen Stammdaten (z.B. Länder) abgefragt, so deutet das auf das nicht existieren eines Caches in der Applikation hin. In diesem Fall hilft es natürlich, immer wieder verwendete Daten (die sich sehr selten ändern) in der Applikation zu cachen und nicht jedes Mal neu zu laden.

Dies macht umso mehr Sinn, je weiter weg die Datenbank (z.B. in der Cloud) sich befindet. Wird in der Applikation ein ORM (Object-Relation Mapper) wie Entitiy Framework eingesetzt, so muss ein besonderes Augenmerk auf das 1+n Problem geworfen werden. Dabei werden zu jedem Datensatz (z.B. Person) einzeln die dazugehörenden Kindelemente (die Telefonnummern der Person) abgefragt, obwohl dies mit einem JOIN in einem einzigen Statement möglich wäre. Viele ORM’s bieten hier Abhilfe, indem man bei der Abfrage angeben kann, welche Objekte zusätzlich geladen werden sollen. Bei Entity Framework ist dies mit der Include() Methode möglich.

Neben all den Patterns findet man im SQL Server Profiler auch Abfragen, die nicht optimal sind oder sogar auf Fehler in der Applikation hindeuten. Werden die Daten zum Beispiel alle zum Client übertragen und dort aggregiert, ist dies sicher nicht optimal. Hier hilft es, die einzelnen Abfragen durchzugehen. Abfragen, die sehr lange dauern oder sehr oft aufgerufen werden, können dann gezielt optimiert werden.
 

Tipps und Tricks

Zum Schluss noch ein paar Tipps und Tricks, auf die man beim Optimieren achten sollte: 

• Optimiere nie, ohne zu messen. Wie im Optimierungsprozess beschrieben, ist der erste Schritt immer das Messen der Performance. Erst danach kann mit dem Optimieren und Ändern des Systems begonnen werden. 
• Führe Messungen auf dem Livesystem durch. Es ist sehr schwierig auf einem Testsystem die genau gleiche Serverauslastung und denselben Workload wie auf dem Livesystem zu simulieren. Zudem ist es meist sehr umständlich, die gleiche Datenmenge wie auf dem produktiven System herzustellen.
• Ändere immer nur etwas auf einmal. Werden 3 Änderungen auf einmal gemacht, so können das zwei Schritte vor und einen zurück sein. Welche Änderung etwas gebracht hat und welche nicht, findet man nur sehr schwer heraus. 
• Betrachte das System immer als Ganzes. Sinn der Performanceoptimierung ist es, dass das System schneller auf Benutzerinteraktion reagiert und der Benutzer/die Benutzerin besser arbeiten kann. Werden nur einzelne Teile des Systems betrachtet, so werden diese zwar schneller, das heisst aber noch lange nicht, dass dies dem Benutzer/der Benutzerin etwas bringt.