Phänomene
Für Website-Besucher:
- Eine IIS-ASP.NET-Webanwendung reagiert sehr langsam beim Ausliefern der Seiten.
- Aus Sicht der Benutzer dauert das Aufrufen von URLs 30 Sekunden oder länger.
- Manche URL-Aufrufe werden mit Datenbank-Timeout-Fehlermeldungen beantwortet.
Für den Server-Admin im IIS Manager:
- In der Liste der Anforderungen im IIS Manager ("<ServerName>" → IIS → Arbeitsprozesse → „<Anwendungspool>“ in Liste doppelklicken → Anforderungen) sind sehr viele Anfragen mit hohen Werten in „Verstrichene Zeit“, die nicht weiter abgearbeitet werden.
Für den Server-Admin im Process Explorer:
- Mit Process Explorer ist zu sehen, dass der entsprechende w3wp.exe mit 20-30% CPU-Auslastung arbeitet.
- Mit Process Explorer ist außerdem zu sehen, dass der entsprechende sqlservr.exe mit höhere CPU-Auslastung arbeitet.
- Beim Doppelklick auf den w3wp.exe-Prozess erscheint das „Properties“-Fenster. Dort auf die „Threads“-Registerkarte wechseln und nach der Spalte „CPU“ absteigend sortieren. Dann mehrfach die weiter oben liegenden Zeilen mit höherer CPU-Auslastung doppelklicken um das Fenster „Stack for thread <Nummer>“ anzuzeigen. Hier ist zu beobachten, dass des öfteren Code der ASP.NET-Anwendung mit Datenbankzugriffen aufgeführt wird.
Für den Server-Admin in der Ereignisanzeige:
- Im Ereignisprotokoll ist zu sehen, dass es Zeitüberschreitungs-Fehler beim Warten auf Antworten der Datenbank gibt.
Hypothese zu der Ursache
Der SQL Server ist ausgelastet.
Der Server nutzt einen SQL Server Express, mit entsprechenden Skalierungsgrenzen, speziell für CPU-Nutzung und RAM-Nutzung.
Diese Grenzen liegen weit unterhalb der CPU- und RAM-Ressourcen des Windows Server.
Deshalb frisst der SQL Server Express auch nicht 100% der CPU und 100% des RAM, sondern maximal bis zur den limitierenden Grenzen.
Die weitere Hypothese ist, dass die Auslastung zum Teil durch zu viele gleichzeitige Nutzer verursacht ist, aber zum großen Teil durch langsame SQL-Abfragen der ASP.NET-Webanwendung.
Suche der Engstellen
Es sollen langsame SQL-Abfragen identifiziert werden.
SQL Server Express hat keinen Tuning Advisor („Datenbankoptimierungsratgeber“), jedoch kann er den SQL Server Profiler ausführen.
Engstellen aufzeichnen:
- SQL Server Profiler starten.
- „Neue Ablaufverfolgung“ aufrufen.
- „Vorlage verwenden“ auf den Wert „Tuning“ stellen.
- Eventuell noch auf Registerkarte „Ereignisauswahl“ wechseln und via „Spaltenfilter“ nur diejenigen Spalten einschließen, die bei „Duration“ (in Millisekunden) einen Wert größer 100 (oder andere Werte) haben.
- Mit Schaltfläche „Ausführen“ starten.
Jetzt eine Weile unter Last aufzeichnen lassen, z. B. 10 Minuten. Anschließend über „Ausgewählte Ablaufverfolgung beenden“ (Rotes Quadrat-Symbol in Werkzeugleiste) beenden.
Die Liste im Hauptfenster kann auch jederzeit in eine SQL-Tabelle exportiert werden. Das ist sinnvoll, um nach „Duration“ zu sortieren, um die langsamsten Abfragen zuerst zu erwischen:
- Menü „Datei“, Menüpunkt „Speichern unter“, Untermenüpunkt „Ablaufverfolgungstabelle“.
- Dann SQL-Server auswählen im erscheinenden Dialog.
- Im Dialog „Zieltabelle“ eine Datenbank im Feld „Datenbank“ auswählen, und im Feld „Tabelle“ einen neuen Namen einer noch nicht existierenden Tabelle angeben.
- Mit „OK“ speichern.
Anschließend kann die Tabelle via SQL Server Management Studio (SSMS) entweder direkt dort gefiltert und sortiert werden. Alternativ auch über SSMS nach Microsoft Excel exportieren:
- SSMS starten und mit Datenbank verbinden.
- Die Zieltabelle im Objekt-Explorer auswählen.
- Rechtsklick auf eine Tabelle.
- Auswählen von „Oberste 1000 Zeilen auswählen“.
- Im erscheinenden „Ergebnisse“-Grid optional die SELECT-Anweisung anpassen und sortieren/filtern und mit F5 das „Ergebnisse“-Grid aktualisieren.
- Dann via Strg+A alles markieren.
- Rechtsklick auf den markierten Bereich.
- „Ergebnisse speichern unter“ auswählen.
- Im erscheinenden Dialog die Datei als *.csv abspreichern.
- Diese Datei in Excel weiter bearbeiten, sortieren, filtern, usw.
Engstellen identifizieren:
- Die aufgezeichnete Liste der SQL-Abfragen manuell durchblättern.
- Auf SQL-Abfragen achten, die in Relation zu anderen SQL-Abfragen hohe Werte in der Spalte „Duration“ aufweisen. „Duration“ ist normalerweise eine Angabe in Millisekunden.
Das sind Abfragen, die lange laufen und potentiell die Ursachen für die langsame ASP.NET-Webanwendung (neben zu vielen gleichzeitigen Nutzern) sind.
SQL-Abfrage weiter analysieren:
- Einen zweiten Entwicklungsrechner mit SQL Server Developer Edition heranziehen.
- Auf diesem Entwicklungsrechner eine strukturelle 1:1-Kopie mit möglichst auch ähnlichen Daten aufsetzen.
- Vom langsamen Produktivsystem mit der ASP.NET-Webanwendung eine langsame SQL-Abfrage in die Zwischenablage kopieren.
- SQL Server Management Studio (SSMS) auf dem Entwicklungsrechner starten.
- „Neue Abfrage“ auf die 1:1-Kopie der Datenbank ausführen.
- Im erscheinenden SQL-Fenster die langsame SQL-Abfrage aus der Zwischenablage einfügen.
- Hauptmenü „Abfrage“, Menüpunkt „Abfrage in Datenbankopitimierungsratgeber analysieren“ anklicken.
- Es öffnet sich ein neues Programm und Fenster „Datenbankopitimierungsratgeber“, schon vorausgefüllt mit der SQL-Abfrage im Hintergrund.
- In Werkzeugleiste auf „Analyse starten“ klicken.
- Ggf. 10-20 Sekunden warten.
Am Ende eines Analyselaufs erscheint die Registerkarte „Empfehlungen“.
Wenn dort „Geschätzte Verbesserung“ einen Wert größer 0% enthält, werden darunter im Bereich „Indexempfehlungen“ SQL-Befehle für neue Indexe angezeigt.
Stehen dort keine Empfehlungen, muss die langsame SQL-Abfrage umgebaut werden.
Beheben der Engstellen bei fehlenden Indexen
In „Geschätzte Verbesserung“ steht ein Wert größer 0%, im Bereich „Indexempfehlungen“ werden SQL-Befehle für neue Indexe angezeigt.
Anwenden der Empfehlungen im langsamen Produktivsystem:
- Im „Datenbankoptimierungsratgeber“ auf Menü „Aktionen“, Menüpunkt „Empfehlungen speichern“ klicken.
- Empfehlungen als neue SQL-Datei speichern.
- SQL-Datei auf Produktivserver übertragen und dort auf die Produktivdatenbank via SSMS ausführen.
Anwenden der Empfehlungen im Entwicklungssystem:
- Im „Datenbankoptimierungsratgeber“ auf Menü „Aktionen“, Menüpunkt „Empfehlungen anwenden“ klicken.
- „Jetzt anwenden“ auswählen.
- „OK“ anklicken.
Anschließend besitzt sowohl das Entwicklungssystem als auch das Produktivsystem die neuen Indexe.
Beheben der Engstellen bei umzubauenden SQL-Abfragen
In „Geschätzte Verbesserung“ steht ein Wert kleiner gleich 0%, bzw. im Bereich „Indexempfehlungen“ werden keine SQL-Befehle für neue Indexe angezeigt.
In so einem Fall ist die SQL-Abfrage zwar langsam (das wurde oben ja mit dem SQL Server Profiler ermittelt), kann aber durch reines Hinzufügen von Indexen nicht mehr verbessert werden.
In so einem Fall sollte die SQL-Abfrage umgebaut werden.
Wir haben gute Erfahrungen mit dem „De-Normalisieren“ von SQL-Abfragen gemacht.
Also durch das Vorberechnen von Spalten/Feldern, werden diese nicht mehr live beim Abfragen durch aufwändige JOINs, SUBSTRs & Co. in teuren SQL-Abfragen ermittelt.
Vielmehr wird ein Scheduler programmiert, der zusätzliche Hilfstabellen und/oder Hilfsfelder, täglich/stündlich automatisch füllt.
Die ehemals langsamen SQL-Abfragen werden dann umgeschrieben, dass sie auf die vorberechneten Felder/Tabellen arbeiten.
Das ist besonders dann attraktiv und wirkungsvoll, wenn:
- Viele gleichzeitige Nutzer.
- Daten sich nur selten ändern.
- Die SQL-Abfragen Lesezugriffe machen.
Auf die Hilfstabellen kann dann wiederum per Datenbankoptimierungsratgeber Indexe ermittelt und gesetzt werden lassen.
Wie kann ermittelt werden, was umgebaut werden muss?
- Eine langsame SQL-Abfrage im Entwicklungssystem im SSMS in neues Abfragefenster via Zwischenablage einfügen.
- Auf Menü „Abfrage“, Menüpunkt „Geschätzten Ausführungsplan anzeigen“ klicken
Es wird nun unterhalb der Abfrage eine Registerkarte „Ausführungsplan“ angezeigt. Dort werden jeweils die Kosten in Prozent angezeigt. Beim Bewegen der Maus über die Abfrage wird auch der relevante Teil der SQL-Abfrage angezeigt.
Das ist ein guter Indikator, welche Abfragen als langsam angesehen werden (z. B. SUBSTR, LIKE mit Wildcards, LEFT, RIGHT, ggf. JOINs, Unterabfragen, usw.).
Hier kann dann ein manuelles Umbauen/De-Normalisieren ansetzen. Nach dem Umbauen dann für die neue Abfrage erneut den Ausführungsplan analysieren.
Weitere Tipps zur Nutzung des Ausführungsplans:
- Manchmal wird ein gelbes Warndreieck
an einem Element im Ausführungsplan angezeigt. Beim Hovern darüber werden oft Tipps angezeigt. Beispielsweise implizite Konvertierungen, wenn ein Feld in einer Abfrage den falschen Typ hat. Es sollte jeweils versucht werden, diese Warnungen zu eliminieren, z. B. durch Umbau der Datenbanktabellen oder der Abfrage selbst.
Mehrfach messen
Der obige Ablauf hat skizziert:
- SQL Server Profiler auf dem Produktivsystem.
- SQL Server Tuning Advisor auf dem Entwicklungssystem.
- Indexe erstellen.
- SQL-Abfragen umbauen.
Diese Ablauf sollte jetzt mehrfach wiederholt werden:
- Für alle langsamen SQL-Abfragen.
- Nach dem Umbau, um dann andere, bisher im Hintergrund liegende Engstellen, hervorzubringen.
Zusammenfassung
Das hier skizzierte Vorgehen hilft nicht, wenn ein Server „physisch“/„physikalisch“ aufgrund der zu großen Menge von Anfragen schlicht an seine Grenzen kommt.
Hier muss mit Load-Balancing und Lastverteilung die Last auf mehrere Webserver und Datenbankserver verteilt werden.
Wobei dieses hier beschriebene Vorgehen jedoch sehr gut helfen kann, ist, Engstellen aufgrund ineffektiver SQL-Abfragen zu identifizieren und zu beheben.
Oft treten diese Engstellen erst unter höherer Last durch (zu) viele gleichzeitige Website-Besucher auf. Deshalb empfiehlt es sich, auch auf Produktivsystemen, die unter merklicher Last stehen, das hier beschriebene Vorgehen durchzuführen.