IO-Performance:

Wenn der Speicher nicht schläft, sondern schnarcht

IO ist nicht gleich IO — welches IO, welche Datei, welche Latenz?

In Kapitel 9 haben wir gesehen, dass PAGEIOLATCH_SH und WRITELOG die Wait-Typen sind, die auf IO-Probleme hinweisen. Jetzt gehen wir einen Schritt tiefer: Was verursacht diesen IO? Welche Datei? Welches Volume? Wie schlimm ist die Latenz wirklich — und ab wann sollte man in echte Panik geraten?

IO-Performance-Probleme haben typischerweise eine von drei Ursachen: Die Storage-Hardware ist zu langsam für die Workload, die Dateiverteilung ist suboptimal (alles auf einem Volume), oder SQL Server generiert mehr IO als nötig — fehlende Indizes, zu kleiner Buffer Pool, ein skalarer UDF der 360.000-mal pro Query aufgerufen wird. Die erste Aufgabe ist immer, zu verstehen welche Datei das Problem hat. Dann erst folgt die Lösung.

Der klassische Diagnosefehler: Jemand sieht PAGEIOLATCH-Waits und bestellt sofort neue Hardware. Drei Wochen später ist die neue NVMe-Karte verbaut — und es läuft immer noch langsam. Warum? Weil ein fehlender Index auf einer 50-Millionen-Zeilen-Tabelle jede noch so schnelle NVMe in die Knie zwingt, wenn er einen Full-Table-Scan auslöst. Erst messen, dann verstehen, dann handeln. Das gilt hier genauso wie in Kapitel 9.

Dieses Kapitel behandelt IO von der Diagnose bis zur Sanierung: Wir schauen uns die relevanten DMVs an, verstehen Latenz-Richtwerte und ihre Grenzen, sprechen über Autogrowth als stilles Drama, erklären Instant File Initialization, und enden mit der kompletten Horror-Show aus Sparfuchs & Partner — 312 ms Schreib-Latenz auf einer einzigen Spindel. Faktor 10.000 gegenüber NVMe. Die vollständige Fallstudie wartet in Kapitel 33.

sys.dm_io_virtual_file_stats: Wo kommt der IO her?

Die DMV sys.dm_io_virtual_file_stats ist die erste Anlaufstelle für IO-Diagnose. Sie zeigt pro Datenbankdatei kumulierte IO-Statistiken seit dem letzten Serverstart: Anzahl der Lese- und Schreiboperationen, Gesamtwartezeit in Millisekunden, und — das Wichtigste — io_stall_read_ms und io_stall_write_ms. Aus diesen Werten berechnen wir die durchschnittliche IO-Latenz pro Operation, und die ist die Zahl, die uns interessiert.

Die Formel ist simpel: Durchschnittliche Latenz = Stall-Zeit geteilt durch Anzahl Operationen. Wenn io_stall_read_ms = 45.000 und num_of_reads = 3.000, dann ist die durchschnittliche Lese-Latenz 15 ms. Das ist auf einer SSD bereits bedenklich; auf einer HDD noch tolerabel. Kontext zählt immer — aber Zahlen erst recht.

-- IO-Latenz pro Datenbankdatei — das wichtigste IO-Diagnose-Query

-- Kumuliert seit Serverstart: für echte Diagnose Snapshots differenzieren

-- (wie in Kapitel 9 gezeigt — gleiche Delta-Technik wie bei Wait Statistics)

SELECT

    DB_NAME(vfs.database_id)                    AS Datenbank,

    mf.physical_name                            AS DateiPfad,

    mf.type_desc                                AS DateiTyp,   -- ROWS = Datendatei, LOG = Transaktionslog

 

    -- Lese-Statistiken: wie viele Lesevorgänge, wie lange haben sie gewartet?

    vfs.num_of_reads                            AS Lesevorgaenge,

    vfs.io_stall_read_ms                        AS LeseStall_ms,

    -- Durchschnittliche Lese-Latenz: Stall geteilt durch Anzahl = ms pro Lesevorgang

    CASE WHEN vfs.num_of_reads > 0

         THEN vfs.io_stall_read_ms / vfs.num_of_reads

         ELSE 0 END                             AS AvgLeseLatenz_ms,

 

    -- Schreib-Statistiken: dasselbe Prinzip für Schreibvorgänge

    vfs.num_of_writes                           AS Schreibvorgaenge,

    vfs.io_stall_write_ms                       AS SchreibStall_ms,

    CASE WHEN vfs.num_of_writes > 0

         THEN vfs.io_stall_write_ms / vfs.num_of_writes

         ELSE 0 END                             AS AvgSchreibLatenz_ms,

 

    -- Gesamtlatenz über alle IO-Operationen: ergibt das "Gesamtbild" der Datei

    CASE WHEN (vfs.num_of_reads + vfs.num_of_writes) > 0

         THEN vfs.io_stall / (vfs.num_of_reads + vfs.num_of_writes)

         ELSE 0 END                             AS AvgGesamtLatenz_ms,

 

    -- Dateigröße auf Disk in MB — hilft beim Einordnen der absoluten IO-Menge

    vfs.size_on_disk_bytes / 1048576            AS DateiGroesse_MB  -- Größe in MB

 

FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs

JOIN sys.master_files mf

    ON mf.database_id = vfs.database_id

    AND mf.file_id    = vfs.file_id

 

-- Nur Dateien mit nennenswertem IO — sonst rauscht das Ergebnis mit idle-Dateien voll

WHERE (vfs.num_of_reads + vfs.num_of_writes) > 100

 

-- Schlechteste Schreib-Latenz oben — dort fängt die Diagnose an

ORDER BY AvgSchreibLatenz_ms DESC;

Diese Query zeigt sofort, welche Datenbankdatei das IO-Bottleneck ist. Typischerweise ist es entweder die Transaktionslog-Datei (wenn WRITELOG in den Wait Stats hoch ist) oder eine Datendatei (wenn PAGEIOLATCH_SH dominiert). In seltenen Fällen ist TempDB der Täter — dazu mehr in Kapitel 13. Die erste Zahl, die du prüfst, ist immer die Log-Datei-Schreib-Latenz: Sie ist der direkte Kostenfaktor für jeden COMMIT in der Datenbank.

 

Hinweis: Kumuliert seit Serverstart — immer Deltas nehmen
Wie sys.dm_os_wait_stats sind auch die Werte in sys.dm_io_virtual_file_stats kumulativ seit dem letzten Serverstart oder seit dem letzten manuellen Reset. Für echte Diagnose: Snapshot zu Beginn der Messung, Snapshot am Ende, Differenz bilden. Oder du nutzt direkt die Delta-Technik aus Kapitel 9 — das Prinzip ist identisch. Absolute Werte ohne Zeitbezug sagen dir wenig über den aktuellen Zustand.

 

 

Abb. 10.1: IO-Latenz-Richtwerte nach Storage-Technologie — Grenzwerte für Diagnose und Planung

Latenzen interpretieren: Was ist gut, was ist schlecht?

Die Latenz-Richtwerte in der folgenden Tabelle sind Anhaltspunkte, keine absoluten Wahrheiten. Ein System mit vorwiegend sequentiellem IO — Data-Warehouse-Queries, Reporting, nächtliche Ladeläufe — toleriert höhere Latenzen als ein OLTP-System mit tausenden kurzer Transaktionen pro Sekunde. Kontext ist alles. Aber auch mit Kontext: 312 ms ist immer schlecht.

 

Storage-Typ	Lese-Latenz (gut)	Schreib-Latenz (gut)	Alarm ab	Typisches Szenario
NVMe PCIe Gen4	< 0,2 ms	< 0,3 ms	> 1 ms	Hochleistungs-OLTP, TempDB, Log-Volumes
SATA/SAS SSD	< 1 ms	< 2 ms	> 5 ms	Standard-Datenbankserver, Data-Volumes
15k SAS HDD (RAID 10)	< 15 ms	< 10 ms	> 30 ms	Legacy, mit Write-Cache BBU
7,2k SATA HDD	< 25 ms	< 20 ms	> 50 ms	Backup, Archiv — niemals für DB!
RAID 5 (HDD)	< 20 ms	< 30 ms	> 40 ms	Niemals für Transaktionslogs
SAN (Fibre Channel)	< 5 ms	< 5 ms	> 20 ms	Stark abhängig von SAN-Konfiguration

Tabelle 10.1: IO-Latenz-Richtwerte (Durchschnittswerte pro IO-Operation)

 

Eine wichtige Ergänzung zu dieser Tabelle: Die Werte gelten für isolierte Tests ohne Last. Unter Produktionslast mit gleichzeitigem Backup, Checkpoint-IO und vielen aktiven Verbindungen verschlechtert sich die gemessene Latenz typischerweise um Faktor 2 bis 5. Wenn dein Storage im isolierten Test p99 von 2 ms schafft, kalkuliere im schlimmsten Fall 10 ms unter Last. Das ist der Wert, der in deine Kapazitätsplanung einfließen sollte.

Die Latenz-Schwellwerte < 5 ms / 5–20 ms / > 20 ms als grobe Daumenregel: Unter 5 ms gibt es in der Regel keinen Handlungsbedarf. Zwischen 5 und 20 ms lohnt die genauere Analyse — vielleicht ist das für den Workload in Ordnung, vielleicht nicht. Über 20 ms ist immer auffällig und rechtfertigt eine tiefere Untersuchung, besonders für Log-Dateien.

RAID 5 und SQL Server: Eine toxische Beziehung

RAID 5 taucht in der Praxis noch erstaunlich häufig auf — oft aus historischen Gründen: Günstig in der Anschaffung, gute Lese-Performance, Redundanz durch Parität. Klingt vernünftig. Für SQL Server Transaktionslogs ist es trotzdem eine toxische Kombination, und der Grund ist der sogenannte Write Penalty.

Bei RAID 5 wird für jeden Schreibvorgang eine Paritätsinformation berechnet und auf einem separaten Disk-Stripe gespeichert. Um die neue Parität zu berechnen, muss zuerst der alte Datenblock gelesen, dann die alte Parität gelesen werden — erst dann kann die neue Parität berechnet und zusammen mit den neuen Daten geschrieben werden. Aus einer einzelnen Schreiboperation werden so vier IO-Operationen: Read-Modify-Write für Daten plus Parität.

 

Warnung: RAID 5 und schreibintensive Workloads — eine toxische Beziehung

RAID 5 hat bei Schreiboperationen einen Write Penalty von Faktor 4: Für jeden einzelnen Schreibvorgang entstehen intern vier IO-Operationen (alter Datenblock lesen, alte Parität lesen, neue Daten schreiben, neue Parität schreiben). SQL Server schreibt Log-Einträge sequentiell, häufig und synchron — jeder COMMIT wartet auf den Log-Flush. Das Ergebnis: WRITELOG-Wait-Zeit 3 bis 5 Mal höher als auf RAID 10. Für Datendateien mit überwiegend Lese-Workload ist RAID 5 tolerierbar. Für Transaktionslogs ist RAID 10 Pflicht — oder eine einzelne schnelle SSD auf einem dedizierten Controller.

 

Der Unterschied in der Praxis: Ein typischer OLTP-Server mit 500 Transaktionen pro Sekunde erzeugt auf einem RAID-10-Log-Volume eine WRITELOG-Wartezeit von 0,5 bis 2 ms pro Commit. Das gleiche System auf RAID 5: 3 bis 8 ms. Auf einer ausgelasteten HDD mit RAID 5 kann der Wert in Spitzenzeiten auf 30 bis 50 ms steigen — und das spüren die Nutzer als Einfrieren der Anwendung, weil jede Transaktion auf den synchronen Log-Flush wartet.

Baseline für IO: Was ist normal für diesen Server?

Wie für Wait Statistics in Kapitel 9 gilt auch für IO-Latenzen: Ohne Baseline ist jede Diagnose Rätselraten mit ernstem Gesicht. 8 ms Schreib-Latenz — ist das schlimm? Kommt drauf an. Wenn der Server normalerweise 1 ms hat, ist das dramatisch. Wenn er immer auf 6 ms war und jetzt 8 ms zeigt, ist es ein leichter Anstieg. Ohne Vergleichswert fehlt der Kontext.

Eine IO-Baseline für sys.dm_io_virtual_file_stats erhebt man am besten mit einem periodischen Snapshot-Job — zum Beispiel alle 15 Minuten — der die Differenz zur letzten Messung berechnet und speichert. Das ergibt über Zeit ein Muster: Wie sind die Latenzen morgens um 8 Uhr bei Login-Sturm? Wie mittags beim Reporting? Wie nachts beim Backup? Dieses Muster ist die Baseline.

-- IO-Baseline als Snapshot speichern — diese Query alle 15 Minuten per SQL Agent Job ausführen

-- Ziel: Trendlinie über Zeit aufbauen, nicht nur Momentaufnahmen betrachten

-- Die Tabelle DBAMonitor.dbo.IO_Snapshots muss vorab angelegt werden

INSERT INTO DBAMonitor.dbo.IO_Snapshots

    (SnapshotTime, DatabaseName, DateiPfad, DateiTyp,

     Reads, ReadStall_ms, Writes, WriteStall_ms, TotalStall_ms)

SELECT

    GETDATE()                                           AS SnapshotTime,

    DB_NAME(vfs.database_id)                           AS DatabaseName,

    mf.physical_name                                   AS DateiPfad,

    mf.type_desc                                       AS DateiTyp,

    -- Kumulierte Werte — die Differenz zwischen zwei Snapshots ist der Intervallwert

    vfs.num_of_reads, vfs.io_stall_read_ms,

    vfs.num_of_writes, vfs.io_stall_write_ms,

    vfs.io_stall

FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs

JOIN sys.master_files mf

    ON mf.database_id = vfs.database_id

    AND mf.file_id    = vfs.file_id;

Das Collect-SqlPerf.ps1-Script in Kapitel 31 automatisiert genau diese Baseline-Erhebung — inklusive IO-Snapshots, Wait Statistics und vielem mehr. Für die manuelle Diagnose reicht der oben gezeigte Insert-Job. Wichtig ist dabei das Tageszeiten-Muster: IO-Latenzen morgens um 9 Uhr (viele Logins, Berichte laufen an) unterscheiden sich typischerweise deutlich von den Werten nachts um 2 Uhr (Backup, Wartungsfenster). Beides zu kennen ist die Baseline.

 

Tipp: Tageszeiten-Muster als Diagnosewerkzeug

Wenn du eine IO-Baseline über mehrere Tage aufbaust, fallen saisonale Muster auf: Montagmorgen-Spitze, Monatsende-Reports, wöchentliche Rebuild-Jobs. Diese Muster helfen bei der Diagnose: Wenn Nutzer sich immer dienstags um 10 Uhr über langsame Berichte beschweren, prüfe erst die IO-Baseline für dienstags 10 Uhr — oft läuft dann ein konkurrierender Job. Musterwerk GmbH hatte genau dieses Problem: Wöchentlicher Index-Rebuild und Montagsreporting überlappten sich — die Lösung war ein verschobenes Wartungsfenster, keine neue Hardware.

 

Autogrowth-Events: Das stille IO-Drama

Wenn eine SQL Server Datenbankdatei ihren konfigurierten Speicherplatz ausschöpft, wächst sie automatisch — durch Autogrowth. Klingt praktisch. Ist es in der Praxis aber oft ein Performance-Problem, das sich regelmäßig wiederholt, weil niemand die Dateigrößen vorab plant. Das Default-Autogrowth-Setting von früher: 1 MB für Datendateien, 10% für Logs. Beides ist für produktive Datenbanken ungeeignet.

Ein Autogrowth-Ereignis blockiert IO für die Dauer des Wachstums. Wenn Instant File Initialization (IFI) aktiv ist — dazu gleich mehr — dauert das Wachstum einer Datendatei nur Millisekunden. Wenn IFI nicht aktiv ist, muss SQL Server die neue Dateifläche mit Nullen füllen. Das dauert bei 1 GB Wachstum auf einer HDD mehrere Sekunden, in denen der IO für alle anderen Datenbanken auf diesem Volume gebremst wird.

Bei Sparfuchs & Partner (Kapitel 33) hatten wir 847 Autogrowth-Events in 120 Minuten — bei 1 MB Autogrowth-Konfiguration und ohne IFI. Das ist kein Wachstum, das ist ein Systemversagen im Dauerzustand. Die Datenbank wuchs im Minutentakt und blockierte dabei jedes Mal den gesamten IO auf der einzigen vorhandenen Spindel. IO-Latenz p95 bei Schreibvorgängen: 312 ms. Auf NVMe wären das 0,03 ms — Faktor 10.000.

-- Autogrowth-Events aus dem Default Trace lesen

-- Der Default Trace läuft standardmäßig und speichert u.a. Autogrowth-Ereignisse

-- Wichtig: der Trace rollt über — für langfristiges Tracking Extended Events nutzen

SELECT

    te.name                         AS Ereignistyp,

    t.DatabaseName,

    t.Filename,

    t.StartTime,

    t.EndTime,

    -- Dauer des Autogrowth-Ereignisses in ms — das ist der IO-Stall für alle anderen!

    t.Duration                      AS Dauer_ms,

    -- Wie viel wurde gewachsen? IntegerData in 8-KB-Seiten → umrechnen in MB

    t.IntegerData * 8 / 1024        AS Wachstum_MB

FROM sys.fn_trace_gettable(

    -- Pfad zum aktiven Default-Trace — variiert, daher dynamisch aus sys.traces lesen

    (SELECT path FROM sys.traces WHERE is_default = 1),

    DEFAULT

) t

JOIN sys.trace_events te ON te.trace_event_id = t.EventClass

-- EventClass 92 = Data File Auto Grow, 93 = Data File Auto Shrink

-- EventClass 94 = Log File Auto Grow, 95 = Log File Auto Shrink

WHERE te.name LIKE '%Auto%'

ORDER BY t.StartTime DESC;

Autogrowth dauerhaft überwachen mit Extended Events

Der Default Trace ist praktisch, aber begrenzt — er rollt über und vergisst ältere Ereignisse. Für systematische Überwachung nutzt man eine Extended-Events-Session:

-- Extended Events Session für dauerhaftes Autogrowth-Monitoring

-- Diese Session einmal anlegen und permanent laufen lassen

-- Daten landen im Ring Buffer — regelmäßig auslesen und wegschreiben

CREATE EVENT SESSION [Autogrowth_Monitor] ON SERVER

ADD EVENT sqlserver.database_file_size_change(

    -- Nur echte Wachstums-Events überwachen, keine manuellen Größenänderungen

    WHERE is_auto = 1

)

ADD TARGET package0.ring_buffer(

    -- 10 MB Ring Buffer — reicht für mehrere Stunden Autogrowth-Ereignisse

    SET max_memory = 10240

)

WITH (MAX_DISPATCH_LATENCY = 30 SECONDS);

 

-- Session starten

ALTER EVENT SESSION [Autogrowth_Monitor] ON SERVER STATE = START;

 

-- Hinweis: Session überlebt keinen Serverneustart ohne WITH (STARTUP_STATE = ON)

-- ALTER EVENT SESSION [Autogrowth_Monitor] ON SERVER

-- WITH (STARTUP_STATE = ON);

Die richtige Lösung für Autogrowth-Probleme ist nicht besseres Monitoring — es ist Vorallokation. Schätze die Datenbankgröße für die nächsten 6 bis 12 Monate und allokiere entsprechend vorab. Wenn du nicht weißt wie groß die Datenbank wachsen wird: Nimm die aktuelle Größe, multipliziere mit 1,5 und weise das als initiale Dateigröße zu. Dann regelmäßig prüfen und bei Bedarf manuell wachsen lassen — planbar und ohne IO-Stall.

Instant File Initialization: Nullen schreiben ist teuer

Wenn SQL Server eine neue Datenbankdatei erstellt oder eine bestehende durch Autogrowth vergrößert, muss der neu belegte Speicherplatz initialisiert werden. Ohne Instant File Initialization (IFI) passiert das durch Nullen schreiben — die gesamte neue Fläche wird sequentiell mit 0x00 gefüllt. Bei 10 GB Wachstum auf einer langsamen HDD bedeutet das: mehrere Minuten IO-Stopp für alle anderen Operationen auf diesem Volume.

IFI überspringt diesen Schritt für Datendateien (MDF, NDF). SQL Server kann den Speicherplatz sofort nutzen, ohne ihn zu nullen. Das funktioniert, weil Windows NTFS dem SQL Server-Dienstkonto die Berechtigung SE_MANAGE_VOLUME_NAME geben kann — "Perform volume maintenance tasks". Mit dieser Berechtigung darf das Dienstkonto Dateien anlegen, ohne zuvor alle Sektoren zu überschreiben.

-- Ist Instant File Initialization aktiv?

-- SQL Server schreibt den Status beim Dienststart ins Error Log

-- Suche nach "Database Instant File Initialization: enabled" oder "disabled"

EXEC xp_readerrorlog 0, 1, 'Instant File Initialization';

 

-- Alternativ: seit SQL Server 2016 auch direkt über sys.dm_server_services prüfbar

-- Der Wert instant_file_initialization_enabled zeigt TRUE oder FALSE

SELECT

    servicename,

    instant_file_initialization_enabled

FROM sys.dm_server_services

WHERE servicename LIKE 'SQL Server (%';

IFI aktivieren ist eine der wenigen wirklich kostenlosen Performance-Verbesserungen: Dem SQL Server-Dienstkonto in den lokalen Sicherheitsrichtlinien (secpol.msc) unter "Local Policies → User Rights Assignment" das Recht "Perform volume maintenance tasks" zuweisen. Danach SQL Server-Dienst neu starten. Das Error Log bestätigt dann "Database Instant File Initialization: enabled". Effekt sofort spürbar bei nächsten Autogrowth-Ereignissen oder Datenbankrestores.

 

Warnung: IFI gilt NICHT für Transaktionslogs

Ein häufiges Missverständnis: IFI beschleunigt Datendateien, aber Transaktionslogs werden immer mit Nullen initialisiert — absichtlich. Log-Dateien müssen aus Sicherheitsgründen vollständig initialisiert werden, weil ältere Log-Sektoren niemals mit "zufälligen" Festplatteninhalten überschrieben werden dürfen. SQL Server garantiert, dass kein früherer Log-Inhalt durch ein überlaufendes Log lesbar wird. Konsequenz: TempDB und Datendateien profitieren stark von IFI. Log-Dateien wachsen immer mit IO-Stall. Deshalb: Log-Dateien immer vorab auf die erwartete Größe allokieren und das Wachstum proaktiv steuern.

 

Ein häufig übersehener IFI-Vorteil: Datenbankrestores werden erheblich schneller. Wenn SQL Server beim Restore neue Datenbankdateien anlegt, muss er ohne IFI zuerst alle Nullen schreiben, bevor die eigentlichen Daten eingespielt werden. Mit IFI beginnt das Einlesen der Backup-Daten sofort. Bei einem 100-GB-Datenbank-Restore auf einer SSD kann das den Unterschied zwischen 20 Minuten und 35 Minuten ausmachen — und im Notfall zählt jede Minute.

NTFS Allocation Unit Size: Der vergessene Tuning-Parameter

Windows formatiert NTFS standardmäßig mit einer Allocation Unit Size von 4 KB. SQL Server arbeitet intern mit 8-KB-Seiten. Das klingt nach einer Kleinigkeit — ist aber im IO-Stack relevant: Wenn eine 8-KB-Datenbankseite geschrieben wird, müssen bei 4-KB-Allocation-Units zwei NTFS-Cluster beschrieben werden. Bei 64-KB-Allocation Units passt eine SQL-Server-Seite bequem in einen Cluster; der IO-Overhead reduziert sich.

-- NTFS Allocation Unit Size prüfen — über xp_cmdshell oder PowerShell

-- Voraussetzung: xp_cmdshell ist aktiviert oder PowerShell-Zugriff vorhanden

 

-- Über fsutil (erfordert xp_cmdshell oder externe Ausführung)

-- EXEC xp_cmdshell 'fsutil fsinfo ntfsinfo D:';

-- Suche nach "Bytes Per Cluster" — sollte 65536 für SQL Server Data Volumes sein

 

-- Alternativ per PowerShell (bevorzugt, kein xp_cmdshell nötig):

-- Get-Volume -DriveLetter D | Select-Object DriveLetter, AllocationUnitSize

 

-- Hinweis: AllocationUnitSize kann NUR beim Formatieren gesetzt werden

-- Bestehende Volumes: Backup erstellen, Volume neu formatieren, Daten zurückschreiben

-- Empfehlung: 64 KB für Data-Volumes, 4 KB für Log-Volumes (Log-IO ist klein und sequentiell)

Achtung: Die Allocation Unit Size kann nicht im laufenden Betrieb geändert werden. Das Volume muss neu formatiert werden — was ein Backup, eine Neuformatierung und einen Restore bedeutet. Für neue Server und neue Volumes: Direkt mit 64 KB formatieren. Für bestehende Server: Im nächsten geplanten Wartungsfenster angehen. Die Performance-Verbesserung ist messbar, aber nicht spektakulär — sie gehört in die Kategorie "richtig machen von Anfang an".

Read-Ahead: SQL Server hilft sich selbst

SQL Server ist kein naiver IO-Konsument — er versucht, IO-Latenzen durch Read-Ahead zu kompensieren. Wenn SQL Server erkennt, dass eine Abfrage sequentiell über viele Seiten einer Tabelle iteriert — Table Scan, Clustered Index Scan, Range Scan über viele Zeilen — fängt er an, Seiten schon zu laden, bevor die Abfrage sie anfordert. Das ist Read-Ahead, auch Sequential Prefetch genannt.

Read-Ahead funktioniert gut für sequentielle Workloads: Data-Warehouse-Queries mit großen Tabellen-Scans, Reporting-Abfragen, DBCC CHECKDB, Index-Rebuilds. Für OLTP-Workloads mit Random-IO — Einzelzugriffe über Primary Key, kleine Suchabfragen — ist Read-Ahead weniger effektiv, weil die nächste benötigte Seite nicht vorhersagbar ist.

Wenn du PAGEIOLATCH-Waits siehst, kann das deshalb zwei sehr verschiedene Ursachen haben: Entweder ist der sequentielle IO schlicht zu groß für das verfügbare Storage — Kapazitätsproblem, löst sich nur mit besserer Hardware oder kleinerem Result Set. Oder es gibt unnötigen Random-IO, weil ein Index fehlt und statt eines Index Seeks ein Table Scan durchgeführt wird — Optimierungsproblem, löst sich mit dem richtigen Index. sys.dm_io_virtual_file_stats zeigt dir das Volumen, nicht die Art des IO. Die Art des IO erkennst du im Ausführungsplan — mehr dazu in Kapitel 15.

 

Hintergrund: Scatter-Gather IO: Wie Read-Ahead intern funktioniert

SQL Server nutzt für Read-Ahead Scatter-Gather IO — eine Windows-API die es erlaubt, mehrere nicht zusammenhängende Speicherbereiche in einem einzigen Systemaufruf zu lesen oder zu schreiben. Das reduziert den Overhead durch weniger Systemaufrufe. Bei einem Table Scan liest SQL Server typischerweise 64 Seiten (512 KB) in einem Schwung vor — statt 64 einzelner 8-KB-IO-Anfragen eine einzige 512-KB-Anfrage. Moderne SSDs und NVMe profitieren davon weniger als HDDs, weil ihre Latenz pro Operation so gering ist. Aber der reduzierte CPU-Overhead durch weniger Systemaufrufe ist auch auf SSDs messbar.

 

IO-Muster: Sequentiell vs. Random — wer braucht was?

SQL Server erzeugt je nach Workload sehr unterschiedliche IO-Muster. Das Verständnis dieser Muster hilft bei der Storage-Auswahl und bei der Diagnose: Ist die Latenz hoch, weil das Storage grundsätzlich zu langsam ist — oder, weil das falsche IO-Muster auf ein für dieses Muster ungeeignetes Storage trifft?

 

IO-Muster	Wer erzeugt es	Optimales Storage	HDD-tauglich?
Sequentiell groß (> 512 KB)	Table Scans, Backup, Index Rebuild, DBCC	SSD, HDD (mit Cache)	Ja, eingeschränkt
Random klein (8–64 KB)	OLTP-Abfragen, Buffer Pool Page Reads/Writes	NVMe, SSD	Nein
Sequentiell klein (< 64 KB)	Log-Writes (WRITELOG), Checkpoint-Einzelseiten	NVMe für niedrigste Latenz	Eingeschränkt
Mixed (zufällig, variabel)	TempDB (Sorts, Hash Joins, Row Versioning)	NVMe, schnellste SSD	Nein

Tabelle 10.2: IO-Muster und geeignete Storage-Technologien

 

OLTP-Workloads erzeugen primär random-kleines IO — viele kurze Lesezugriffe auf einzelne 8-KB-Seiten verteilt über die gesamte Datenbank. Dieses Muster ist das Todesurteil für mechanische Festplatten: Eine 7,2k-SATA-HDD schafft etwa 100 bis 150 random-IOPS. Ein SQL Server mit 200 aktiven Verbindungen und 1.000 kurzen Transaktionen pro Sekunde benötigt leicht 5.000 bis 20.000 IOPS. Das passt nicht zusammen — eine NVMe schafft problemlos 500.000 bis 1.000.000 IOPS. Das ist der Unterschied, den du in der Latenz siehst.

Live-Diagnose mit sys.dm_io_pending_io_requests

sys.dm_io_virtual_file_stats zeigt historische Durchschnittswerte — gut für Trends, aber nicht für die Frage "Was passiert gerade jetzt?". Wenn du während eines akuten Performance-Problems siehst was genau auf IO wartet, ist sys.dm_io_pending_io_requests die richtige DMV:

-- Aktuelle ausstehende IO-Anfragen — Echtzeit-Diagnose bei akutem IO-Problem

-- Wenn diese View dauerhaft viele Einträge zeigt, ist das IO-Subsystem überlastet

SELECT

    io_type,              -- 'read' oder 'write' — hilft beim Eingrenzen des Problems

    io_pending,           -- 1 = der IO ist noch nicht abgeschlossen

    io_pending_ms_ticks,  -- wie lange wartet dieser IO schon? (in ms-Ticks)

    scheduler_address,    -- welcher Scheduler wartet — für Korrelation mit sys.dm_os_schedulers

    io_handle             -- Handle auf die Datei — für weitere Zuordnung nutzbar

FROM sys.dm_io_pending_io_requests

WHERE io_pending = 1       -- nur tatsächlich wartende Requests

ORDER BY io_pending_ms_ticks DESC;  -- älteste wartende Requests oben

Wenn in dieser View dauerhaft viele Einträge mit hohen io_pending_ms_ticks auftauchen, ist das IO-Subsystem in Echtzeit überlastet. Das ist kein kumulierter Durchschnitt der Vergangenheit — das ist der aktuelle Zustand. Ein gut dimensioniertes IO-System zeigt hier selten mehr als 5 bis 10 gleichzeitig wartende Requests. Mehr als 20 bis 30 gleichzeitige pending IO-Requests auf demselben Volume sind ein klares Signal für IO-Sättigung.

DiskSpd: Die physische IO-Kapazität messen

In Kapitel 2 haben wir DiskSpd als Tool für Storage-Benchmarking kennengelernt. Hier verbinden wir das mit konkreten SQL Server IO-Mustern: Wie viel kann das Storage wirklich leisten — und reicht das für die geplante Workload?

Die wichtigste Erkenntnis aus Kapitel 2: DiskSpd misst die rohe IO-Kapazität unter Last. Das Ergebnis ist die Obergrenze, die SQL Server unter keinen Umständen überschreiten kann. Wenn DiskSpd bei 8-KB-Random-Writes 50.000 IOPS misst, ist das die Decke. SQL Server mit 80.000 IOPS Bedarf wird auf diesem Storage immer leiden.

-- DiskSpd-Aufruf für SQL Server OLTP-IO-Simulation

-- Ausführen in einer CMD oder PowerShell auf dem SQL Server (nicht in T-SQL)

-- Tool-Download: https://github.com/microsoft/diskspd

 

-- OLTP-typisches Muster: 8 KB Blockgröße, 70% Read / 30% Write, zufällige Zugriffe

-- diskspd.exe -b8K -d60 -r -t8 -o32 -w30 -L D:SQLDataestfile.dat

 

-- Parameter-Erklärung:

-- -b8K    Blockgröße 8 KB (SQL Server Seitengröße)

-- -d60    Testdauer 60 Sekunden

-- -r      Random IO (nicht sequentiell — OLTP-Muster)

-- -t8     8 Threads (simuliert 8 parallele Verbindungen)

-- -o32    32 ausstehende IO-Anfragen pro Thread (Queue Depth 32)

-- -w30    30% Schreibanteil (OLTP-typisch)

-- -L      Latenz-Histogramm ausgeben (wichtig! zeigt p50, p90, p99, p999)

 

-- Ausgabe interpretieren:

-- IOPS > 50.000 für OLTP → gut für mittlere Last

-- Latenz p99 < 1 ms → exzellent (NVMe-Niveau)

-- Latenz p99 < 5 ms → akzeptabel (SSD)

-- Latenz p99 > 20 ms → problematisch für OLTP

Das Latenz-Histogramm in der DiskSpd-Ausgabe (-L Flag) ist besonders wertvoll: Durchschnittswerte verschleiern Ausreißer. Eine SSD kann p50-Latenz von 0,5 ms haben aber p99-Latenz von 8 ms — das bedeutet, 1% aller IO-Operationen warten 8 ms oder länger. Bei 10.000 IOPS sind das 100 Operationen pro Sekunde mit hoher Latenz. In einer OLTP-Anwendung mit kurzen Transaktionen ist das der Unterschied zwischen 50 ms und 200 ms Response Time für die betroffenen Requests.

SAN, NAS und virtuelle Storage: Engpässe die sich tarnen

In virtualisierten Umgebungen und auf SAN/NAS-Storage gilt besondere Vorsicht: Das, was SQL Server als IO-Latenz misst, ist nicht notwendigerweise das, was am physischen Datenträger passiert. Zwischen SQL Server und dem Bit auf der Festplatte liegen oft: Hypervisor-IO-Stack, virtuelle Disk-Controller, SAN-Fabric, SAN-Controller, Cache-Tier, Storage-Pools mit automatischem Tiering. Jede dieser Schichten kann eine Latenzquelle sein.

Thin Provisioning ist eine der häufigsten Ursachen für überraschende IO-Performance-Einbrüche: Das Volume ist dünn allokiert — der erste Schreibzugriff auf einen neuen Block erfordert eine echte Allokation auf dem Storage-Array. Dieser erste Schreibzugriff ist erheblich langsamer als alle folgenden. Wenn SQL Server eine neue Datenbankdatei anlegt oder stark durch Autogrowth wächst, kann das auf Thin-Provisioned Storage zu drastisch erhöhten Latenzen führen — die sich nach dem ersten Wachstumszyklus normalisieren.

 

Warnung: SAN-Engpässe tarnen sich als SQL Server Probleme

Ein häufiges Diagnoseproblem in Unternehmensumgebungen: sys.dm_io_virtual_file_stats zeigt hohe Latenzen, aber der SAN-Administrator besteht darauf, das Array sei nicht ausgelastet. Wer hat Recht? Beide können Recht haben — und trotzdem hohe SQL-Latenzen vorliegen. Ursachen die das SAN nicht direkt zeigt: Storage-Tiering das gerade migrätion durchführt, Thin-Provisioning Allokationen, Deduplizierung unter Last, geteilte SAN-Ports mit anderen hochlastigen VMs. Lösung: Storage-Latenz direkt am SAN-Controller messen, nicht nur die Queue-Tiefen. DiskSpd direkt auf dem Volume ausführen — wenn DiskSpd dieselben hohen Latenzen zeigt wie SQL Server, liegt das Problem nicht in SQL Server sondern im Storage-Stack.

 

Storage-Tiering — automatisches Verschieben von Daten zwischen schnellen und langsamen Tiers basierend auf Zugriffshäufigkeit — ist ein weiteres Stolperfallmuster. SQL Server greift auf Daten in vorhersehbaren Mustern zu, aber der Tage kann sich verändert: Eine Tabelle die monatelang kaum gelesen wurde liegt jetzt auf langsamem Tier — und ein neuer Report der täglich auf sie zugreift führt zu schlechter IO-Performance bis das Tiering die Daten "promoviert" hat. Das kann Stunden dauern.

Dateiverteilung: Wer teilt, der regiert

 

Abb. 10.2: Optimale IO-Dateiverteilung auf separate Volumes — was gehört wohin

Die einfachste und wirkungsvollste IO-Optimierung ist eine vernünftige Dateiverteilung. Wenn Datendateien, Transaktionslog, TempDB und Betriebssystem auf verschiedenen Volumes liegen, können sie unabhängig voneinander IO ausführen. Wenn alles auf einem Volume liegt, konkurrieren sie um dieselbe Bandbreite und denselben IO-Controller — genau das war das Problem bei Sparfuchs: eine einzige Spindel für alles.

 

Volume	Inhalt	Storage-Empfehlung	Begründung
C:\ oder OS-Drive	Windows, SQL Binaries, Error Log	SSD	Kein Engpass; kein Produktions-IO
D:\ Data	Datenbankdateien (MDF, NDF)	SSD oder NVMe	Random-Read IO; ausreichend IOPS
L:\ Log	Transaktionslogs (LDF)	SSD oder NVMe	Sequentielles Schreiben; eigenes Volume Pflicht
T:\ TempDB	TempDB (alle Dateien)	NVMe — schnellstes Medium	Latenz-sensitiv; jede Abfrage berührt TempDB
B:\ Backup	Backup-Dateien	HDD oder NAS	Großes sequentielles IO; günstigeres Medium OK

Tabelle 10.3: Empfohlenes Volume-Layout für SQL Server

 

Das Log-Volume verdient besondere Aufmerksamkeit: Transaktionslog-IO ist sequentiell. SQL Server schreibt Log-Einträge hintereinander, ohne Random-IO. Sequentielles IO ist das, was HDDs noch tolerabel hinbekommen — und das, was SSDs mit sehr niedrigen Latenzen beherrschen. Deshalb ist das Log-Volume der Ort, wo selbst eine mittelklassige SSD auf einem dedizierten Controller ausreicht. Vorausgesetzt: kein anderer konkurrierender IO auf demselben Volume.

 

Tipp: TempDB bekommt das schnellste verfügbare Medium

TempDB erhält immer das schnellste Storage, das verfügbar ist — idealerweise NVMe. TempDB-IO ist extrem latenz-sensitiv: Sortierungen, Hash Joins, temporäre Tabellen, Row Versioning, Spill-to-Disk bei zu kleinen Memory Grants — alles landet in TempDB. TempDB-Latenz ist direkter Durchsatzverlust für alle Abfragen die TempDB nutzen, und das sind in einem typischen System die meisten. Mehr zur TempDB-Konfiguration — Dateianzahl, Vorab-Größe, Contention-Diagnose — in Kapitel 13.

 

Fallstudie: Sparfuchs & Partner — 312 ms Write-Latenz

Kein IO-Kapitel wäre vollständig ohne den Blick auf das, was passiert, wenn alle schlechten Entscheidungen gleichzeitig getroffen werden. Bei Sparfuchs & Partner (BUCHSQL01, vollständige Fallstudie in Kapitel 33) maßen wir p95-Schreib-Latenzen von 312 ms. Zum Vergleich: Eine moderne NVMe-SSD liefert p95-Schreib-Latenzen von 0,03 bis 0,1 ms. Das ist ein Faktor von 3.000 bis 10.000 — auf einem produktiven SQL Server, mit echten Nutzern, mit echten Buchungsdaten.

Die sys.dm_io_virtual_file_stats Query auf BUCHSQL01 lieferte folgende Ergebnisse:

 

Datei	Typ	Avg Lese-Latenz	Avg Schreib-Latenz	Bewertung
BUCHSQL01_Data.mdf	ROWS	94 ms	287 ms	Kritisch
BUCHSQL01_Log.ldf	LOG	78 ms	312 ms	Katastrophal
tempdb.mdf	ROWS	103 ms	298 ms	Kritisch

Tabelle 10.4: sys.dm_io_virtual_file_stats Ergebnis auf BUCHSQL01 (Sparfuchs & Partner)

 

Die Ursachen in komprimierter Form — der vollständige Sanierungsplan wartet in Kapitel 33:

Eine einzige 7,2k-SATA-HDD für OS + SQL Server Binaries + alle Datenbankdateien + Transaktionslogs + TempDB + Backups.

Autogrowth auf 1 MB konfiguriert — 847 Events in 120 Minuten, jedes mit Null-Fill (IFI deaktiviert).

Transaktionslog 183 GB bei 24 GB Datenbankgröße — kein einziges Log-Backup je gemacht, VLF-Zahl: 48.312.

TempDB auf dem gleichen Volume wie alles andere — konkurriert um dieselbe Spindel.

Keine RAID-Konfiguration — eine einzige Spindel ohne jede Redundanz und ohne Write-Cache.

IFI deaktiviert — jedes der 847 Autogrowth-Events füllte den neu allokierten Platz mit Nullen.

Wenn du fragst, wie Nutzer damit überhaupt gearbeitet haben: Schlecht. Sehr schlecht. Die Beschwerden kamen täglich. Der SQL Server war nicht ausgefallen — er arbeitete nur mit der Geschwindigkeit von Schmelzkäse. In Kapitel 33 zeigen wir den vollständigen Sanierungsplan und wie man Prioritäten setzt, wenn man nicht alles gleichzeitig reparieren kann. Spoiler: IFI aktivieren und Autogrowth auf vernünftige Werte setzen kostet nichts und bringt sofortige Verbesserung.

Diagnose: IO-Performance

Kasten 1: Symptome

 

Hinweis: Woran erkennst du IO-Probleme?
PAGEIOLATCH_SH oder PAGEIOLATCH_EX als dominanter Wait Type in sys.dm_os_wait_stats (Kapitel 9).
WRITELOG-Waits prominent — direktes Signal für Log-IO als Bottleneck.
Nutzer melden: "Es hängt kurz, dann geht es wieder" — klassisches IO-Stottermuster bei Autogrowth.
SQL Server Error Log: "SQL Server has encountered I/O requests taking longer than 15 seconds to complete" — das ist ein ernst zu nehmendes Warning.
sys.dm_io_virtual_file_stats: durchschnittliche Schreib-Latenz für Log-Datei > 20 ms.
Windows Performance Monitor: "PhysicalDisk: Avg. Disk Queue Length" dauerhaft > 2.
Autogrowth-Events im Default Trace häufiger als 5 pro Tag — Zeichen für Unterallokation.
TempDB und Datenbankdateien auf demselben Volume — strukturelles Risiko auch ohne akute Probleme.

 

Kasten 2: So misst du das

 

Tipp: IO-Sofortdiagnose in drei Schritten
Schritt 1 — Welche Datei hat das Problem? sys.dm_io_virtual_file_stats mit der Latenz-Query aus diesem Kapitel ausführen. Höchste Schreib-Latenz = Einstiegspunkt.
Schritt 2 — Wie schlimm ist es gerade? sys.dm_io_pending_io_requests prüfen. Mehr als 20 dauerhaft ausstehende Requests auf einem Volume = akute IO-Sättigung.
Schritt 3 — Autogrowth-Historie prüfen. Query auf Default Trace für Autogrowth-Events der letzten 24 Stunden. Mehr als 5 bis 10 Events = Vorallokation fehlt.
Für systematische Baseline: IO-Snapshots regelmäßig speichern (Collect-SqlPerf.ps1 in Kapitel 31 macht das automatisch) und Trendlinien vergleichen.

 

Kasten 3: Typische Fehlinterpretationen

 

Warnung: Was dich auf die falsche Fährte lockt
Fehlinterpretation 1: Hohe Latenzen direkt nach Serverstart. Der Buffer Pool ist noch kalt — viel Read-IO ist normal und klingt ab, sobald die häufig genutzten Seiten im Cache sind.
Fehlinterpretation 2: SSD bedeutet kein IO-Problem. Eine überlastete SSD mit zu kleiner Queue-Tiefe oder mit konkurrierendem Backup-IO zeigt trotzdem hohe Latenzen.
Fehlinterpretation 3: Backup-IO zählt auch. Wenn ein Vollbackup läuft und gleichzeitig die Produktionslatenzen steigen, ist das Backup der Täter — kein echtes Dauerproblem.
Fehlinterpretation 4: RAID 5 reicht für Lesezugriffe. Stimmt — aber der Write Penalty schlägt beim Transaction Log durch jeden COMMIT. Eine DB mit vorwiegend Read-Workload auf RAID 5 kann trotzdem hohe WRITELOG-Waits haben, wenn Schreibtransaktionen häufig sind.
Fehlinterpretation 5: Hohe Latenz für tempdb ist normal. Nein. TempDB-Latenz ist direkter Abfragedurchsatzverlust. > 5 ms auf tempdb auf einer SSD ist ein Problem, kein Kavaliersdelikt.

 

Kasten 4: Erste Gegenmaßnahmen

 

Tipp: IO-Triage ohne neue Hardware
1. IFI aktivieren (falls nicht vorhanden): kostet nichts, bringt sofortige Verbesserung bei Autogrowth-Events — Dienstkonto-Berechtigung setzen, Dienst neu starten.
2. Autogrowth-Größe anpassen: 1 MB ist absurd. Mindestens 256 MB bis 1 GB für Datendateien; für Log-Dateien nach Schätzung der täglichen Log-Generierung.
3. Vorallokation statt Autogrowth-Reaktion: ALTER DATABASE … MODIFY FILE … SIZE = … — Datei auf Betriebsgröße setzen und Autogrowth als Notfallnetz konfigurieren.
4. Log-Datei auf eigenes Volume, wenn möglich — auch, wenn physisch noch dasselbe Laufwerk.
5. TempDB auf das schnellste verfügbare Volume verschieben — höchste Wirkung pro Aufwand.
6. Backup-Fenster verschieben, wenn Backup-IO mit Produktions-IO kollidiert — kein Hardware-Budget nötig.

 

Zusammenfassung

IO-Performance-Probleme sind in der Praxis häufig und haben klare, messbare Ursachen. Die gute Nachricht: Die meisten IO-Probleme lassen sich ohne neue Hardware deutlich verbessern — durch IFI, Vorallokation, sinnvolle Dateiverteilung und vernünftige RAID-Konfiguration. Die schlechte Nachricht: Wenn die Hardware grundsätzlich falsch dimensioniert ist — eine einzige 7,2k-Spindel für alle SQL-Dateien — hilft am Ende nur neue Hardware. Sparfuchs & Partner ist das Lehrbeispiel für beides.

sys.dm_io_virtual_file_stats ist die primäre DMV für IO-Diagnose: Latenz pro Datenbankdatei, kumuliert seit Serverstart.

Latenz-Richtwerte: NVMe < 0,2 ms, SSD < 1 ms, HDD < 15 ms. Alles über 20 ms ist ein Problem — besonders für Log-Dateien.

WRITELOG-Waits aus Kapitel 9 korrelieren direkt mit Log-Datei-Schreib-Latenz: Das ist der COMMIT-Kostenfaktor.

RAID 5 für Transaktionslogs ist toxisch: Write Penalty von Faktor 4 macht jeden COMMIT teurer. RAID 10 ist Pflicht.

Autogrowth-Events sind kein Feature — sie sind ein Symptom für fehlende Dateigrößenplanung.

IFI aktivieren: kostet nichts, bringt sofort messbare Verbesserung bei Datendatei-Wachstum und Restores.

Dateiverteilung: Data, Log, TempDB, OS und Backup auf separate Volumes — TempDB bekommt immer das schnellste Medium.

NTFS Allocation Unit Size: 64 KB für SQL Server Data Volumes — beim Formatieren setzen, nicht im Nachhinein.

DiskSpd für Benchmarks: Misst die physische IO-Kapazität — der Vergleich mit der SQL-Last zeigt ob Hardware oder Konfiguration das Problem ist.

IO-Baseline aufbauen: Tageszeiten-Muster kennen, bevor etwas brennt — nicht erst, wenn der Anruf kommt.

 

Im nächsten Kapitel widmen wir uns dem Arbeitsspeicher: Buffer Pool, Page Life Expectancy, NUMA-Memory und der Frage, was passiert, wenn max server memory zu großzügig konfiguriert ist — und SQL Server dem Windows-Kernel den Speicher wegnimmt, den der für das Paging braucht. Spoiler: Es endet nicht gut, und NUMA macht es komplizierter als du vielleicht denkst.

 

Kapitel 11