Es ist einer dieser typischen DBA-Tage: Eben läuft noch alles rund - und im nächsten Moment leuchten auf einer Ihrer SQL-Server-Instanzen rote Warnmeldungen auf. Die CPU-Auslastung schnellt in die Höhe, die Festplatten-IO geht durch die Decke, der Speicher ist stark belastet. Es dauert nicht lange, bis die ersten Anrufe kommen: "Mit der Datenbank stimmt etwas nicht!"

Wurde mit der neuen Softwareversion vielleicht die Datenbankstruktur geändert oder eine Stored Procedure angepasst? Liegt es am Service Pack, das gestern Abend installiert wurde? Oder hat eine Datenlast einen Recompile ausgelöst, und der neue Ausführungsplan ist für diese Abfragen weniger geeignet als der alte? Möglich ist vieles.

Aber wo fängt man mit der Fehlersuche an?

Der Blick auf die Servermetriken

Zuerst werfen Sie einen Blick auf die grundlegenden Servermetriken zur Ressourcennutzung - und stellen fest: Ja, der Server steht wirklich unter Druck. Abbildung 1 zeigt die Situation in Redgate Monitor.

Abbildung 1

Die CPU (blaue Linie) war über mehrere Stunden hinweg stark ausgelastet. Außerdem gab es deutliche Spitzen bei den IO-Zeiten auf der Festplatte (grün) sowie bei den Wait Times (orange). Auch die Speichernutzung (lila) war hoch und ist weiter angestiegen. Am Ende des Zeitraums fällt die CPU-Belastung wieder ab. Den Prozess, der für die dauerhaft hohe CPU-Last verantwortlich ist, kennen Sie - er ist unschön, aber derzeit normal, da er sich aktuell nicht weiter optimieren lässt.

Die wirklich auffälligen Werte sind die ungewöhnlichen Peaks bei CPU, IO und möglicherweise auch bei den Wait Times.

Also, was ist der nächste Schritt? Untersuchen Sie den CPU-Druck genauer? Schauen Sie sich die Wartetypen an, die in diesem Zeitraum ungewöhnlich hoch waren? Oder analysieren Sie die IO-Spitzen, um herauszufinden, welche Dateien oder Tabellen die Hotspots sind?

Ohne Monitoring müssten Sie jede einzelne Kennzahl separat analysieren und dann versuchen, die Zusammenhänge selbst herzustellen - hier ein Beispiel dafür. Mit Redgate Monitor können Sie dagegen direkt auf aussagekräftige Metriken und Berichte zurückgreifen.

Je nach Situation kann es sein, dass man verschiedene Ansätze ausprobieren muss. Für mich ist es aber meist der beste erste Schritt, mir die Abfragen anzuschauen, die in diesem Zeitraum gelaufen sind. Wenn ich sehe, dass bestimmte Abfragen länger oder häufiger liefen und dabei CPU oder Festplatten-IO stark beansprucht haben, sehe ich mir direkt ihre Ausführungspläne an.

Teure Abfragen aufschlüsseln

Oft liegt die Ursache für Probleme im System an einer oder mehreren Abfragen, die gerade laufen. Praktisch ist, dass Redgate Monitor am oberen Bildschirmrand eine Top-10-Liste der teuersten Abfragen anzeigt, die während des gewählten Zeitraums ausgeführt wurden - in diesem Beispiel zwischen 9:30 Uhr und etwa 13:30 Uhr. Abbildung 2 zeigt die Abfragen, sortiert nach Dauer (MS).

Abbildung 2

Die langsamste Abfrage brauchte 1.721 ms. Den Abfragetext können wir uns direkt ansehen, indem wir sie anklicken. In Abbildung 3 ist zu sehen, dass es sich dabei um eine Abfrage handelt, die Daten aus den Systemtabellen abruft.

Abbildung 3

Wenn wir uns die Spalte Ausführungsanzahl in Abbildung 2 ansehen, fällt auf: Diese Abfrage lief nur ein einziges Mal in diesem Zeitraum. Eine Abfrage, die einmal für 400 ms läuft, kann die ungewöhnliche Systemlast also nicht erklären. Wir müssen also weitergraben.

Die zweitschnellste Abfrage ist ebenfalls eine Systemabfrage, die nur einmal ausgeführt wurde. Auch die kann keine ungewöhnliche Last verursacht haben. Die dritte Abfrage ist dagegen spannender: Sie lief im Schnitt 200 ms. In Abbildung 4 sehen wir ihren Text.

Abbildung 4

Diese Abfrage zieht Adressdaten aus der AdventureWorks-Datenbank. Klingt erst einmal harmlos - 200 ms im Schnitt. Aber: Sie wurde in diesem Zeitraum 2.367 Mal ausgeführt. Jetzt haben wir eine Abfrage, die sich wirklich lohnt, genauer unter die Lupe zu nehmen!

Wie man sieht, ist die Dauer allein kein verlässliches Maß. Wir sollten immer auch die Ausführungsanzahl und weitere Metriken wie die logischen Lesevorgänge im Blick behalten. Abbildung 5 zeigt die Top 5 der 10 teuersten Abfragen, diesmal sortiert nach der Ausführungsanzahl.

Abbildung 5

An der Spitze taucht hier eine ganz neue Abfrage auf. Sie dauert im Schnitt nur 4 ms, wurde aber in diesem Zeitraum sage und schreibe 500.000 Mal aufgerufen! Ich kenne diese Abfrage bereits - sie ist für die konstant hohe CPU-Last verantwortlich. Sie gehört fest zu unserem Workload, wurde schon optimiert und ist trotz ihrer Häufigkeit kein Problemkandidat, wenn wir ungewöhnliche Lastspitzen sehen.

Die zweite Abfrage ist wieder die Adresse-Abfrage von oben. Die nächsten drei Abfragen wurden ebenfalls tausendfach aufgerufen, tragen also zur Gesamtauslastung bei, haben aber wegen ihrer sehr kurzen Laufzeit nur geringe direkte Auswirkungen.

Abbildung 6 zeigt die gleiche Abfrageliste, diesmal sortiert nach logischen Lesevorgängen:

Abbildung 6

Dabei fällt auf: Einige Abfragen gegen die Systemtabellen verursachten die meisten logischen Lesevorgänge, aber auch die Adresse-Abfrage taucht wieder ganz oben auf.

Damit sehen wir klar: Es gibt eine Abfrage, die extrem oft ausgeführt wird, und eine andere, die egal nach welchem Kriterium immer wieder oben erscheint. Erstere sollten wir im Blick behalten, falls sie plötzlich häufiger als normal auftritt. In diesem Fall ist es aber die Abfrage Adresse, die sich als Hauptkandidat für Optimierungen anbietet.

Ausführungspläne prüfen

Schauen wir uns die Adresse-Abfrage also genauer an. Neu in Redgate Monitor Version 6 ist die Möglichkeit, Ausführungspläne direkt im Tool einzusehen. Früher musste man sich dazu den plan_handle holen und über die dynamischen Management-Views eigene Abfragen starten - oder, in Azure SQL Database bzw. ab SQL Server 2016, den Query Data Store nutzen.

In Redgate Monitor reicht ein Klick auf eine Schaltfläche - das habe ich bisher verschwiegen, als ich die Abfrage gezeigt habe. Abbildung 7 zeigt die Abfrage im Vollbild.

Abbildung 7

Wenn Sie eine Abfrage gefunden haben, die ein Kandidat für Optimierung ist, sollten Sie unbedingt ihren Ausführungsplan ansehen. Dieser zeigt, wie der Abfrageoptimierer entschieden hat, die Abfrage auszuführen: welche Indizes genutzt werden, ob ein Index-Seek oder ein Scan erfolgt, wie die Tabellen verknüpft werden und vieles mehr.

All diese Infos helfen zu erkennen, ob es Ansatzpunkte für Optimierungen gibt. In Redgate Monitor klicken wir dafür einfach auf Abfrageplan anzeigen.

Abbildung 8

Damit öffnet sich der Ausführungsplan direkt in Redgate Monitor - ganz ohne SQL Server Management Studio. Das Plan-Handle wird ebenfalls mit angezeigt, sodass Sie den Plan, falls er noch im Cache liegt, später auch in SSMS laden können.

Redgate Monitor zeigt den zwischengespeicherten Plan im vertrauten Layout, ähnlich wie die Standardpläne in SSMS. Anstatt die Eigenschaften jedes Operators in einem separaten Fenster zu öffnen, können Sie einfach den Link EIGENSCHAFTEN unter dem jeweiligen Operator ausklappen. Bestimmte Operationen und Warnungen hebt Redgate Monitor sogar extra hervor - wie in Abbildung 9.

Abbildung 9

Oben sehen wir die teuersten Operationen im Plan - basierend auf den geschätzten Kosten des Optimierers. Und wichtig: Auch im „tatsächlichen“ Ausführungsplan handelt es sich immer nur um Schätzungen. Diese Werte sind aber hilfreich, um in komplexen Plänen die größten Kostentreiber zu identifizieren. Hier sehen wir zum Beispiel einen Scan auf dem geclusterten Index der Tabelle Adresse. Außerdem sind Operationen sichtbar, die viel I/O verursachen können.

Zusätzlich sehen wir eine Warnung zu einem fehlenden Index. Mit einem Klick auf den Vorschlag können Sie die Definition des Indexes ansehen und bewerten.

Abbildung 10

In unserem Fall sucht die Abfrage nach einer bestimmten Stadt in der Tabelle Adresse. Da es keinen passenden nicht-geclusterten Index gibt, der nach Stadt sortiert ist, wählt der Optimierer einen Scan über den gesamten geclusterten Index. Das heißt, SQL Server müsste alle Zeilen durchsuchen, um die wenigen passenden Datensätze zurückzugeben.

Das führt dazu, dass unnötig viele logische Lesevorgänge (IO) anfallen. Würden wir den vorgeschlagenen Index anlegen, könnte der Optimierer diesen nutzen und die Daten mit deutlich weniger IO zurückgeben.

Wichtig: Die Hinweise zu fehlenden Indizes sind nur Vorschläge. Man sollte nicht blind jeden Index anlegen. Manche Vorschläge sind unpassend, manche lassen sich besser durch einen allgemeinen Index abdecken. Und manchmal wird eine Abfrage so selten ausgeführt, dass ein zusätzlicher Index keinen echten Nutzen bringt.

Die Warnung ist also ein hilfreicher Hinweis, sollte aber immer kritisch geprüft werden. In SSMS lassen sich Metriken einzelner Läufe erfassen, um genau zu messen, welchen Nutzen ein neuer Index für Laufzeit und IO-Last bringt.

Denken Sie auch daran, den Indexvorschlag im Gesamtkontext zu bewerten: Würde er auch anderen Abfragen helfen? Die DMVs für fehlende Indizes liefern dazu weitere nützliche Informationen.

Und zuletzt: Welche Auswirkungen hätte ein zusätzlicher Index auf Schreibvorgänge in dieser Tabelle? Zwar verursacht ein einzelner neuer Index selten Probleme, aber wenn es schon viele Indizes gibt, kann ein weiterer unnötigen Overhead bei Änderungen erzeugen.

Kurz gesagt: Ein sauberes Testverfahren ist Pflicht, um sicherzustellen, dass Optimierungen im Gesamtsystem wirklich Vorteile bringen.

Zusammenfassung

Wir haben mit Redgate Monitor auffällige Verhaltensweisen auf dem Server entdeckt und konnten diese auf eine konkrete Abfrage eingrenzen. Genau dort haben wir nun einen Ansatzpunkt für Optimierungen gefunden.

Und genau das sollte ein Monitoring-Tool leisten: Probleme sichtbar machen und Ansatzpunkte liefern. Die eigentliche Lösung testen Sie dann in Ihrer Entwicklungsumgebung und bringen sie anschließend über den Freigabeprozess in die Produktion, um ähnliche Probleme künftig zu vermeiden.