Mit drei Abfragen zum fehlenden Index – Performance Tuning beim Microsoft SQL Server

Für eine halbe Stunde war die CPU des Datenbank-Servers bei 100 Prozent. Mehr Informationen hat der Kunde nicht, ist aber trotzdem an der Ursache und vor allem einer nachhaltigen Lösung des Problems interessiert. 

Voraussetzungen

Dass ich dem Kunden überhaupt kurzfristig helfen konnte, lag vor allem daran, dass wir wenige Wochen zuvor eine entsprechende Datensammlung eingerichtet hatten.

Alle 15 Minuten werden mit Hilfe der Prozedur sp_BlitzFirst aus dem First Responder Kit von Brent Ozar relevante Informationen in eine eigene Datenbank geschrieben. Nutzer von Oracle aktivieren dazu AWR oder Statspack. Nutzer des SQL Servers sind eben auf zusätzliche Software angewiesen.

Wenn ihr diese Datensammlung auf mehreren Servern einrichten möchtet, empfehlen wir die Verwendung eines PowerShell-Skriptes. Sprecht uns dazu gerne an. 

Abfrage 1: Gibt es Auffälligkeiten bei den Wartestatistiken?

Um mir einen ersten Überblick zu verschaffen, aggregiere ich die relevanten Wartestatistiken nach Kategorien und schränke grob auf den betroffenen Zeitraum ein. 

Die Abfrage zeigt, dass es eine sehr deutliche Erhöhung im Bereich der CPU-Wartezeiten gibt. Auch andere Kategorien steigen an, aber das werden vermutlich nur Folgeerscheinungen sein, die daher zunächst nicht weiter betrachtet werden.  

Abfrage 2: Welche Abfrage hatte den höchsten CPU-Verbrauch?

Bei jeder Datenerhebung werden auch die fünf Abfragen mit dem höchsten Ressourcenverbrauch je Kategorie (CPU, Reads, Writes etc.) gespeichert.

Hier interessiert mich erstmal nur die erste Abfrage in der Kategorie „CPU“ zu jedem Zeitpunkt, denn so fällt die Veränderung sofort auf. 

Damit ist nicht nur der Text der Abfrage bekannt, sondern auch der verwendete Ausführungsplan sowie das SQL Handle der Abfrage. Mit diesem SQL Handle kann auch der aktuelle Ausführungsplan der Abfrage als Vergleich ermittelt werden.  

Welche Situation ganz genau zu dem sehr ungünstigen Ausführungsplan geführt hat, kann ich auf die Schnelle gar nicht sagen und eine detaillierte Analyse wäre vermutlich auch nicht sehr zielführend.

Denn der typischerweise genutzte Ausführungsplan ist wesentlich besser und die Abfrage taucht sonst auch nicht unter den Abfragen mit dem höchsten Ressourcenverbrauch auf.

Ziel ist es hier also, diesen Ausführungsplan zu analysieren und durch einen zusätzlichen Index weiter zu optimieren und damit auch zu stabilisieren. 

Abfrage 3: Welche Indizes schlägt der SQL Server selbst vor?

Der Abfrageoptimierer wählt bei der Erstellung eines Abfrageplans die effizientesten Indizes für die vorliegenden Filterbedingungen aus. Fehlen diese Indizes, verwendet er die kostengünstigsten verfügbaren Methoden und speichert zusätzlich Empfehlungen für optimale Indizes. Über die Funktion „Fehlende Indizes“ können diese Empfehlungen eingesehen werden, um zu entscheiden, ob die Indizes implementiert werden sollen.

Details findet ihr in der Dokumentation.

Auch wenn bereits bei der Darstellung der Ausführungspläne zusätzliche Indizes vorgeschlagen werden, so gibt die Prozedur sp_BlitzIndex, ebenfalls aus dem First Responder Kit, eine noch bessere Übersicht. Es werden auch die bereits bestehenden Indizes aufgelistet und zudem alle für die Tabellen vorgeschlagenen Indizes mit vielen Details aufgelistet. 

Die Bewertung: Was kann ich aus den Informationen lesen?

Ganz wichtig: Bitte folgt nie blind den Empfehlungen des SQL Servers. Denn der SQL Server ist wie ein kleines Kind und wünscht sich gerne auch mal Sachen, die nicht unbedingt benötigt werden. Und er kann seine Wünsche auch nicht immer klar äußern, also bei Indizes mit mehr als einer Spalte nicht bestimmen, welche Spaltenreihenfolge die beste ist.

Daher werfen wir zunächst einen Blick auf die vorhandenen Indizes und die in der betroffenen Abfrage genutzten Spalten.

Auf der Tabelle PWORKSPACEOBJECT ist die Spalte pobject_name bereits einzeln indiziert, die Spalte arev_category allerdings nur in zwei Indizes als zweite Schlüsselspalte enthalten. Daher ist auch der Key Lookup im Ausführungsplan notwendig: Diesen würde ich jedoch gerne vermeiden, da er lediglich eine Integer-Zahl ermittelt. Die Spalte puid ist der Primärschlüssel und daher in allen Indizes immer enthalten. Es geht hier also nur um die beiden Spalten pobject_name und arev_category, für die dann auch ein entsprechender Index vorgeschlagen wird.

Auf der Tabelle PDATASET gibt es bereits einen Index auf der Spalte aoid, aber keinen Index auf der ebenfalls von der Abfrage benötigten Spalte arev_category. Auch hier wird ein Index vorgeschlagen, der zunächst sinnvoll erscheint, da er die betroffenen Spalten der Abfrage enthält. Aber ein genauerer Blick auf Abfrage und Ausführungsplan zeigt, dass der beste Zugriff auf die Tabelle PDATASET immer über den Primärschlüssel erfolgen wird und dieser bereits durch den entsprechenden Index optimal unterstützt wird. 

Die Entscheidung: Welcher Index soll angelegt werden?

Meinen Überlegungen zufolge sollte der auf der Tabelle PWORKSPACEOBJECT vorgeschlagene Index erstellt werden, da er die Abfrage optimal unterstützt.

Durch die Bereitstellung dieses passenden Indizes wird dann auch für die Zukunft verhindert, dass der SQL Server einen sehr schlechten Ausführungsplan erstellt, so wie es mal für eine halbe Stunde vorgekommen ist. 

Das Ergebnis: Der neue Index wird genutzt

Nach ein paar Tagen kann ich sagen: Der neue Index wird genutzt. Auch die Performance hat sich verbessert, die CPU-Zeit ist auf knapp die Hälfte gesunken. Ob der Plan jetzt über lange Zeit auch bei ungünstigen Parametern stabil bleibt, kann ich noch nicht mit Sicherheit sagen. Aber bis jetzt sieht es gut aus.  

Fazit: Drei Abfragen und ein paar Überlegungen

Mit nur drei Abfragen und der sorgfältigen Analyse der ermittelten Daten kann eine einmalige Auffälligkeit analysiert und ein passender Index ermittelt werden.

Auch ohne eine konkrete Auffälligkeit kann das hier beschriebene Vorgehen sehr einfach Abfragen ermitteln, die viele Ressourcen benötigen und für diese dann Optimierungspotenziale aufzeigen.

Sollen wir gemeinsam auch eure Datenbank analysieren? Sprecht uns an.

Seminarempfehlung