en
de

SQL Server Performance Optimierung – Teil 2

12 August 2013
| |
Lesezeit: 5 Minutes

Nachdem sich der erste Teil dieses Blogs mit dem Optimierungsprozess und der Hardware beschäftigt hat, tauchen wir nun in die Datenbank und ihre physikalische und logische Struktur ein. Auch auf diesen Ebenen haben wir verschiedene Werkzeuge zur Verfügung, um die Performance zu messen, Ursachen für langsame Queries zu finden und diese zu beheben.

Physikalische Datenbankstruktur

Unter der physikalischen Datenbankstruktur verstehen wir die Tabellen, Views, Indizes und die Stored Procedures, wie sie in der Datenbank vorhanden sind. Diese haben einen sehr grossen Einfluss auf die Performance, denn hier werden die eigentlichen Daten gespeichert und abgefragt.

Um Aussagen über die Performance auf diesem Level zu machen, helfen uns die beiden folgenden Befehle weiter:

SET STATISTICS IO ON
SET STATISTICS TIME ON

Führen wir diese zwei Befehle im SQL Server Management Studio vor einem Query aus, so liefert uns der Server nach Beenden des Queries Informationen zu Ausführungszeit und zu den Zugriffen auf das IO System. Je kleiner diese Zahlen sind, desto besser. Um noch weitere Details zu einer Abfrage zu erhalten, können wir uns im Management Studio mit Ctrl + M den Actual Execution Plan anzeigen lassen. Dieser zeigt die einzelnen Schritte an, welche der SQL Server ausführt.

Execution Plan

Execution Plan

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

ExecutionPlan Azure

Execution Plan Windows Azure SQL Database

Tauchen im Executionpan 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 einzene Datenbank eingestellt.

Microsoft stellt zu den bereits erwähnten Werkzeugen zusätzlich den „Database Engine Tuning Advisor“ zur Verfügung. Dieser ist im SQL 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.

Database Engine Tuning Advisor

Database Engine Tuning Advisor

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.

Logische Datenbankstruktur

Über dem physikalischen Datenmodell befindet sich die logische Datenstruktur. Diese Struktur ist ein Modell der Daten welches aufzeigt, wie die Businessentitäten in Tabellen abgelegt werden sollen. Beim Übergang vom logischen ins physikalische Datenmodell findet die Normalisierung der Daten statt. Und genau bei diesem Übergang kann die Performance verloren gehen.

Bevor wir uns jedoch um die Ursache kümmern, zuerst wieder Schritt 1 im Performance Optimierungsprozess: Messen. Wir haben bereits im vorherigen Kapitel hierfür ein paar Tools kennengelernt. Ein weiteres nützliches Instrument ist der SQL Server Profiler, welcher sich im SQL Server Management Studio unter Tools – SQL Server Profiler befindet.

SQL Server Profiler

SQL Server Profiler

Der SQL Server Profiler 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. Leider steht der SQL Server Profiler für Azure Datenbanken (noch) nicht zur Verfügung.

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.

Abfragen, welche Daten aggregieren, d.h. als SUM, AVG oder COUNT zurückgeben, benötigen in den meisten Fällen sehr viele Lesezugriffe. Sie greifen nicht nur auf einzelne Datensätze zu, sondern auf sehr viele, die dann verdichtet werden. Wird aus dem SQL Server Profiler Trace ersichtlich, dass dies häufig der Fall ist, macht es eventuell Sinn diese Daten im Voraus zu berechnen. Der SQL Server bietet hierfür sogenannte Indexed Views an. Dabei wird eine View erstellt, welche uns die Daten so aggregiert, wie wir sie in den Abfragen benötigen. Diese View erhält danach zusätzlich einen Unique Clustered Index. Der Index dabei speichert direkt die aggregierten Daten. Ändern nun die unterliegenden Daten, so werden die Daten in der View automatisch mit aktualisiert. Dieses Verhalten können wir natürlich auch selber bauen. Die aggregierten Daten werden in einer eigenen Tabelle gespeichert und mittels Triggers bei Änderungen aktualisiert. Da das Erstellen einer Indexed View oder einer Tabelle mit aggregierten Daten wiederum ein Hinzufügen von Redundanzen ist, macht es nur Sinn, wenn wir vorwiegend SELECT Statements haben und die Daten nicht laufend modifiziert werden.

Bevor wir uns im dritten Teil des Blogs der Applikation und dem Datenbankzugriff widmen, sei hier nochmals der letzte Schritt im Performanceverbesserungsprozess in Erinnerung gerufen, die Validierung. Nach jeder Änderung muss die Performance wieder gemessen und mit dem Ausgangswert verglichen werden.

Kommentare (0)

×

Updates

Schreiben Sie sich jetzt ein für unsere zwei-wöchentlichen Updates per E-Mail.

This field is required
This field is required
This field is required

Mich interessiert

Select at least one category
You were signed up successfully.

Erhalten Sie regelmäßige Updates zu neuen Blogartikeln

Jetzt anmelden

Oder möchten Sie eine Projektanfrage mit uns besprechen? Kontakt aufnehmen »