SQL Server Architektur & Internals:
Was unter der Haube passiert — und warum das deine Performance betrifft
Warum ein DBA die Architektur verstehen muss
Es gibt zwei Arten von DBAs. Die erste Art tippt Befehle ein, die sie irgendwo gelesen hat, und hofft, dass es klappt. Die zweite Art versteht, was passiert, wenn sie Enter drückt — und kann deshalb Probleme lösen, die die erste Art nur mit einem Schulterzucken quittiert. Dieses Kapitel macht aus dir die zweite Art.
SQL Server ist keine Black Box. Es ist ein hochkomplexes System mit klaren Schichten, definierten Verantwortlichkeiten und vorhersehbarem Verhalten — wenn man weiß, wie es aufgebaut ist. Wer den Buffer Pool versteht, weiß warum die Page Life Expectancy plötzlich einbricht. Wer den Query Optimizer kennt, weiß warum ein Plan Cache voll mit Single-Use-Plans den Server ausbremst. Wer WAL begriffen hat, schläft nachts ruhiger.
Dieses Kapitel legt das Fundament für alle folgenden Kapitel in Teil III und IV. Du wirst hier Begriffe lernen, auf die wir in Kapitel 9 (Wait Statistics), Kapitel 11 (Memory Management), Kapitel 15 (Ausführungspläne) und Kapitel 18 (Parameter Sniffing) immer wieder zurückgreifen. Betrachte es als die technische Einweisung, bevor du das erste Mal das Cockpit betrittst.
|
Hinweis: Voraussetzungen |
|---|
|
Dieses Kapitel setzt voraus, dass du weißt, was eine Datenbank ist und grundlegende SQL-Operationen kennst. Wir erklären nicht, was ein SELECT ist — wir erklären, was SQL Server damit macht. |
|
Querverweise: Die NUMA-Architektur aus Kapitel 1 spielt hier eine zentrale Rolle — insbesondere beim SQLOS und der NUMA-Awareness des Buffer Pools. Virtualisierungsaspekte (Kapitel 3) werden in den jeweiligen Abschnitten explizit erwähnt. |
Die große Übersicht: Relational Engine vs. Storage Engine
SQL Server besteht im Kern aus drei großen Blöcken: der Relational Engine, der Storage Engine und dem SQLOS. Diese drei Teile kommunizieren miteinander, haben aber klar getrennte Zuständigkeiten — und genau diese Trennung erklärt viele Performance-Phänomene.
Abb. 4.1: SQL Server Architektur-Überblick: Relational Engine, Storage Engine, SQLOS und Buffer Pool
Die Relational Engine ist zuständig für alles, was mit dem Verarbeiten von Abfragen zu tun hat: Parsen, Optimieren, Planen, Ausführen. Sie nimmt SQL-Text entgegen und gibt Ergebnismengen zurück. Die Storage Engine dagegen ist für Datenspeicherung und -zugriff verantwortlich: B-Tree-Navigation, Locking, Transaktionen, Write-Ahead Logging.
Zwischen beiden steht der Buffer Pool — der gemeinsame Speicher, in dem alle 8-KB-Datenpages cached werden. Die Relational Engine fordert Pages an, die Storage Engine liefert sie (entweder aus dem Buffer Pool oder von Disk), und der SQLOS koordiniert, welcher Worker Thread wann auf welchem Scheduler läuft.
|
Definition: Page (8 KB) |
|---|
|
SQL Server liest und schreibt Daten immer in Einheiten von 8 KB — sogenannten Pages. Eine Page enthält typischerweise mehrere Tabellenzeilen. Auch, wenn du nur eine einzige Zeile anforderst, lädt SQL Server die gesamte 8-KB-Page in den Buffer Pool. |
|
Das ist kein Designfehler, sondern Absicht: Nachfolgende Zugriffe auf Zeilen derselben Page sind kostenlos, weil die Page bereits im RAM liegt. Dieses Prinzip heißt "Locality of Reference" — und es ist der Grund, warum sequenzieller Zugriff fast immer schneller ist als Einzelzeilen-Zugriff. |
Datenseiten und Extents: Die physische Struktur der Daten
Um wirklich zu verstehen, wie SQL Server mit Daten umgeht, muss man eine Ebene tiefer gehen: auf die physische Struktur der Datendateien. Alles — jede Tabelle, jeder Index, jede temporäre Struktur — ist in Pages und Extents organisiert. Wer das nicht kennt, stolpert irgendwann über Autogrowth-Probleme, Fragmentierung oder TempDB-Contention, ohne zu verstehen, warum.
Aufbau einer 8-KB-Page
Jede Page in SQL Server ist exakt 8.192 Bytes groß — aufgeteilt in drei logische Bereiche: den Page Header, den Datenbereich und das Row Offset Array am Ende der Page.
|
Bereich |
Größe |
Inhalt |
|---|---|---|
|
Page Header |
96 Bytes |
Page-ID, Typ, Prüfsumme, LSN (Log Sequence Number), freier Speicher |
|
Datenbereich |
bis 8.060 Bytes |
Die eigentlichen Zeilen (Rows) oder Index-Einträge |
|
Row Offset Array |
variabel |
Zeiger auf den Anfang jeder Zeile auf der Page — rückwärts am Ende gespeichert |
Tab. 4.1: Aufbau einer SQL Server Data Page
Der Page Header enthält unter anderem die LSN des letzten Redo-Log-Eintrags, der diese Page verändert hat — das ist die Verbindung zwischen Buffer Pool und Transaction Log. Wenn SQL Server nach einem Absturz eine Page wiederherstellt, vergleicht er diese LSN mit dem Log, um festzustellen, ob die Page aktuell oder veraltet ist.
Das Row Offset Array wächst von hinten nach vorne. Neue Zeilen landen im Datenbereich von vorne nach hinten; neue Offset-Einträge kommen vom Ende der Page. Wenn vorn und hinten sich treffen — sprich: die Page ist voll — braucht SQL Server einen Page Split. Page Splits sind teuer: Eine neue Page muss alloziert, Daten umkopiert und Indizes aktualisiert werden. Hohe Fragmentierung entsteht durch viele Page Splits.
Extents: Acht Pages als Allokationseinheit
Einzelne Pages werden nicht einzeln vom Betriebssystem angefordert — das wäre zu ineffizient. SQL Server gruppiert Pages in Extents: Ein Extent ist immer genau acht aufeinanderfolgende Pages (8 × 8 KB = 64 KB). Extents sind die Mindesteinheit für Speicherallokation auf Dateiebene.
SQL Server kennt zwei Arten von Extents: Uniform Extents und Mixed Extents. Ein Uniform Extent gehört vollständig einem einzelnen Objekt — alle acht Pages sind für dieselbe Tabelle oder denselben Index reserviert. Ein Mixed Extent hingegen kann Pages für bis zu acht verschiedene Objekte enthalten. Das klingt nach einem Detail, ist aber für Performance-Diagnosen relevant: TempDB-Contention entsteht oft durch Streit um Mixed Extents — genau das analysieren wir in Kapitel 13.
|
Extent-Typ |
Zugehörigkeit |
Wann genutzt |
Performance-Aspekt |
|---|---|---|---|
|
Uniform Extent |
Gehört einem Objekt vollständig |
Ab der 9. Page eines Objekts |
Weniger Verwaltungsaufwand, weniger Contention |
|
Mixed Extent |
Bis zu 8 Objekte teilen sich die Pages |
Für die ersten 8 Pages eines neuen Objekts |
Höherer Verwaltungsaufwand, potenzielle Contention |
Tab. 4.2: Uniform vs. Mixed Extents
IAM, GAM und SGAM: Die Allokations-Buchhaltung
SQL Server führt Buch über alle allozierten Extents mithilfe spezieller System-Pages:
Warum ist das praxisrelevant? Weil bei hoher TempDB-Aktivität — vielen parallelen Sessions, die gleichzeitig temporäre Tabellen anlegen — alle Sessions auf dieselben GAM- und SGAM-Pages zugreifen müssen. Das erzeugt PAGELATCH-Waits auf genau diesen System-Pages: PFS_PAGE, GAM_PAGE, SGAM_PAGE. Das Symptom: viele parallele Sessions verlangsamen sich gegenseitig, obwohl eigentlich genug Ressourcen vorhanden sind. Lösung: TempDB mit mehreren Datendateien (Kapitel 13).
Page-Nutzung diagnostizieren: sys.dm_db_database_page_allocations
Ab SQL Server 2012 gibt es eine DMF, die dir erlaubt, die Allokation von Pages auf Objekt-Ebene zu sehen — ohne DBCC-Befehle oder undokumentierte Syscalls. Sehr praktisch für die Diagnose von Fragmentierung oder Objekt-Größen:
-- Page-Allokation für eine Tabelle abfragen
-- Zeigt: welche Pages gehören zu welchem Objekt, wie fragmentiert ist es?
-- Achtung: Auf großen Datenbanken kann das viele Zeilen zurückgeben — am besten auf konkrete Objekte filtern
SELECT
object_name(object_id) AS tabelle,
index_id,
allocation_unit_type_desc,
page_type_desc,
page_free_space_percent, -- Wie voll ist die Page?
is_allocated,
is_mixed_page_allocation -- Mixed Extent?
FROM sys.dm_db_database_page_allocations(
DB_ID(), -- aktuelle Datenbank
OBJECT_ID('dbo.Bestellungen'), -- nur diese Tabelle
NULL, -- alle Index-IDs
NULL, -- alle Partition-IDs
'DETAILED' -- Details anfordern (kostet mehr Zeit)
)
ORDER BY page_type_desc, page_free_space_percent;
-- Schneller Überblick: Wie viele Pages nutzt jede Tabelle?
SELECT
OBJECT_NAME(i.object_id) AS tabelle,
i.name AS index_name,
SUM(a.total_pages) AS seiten_gesamt,
SUM(a.total_pages) * 8 AS kb_gesamt
FROM sys.indexes i
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE OBJECT_NAME(i.object_id) IS NOT NULL
GROUP BY i.object_id, i.name
ORDER BY seiten_gesamt DESC;
Diese Abfrage ist besonders nützlich, wenn du verstehen willst, warum eine Tabelle so viel Speicherplatz belegt — oder warum ein Index trotz geringer Datenmenge viele Pages beansprucht. Hohe page_free_space_percent-Werte in Kombination mit vielen Pages deuten auf starke Fragmentierung hin: viele halbvolle Pages, viele Page Splits in der Vergangenheit. Das ist ein starkes Signal, dass der betreffende Index gewartet werden sollte — mehr dazu in Kapitel 17.
Der Buffer Pool: SQL Servers Herz aus RAM
Der Buffer Pool ist der wichtigste Speicherbereich von SQL Server. Hier cached SQL Server alle Datenpages — sowohl unveränderte (clean) als auch modifizierte (dirty). Das Ziel ist simpel: Je mehr Daten im RAM liegen, desto seltener muss SQL Server auf Disk zugreifen. Und Disk-Zugriffe sind teuer. Selbst auf moderner NVMe-SSD kostet ein zufälliger Lesezugriff 50–150 Mikrosekunden; im RAM sind es Nanosekunden — Faktor 1.000.
Der Buffer Pool ist NUMA-aware: Er hält für jeden NUMA-Node einen eigenen Speicherbereich vor, damit Threads, die auf Node 0 laufen, bevorzugt auf Pages aus dem RAM von Node 0 zugreifen. Wenn du auf einem Server mit mehreren NUMA-Nodes arbeitest, ist das keine Kleinigkeit — Remote-NUMA-Zugriffe können die Latenzen vervielfachen. Mehr dazu in Kapitel 1 (NUMA-Topologie) und Kapitel 11 (Memory Management).
Page-Lebenszyklus: Einlesen, Modifizieren, Schreiben
Eine Page durchläuft im Buffer Pool einen definierten Lebenszyklus. Verstehe diesen Zyklus, und du verstehst gleichzeitig Checkpoint-Verhalten, Dirty-Page-Mengen und IO-Muster auf deinem Server.
Abb. 4.2: Buffer Pool Page-Lifecycle: Einlesen, Dirty werden, Checkpoint und Lazy Writer
Der Zyklus im Überblick: SQL Server liest eine Page von Disk in den Buffer Pool (Physical Read). Dort wird die Page gecached und verbleibt so lange im RAM wie möglich. Wenn eine Transaktion die Page modifiziert, wird sie als "dirty" markiert — sie enthält jetzt Daten, die noch nicht auf Disk persistiert sind. Irgendwann schreibt entweder der Lazy Writer oder ein Checkpoint die Dirty Page auf Disk (Clean Write). Danach ist die Page wieder clean.
|
Hintergrund: LRU-2 Seitenersetzung |
|---|
|
Wenn der Buffer Pool voll ist und neue Pages eingelesen werden müssen, braucht SQL Server eine Strategie, welche alten Pages verdrängt werden. SQL Server verwendet einen LRU-2-Algorithmus (Least Recently Used, zweifacher Zugriff). |
|
Im Gegensatz zu einfachem LRU werden Pages nur dann als "hot" eingestuft, wenn sie mindestens zweimal zugegriffen wurden. Eine Page die einmal gelesen und nie wieder gebraucht wird (klassischer Full Table Scan!) verdrängt nicht unnötig wertvolle Pages aus dem Cache. |
|
Das erklärt, warum ein großer Table Scan den Buffer Pool "kalt" schießen kann — er liest massenhaft Pages einmalig ein und verdrängt damit Pages, auf die regelmäßig zugegriffen wird. Dieses Phänomen heißt "Buffer Pool Pollution". |
PLE — Page Life Expectancy: Der Pulsschlag des Buffer Pools
Die Page Life Expectancy (PLE) misst, wie lange eine Page im Durchschnitt im Buffer Pool verbleibt — in Sekunden. Sie ist einer der wichtigsten Indikatoren für den RAM-Gesundheitszustand deines SQL Servers.
Die traditionelle Faustregel lautet: PLE > 300 Sekunden ist akzeptabel. Diese Zahl stammt aus einer Zeit, als SQL Server typischerweise auf Servern mit 4–8 GB RAM lief. Heute, mit 128 oder 256 GB RAM, sollte die PLE deutlich höher sein — 1.000 bis 4.000 Sekunden sind auf gut ausgestatteten Servern normal. Bei Musterwerk GmbH (Instanz MWSQL01), einem typisch ausgestatteten Produktionsserver mit 64 GB RAM, liegt die PLE konstant bei etwa 2.800 Sekunden — ein gesunder Wert, der zeigt, dass der Buffer Pool die wichtigsten Working-Sets hält.
Wichtiger als die absolute Zahl ist der Trend: Wenn die PLE innerhalb von Stunden von 3.000 auf 200 Sekunden fällt, läuft etwas Konkretes schief — ein neuer Job, eine schlecht optimierte Abfrage, oder ein Batchprozess der den Buffer Pool leert. Die Baseline ist entscheidend — mehr dazu in Kapitel 9.
-- PLE pro NUMA-Node abfragen
-- Wichtig: Auf NUMA-Systemen gibt es einen Wert pro Node!
-- Nur den Gesamtwert (NUMA-Node 999) zu schauen führt in die Irre,
-- wenn ein einzelner Node unter Druck steht
SELECT
object_name,
counter_name,
instance_name,
cntr_value AS ple_sekunden
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'
AND object_name LIKE '%Buffer Manager%'
-- OR object_name LIKE '%Buffer Node%' -- für NUMA-Nodes einzeln
ORDER BY instance_name;
Auf einem System mit vier NUMA-Nodes kann die PLE auf Node 0 bei 4.000 Sekunden stehen, während Node 2 nur 150 Sekunden erreicht — ein klares Indiz für ungleichmäßige Speichernutzung oder ein NUMA-Konfigurationsproblem. Kapitel 11 geht auf genau dieses Szenario ein.
Lazy Writer und Checkpoint: Wer schreibt wann auf Disk?
SQL Server nutzt zwei Mechanismen, um Dirty Pages auf Disk zu schreiben. Beide haben unterschiedliche Trigger und Prioritäten:
|
Mechanismus |
Trigger |
Ziel |
IO-Typ |
|---|---|---|---|
|
Lazy Writer |
Buffer Pool läuft voll (wenig freie Pages) |
Speicher freigeben |
Schreibt Dirty Pages aus; verworfen, wenn clean |
|
Automatic Checkpoint |
Log-Größe oder Timeout (Recovery Interval) |
Crash-Recovery-Zeit begrenzen |
Schreibt alle Dirty Pages seit letztem Checkpoint |
|
Indirect Checkpoint |
Dirty Page Count überschreitet Ziel (= 0,9 × Ziel-Recovery-Zeit) |
Gleichmäßiges IO, keine Spitzen |
Kontinuierlich, vorhersehbar |
|
Manual Checkpoint |
CHECKPOINT T-SQL-Befehl |
Manuell, z.B. vor Maintenance |
On demand |
Tab. 4.3: Checkpoint-Mechanismen im Vergleich
Automatic Checkpoint war lange der Standard. Das Problem: Wenn die Recovery-Interval-Grenze erreicht wird, startet ein Checkpoint und schreibt u.U. riesige Mengen an Dirty Pages auf einen Schlag auf Disk — ein IO-Spike, der andere Operationen verlangsamt. Ab SQL Server 2012 ist Indirect Checkpoint der empfohlene Weg, weil er IO gleichmäßig verteilt.
|
Tipp: Indirect Checkpoint aktivieren |
|---|
|
Ab SQL Server 2016 ist Indirect Checkpoint für neue Datenbanken Standard (Target Recovery Time = 60 Sekunden). Für ältere Datenbanken oder, wenn das Verhalten des Auto-Checkpoints bekannt problematisch ist, empfiehlt sich: |
|
ALTER DATABASE [MeineDB] SET TARGET_RECOVERY_TIME = 60 SECONDS; |
|
Das hält Dirty-Page-Counts niedrig und IO-Muster gleichmäßig. Ausnahme: TEMPDB — dort ist Indirect Checkpoint seit SQL 2016 immer aktiv, egal was du einstellst. |
Buffer Pool Extensions (BPE): Wenn RAM nicht reicht
Buffer Pool Extensions erlauben es, eine schnelle SSD als Erweiterung des Buffer Pools zu nutzen. Clean Pages, die aus dem RAM-Buffer-Pool verdrängt werden, landen dann auf der SSD statt vollständig verworfen zu werden — ein zweistufiger Cache.
In der Praxis ist BPE selten die richtige Antwort. Wenn der Buffer Pool unter Druck steht, ist die echte Lösung mehr RAM. BPE kann sinnvoll sein, wenn du NVMe-SSDs hast und der Server kurzfristig nicht aufgerüstet werden kann — aber auf herkömmlichen SATA-SSDs ist der Vorteil oft marginal, während der Verwaltungsaufwand steigt. In virtualisierten Umgebungen (Kapitel 3) ist BPE meist kontraproduktiv.
Der Lock Manager: Gleichzeitigkeit und Konsistenz
Mehrere Benutzer greifen gleichzeitig auf dieselben Daten zu. Das klingt nach einem Problem — und ohne den Lock Manager wäre es auch eines. Der Lock Manager ist die Instanz, die dafür sorgt, dass keine zwei Transaktionen sich gegenseitig in die Quere kommen, ohne es zu merken. Er ist einfach ausgedrückt der Verkehrspolizist der Datenkonsistenz.
Locks sind temporäre Ressourcen, die SQL Server während einer Transaktion hält und am Ende wieder freigibt. Der Lock Manager koordiniert, welche Locks kompatibel sind, wer warten muss, und wann eine Situation eskaliert oder zum Deadlock wird. Das vollständige Bild zu Blocking und Deadlocks findest du in Kapitel 14 — hier legen wir das konzeptionelle Fundament.
Lock-Granularität: Von Zeile bis zur Datenbank
SQL Server kann Locks auf verschiedenen Granularitätsebenen vergeben. Grober Granulat bedeutet weniger Verwaltungsaufwand, aber mehr Konflikte. Feiner Granulat bedeutet mehr Parallelität, aber mehr Overhead für den Lock Manager.
|
Granularität |
Beschreibung |
Wann sinnvoll |
Overhead |
|---|---|---|---|
|
RID (Row ID) |
Einzelne Zeile in einem Heap (ohne Clustered Index) |
Sehr gezielter Einzel-Zeilen-Zugriff |
Hoch (viele Lock-Einträge) |
|
KEY |
Einzelner Schlüsselwert in einem Index |
Standard für Index-Abfragen |
Mittel |
|
PAGE |
Gesamte 8-KB-Datenseite (alle Zeilen) |
Bei Bereichsabfragen, häufig durch Eskalation |
Niedrig |
|
EXTENT |
Acht aufeinanderfolgende Pages |
Nur intern bei Allokation |
Sehr niedrig |
|
TABLE (HoBT) |
Gesamte Tabelle |
Lock Escalation, kleine Tabellen, DDL |
Minimal |
|
DATABASE |
Gesamte Datenbank |
Backup, Restore, Offline-Operationen |
Minimal |
Tab. 4.4: Lock-Granularitäten in SQL Server
Lock-Typen: Was darf gleichzeitig, was nicht?
Nicht jeder Lock ist gleich. SQL Server kennt verschiedene Lock-Modi, die unterschiedlich kompatibel miteinander sind. Das Grundprinzip: Lesende Operationen (Shared Locks) sind untereinander kompatibel — viele Leser gleichzeitig sind kein Problem. Schreibende Operationen (Exclusive Locks) sind mit niemandem kompatibel.
|
Lock-Typ |
Abkürzung |
Beschreibung |
Typischer Auslöser |
|---|---|---|---|
|
Shared |
S |
Für Lesezugriffe — mehrere S-Locks gleichzeitig möglich |
SELECT |
|
Exclusive |
X |
Für Schreibzugriffe — kein anderer Lock gleichzeitig |
INSERT, UPDATE, DELETE |
|
Update |
U |
Übergang vor Exclusive — verhindert Deadlock-Muster |
UPDATE (vor dem eigentlichen Write) |
|
Intent Shared |
IS |
Ankündigung: "Ich werde Shared Locks auf untergeordneter Ebene nehmen" |
SELECT mit feinerer Granularität |
|
Intent Exclusive |
IX |
Ankündigung: "Ich werde Exclusive Locks auf untergeordneter Ebene nehmen" |
DML auf Tabellenebene |
|
Shared + Intent Exclusive |
SIX |
Tabelle gesperrt für Lesen; einzelne Rows exklusiv |
Selten, z.B. bei Cursor-Operationen |
Tab. 4.5: Lock-Typen und ihre Bedeutung
Intent Locks klingen zunächst verwirrend, sind aber ein elegantes Konzept: Bevor SQL Server einen feingranularen Lock (z.B. auf Row-Ebene) hält, setzt er auf allen übergeordneten Ebenen (Page, Table) einen Intent Lock. Das erlaubt dem Lock Manager, sehr schnell zu prüfen, ob eine grobe Operation (z.B. ein Table Lock) mit laufenden feinen Operationen kompatibel ist — ohne jede einzelne Row-Lock zu prüfen.
Lock-Kompatibilitätsmatrix
Diese Matrix zeigt, welche Lock-Typen gleichzeitig auf derselben Ressource gehalten werden können (✓ = kompatibel, ✗ = blockiert):
|
Vorhanden ↓ / Angefordert → |
IS |
S |
U |
IX |
SIX |
X |
|---|---|---|---|---|---|---|
|
IS (Intent Shared) |
✓ |
✓ |
✓ |
✓ |
✓ |
✗ |
|
S (Shared) |
✓ |
✓ |
✓ |
✗ |
✗ |
✗ |
|
U (Update) |
✓ |
✓ |
✗ |
✗ |
✗ |
✗ |
|
IX (Intent Exclusive) |
✓ |
✗ |
✗ |
✓ |
✗ |
✗ |
|
SIX (Shared+Intent Excl.) |
✓ |
✗ |
✗ |
✗ |
✗ |
✗ |
|
X (Exclusive) |
✗ |
✗ |
✗ |
✗ |
✗ |
✗ |
Tab. 4.6: Lock-Kompatibilitätsmatrix
Lock Escalation: Wenn viele kleine Locks zu einem großen werden
Lock-Granularität hat ihren Preis: Hält eine Transaktion sehr viele feine Locks (z.B. Tausende Row-Locks), verbraucht das Speicher und Verwaltungsaufwand. SQL Server greift dann zu einem drastischen Mittel: Lock Escalation. Alle feinen Locks werden durch einen einzigen Table Lock ersetzt. Weniger Overhead — aber deutlich mehr Blockierung anderer Transaktionen.
Lock Escalation wird ausgelöst, wenn eine Transaktion auf einer einzelnen Tabelle mehr als 5.000 Lock-Einheiten hält. Das ist keine konfigurierbare Schwelle (es sei denn, du deaktivierst Escalation per ALTER TABLE), sondern ein fester interner Wert. Das Problem: Wenn eine Massenoperation (z.B. ein Batch-Update von 100.000 Zeilen) Lock Escalation auslöst, ist die gesamte Tabelle für alle anderen Schreiber und Leser (ohne READ_COMMITTED_SNAPSHOT) gesperrt, bis die Transaktion abgeschlossen ist.
|
Tipp: Lock Escalation vermeiden |
|---|
|
Zwei Strategien gegen unerwünschte Lock Escalation: |
|
1. Batching: Große Operationen in kleinere Batches aufteilen, z.B. UPDATE in Tranchen von 1.000 Zeilen. So wird die Eskalationsschwelle nie erreicht. |
|
2. RCSI aktivieren: Read Committed Snapshot Isolation eliminiert Shared Locks bei Lesern vollständig — Leser blockieren Schreiber nicht mehr und umgekehrt. Kapitel 14 und 29 behandeln das im Detail. |
|
ALTER TABLE dbo.Bestellungen SET (LOCK_ESCALATION = DISABLE); — Vorsicht: Nur, wenn du weißt, was du tust! |
Aktive Locks diagnostizieren: sys.dm_tran_locks
sys.dm_tran_locks gibt dir einen Echtzeit-Überblick über alle aktuell gehaltenen Locks auf dem Server. Das ist die erste Anlaufstelle, wenn du Blocking-Probleme vermutest oder verstehen willst, wer was blockiert.
-- Aktive Locks und Blocking-Ketten anzeigen
-- Zeigt: wer wartet, wer blockiert, welche Ressource, welcher Lock-Typ
SELECT
tl.request_session_id AS session_id,
tl.resource_type, -- ROW, PAGE, OBJECT, DATABASE...
tl.resource_database_id,
DB_NAME(tl.resource_database_id) AS datenbank,
tl.resource_description, -- Konkrete Ressource (z.B. Page-ID)
tl.request_mode, -- S, X, U, IS, IX...
tl.request_status, -- GRANT = gehalten, WAIT = wartend
tl.request_owner_type, -- TRANSACTION, SESSION...
er.blocking_session_id, -- Wer blockiert diese Session?
er.wait_type, -- Was ist der aktuelle Wait?
er.wait_time / 1000.0 AS wartezeit_sekunden
FROM sys.dm_tran_locks tl
LEFT JOIN sys.dm_exec_requests er
ON tl.request_session_id = er.session_id
WHERE tl.request_status = 'WAIT' -- Nur wartende Anfragen
OR er.blocking_session_id IS NOT NULL -- Oder blockierte Sessions
ORDER BY er.wait_time DESC;
-- Zusammenfassung: Wie viele Locks pro Typ?
SELECT
resource_type,
request_mode,
request_status,
COUNT(*) AS anzahl
FROM sys.dm_tran_locks
GROUP BY resource_type, request_mode, request_status
ORDER BY anzahl DESC;
Wir gehen in Kapitel 14 tief in Blocking und Deadlocks ein — mit vollständigen Diagnose-Workflows und dem Deadlock-Graphen. Hier reicht das Grundverständnis: Lock Manager koordiniert Parallelität, Lock Escalation kann Probleme verursachen, und sys.dm_tran_locks ist dein erster Blick in die aktuelle Locking-Situation.
SQLOS: SQL Servers eigenes Betriebssystem
SQL Server hat kein normales Verhältnis zum Windows-Scheduler. Während andere Anwendungen brav darauf warten, dass Windows ihnen CPU-Zeit zuteilt, hat SQL Server ein eigenes Scheduling-System eingebaut: den SQLOS (SQL Server Operating System). Kein Marketing-Begriff, sondern eine technische Realität.
Der Grund ist historisch und praktisch: SQL Server braucht extrem feingranulare Kontrolle über seine Worker Threads, weit mehr als ein generischer OS-Scheduler bieten kann. Und er braucht NUMA-Awareness, die Windows nicht automatisch liefert.
Scheduler, Worker Threads und Tasks
Das SQLOS-Modell hat drei Ebenen: Schedulers, Worker Threads und Tasks.
|
Konzept |
Beschreibung |
Anzahl / Limit |
|---|---|---|
|
Scheduler |
Ein Scheduler pro logische CPU. Er verwaltet Worker Threads, die auf dieser CPU laufen. |
1 pro logische CPU (Standard) |
|
Worker Thread |
Ein Thread aus dem Thread-Pool, der Tasks ausführt. Wartet kooperativ, wenn er blockiert ist. |
max worker threads konfigurierbar |
|
Task |
Kleinste Arbeitseinheit — eine konkrete Aufgabe (z.B. Parallel-Worker für eine Abfrage) |
Beliebig viele pro Request |
Tab. 4.7: SQLOS-Abstraktionsebenen
Jeder Scheduler verwaltet eine Queue von Worker Threads, die darauf warten, Tasks auszuführen. Ein Worker Thread nimmt sich einen Task, führt ihn aus — und gibt den Scheduler freiwillig wieder frei, wenn er auf etwas warten muss (IO, Lock, Signal). Das ist kooperatives Scheduling.
Kooperatives Scheduling: Warum SQL Server nicht präemptiv ist
Windows-Threads werden präemptiv gescheduled: Das OS kann einen Thread jederzeit unterbrechen und einem anderen die CPU geben. SQL Server macht das anders. Worker Threads laufen kooperativ: Sie geben die CPU freiwillig ab, wenn sie warten müssen. Das erlaubt SQL Server, Context-Switches extrem effizient zu verwalten und vermeidet, dass Worker Threads an ungünstigen Stellen unterbrochen werden.
Das hat eine wichtige Konsequenz: Wenn ein Worker Thread nicht kooperiert — weil er z.B. in einer langen berechnungsintensiven Operation steckt oder in einem externen COM-Aufruf hängt — blockiert er seinen Scheduler. SQL Server erkennt das (nach einigen Sekunden) und meldet es als "Non-Yielding Scheduler"-Fehler. Das ist einer der wenigen Fälle, wo SQL Server tatsächlich unstabil werden kann.
|
Warnung: Non-Yielding Scheduler |
|---|
|
Taucht im SQL Server Error Log "Non-Yielding Scheduler" auf, ist das ein ernstes Warnsignal. Es bedeutet, dass ein Worker Thread den Scheduler für zu lange Zeit blockiert. |
|
Typische Ursachen: Schlecht geschriebener CLR-Code, externe Calls via Linked Server, bestimmte interne Operationen bei extremer CPU-Auslastung. In seltenen Fällen deutet es auf Bugs in SQL Server selbst hin. |
|
Der Server kann in dieser Situation hängen oder abstürzen. Sofort ins Error Log schauen und die Ursache identifizieren — vor allem welche Datenbank und welches Query betroffen war. |
NUMA-Awareness des SQLOS
Jeder Scheduler in SQL Server ist einem NUMA-Node zugeordnet. Der SQLOS versucht, Threads so zu schedulen, dass sie auf demselben Node laufen wie die Daten, auf die sie zugreifen — minimale Remote-Speicherzugriffe, maximale Performance.
Wenn NUMA falsch konfiguriert ist (z.B. Soft-NUMA statt echter NUMA-Topologie, falsche Affinity-Masks), kann SQL Server den Vorteil von NUMA-Awareness verlieren. Das haben wir in Kapitel 1 ausführlich besprochen und in Kapitel 11 schauen wir uns an, wie man das mit DMVs diagnostiziert.
Resource Governor: Wer bekommt was, und wer geht leer aus
Der Resource Governor ist SQL Servers eingebauter Traffic-Cop für CPU und Memory. Ohne ihn gilt das Gesetz des Dschungels: Wer am lautesten schreit (sprich: wer die teuerste Abfrage hat), bekommt die meisten Ressourcen. Der Resource Governor ändert das — er erlaubt, verschiedenen Workloads definierte Ressourcen-Budgets zuzuweisen und durchzusetzen.
Klingt nach einem Feature für große Rechenzentren? Ist es auch. Aber es gibt kleinere Szenarien, in denen der Resource Governor echten Wert liefert — und die kennen zu nicht, kann teuer werden.
Architektur: Resource Pools und Workload Groups
Der Resource Governor hat zwei Konfigurationsebenen: Resource Pools und Workload Groups.
Ein Resource Pool definiert die Ressourcengrenzen auf Systemebene: minimale und maximale CPU-Prozente, minimaler und maximaler Arbeitsspeicher. SQL Server liefert zwei eingebaute Pools: default (für alles, was nicht explizit zugewiesen wurde) und internal (für interne SQL Server-Prozesse — nicht anfassbar).
Innerhalb eines Pools können mehrere Workload Groups existieren. Eine Workload Group fügt eine weitere Feingranularität hinzu: Innerhalb des Pool-Budgets kann sie zusätzlich eigene Grenzen für gleichzeitige Anfragen, maximale Abfragekosten (aus Sicht des Optimizers) oder Memory Grants definieren. Jede eingehende Session landet — basierend auf einer Classifier Function — in genau einer Workload Group.
|
Konfigurationselement |
Beschreibung |
Typische Einstellungen |
|---|---|---|
|
Resource Pool |
Systemweite CPU/RAM-Grenzen |
MIN_CPU_PERCENT, MAX_CPU_PERCENT, MIN_MEMORY_PERCENT, MAX_MEMORY_PERCENT |
|
Workload Group |
Grenzen innerhalb eines Pools |
MAX_DOP, REQUEST_MAX_CPU_TIME_SEC, REQUEST_MAX_MEMORY_GRANT_PERCENT |
|
Classifier Function |
T-SQL-Funktion, die jede neue Session einer Workload Group zuordnet |
Basierend auf Login, Host, App-Name, Tageszeit… |
Tab. 4.8: Resource Governor Komponenten
Wann setzt man den Resource Governor ein?
Der Resource Governor ist kein Allheilmittel — und er kostet selbst Overhead. Aber in bestimmten Situationen ist er unverzichtbar:
Für Memory Grants ist der Resource Governor besonders relevant — er kann begrenzen, wie viel RAM eine einzelne Abfrage für Sort- und Hash-Operationen anfordern darf. Das verhindert, dass ein einzelner schlecht optimierter Query den kompletten verfügbaren Arbeitsspeicher für Memory Grants blockiert und alle anderen Abfragen in die Warteschlange zwingt. Kapitel 12 (Memory Grants und Spills) baut auf diesem Konzept auf.
-- Einfaches Resource Governor Setup: Reporting vs. OLTP trennen
-- Schritt 1: Resource Pools anlegen
CREATE RESOURCE POOL ReportingPool
WITH (MIN_CPU_PERCENT = 0, -- kein Minimum — Report wartet, wenn nötig
MAX_CPU_PERCENT = 40, -- höchstens 40% CPU für Reports
MIN_MEMORY_PERCENT = 0,
MAX_MEMORY_PERCENT = 30); -- höchstens 30% Memory Grants
CREATE RESOURCE POOL OLTPPool
WITH (MIN_CPU_PERCENT = 30, -- mindestens 30% CPU garantiert für OLTP
MAX_CPU_PERCENT = 100,
MIN_MEMORY_PERCENT = 0,
MAX_MEMORY_PERCENT = 80);
-- Schritt 2: Workload Groups anlegen
CREATE WORKLOAD GROUP ReportingGroup
WITH (MAX_DOP = 4, -- maximal 4 Kerne pro Report
REQUEST_MAX_CPU_TIME_SEC = 120) -- Report wird nach 2 Min. abgebrochen
USING ReportingPool;
CREATE WORKLOAD GROUP OLTPGroup
USING OLTPPool;
-- Schritt 3: Classifier Function erstellen
-- Einfaches Beispiel: bestimmten Login in Report-Pool leiten
CREATE FUNCTION dbo.ResourceClassifier()
RETURNS SYSNAME WITH SCHEMABINDING
AS BEGIN
-- Reporting-Logins in die Reporting-Gruppe leiten
IF SUSER_NAME() IN ('ReportUser', 'PowerBIService')
RETURN 'ReportingGroup';
RETURN 'OLTPGroup'; -- Standard: OLTP-Gruppe
END;
-- Schritt 4: Resource Governor aktivieren
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.ResourceClassifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;
Einen vollständigen Exkurs zum Resource Governor und wie er Memory Grants begrenzt findest du in Kapitel 12. Dort zeigen wir auch, wie man mit dem Resource Governor gezielt gegen "Memory Grant Waits" (RESOURCE_SEMAPHORE) vorgeht — einem der häufigsten Probleme auf Servern mit gemischten Workloads.
Query Processing: Von SQL-Text zum Ergebnisset
Eine SQL-Abfrage legt eine lange Strecke zurück, bevor das erste Ergebnisset zurückkommt. Dieser Weg ist nicht zufällig — er folgt einer festen Pipeline, und jede Stufe kann Probleme verursachen, die sich als Performance-Problem manifestieren.
|
Phase |
Aufgabe |
Mögliche Probleme |
|---|---|---|
|
Parse |
SQL-Text → Query Tree. Syntaxprüfung, Tokenisierung. |
Syntaxfehler (selten Performance-relevant) |
|
Algebrize |
Objekte auflösen: Tabellen, Indizes, Spalten. Permissions prüfen. |
Compilation-Lock bei Schemaänderungen |
|
Optimize |
Kostenbasierte Planauswahl. Statistiken lesen, Alternativen bewerten. |
Schlechte Statistiken → schlechter Plan → CPU-Explosion |
|
Execute |
Plan ausführen. Pages anfordern, Locks halten, Results zurückliefern. |
IO-Waits, Lock-Waits, Memory Grants |
Tab. 4.9: Query Processing Pipeline
Plan Cache: Das Gedächtnis des Optimizers
Der Query Optimizer ist teuer. Eine Abfrage zu optimieren kann hunderte von Millisekunden dauern — für komplexe Queries sogar Sekunden. Deshalb cached SQL Server fertige Ausführungspläne im Plan Cache (auch Procedure Cache genannt). Kommt dieselbe Abfrage wieder, wird der gecachte Plan wiederverwendet — Kompilierungszeit: null.
Das klingt wie eine gute Sache, und meistens ist es das auch. Aber der Plan Cache ist gleichzeitig eine der häufigsten Problemquellen in SQL Server-Umgebungen, die wir ab Kapitel 18 ausführlich behandeln.
Parametrische vs. Ad-hoc-Abfragen und Single-Use Plans
SQL Server unterscheidet zwischen parametrischen und Ad-hoc-Abfragen. Eine parametrische Abfrage verwendet Parameter (z.B. stored procedures oder explizite sp_executesql-Calls) — der Plan wird mit den spezifischen Parameterwerten kompiliert und gecached. Bei identischer Query-Struktur mit anderen Parametern wird der vorhandene Plan wiederverwendet (oder ein neuer kompiliert, falls er nicht passt).
Ad-hoc-Abfragen — also direkte SQL-Strings ohne Parametrisierung — werden oft als einmalige Pläne gecached. Jede Variante des Query-Strings erzeugt einen eigenen Cache-Eintrag. Das Ergebnis: ein Plan Cache voller Single-Use Plans, die RAM belegen und nie wieder genutzt werden.
-- Single-Use Plans im Plan Cache identifizieren
-- Viele davon sind ein Indiz für fehlende Parametrisierung
-- (typisches Trendforge-Problem — wir sehen das in Kapitel 34)
SELECT
usecounts, -- Wie oft wurde dieser Plan genutzt?
size_in_bytes / 1024 AS kb, -- Speicherverbrauch
objtype, -- Proc, Adhoc, Prepared...
text
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE usecounts = 1 -- Nur einmal genutzt = Single-Use
AND objtype = 'Adhoc' -- Ad-hoc-Abfragen
ORDER BY size_in_bytes DESC -- Größte zuerst
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;
Wenn dieser Query Hunderte oder Tausende Einträge zurückgibt, hast du ein Parametrisierungsproblem. Die Lösung ist entweder "Optimize for Ad-hoc Workloads" (Kapitel 5) oder die Applikation auf Parametrisierung umzustellen. Letzteres ist die bessere Lösung, ersteres der schnelle Notfalleingriff.
Plan Cache Invalidierung: Wann werden Pläne verworfen?
Ein gecachter Plan ist kein ewiger Freund. SQL Server verwirft Pläne unter verschiedenen Bedingungen — manchmal sinnvoll, manchmal unnötig und kostspielig:
Der Zusammenhang zwischen Plan Cache Invalidierung und CPU-Last ist wichtig: Hohe Kompilierungsraten belasten die CPU, auch, wenn die eigentlichen Abfragen günstig sind. Auf Servern mit hoher Compile-Rate (messbar über den Performance Counter "SQL Compilations/sec") kann CPU-Last entstehen, die nichts mit der Abfrage-Komplexität zu tun hat. Wir messen das in Kapitel 9 (Wait Statistics) und analysieren es in Kapitel 16 und 18 im Detail.
Toxic Plans: Wenn der Cache zum Feind wird
Manchmal ist ein gecachter Plan nicht gut — er ist schlecht. Sehr schlecht. Das passiert, wenn Parameter Sniffing einen Plan mit untypischen Parameterwerten kompiliert hat, der für alle anderen Aufrufe ineffizient ist. Der Plan sitzt im Cache und wird wiederverwendet, obwohl er jedes Mal Schaden anrichtet. Solche Pläne heißen gelegentlich "Toxic Plans".
-- Teuerste Pläne im Cache nach durchschnittlicher CPU-Zeit
-- Toxic Plans erkennt man oft an sehr hoher durchschnittlicher CPU
-- bei gleichzeitig vielen Ausführungen
SELECT TOP 20
qs.execution_count,
qs.total_worker_time / qs.execution_count AS avg_cpu_mikrosekunden,
qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
qs.total_elapsed_time / qs.execution_count AS avg_dauer_mikrosekunden,
SUBSTRING(st.text, 1, 200) AS query_text,
qp.query_plan -- Ausführungsplan als XML
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY avg_cpu_mikrosekunden DESC; -- Teuerste pro Ausführung zuerst
-- Plan direkt aus dem Cache entfernen (gezielt, nicht alles löschen!)
-- Beim nächsten Aufruf wird ein neuer Plan kompiliert
DBCC FREEPROCCACHE(plan_handle); -- plan_handle aus obiger Abfrage nehmen
Ein gezieltes DBCC FREEPROCCACHE mit spezifischem plan_handle ist chirurgisch — es entfernt nur diesen einen Plan, nicht den gesamten Cache. Das ist die bevorzugte Methode gegenüber dem "alles löschen"-Ansatz, der vorübergehend zu massiven Recompilationen auf dem gesamten Server führt. Kapitel 18 zeigt vollständige Strategien gegen Parameter Sniffing und Toxic Plans.
Log Buffer und Commit-Verarbeitung: Wie Transaktionen wirklich enden
Jeder kennt das COMMIT-Statement. Wenige wissen, was danach passiert. Und noch weniger wissen, dass genau dieser Ablauf einer der häufigsten Performance-Engpässe auf produktiven SQL Servern ist. Der Log Buffer ist der Schlüssel — er sitzt zwischen deiner Transaktion und der Disk, und seine Geschwindigkeit bestimmt deine Commit-Latenz.
Der Log Buffer im RAM
Write-Ahead Logging bedeutet: Bevor eine Daten-Page auf Disk geschrieben wird, muss der zugehörige Log-Record sicher auf Disk sein. Aber SQL Server schreibt nicht jeden einzelnen Log-Record sofort auf Disk — das wäre viel zu ineffizient. Stattdessen puffert SQL Server Log-Records im sogenannten Log Buffer im RAM: einem ringförmigen Speicherbereich, der per Default bis zu 60 KB groß ist, aber dynamisch wachsen kann.
Log-Records werden kontinuierlich in diesen Buffer geschrieben. Der Buffer wird auf Disk geflusht — diesen Vorgang nennt man Log Hardening — in folgenden Situationen:
WRITELOG-Wait: Der Fingerabdruck langsamer Commit-Operationen
Jedes Mal, wenn SQL Server auf den Flush des Log Buffers wartet — also, wenn ein COMMIT nicht sofort zurückkehrt, weil der Log-Record noch nicht auf Disk ist — entsteht ein WRITELOG-Wait. Das ist einer der häufigsten Wait Types auf transaktionsintensiven Systemen, und er sagt dir sehr direkt: "Deine Log-Disk ist zu langsam für deine Commit-Rate."
-- WRITELOG-Waits aktuell messen
-- Zeigt wie viel Zeit SQL Server seit dem letzten Restart auf Log-Flushes gewartet hat
SELECT
wait_type,
waiting_tasks_count,
wait_time_ms,
-- Durchschnittliche Wartezeit pro Wait-Event:
wait_time_ms / NULLIF(waiting_tasks_count, 0) AS avg_wait_ms,
max_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type = 'WRITELOG'
ORDER BY wait_time_ms DESC;
-- Commit-Latenz live messen (über kurze Zeiträume samplen):
DECLARE @t1 DATETIME2 = SYSDATETIME();
BEGIN TRANSACTION;
-- Minimale Änderung — nur um einen Commit zu messen
UPDATE dbo.PingTabelle SET Zeitstempel = SYSDATETIME() WHERE Id = 1;
COMMIT;
SELECT DATEDIFF(MICROSECOND, @t1, SYSDATETIME()) AS commit_dauer_mikrosekunden;
-- Zielwert auf NVMe: < 100 µs. Auf SATA-SSD: 200–500 µs.
-- Auf Sparfuchs-Hardware (Spindel): typischerweise 5.000–15.000 µs.
Die Commit-Latenz ist eine der direktesten Messgrößen für die Log-Disk-Qualität. Auf moderner NVMe-SSD sollte ein einzelner Commit unter 100 Mikrosekunden dauern. Auf SATA-SSD sind 200–500 Mikrosekunden realistisch. Auf einer mechanischen Festplatte — wie bei Sparfuchs & Partner, wo Transaction Log, Daten, TempDB und Betriebssystem auf einer einzigen Spindel liegen — sind 5.000 bis 15.000 Mikrosekunden keine Seltenheit. Das bedeutet: Pro Transaktion verliert man bis zu 15 Millisekunden allein durch den Commit. Bei einer Applikation die 200 Transaktionen pro Sekunde abfeuert, ist das Rechenaufgabe fürs Kopfrechnen.
Delayed Durability: Wenn Konsistenz gegen Performance getauscht wird
Ab SQL Server 2014 gibt es Delayed Durability — eine Option, die den synchronen Log-Flush beim COMMIT auflöst. Commits kehren zurück, bevor der Log-Record physisch auf Disk ist. Stattdessen werden Log-Records gebatcht und verzögert auf Disk geschrieben.
Das reduziert WRITELOG-Waits massiv. Der Preis: Im Falle eines Absturzes gehen die letzten committed Transaktionen verloren — bis zu 60 KB Log-Daten, also je nach Transaktionsgröße Dutzende bis Hunderte Transaktionen. Delayed Durability ist für Szenarien gedacht, in denen Datenverlust in diesem Umfang akzeptabel ist — zum Beispiel für Logging-Tabellen oder Session-Tracking. Für Finanztransaktionen oder Bestellungen ist es keine Option.
|
Tipp: Instant File Initialization — die vergessene Optimierung |
|---|
|
Verwandt mit dem Log-Thema, aber für Datendateien: Instant File Initialization (IFI) erlaubt SQL Server, neue Datendatei-Allokationen sofort zu nutzen, ohne die Bytes vorher auf null zu setzen. |
|
Für Log-Dateien gilt IFI nicht — diese müssen immer initialisiert werden, aus Sicherheitsgründen. Für Datendateien aber kann IFI Autogrowth-Operationen von Sekunden auf Millisekunden reduzieren. |
|
Wie du IFI aktivierst und was es bringt, erklärt Kapitel 5 (Serverkonfiguration) im Detail. Spoiler: Es ist eine der einfachsten Optimierungen mit dem größten Effekt auf Autogrowth-Performance. |
Transaction Log Internals: Das WAL-Prinzip
Das Transaction Log ist nicht optional. Es ist das Fundament der ACID-Garantien von SQL Server — ohne es gäbe es keine Durability, keine Crash Recovery, keine vollständigen Backups. Wer das Transaction Log missachtet (oder, wie in manchen Umgebungen gesehen, nie ein Log-Backup macht — wir reden über dich, Sparfuchs), zahlt irgendwann einen sehr hohen Preis.
Write-Ahead Logging (WAL)
SQL Server folgt dem Write-Ahead Logging-Prinzip: Bevor eine modifizierte Datenseite auf Disk geschrieben wird, muss der zugehörige Log-Eintrag auf Disk sein. Immer. Ausnahmslos.
Das klingt nach Mehraufwand — und das ist es auch, aber aus gutem Grund: Wenn SQL Server abstürzt, kann er aus dem Transaction Log genau rekonstruieren, welche Transaktionen committed waren und welche nicht. Ohne WAL wäre nach einem Absturz unklar, in welchem Zustand die Datenpages sind.
|
Definition: Log Sequence Number (LSN) |
|---|
|
Jeder Log-Eintrag bekommt eine Log Sequence Number (LSN) — eine monoton steigende Nummer, die jeden einzelnen Log-Record eindeutig identifiziert. |
|
LSNs sind das Rückgrat von Backup und Recovery, Replikation, Always On und Change Data Capture. Wenn du je einen "log sequence number out of order"-Fehler gesehen hast, weißt du, dass LSN-Konsistenz kein Detail ist. |
|
Format: (VLF-Sequenznummer):(Offset in VLF):(Slot-Nummer) — z.B. 0000:001a3f8b:0001 |
Log-Dateistruktur: VLFs und ihre Tücken
Die Log-Datei einer SQL-Server-Datenbank ist intern in Virtual Log Files (VLFs) unterteilt. VLFs sind die kleinsten Einheiten, die SQL Server für Log-Truncation und Backup verwendet. Zu viele VLFs verlangsamen den Startup und die Recovery. Zu wenige bedeuten, dass die Log-Datei häufig wächst und unnötige IO-Spikes erzeugt.
-- VLF-Anzahl der aktuellen Datenbank prüfen
-- Ziel: unter 50 VLFs für normale Datenbanken
-- > 1.000 VLFs = ernstes Problem (Sparfuchs: 48.312 VLFs!)
DBCC LOGINFO; -- jede Zeile = ein VLF
-- Alternativ: Anzahl zählen
SELECT COUNT(*) AS vlf_anzahl
FROM sys.dm_db_log_info(DB_ID()); -- ab SQL Server 2016
Sparfuchs & Partner hatte auf ihrer Produktionsdatenbank 48.312 VLFs — das Ergebnis von 847 Autogrowth-Events à 1 MB über die Laufzeit des Servers. Jedes Autogrowth erzeugt neue VLFs, und bei kleinen Autogrowth-Werten sind das viele kleine VLFs. Die Recovery nach einem Neustart dauerte dadurch fast 20 Minuten. Wir sehen diesen Fall in Kapitel 33 im Detail.
In Kapitel 6 (Datenbankeinstellungen) zeigen wir, wie man VLFs durch korrektes Log-Sizing und sinnvolle Autogrowth-Werte von Anfang an vermeidet — und wie man bei einer Datenbank mit zu vielen VLFs aufräumt.
Log Truncation: Wann und warum
Log Truncation bedeutet nicht, dass die Log-Datei kleiner wird — es bedeutet, dass der Speicherplatz der Log-Datei wiederverwendet werden kann. Committed und gebackupte Transaktionen werden als nicht mehr benötigt markiert; die entsprechenden VLFs können überschrieben werden.
Log Truncation passiert automatisch nach einem Log-Backup (bei Full und Bulk-Logged Recovery Model) oder bei jedem Checkpoint (Simple Recovery Model). Was Log Truncation verhindert: eine aktive Transaktion, eine offene Replikations-Subscription, ein aktives AG, oder das Fehlen von Log-Backups. Das Ergebnis ist ein wachsendes Transaction Log — in extremen Fällen bis zur vollständigen Disk-Auslastung.
|
Recovery Model |
Log-Truncation-Trigger |
Backup-Anforderung |
Typischer Einsatz |
|---|---|---|---|
|
Simple |
Automatisch bei Checkpoint |
Kein Log-Backup nötig |
Entwicklung, kleine DBs, kein RPO-Bedarf |
|
Full |
Nach Log-Backup |
Log-Backups zwingend |
Produktionsdatenbanken, RPO < 1h |
|
Bulk-Logged |
Nach Log-Backup (Bulk-Ops minimal geloggt) |
Log-Backups zwingend |
Massendaten-Loads in Produktion |
Tab. 4.10: Recovery Models und Log-Truncation
Parallelismus: Wenn SQL Server mehrere CPUs gleichzeitig nutzt
SQL Server kann eine einzelne Abfrage auf mehrere Worker Threads verteilen — Intra-Query Parallelism. Das klingt immer nach einer guten Idee, ist es aber nicht automatisch. Parallelismus erzeugt Koordinationsaufwand, belegt mehrere Kerne für eine einzelne Abfrage, und kann andere Abfragen hungern lassen.
Der Query Optimizer entscheidet, ob eine Abfrage parallel ausgeführt wird, basierend auf den geschätzten Kosten und dem eingestellten Cost Threshold for Parallelism. Wenn der Optimizer die Kosten unter dem Threshold schätzt, läuft die Abfrage seriell. Wenn drüber — und MAXDOP > 1 — kann ein paralleler Plan gewählt werden.
Exchange Operators: Das Rückgrat des Parallelismus
In einem parallelen Ausführungsplan tauchen Exchange Operators auf (auch Distribute Streams, Gather Streams oder Repartition Streams genannt). Sie sind die Schnittstelle zwischen parallelen und seriellen Teilen des Plans — und sie sind teuer.
Exchange Operators müssen Daten zwischen Worker Threads koordinieren, was Synchronisation und Pufferung erfordert. CXPACKET-Waits (und seit SQL 2017: CXCONSUMER-Waits) entstehen genau hier: Wenn ein Thread auf einen anderen warten muss, bevor er weitermachen kann. Wir schauen uns das in Kapitel 9 (Wait Statistics) und Kapitel 15 (Ausführungspläne) genauer an.
|
Tipp: Parallelismus ist ein Werkzeug, keine Allheilmittel |
|---|
|
Wenn du CXPACKET-Waits siehst, bedeutet das nicht automatisch, dass du MAXDOP auf 1 setzen musst. Manchmal sind parallele Abfragen tatsächlich schneller — und CXPACKET ist nur der Preis dafür. |
|
Die Frage ist immer: Was ist die Gesamtlaufzeit der Abfrage? Und was kostet Parallelismus im Vergleich zu serieller Ausführung? Signal Wait-Anteil ist entscheidend: Hohe Signal Waits = CPU-Hunger, nicht Parallelismus-Problem. |
|
MAXDOP und Cost Threshold gemeinsam konfigurieren — Kapitel 5 zeigt die Formeln. |
Diagnose: Architektur-Probleme erkennen und beheben
|
Warnung: Symptome: Architektur-bedingte Performance-Probleme |
|---|
|
• Hohe PAGEIOLATCH-Waits (SQL Server wartet auf Disk): Buffer Pool zu klein, zu wenig RAM |
|
• PLE unter 300 Sekunden (oder stark sinkend): Speicherdruck, Buffer Pool wird nicht warm |
|
• Hohe RESOURCE_SEMAPHORE-Waits: Memory Grants werden nicht gewährt → Kapitel 12 |
|
• SOS_SCHEDULER_YIELD-Waits: CPU-Sättigung, Scheduler überlastet → Kapitel 9 |
|
• Non-Yielding Scheduler im Error Log: Worker Thread blockiert Scheduler dauerhaft |
|
• Viele Single-Use Plans im Cache (hohe Compile-Rate): fehlende Parametrisierung |
|
• Hohe WRITELOG-Waits: Log-Disk zu langsam für die Commit-Rate der Applikation |
|
• Blocking-Ketten (LCK_M_X-Waits): Lock Escalation oder lang laufende Transaktionen |
|
Tipp: So misst du das |
|---|
|
— Buffer Pool Auslastung und PLE (pro NUMA-Node!) |
|
SELECT object_name, counter_name, instance_name, cntr_value |
|
FROM sys.dm_os_performance_counters |
|
WHERE counter_name IN ('Page life expectancy', 'Page reads/sec', 'Page writes/sec') |
|
AND object_name LIKE '%Buffer%'; |
|
|
|
— Scheduler-Auslastung: Wie viele Tasks warten auf einen Scheduler? |
|
SELECT scheduler_id, current_tasks_count, runnable_tasks_count, |
|
work_queue_count, pending_disk_io_count |
|
FROM sys.dm_os_schedulers |
|
WHERE status = 'VISIBLE ONLINE' |
|
ORDER BY runnable_tasks_count DESC; |
|
|
|
— Plan Cache Übersicht: Wie viel RAM, wie viele Single-Use-Plans? |
|
SELECT objtype, COUNT(*) AS anzahl, |
|
SUM(size_in_bytes) / 1048576 AS gesamt_mb, |
|
SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS single_use |
|
FROM sys.dm_exec_cached_plans |
|
GROUP BY objtype |
|
ORDER BY gesamt_mb DESC; |
|
Hintergrund: Typische Fehlinterpretationen |
|---|
|
• "Hohe Page Reads/sec = Problem": Nicht unbedingt. Hohe Read-Rates sind normal beim Start, wenn der Buffer Pool noch kalt ist. Erst, wenn sie dauerhaft hoch sind UND die PLE sinkt, ist das ein Problem. |
|
• "Dirty Pages = Problem": Dirty Pages im Buffer Pool sind vollkommen normal — sie zeigen schreibintensive Workloads. Problematisch werden sie erst, wenn Checkpoint-IO andere Operationen blockiert. |
|
• "Viele Worker Threads = Problem": Nicht per se. Worker Threads, die im Leerlauf warten, verbrauchen wenig Ressourcen. Relevant ist, wenn max worker threads erreicht wird — dann werden neue Verbindungen abgewiesen. |
|
• "Plan Cache voll = Problem": Plan Cache nutzt verfügbaren RAM sinnvoll. Problematisch ist nur, wenn Single-Use Plans den Cache dominieren und anderen Daten keinen Platz lassen. |
|
• "CXPACKET überall = MAXDOP auf 1 setzen": Das ist die häufigste Fehlreaktion auf Parallelismus-Waits. CXPACKET zeigt Parallelismus — nicht ob er schlecht ist. Die richtige Antwort ist Analyse, nicht Panikreaktion. |
|
Tipp: Erste Gegenmaßnahmen |
|---|
|
• PLE sinkt stark → Baseline prüfen, Abfragen mit hohem logical reads identifizieren (sys.dm_exec_query_stats), RAM-Aufrüstung planen |
|
• Viele Single-Use Plans → "Optimize for Ad-hoc Workloads" aktivieren (sofort, ohne Neustart): sp_configure 'optimize for ad hoc workloads', 1; RECONFIGURE; |
|
• Hohe runnable_tasks_count im Scheduler → CPU-Auslastung prüfen, teuerste Abfragen identifizieren → Kapitel 9 und 15 |
|
• Non-Yielding Scheduler → Error Log sichern, betroffene Abfrage/Datenbank identifizieren, ggf. Service neu starten (letzter Ausweg) |
|
• Hohe WRITELOG-Waits → Commit-Latenz messen, Log-Disk isolieren falls möglich, Delayed Durability evaluieren, wenn Datenverlust akzeptabel |
|
• Blocking-Ketten → sys.dm_tran_locks prüfen, Lock Escalation untersuchen, ggf. Transaktionen kürzen oder RCSI evaluieren |
Zusammenfassung
SQL Server ist keine Black Box — es ist ein System aus klar definierten Schichten, die alle zusammenarbeiten. Die Relational Engine nimmt SQL entgegen und erzeugt Ausführungspläne. Die Storage Engine liefert Daten, verwaltet Locks und garantiert Transaktionskonsistenz durch Write-Ahead Logging. Der Buffer Pool ist der Dreh- und Angelpunkt: Hier landen alle Datenpages, und seine Gesundheit lässt sich direkt an der PLE ablesen. Der SQLOS koordiniert alles — mit kooperativem Scheduling und NUMA-Awareness.
Neu hinzugekommen in diesem Kapitel: Das Verständnis der physischen Datenstruktur (Pages, Extents, IAM/GAM/SGAM) gibt dir den Hintergrund für Fragmentierungs- und TempDB-Probleme. Der Lock Manager erklärt, warum Gleichzeitigkeit manchmal schmerzt — und was Lock Escalation dabei anrichtet. Der Log Buffer und die Commit-Verarbeitung erklären, warum deine Log-Disk so wichtig ist und was WRITELOG-Waits bedeuten. Der Resource Governor zeigt, wie du Workloads voneinander trennst, bevor sie sich gegenseitig zerstören.
Das Wichtigste in Kürze:
Ausblick auf Kapitel 5
Jetzt wo du weißt, wie SQL Server intern funktioniert, ist es Zeit, diese Maschine richtig einzustellen. Kapitel 5 widmet sich der Serverkonfiguration — mit besonderem Augenmerk auf Max Server Memory, MAXDOP und Cost Threshold for Parallelism. Und natürlich auf die Einstellungen, die du besser nie anfasst — aber die erfahrungsgemäß trotzdem immer irgendwo aktiviert sind. Priority Boost zum Beispiel. Die Konfigurierung, die Sparfuchs bis heute aktiv hat. SQL 2008 hat sie als deprecated eingestuft. Manchmal braucht es halt eine Weile.
Kapitel 5