dtcSoftware - powerShellDba

📌 Was ist ein Deadlock?

GRUNDLAGEN

Ein Deadlock (deutsch: "Verklemmung") tritt in SQL Server auf, wenn zwei oder mehr Transaktionen sich gegenseitig blockieren, weil jede eine Ressource (z. B. eine Zeile, eine Seite oder eine Tabelle) gesperrt hat, die die jeweils andere benötigt. Das Ergebnis ist eine zyklische Abhängigkeit, aus der es keinen automatischen Ausweg gibt – die Transaktionen warten endlos aufeinander.

Beispiel:

Transaktion A hält eine Sperre auf Zeile 1 und möchte Zeile 2 sperren.
Transaktion B hält eine Sperre auf Zeile 2 und möchte Zeile 1 sperren.
Beide warten nun auf die Freigabe der jeweils anderen Ressource – ein Deadlock.

💡 Wichtig: SQL Server löst Deadlocks automatisch auf, indem es eine der Transaktionen als "Opfer" auswählt und mit einem Fehler (Error 1205) beendet. Die andere Transaktion kann dann erfolgreich abschließen. Das Opfer sollte von der Anwendung wiederholt (RETRY) werden.

🔍 Häufige Ursachen von Deadlocks

URSACHEN

Deadlocks werden oft durch schlecht optimierte Abfragen, falsche Indexstrategien oder ineffiziente Transaktionsdesigns verursacht. Typische Auslöser sind:

Zugriff auf Tabellen in unterschiedlicher Reihenfolge: Transaktion A greift auf Tabelle X und dann Y zu, Transaktion B auf Y und dann X.
Zu lange Transaktionen: Je länger eine Transaktion läuft, desto höher die Wahrscheinlichkeit von Konflikten.
Fehlende Indizes: Führen zu Table Scans, die mehr Sperren auf höheren Ebenen (z. B. Tabellensperren) verursachen.
Hohe Parallelität (MAXDOP): Parallele Abfragen können Deadlocks begünstigen, da mehrere Threads auf die gleichen Ressourcen zugreifen.
Lock-Eskalation: Wenn eine Transaktion zu viele Zeilen sperrt, kann SQL Server die Sperre auf Seiten- oder Tabellenebene erhöhen – das erhöht das Deadlock‑Risiko.

🔎 Deadlocks erkennen – Die besten Methoden

MONITORING

Um Deadlocks zu analysieren, müssen Sie sie zuerst aufspüren. SQL Server bietet mehrere Möglichkeiten:

1. System Health Extended Events Session

Seit SQL Server 2008 R2 ist die system_health Session standardmäßig aktiv. Sie zeichnet unter anderem Deadlock-Ereignisse (xml_deadlock_report) im Ring Buffer auf. Ein großer Vorteil: Kein zusätzliches Setup – die Session läuft immer im Hintergrund, ohne merklichen Performance-Overhead.

-- Deadlocks aus der System Health Session auslesen
SELECT
  xed.value('@timestamp', 'datetime') AS Creation_Date,
  xed.query('.') AS DeadlockGraph
FROM (
  SELECT CAST([target_data] AS XML) AS Target_Data
  FROM sys.dm_xe_session_targets AS xt
  INNER JOIN sys.dm_xe_sessions AS xs
    ON xs.address = xt.event_session_address
  WHERE xs.name = N'system_health'
    AND xt.target_name = N'ring_buffer'
) AS XML_Data
CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xed)
ORDER BY Creation_Date DESC;

2. Trace Flags 1204 / 1222 (klassisch)

Die Trace Flags 1204 und 1222 schreiben Deadlock-Informationen in das SQL Server Errorlog. 1204 liefert menschenlesbare, knotenbasierte Ausgaben, 1222 dagegen ein XML-Format (ähnlich dem Deadlock-Graphen). Nachteil: Sie müssen aktiviert werden und Deadlocks werden nur nachträglich protokolliert – nicht proaktiv.

-- Trace Flag 1222 aktivieren
DBCC TRACEON (1222, -1);
DBCC TRACESTATUS; -- Prüfen, ob Flags aktiv sind

⚠️ Achtung: Trace Flags 1204/1222 belasten die Performance und sollten nur temporär zu Analysezwecken aktiviert werden. Für den Dauerbetrieb sind Extended Events oder der DeadlockCollector die bessere Wahl.

📊 Deadlock-Graph analysieren – das Herzstück

ANALYSE

Das von Extended Events oder Trace Flag 1222 gelieferte XML enthält alle Informationen über den Deadlock: beteiligte Prozesse, gesperrte Ressourcen, Sperrmodi, Ausführungskontext und SQL-Text. Dieses XML können Sie in SQL Server Management Studio (SSMS) grafisch darstellen lassen.

So visualisieren Sie den Deadlock-Graph in SSMS:

Kopieren Sie das <deadlock>-XML in eine neue Abfrage.
Klicken Sie mit der rechten Maustaste auf das XML und wählen Sie „Datei speichern unter…“ mit der Endung .xdl.
Öffnen Sie die gespeicherte .xdl-Datei in SSMS – der Deadlock wird als grafisches Wartediagramm dargestellt.

-- Beispiel: XML für einen Deadlock-Graphen (Auszug)
<deadlock>
  <victim-list>
    <victimProcess id="process123" />
  </victim-list>
  <process-list>
    <process id="process123" ... />
    <process id="process456" ... />
  </process-list>
  <resource-list>
    <keylock ... />
  </resource-list>
</deadlock>

💡 Profi-Tipp: Der Deadlock-Graph zeigt die zyklische Abhängigkeit als Pfeile (request‑ und owner‑Kanten). Besonders wichtig: Die <victim-list> verrät, welche Transaktion SQL Server geopfert hat – anhand ihres SPID können Sie dann den dazugehörigen SQL-Text und Query‑Plan nachvollziehen.

🛠️ Deadlocks beheben – praktische Ansätze

LÖSUNGEN

Nachdem Sie den Deadlock-Graphen analysiert haben, können Sie gezielt eingreifen:

Transaktionen kürzen: Fassen Sie mehrere Operationen in einer Transaktion zusammen? Reduzieren Sie die Anzahl der beteiligten Schritte und halten Sie Transaktionen so kurz wie möglich.
Zugriffsreihenfolge standardisieren: Sorgen Sie dafür, dass alle Transaktionen auf Tabellen in der gleichen Reihenfolge zugreifen (z. B. immer zuerst Tabelle A, dann B).
Indizes optimieren: Setzen Sie nicht‑clustered Indizes, um Table Scans zu vermeiden – niedrigere Sperrgranularität reduziert das Deadlock‑Risiko.
Sperrisolation anpassen: Erwägen Sie für lesende Abfragen READ UNCOMMITTED oder READ COMMITTED SNAPSHOT (letzteres setzt Row‑Versionierung ein).
Deadlock‑Priorität setzen: Über SET DEADLOCK_PRIORITY können Sie steuern, welche Transaktion im Konfliktfall geopfert wird (nützlich für Berichtsabfragen).
Parallelität drosseln: Reduzieren Sie den MAXDOP-Wert für Abfragen, die häufige Deadlocks verursachen.

-- Deadlock-Priorität niedrig setzen (diese Transaktion wird eher geopfert)
SET DEADLOCK_PRIORITY LOW;

-- Query‑Hint: Parallelität auf 2 Threads beschränken
SELECT ... OPTION (MAXDOP 2);

📦 DeadlockCollector – der Dauerbeobachter

TOOL 1

Der DeadlockCollector ist ein Skript‑Paket, das die System Health Extended Event Session ausliest und alle Deadlocks strukturiert in einer eigenen Datenbank ablegt – samt SQL-Text, Query‑Plänen, beteiligten Prozessen und Sperrinformationen.

✅ Kein Setup: Nutzt die immer aktive System Health Session – kein zusätzlicher XEvent‑Overhead.
✅ Langzeithistorie: Der Ring Buffer würde sich überschreiben – der Collector speichert Deadlocks dauerhaft für Trendanalysen über Wochen und Monate.
✅ Duplikatschutz: SHA‑Hash aus Zeitstempel und Graph‑Inhalt identifiziert Deadlocks eindeutig.
✅ AG‑aware: Auf einem Always On Secondary (READ_ONLY) wird der Job automatisch übersprungen.
✅ Zusätzliche Importfunktion: InsertDeadLock kann auch eine gespeicherte .xel-Datei nachträglich verarbeiten.

-- Installation (als sysadmin ausführen)
-- 1. SetUpDeadlockCollectorDb.sql ausführen – erstellt DB DeadlockCollector, Tabellen, Views, Prozeduren
-- 2. SetUpDeadlockCollectorJob.sql ausführen – erstellt den stündlichen SQL Agent Job
-- 3. Job manuell starten, um vorhandene Deadlocks sofort zu erfassen
EXEC msdb.dbo.sp_start_job N'DeadlockCollector';

-- Ergebnis prüfen
SELECT TOP 20 Event_DateTime, DatabaseName_1, SQLText_1, LockMode_1, AffectedProcesses
FROM DeadlockCollector.dbo.vwvDeadlockInfos
ORDER BY Event_DateTime DESC;

📌 Installationshinweis: Beide Skripte müssen als sysadmin ausgeführt werden. Der stündliche Job sammelt neue Deadlocks aus dem Ring Buffer und schreibt sie persistiert in die Zieltabelle. Die Aggregations‑Views erleichtern die Auswertung nach Zeit, Datenbanken und SQL‑Texten.

⚡ Get-sqmDeadlockReport – PowerShell meets Deadlock‑Analyse

TOOL 2

Das sqmSQLTool (PowerShell Modul) enthält die Funktion Get-sqmDeadlockReport, die ebenfalls Deadlocks aus der System Health Session ausliest, aber direkt ein PowerShell‑Objekt zurückgibt. Das ist ideal für Ad‑hoc‑Analysen oder die Integration in automatisierte Monitoring‑Workflows.

Beispielhafte Nutzung:

# Modul installieren (falls nicht vorhanden)
Install-Module -Name sqmSQLTool -Force

# Deadlock‑Report für die aktuelle Instanz abrufen
Get-sqmDeadlockReport -SqlInstance "localhost" | 
  Select-Object Event_DateTime, Victim_SPID, SQLText_1, SQLText_2 |
  Format-Table -AutoSize

# Report als CSV exportieren
Get-sqmDeadlockReport -SqlInstance "SQL01" | 
  Export-Csv -Path "C:\Temp\Deadlocks.csv" -NoTypeInformation

# Deadlocks der letzten 24 Stunden filtern
$deadlocks = Get-sqmDeadlockReport -SqlInstance "SQL01"
$deadlocks | Where-Object { $_.Event_DateTime -ge (Get-Date).AddDays(-1) }

💡 Hinweis: Das sqmSQLTool enthält über 70 Funktionen für AlwaysOn, Backup & Restore, Sicherheit, Diagnose und Performance. Die Deadlock‑Funktion nutzt intern die gleiche XEvent‑Quelle wie der DeadlockCollector, ist aber speziell für die schnelle Kommandozeilen‑Analyse optimiert. Alle Details zum Tool finden Sie auf der Projektseite: https://www.powershelldba.de/.

🛡️ Deadlocks vorbeugen – Best Practices

PRÄVENTION

Transaktionen so kurz wie möglich: Weniger Schritte → geringere Wahrscheinlichkeit von Konflikten.
Einheitliche Zugriffsreihenfolge: Definieren Sie für alle Anwendungen eine verbindliche Reihenfolge, in der Tabellen angesprochen werden.
Indizes für häufige Suchargumente: Vermeiden Sie Table Scans (mit niedrigen Sperrgranularitäten).
READ COMMITTED SNAPSHOT nutzen: Lesende Abfragen blockieren dann keine schreibenden Transaktionen – reduziert Deadlocks drastisch.
Verbindungspooling und Retry‑Logik in der App: Nach einem Deadlock (Error 1205) sollte die App die Transaktion automatisch wiederholen.
SQL Server Agent Job für DeadlockCollector: Lassen Sie den Collector stündlich laufen – so haben Sie immer einen historischen Überblick über auftretende Deadlocks.
LAST_DEADLOCK_PRIORITY setzen: Wenn Sie bestimmte Abfragen (z. B. Berichte) bevorzugt als Opfer markieren möchten.

⚠️ Fehlersuche – wenn nichts mehr hilft

TROUBLESHOOTING

Deadlocks nur mit bestimmten Abfragen: Prüfen Sie die Ausführungspläne – oft fehlt ein Index oder eine veraltete Statistik führt zu einer schlechten Join‑Strategie.
Keine Deadlocks im DeadlockCollector, aber sporadische Timeouts: Verwenden Sie die System Health Session direkt mit der oben genannten XQuery – vielleicht erzeugt der Collector nicht alle Deadlocks (prüfen Sie den Jobstatus).
Deadlock wird immer durch den gleichen Prozess ausgelöst: Setzen Sie für diesen Prozess die Deadlock-Priorität auf LOW, um andere nicht zu gefährden.
Deadlocks mit TABLOCK‑Hinweisen: Erwägen Sie, die Sperrgranularität auf Zeilenebene zu reduzieren.

🚨 Notfall‑Tipp: Wenn Sie unter Produktionslast einen Deadlock vermuten, starten Sie die Deadlock-Extended‑Events‑Session mit Dateiziel (asynchronous_file_target). Das XML wird in eine Datei geschrieben – belastet die Instanz weniger als ein SQL Profiler Trace.

📚 Nützliche Links & Tools

RESSOURCEN

⚙️ DeadlockCollector: https://www.powershelldba.de/deadlockcollector – Skript‑basierte Langzeit‑Historie von Deadlocks in einer eigenen Datenbank.
🧰 sqmSQLTool: https://www.powershelldba.de/ – PowerShell‑Modul mit der Funktion Get-sqmDeadlockReport für Ad‑hoc‑Deadlock‑Analysen.
📖 Offizielle Deadlock‑Dokumentation: SQL Server Deadlocks Guide – Microsofts umfassendes Referenzdokument.