CXPACKET Waits bei MSSQL-Parallelverarbeitung

Einleitung

In vielen der von unseren Kunden beauftragten SQL Server Assessments hat sich gezeigt, dass ein bestimmtes Ereignis oft an der Spitze aller Wait-Events steht, nämlich das CXPACKET Wait-Event.

Was geschieht da?
Wie können diese Waits reduziert werden?
Sollte man versuchen, diese Waits zu reduzieren?

In diesem Artikel wird auf dieses Event genauer eingegangen und dargestellt, wie man es analysieren und ggf. beeinflussen kann.

Was sind CXPACKET-Waits?

Bei diesen Events handelt es sich um Waits die im Zusammenhang mit Parallelverarbeitung auftreten. Parallelverarbeitung bedeutet in der Datenbank immer, dass eine Abfrage einem Koordinator-Prozess übergeben wird. Dieser bricht die auszuführende Arbeit in Teilaufgaben auf und beauftragt damit sogenannte Worker-Prozesse. Der Koordinator-Prozess erhält zum Abschluss die Teilergebnisse der Worker und fasst diese zum Gesamtergebnis zusammen.

Was sagt Brent Ozar zu diesem Thema?

Brent Ozar, der bekannte Tuning-Spezialist, empfiehlt bei einem hohen CXPACKET-Wait Wert zur Reduzierung der CXPACKET-Waits den Parameter “Cost Threshold for Parallelism” der MSSQL Instanz von seinem Default-Wert 5 auf 50 zu setzen. Dies ist sicherlich eine sehr pauschale Aussage und man sollte sich, wie es immer der Fall ist, seine individuelle Situation in der zu untersuchenden Datenbank genau ansehen.

Ansehen? Wie macht man das?

Wesentliches Instrument, um die Wait-Events zu analysieren ist die dynamische Management View (DMV) sys.dmos_wait_stats. Dynamisch heißt in diesem Zusammenhang übrigens, dass man über diese View direkt auf die im Hauptspeicher vorhandenen Daten zugreift. Zu analysieren sind aber niemals allein die aufgetretenen Wait-Events, sondern auch immer verdächtige Statements, also die typischerweise in einer Datenbank ausgeführten Statements und deren Realzeit, von daher empfiehlt es sich schon aus diesem Grund, eine separate Testumgebung zu verwenden, die bezüglich ihrer Leistungsdaten der Produktionsdatenbank gleicht. Bei der Auswahl der Testkandidaten sind deren Ausführungspläne zu betrachten und hier insbesondere, ob mit der aktuellen Datenbankeinstellung eine serielle oder eine parallele Verarbeitung dieser Abfrage erfolgt. Näheres hierzu weiter unten.

Die Testumgebung: StackOverflow2013

Zur Durchführung der Tests wurde die StackOverflow2013 Datenbank auf einem Laptop mit 32 GB Hauptspeicher und den folgenden Prozessoreigenschaften verwendet:

PS C:\> $processor=Get-ComputerInfo -Property CsProcessors
PS C:\> $processor.CsProcessors


Name                      : 11th Gen Intel(R) Core(TM) i7-1195G7 @ 2.90GHz
Manufacturer              : GenuineIntel
Description               : Intel64 Family 6 Model 140 Stepping 2
Architecture              : x64
AddressWidth              : 64
DataWidth                 : 64
MaxClockSpeed             : 2918
CurrentClockSpeed         : 2918
NumberOfCores             : 4
NumberOfLogicalProcessors : 8
ProcessorID               : BFEBFBFF000806C2
SocketDesignation         : CPU 1
ProcessorType             : CentralProcessor
Role                      : CPU
Status                    : OK
CpuStatus                 : Enabled
Availability              : RunningOrFullPower

Ein komprimiertes Backup der StackOverflow2013 Datenbank (komprimiert 10 GB, als DB ca. 50 GB) kann man im Internet herunterladen. Brent Ozar bietet diese beispielsweise unter How to Download the Stack Overflow Database - Brent Ozar Unlimited® an. In dem hier durchgeführten Test werden lediglich die beiden Tabellen dbo.Comments (24.534.730 Zeilen) und dbo.Posts (17.142.169 Zeilen) mit der folgenden Abfrage verwendet:

-- Hier wird eine top-Klausel verwendet,
-- um gezielt eine bestimmte Anzahl von Datensätzen zu ermitteln
-- und damit die Möglichkeit zu haben, über diese Anzahl die 
-- Laufzeit zu beeinflussen

with X as (
select p.Body,c.Text
from comments c, Posts p
where c.PostId = p.id
and c.PostId % 4 = 0
and p.id % 5 = 0
) 
select  top(1000000) * from X

Ausführungspläne für das Test-Statement

Wird das Statement seriell ausgeführt, so wird hierfür der folgende Ausführungsplan verwendet:

Geschätzter Ausführungsplan bei serieller Verarbeitung

Tatsächlicher Ausführungsplan bei serieller Verarbeitung

Bei Parallelverarbeitung ergibt sich der folgende Plan, die schwarzen Pfeile auf gelbem Hintergrund symbolisieren hier die Schritte mit Parallelverarbeitung:

Geschätzter Ausführungsplan bei Parallelverarbeitung

Tatsächlicher Ausführungsplan bei Parallelverarbeitung

Die geschätzten Kosten für die parallele Verarbeitung lagen bei 2171, für serielle Verarbeitung bei 3119 und dieser Wert ist der Schwellwert der als Umschaltpunkt zwischen paralleler und serieller verwendet werden muss: Bis 3119 einschließlich erfolgt in diesem Fall die Verarbeitung seriell, darüber parallel.

Abfrage der OS Wait Statistiken

Es gibt eine sehr große Zahl (ca. 1200) verschiedener OS Wait-Statistiken. Im Rahmen dieses Dokumentes werden jedoch nur diejenigen betrachtet, die mit der parallelen Verarbeitung von Datenbankzugriffen in Verbindung stehen. Diese haben als Namenspräfix die beiden Buchstaben CX. Es handelt sich hier um die folgenden Werte (aus einer "SQL Server Wait Types Library"):

Zur Abfrage der Wait-Statistiken wird das folgende Statement verwendet:

select * from sys.dm_os_wait_stats where wait_type like 'CX%'

Bereinigungen vor jedem Testlauf

Vor jedem neuen Testlauf wurden drei Bereinigungs-Aktionen durchgeführt (auch hier der Hinweis, dies nur in einer nicht-produktiven Umgebung zu machen):

Zurücksetzen der Betriebssystem-Wait-Statistiken
Löschen des Cache
Löschen des Buffer-Cache

Zum Zurücksetzen der Betriebssystem-Statistiken wurde das folgende Statement verwendet. Hierdurch werden insbesondere die in der o.a. Tabelle aufgeführten CX-Waits zurückgesetzt, so dass sie im Anschluss an den jeweiligen Test genau die Werte des jeweiligen Laufs wiedergeben.

dbcc sqlperf ('sys.dm_os_wait_stats', CLEAR)

Zum Löschen der beiden Caches bietet das verwendete und im folgenden Abschnitt beschriebene Tool zur Durchführung von Stresstests (SQLQueryStress von Adam Machanic) zwei entsprechende Buttons.

Performancemessungen

Zur Durchführung von Stresstests und Erfassung der zugehörigen Ausführungszeiten wurde die oben bereits erwähnte SQLQUeryStress GUI verwendet. Diese bietet die Möglichkeit, ein oder mehrere SQL Statements in einem Fensterbereich, so wie die Anzahl der Iterationen und die Anzahl paralleler Sessions zu spezifizieren.

Nach Abarbeitung der Threads und Iterationen erhält man als wesentliche Informationen

Durchschnittliche CPU Sekunden pro Iteration
Durchschnittliche Echtzeit-Sekunden pro Iteration
Gesamte Laufzeit aller Threads und Iterationen

Die folgende Abbildung zeigt die GUI mit der oben bereits vorgestellten Abfrage mit einem Joing über die Tabellen dbo.Comments und dbo.Posts, die fünf mal in 10 parallelen Threads ausgeführt wurde. Durch die Wahl einer Thread-Anzahl von mindestens 8 (hier 10) ist sichergestellt, dass alle CPU-Kerne ausgelastet werden.

Der erste und in der folgenden Graphik dargestellte Test wurde mit serieller Verarbeitung ausgeführt, das heißt, der Instanz-Parameter “Cost Threshold for Parallelism” stand auf einem Wert kleiner oder gleich 3119 (NB: Der Default-Wert ist 5, der empfohlene Wert ist 50 und der optimale Wert ist Ergebnis ausgiebiger Tests unter Verwendung der typischen Abfragen).

Stresstest-Ergebnisse für serielle Verarbeitung

select * from sys.dm_os_wait_stats where wait_type like 'CX%'

Erwartungsgemäß existieren hier keine CX-Waits, da hier serielle Verarbeitung erfolgt:

Mainzer Datenfabrik - CXPACKET Waits bei MSSQL-Parallelverarbeitung

Stresstest-Ergebnisse für parallele Verarbeitung

Auch hier die o.a. Abfrage zu den Waits und hier die entsprechenden durch Parallelverarbeitung aufgetretenen Werte. Ein schöner Vergleich der Signal-Waits (letzte Spalte der folgenden Tabelle) mit einer alltäglichen Situation findet sich bei Pinal Dave unter folgendem Link SQL SERVER - Signal Wait Time Introduction with Simple Example

Fazit

CXPACKET-Waits sind zunächst einmal lediglich ein Hinweise darauf, dass Parallelverarbeitung stattfindet. Parallelverarbeitung bedeutet grundsätzlich, dass ein gewisser Overhead zur Koordinierung der parallelen Prozesse erforderlich ist. Steht ausreichend CPU-Kapazität zur Verfügung, so kann man pauschal sagen, dass die Parallelverarbeitung zur Beschleunigung der Abfragen führt. Stehen die CPUS aber, wie in diesem Artikel gezeigt, unter permanenter hoher Auslastung, so kann die Performance durch Erzwingen serieller Verarbeitung über den Instanzparameter “Cost Threshold for Parallelism” erhöht werden. Die folgende Tabelle fasst die gerundeten Ergebnisse aus dem hier gezeigten Beispiel abschließend zusammen. Die gegenüber der Realzeit sehr hohe Zeit der CX_PACKET_WAITS sind hier als Summer der Waits aller 8 Kerne zu verstehen.

Dem aufmerksamen Leser wird aufgefallen sein, dass die 120:41 Minuten CX_PACKET_WAITS (ca. 15 Minuten) mehr Zeit bedeuten, als die Realzeit (14 Minuten). Diese Differenz können wir nur so deuten, dass bei der hohen Anzahl von Gesamt-Waits (19.584.608) sehr viele sehr kurze Waits aufgetreten sind, die sich durch Rundungsfehler im Sub-Millisekunden-Bereich aufaddiert haben.

Wenn Sie mehr zu diesem Thema erfahren möchten, stehen Ihnen unsere erfahren Expert:innen bei Rückfragen zur Verfügung. Kontaktieren Sie uns dafür gerne über unser Kontaktformular. Wir freuen uns von Ihnen zu hören!