TroubleShooting von Performance-Problemen im SQL Server

Einführung in das Problem

Gerade in dieser Woche hatte ich wieder mit mehreren Kunden zu tun, die Probleme mit Ihren Applikation haben, dass sich die nutzenden Fachabteilungen über die Performance innerhalb der Applikation beschweren. Hier werden dann in der Regel erst einmal alle Optionen in der Applikation mit dem Hersteller, einem Support-Partner oder dem Entwickler besprochen und (glücklicherweise) das Problem erst einmal in der Applikation gesucht. Meist ergibt es sich aber dann, dass man irgendwie irgendwann auf die Leistung des SQL Servers zu sprechen kommt…

Meine Erfahrung mit Applikations-Support sind hier so vielfältig wie die einzelnen Applikationen, hier hat man manchmal mit echten Experten zu tun, aber manchmal leider auch mit Fachidoten, die zwar das Business gut verstehen oder ihren Code in und auswendig kennen, aber von Datenbanken nur rudimentär etwas verstehen. Dann hört man beispielsweise Aussagen wie „der Code ist schon seit Jahren unverändert und der läuft sehr performant“ und dann schaut man sich die Abfragen an und findet beispielsweise zahlreiche „SELECT * FROM TABLE WHERE…“ Statements, oder nur Tabellen mit einem Primary Key, aber 60 Spalten und mehr als 1.000.000 Zeilen. Ja, auch solche Abfragen können unter gewissen Bedingungen recht performant ausgeführt werden, aber in den meisten Fällen funktioniert das leider eben nicht.

Und wie in dieser Woche mehrfach gesehen, liegen die Probleme nicht immer unbedingt an inperformanten Abfragen oder sub-optimalen Datenbank-/Tabellenstrukturen, sondern schon in der Basis. Nämlich dann wenn der SQL Server nicht fachgerecht installiert und konfiguriert wurde, oder schon im Sizing oder der Planung Fehler gemacht wurden. Was ich damit meine, was ich bei diesen Problemfällen in dieser Woche festgestellt habe, und wie ich unter anderem bei der Analyse vorgehe, darum geht es in diesem Blogbeitrag.

Kundenbeispiel: Performance-Probleme und der Wait-Type „CXPacket“

Ich nehme hier ein Kundenbeispiel aus meinem Tagesgeschäft, um es zu verdeutlichen:
Der Kunde betreibt einen SQL Server 2019 Standard Edition als Standalone auf einer virtuellen Maschin installiert und in dieser SQL Server Instanz befinden sich zehn Datenbanken für die einzelnen Applikationen bzw Applikationsteile. Auf diese Datenbanken wird sowohl mit einer Legacy-Applikation, diversen Python-Skripten und einer selbstgeschriebenen „Reporting“-Lösung zugegriffen. Die Abfragen bzw die Performance der abfragenden Applikationen sind zeitkritisch, da es sich um einen Dienstleister aus dem Finanz-Sektor handelt. Die Applikationen liegen zur Hauptsache ebenfalls auf dem SQL-Server, ist zwar – für die Leistung des SQL Servers – nicht optimal aber derzeit nicht änderbar.

Die Beschwerden kommen von den Mitarbeitern, die eine beim Ausführen unterschiedlicher Funktionen bzw Prozessen (nicht immer) eine Verlängerung der Laufzeiten, also eine Verlangsamung des Programmes feststellen, so dass gewohnte Prozesse statt Sekunden plötzlich Minuten dauern. Der Kunde schreibt hierzu:

• User melden „System ist langsam“ 
• Unsere Interpretation bisher meist: DB Queries unserer Python Tools  und der Applikation selbst gegen die App-DB sind noch deutlich langsamer als üblich

Was wir oft an Last während der Performance-Probleme beobachten:

• Abfragen von Beständen, Vorgängen etc. in der Applikation durch unsere Tools
• Schreiben (u.a. auf dieselben Tabellen) durch Hintergrundprozesse der Applikation selbst. (werden durch Importe und User-Interaktionen getriggert)

Der Kunde selber konnte mit dem vorhandenen Wissen anhand der Symptome und den erlangten Erkenntnissen weder eine spezifische Abfrage, oder mehrere zusammenhängende Prozesse oder gar eine Root Cause finden. So kam die Frage nach Unterstützung zu mir und wurde um Unterstützung gebeten.

Vorgehensweise zur Ermittlung der Problematik

Um nun den Performance-Problemen auf die Spur zu kommen, habe ich mir natürlich erst einmal das gesamte System erklären lassen und dann selbst mit der Untersuchung der Basis angefangen, denn ohne stabile Installation und Konfiguration (Beispielsweise hier), macht auch eine Optimierung der einzelnen Abfragen oder der Datenbank-Struktur keinen Sinn.

Daher habe ich mit den Eckdaten wie CPU, RAM und Platten angefangen (auch hier gab es Punkte die nicht optimal waren und addressiert wurden), und bin dann übergegangen zur Überprüfung der SQL Server Instanz bzw deren Konfiguration. Bei einer virtuellen Maschine mit 128GB Arbeitsspeicher mit lokal laufender Applikation und Python-Skripten wurde der Wert für Max Memory auf 100GB (aka 102400MB) gesetzt, an der Zuordnung/Nutzung der CPUs und Kerne wurde nichts angepasst (was ich grundsätzlich für ausgesprochen richtig halte!)

Bei der weiteren Analyse musste ich dann feststellen, dass die Werte für MaxDoP und der Cost Threshold noch auf dem Default konfiguriert waren, was funktionieren kann, aber bei dem akuten Problemfall eher kritisch zu sehen ist und auf einen elementaren Baustein der Ursache hinweist. Aufgrund dieses Findings habe ich mir als nächstes die Übersicht der Wait-Types angeschaut, eine passende Abfrage dazu kann man sich von den einschlägigen Webseiten im Netz herauskopieren (beispielsweise von Paul Randall hier => SQLSkills)

Und wie nicht anders zu erwarten, war der höchste Wait Type… CXPACKET

Wie nun der Wait-Type CXPacket und die Konfiguration des „Maximaler Grad an Parallelität“ sowie des „Schwellwert für die Parallelität“ zusammenhängen, werde ich in den folgenden Abschnitten versuchen einfach und trotzdem einleuchtend zu erklären.

Die Bedeutung von Wait-Types im SQL Server habe ich in einem weiteren Blog-Beitrag hier begonnen zu erläutern.

Erkenntnis und Zusammenhang aus dem Kundenproblem

Ihr seht, dass bei nicht sorgfältiger Konfiguration (oder Default-Konfiguration) dieser beiden Parameter, Probleme mit der Performance des SQL Servers sowie der Applikation(en) im Grunde vorprogrammiert sind.

Die Lösung – Optimierung von MaxDoP und CTfP

Ich habe also dem Kunden dringend empfohlen, seine Konfiguration des SQL Servers zeitnah anzupassen und erst einmal mit den gängigen Vorschlägen der Community und des Herstellers zu beginnen. Desweiteren wird der Kunde gemeinsam mit mir