Verständnis von Wait-Types: Ein Schlüssel zur SQL Server-Performance

Was sind Wait-Types?

Definition und Konzept von Wait-Types

Wie der Name es bereits vermuten lässt, wartet der SQL Server auf etwas und protokolliert den Grund des Wartens in den entsprechenden Countern für jeden einzelnen Warte-Typen. Warten ist beim SQL Server ein allgegenwärtiges Konzept, denn es bezieht sich auf den Zustand, in dem eine Abfragen oder Anforderungen auf Ressourcen wartet, um fortzufahren. Die Kenntnis und das Verständnis von Warte-Typen sind daher von entscheidener Bedeutung für das TroubleShooting von Performance-Problemen. Daher ist die Analyse von „Wait-Types“ auch ein entscheidender Punkt in der Leistungsoptimierung im SQL Server.

Jeder Warte-Typ hat seine Bedeutung und kann so auf das bestehende Problem bzw die jeweilige Ressourcen-Anforderung hinweisen. So können solche Warteereignisse auf „Probleme“ mit den einzelnen Ressourcen wie CPU, Arbeitsspeicher, Festplatte oder andere Systemanforderungen hindeuten. So kann man zum Beispiel über den Warte-Typen „PAGEIOLATCH“ auf Enpässe oder gar Blockaden im Storage-Bereich schliessen.

Warum sind Wait-Types wichtig für die Leistungsoptimierung?

Wie bereits oben geschrieben, kann man mittels Warte-Typen ermitteln wo unter Umständen Probleme entstehen oder Engpässe bereits vorhanden sind. Es macht also Sinn, diese unterschiedlichen Wartetypen zu monitoren oder zumindest in regelmäßigen Abständen zu überprüfen, ob es hier Veränderungen oder Auffälligkeiten gibt. Natürlich gibt es auch Wartetypen, die normal im laufenden Betrieb eines SQL Servers sind und daher eher vernachlässigt werden können.

Schaut man sich das Skript von Paul Randall an, dann wird man sehen, dass er alle unwichtigen Wait-Types als EXCLUDES aufgelistet hat:

WHERE [wait_type] NOT IN (
— These wait types are almost 100% never a problem and so they are
— filtered out to avoid them skewing the results. Click on the URL
— for more information.

Alle anderen Wait-Types ermöglichen es Administratoren, Engpässe oder ineffiziente Abfragen zu identifizieren. Denn die Art des Wait-Types kann von Hardware, Konfiguration und Anwendungsdesign beeinflusst werden. Eine falsche Konfiguration oder Unterdimensionierung der Hardware kann so zu häufigen oder hohen Wartezeiten führen. Je nach Typ und Erkenntnissen können verschiedene Maßnahmen zur Optimierung ergriffen werden. Dies kann die Anpassung von SQL Server-Einstellungen, Indexerstellung, Neuschreiben von Abfragen oder die Aktualisierung der Hardware umfassen.

Im Folgenden möchte auf einen bestimmten Warte-Typen eingehen, der mir immer wieder und leider viel zu häufig begegnet, da er meist auf Probleme hindeutet.

Der Wait-Type „CXPacket“ im Detail

Erklärung von „CXPacket“ und seiner Bedeutung

Aus dem Blog-Beitrag von Paul Randall – Knee-Jerk Wait Statistics : CXPACKET – SQLPerformance.com – kann man entnehmen, dass der Wait-Type „CXPacket“ wie folgt entsteht:

„CXPacket“ ist ein Wait-Type, der auftritt, wenn eine Abfrage parallel verarbeitet wird und es zu einer Koordination zwischen den parallelen Threads kommt. Dieser Wait-Type ist normalerweise unvermeidlich, wenn parallelisierte Abfragen ausgeführt werden.

Im Detail steht das CX für „Content Exchange“ und deutet somit auf den Austausch von Daten-Paketen zwischen unterschiedlichen Prozessen hin. In diesem Fall zwischen dem Hauptprozess und den Child-Prozessen, die die Abarbeitung der Abfrage in den einzelnen Kernen übernommen haben, daher auch der Zusammenhang mit der Parallelisierung.

Ein gewisser Pozentsatz an Wartezeit auf eben diese Kommunikation zwischen den Prozessen bei der Parallelisierung ist unausweichlich und deutet nicht auf ein Problem hin, sondern ist erwartet. Steigt dieser Wert im Verhältnis oder ist wie im folgenden Screenshot die Nummer 1 und hat einen höheren Prozentwert, dann könnte sich hier ein Problem verstecken.

Wann und warum tritt „CXPacket“ auf?

Parallelisierte Abfragen: „CXPacket“ tritt auf, wenn Abfragen oder andere Datenverarbeitungs-Operationen parallel auf mehreren Prozessoren oder Kernen ausgeführt werden. In solchen Fällen ist ein Austausch von Informationen zwischen den parallel laufenden Threads erforderlich, um die Daten zu kombinieren oder zu synchronisieren.

Hohe Parallelität: Dieser Wait-Type kann bei Abfragen auftreten, die eine hohe Parallelität erfordern. Wenn viele parallele Threads beteiligt sind, ist der „CXPacket“-Wait ein Zeichen dafür, dass die Kommunikation und Koordination zwischen den Threads stattfindet.

Hohes Datenvolumen: Wenn Abfragen große Datenmengen verarbeiten und diese parallel aufgeteilt werden, ist „CXPacket“ häufiger anzutreffen.

Der Wait-Type „CXPacket“ ist im Allgemeinen nicht problematisch und zeigt an, dass der SQL Server seine Ressourcen effizient für die parallele Verarbeitung nutzt. Es ist jedoch wichtig, sicherzustellen, dass die Parallelverarbeitung gut konfiguriert ist und dass die Wartezeiten nicht übermäßig sind. Eine Überdimensionierung von Parallelität kann zu Leistungsproblemen führen, und die richtige Balance zwischen Parallelität und Ressourcennutzung ist entscheidend.

Daher sollte man sich an die Empfehlungen des Herstellers bzw der Community halten, zumindest als Initialwert, um dann über die Zeit seine eigenen Erfahrungen mit der eigenen Workload zu machen und die optimalen Werte für seinen Workload selber zu ermitteln.

Optimierung von „CXPacket“ und anderen Wait-Types

Die Bedeutung der richtigen Konfiguration und Überwachung

• Max Degree of Parallelism (MaxDoP) optimieren: Stellen Sie sicher, dass der MaxDoP-Wert sorgfältig konfiguriert ist. Ein zu hoher Wert kann zu übermäßiger Parallelität und einem Anstieg von „CXPacket“-Wartenzeiten führen. Eine geeignete Einstellung von MaxDoP kann die Anzahl der parallelisierten Threads begrenzen und die Synchronisation zwischen ihnen verbessern. Hierzu empfiehlt beispielsweise die Microsoft Dokumentation folgendes:

• Cost Threshold for Parallelism (CTfP) anpassen: Der CTfP legt die Schwelle fest, ab der eine Abfrage parallelisiert wird. Durch die Anpassung dieser Schwelle können Sie steuern, welche Abfragen parallel ausgeführt werden. Eine angemessene CTfP-Einstellung kann verhindern, dass einfache Abfragen unnötig parallelisiert werden.
  
  Hierzu gibt es keine klaren Empfehlungen (mehr), es ist im Grunde alles von der eigenen Workload abhängig, was bedeutet man muss seine eigene Workload analysieren und daran orientieren. Brent Ozar schreibt als Beispiel für OLTP Systeme könnte man mit einem Wert zwischen 30 und 40 starten, dann kommt aber ein Reportsystem bei dem die Kosten für die Ausführung der einzelnen Queries immer oberhalb von 200 liegen… also ich orientiere mich an den Empfehlungen von Brent Ozar für OLTP Systeme, da die meisten meiner Kunden eher Legacy-Applikationen betreiben und keine Highend-HighPerformance-DataWarehouse-SQL Server betreiben.
  
  Ich nutze den Wert 40 als Initalwert mit dem Hinweis, dass man hier ggfs nachbessern müsste, wenn die Workload stabil ist und der SQL Server nicht optimal läuft.

Bewährte Praktiken zur Behandlung von „CXPacket“ und anderen Warteereignissen

Wie aus den oberen Abschnitten erlesbar ist, so kann man mit gewissen Konfigurationen einen Einfluss auf die Häufigkeit und/oder Dauer von Wartevorgängen – insbesondere CXPacket – nehmen.

Zum einen sollte man natürlich eine gewisse solide Basis für den SQL Server geschaffen haben, die Infrastruktur, das Betriebssystem, die Ressourcen und die Grund-Installation sollten gemäß den Hersteller-Empfehlungen und der Community entsprechen. Ausserdem sollte man ein gewisses House-Keeping auf dem SQL Server etabliert haben, damit es nicht zu anderen Problemen mit der Performance kommt, die nicht an Wartezeiten hängen, wie zum Beispiel eine regelmäßige Index-Pflege.

Desweiteren sollte man zur Reduzierung von CXPacket, sich auch die allgemeine Index-Struktur seiner Datenbank(en) anschauen und ggfs nachbessern. Beispielsweise kann man über verschiedene DMV aus dem SQL Server herausbekommen, bei welcher Abfrage es eine Warnung zu einem fehlenden Index im Ausführungsplan gibt. Oder man findet im Internet, mit der Suchmaschine seines Vertrauens, auch Abfragen, die einem direkt eine Liste mit fehlenden Indexen und deren CREATE-Statement liefern. Also mit einer Optimierung der Index-Struktur kann man ebenfalls für eine Reduzierung der CXPacket-Wartezeiten sorgen.

nicht nur Indexe, sondern auch Abfragen optimieren

Aporopos Optimierung… natürlich sollte man auch die eigentlich Abfragen versuchen soweit wie möglich zu optimieren, umstellen, umschreiben, parametrisieren, Applikationslogik aus den Abfragen entfernen und in die Applikation selber verlagern (wo sie hingehört).

Als letzten Schritt kann es natürlich auch einfach helfen, das Problem mit der Performance einfach mit Hardware zu erschlagen… 😉
Natürlich kann es sinnvoll sein, sich auch mal Gedanken über die Ressourenausstattung des SQL Servers Gedanken zu machen, ob sich unter Umständen über den Lauf der Zeit etwas an der Datenbank-Größe verändert hat, mehr User im Unternehmen arbeiten und nun zeitgleich die Applikation benutzen usw.

Gerade in der letzten Woche gesehen… SQL Server auf einer VM mit 4 Instanzen, aber jede Instanz nur 8GB bei Gesamt 40GB RAM… aber Datenbanken mit fast 1 TB an vollen Datendateien, dass es hier zu Problemen kommt, war absehbar… (PLE, Memory Pressure und CXPacket waren hier Stichworte)

Weitere Ressourcen und Vertiefung

Als Buch kann ich – aus eigener Erfahrung – empfehlen, das Buch von Enrico van de Laar zu kaufen: „Pro SQL Server 2019 Wait Statistics“

Empfohlene Lektüre und Ressourcen zur Vertiefung des Themas

1. Offizielle Microsoft-Dokumentation:

• Die offizielle Microsoft-Dokumentation bietet detaillierte Informationen zu verschiedenen SQL Server-Themen, einschließlich Wait-Types und Leistungsoptimierung. Du kannst die Dokumentation unter https://docs.microsoft.com finden und dort nach spezifischen Themen suchen.

2. SQLSkills-Blog:

• Der SQLSkills-Blog, den du bereits genutzt hast, ist eine ausgezeichnete Quelle für tiefgehende Informationen und Ratschläge von renommierten SQL Server-Experten wie Paul Randal und Kimberly Tripp. Du findest ihn unter https://www.sqlskills.com/blogs.

3. Brent Ozar Unlimited:

• Die Website „Brent Ozar Unlimited“ enthält eine Vielzahl von Ressourcen zur SQL Server-Leistungsoptimierung und bietet regelmäßig aktualisierte Blogbeiträge und Whitepapers. Besuche sie unter https://www.brentozar.com.

4. SQLPerformance.com:

• Diese Website enthält informative Artikel und Blogposts zu verschiedenen SQL Server-Themen, einschließlich Wait-Types und Leistungstuning. Sie wird von SQL Server-Experten betrieben und ist unter https://www.sqlperformance.com erreichbar.

5. MSSQLTips:

• MSSQLTips ist eine Community-Website mit einer Fülle von SQL Server-Beiträgen und Tipps zur Leistungsoptimierung. Du findest sie unter https://www.mssqltips.com.

6. Books Online (BOL):

• Die SQL Server-Bücher online sind ein umfassendes Nachschlagewerk für alle Aspekte des SQL Servers. Die BOL enthalten Informationen zu Konfigurationsoptionen, Leistungsoptimierung und Wait-Types. Du findest sie unter https://docs.microsoft.com/en-us/sql/sql-server/sql-server-ver15-books-online.

7. Stack Exchange (DBA Stack Exchange):

• Das DBA Stack Exchange ist eine Community-basierte Frage-und-Antwort-Plattform, auf der viele erfahrene Datenbankadministratoren Fragen zur SQL Server-Leistung und zu Wait-Types beantworten. Du findest sie unter https://dba.stackexchange.com.