SQL-Server-E/A-Latenz mit Intervallmessungen untersuchen

Eine Datei mit vier Millisekunden durchschnittlicher Leselatenz seit dem Start kann während einer aktuellen Störung trotzdem sehr langsam sein. Umgekehrt kann ein alter langsamer Wartungslauf den Durchschnitt lange erhöhen. Die Dateizähler von SQL Server sind kumulativ. Ein einzelner Messpunkt beantwortet deshalb selten die Frage nach dem aktuellen Zustand.

Messen Sie einen definierten Zeitraum und ordnen Sie ihm die tatsächlich aktive Last zu. Trennen Sie Datendateilesen, Datendateischreiben und Protokollschreiben. Diese Zugriffsmuster wirken unterschiedlich auf die Antwortzeit einer Anwendung.

Erst Differenzen bilden, dann teilen

Die schreibgeschützte Diagnose erfasst zwei Messpunkte mit fünf Sekunden Abstand und erstellt nur eine lokale temporäre Tabelle. Die Verbindung benötigt die zur Version passenden Diagnoserechte.

SELECT database_id, file_id, sample_ms, num_of_reads, num_of_writes,
       io_stall_read_ms, io_stall_write_ms
INTO #IoBefore
FROM sys.dm_io_virtual_file_stats(NULL, NULL);

WAITFOR DELAY '00:00:05';

SELECT DB_NAME(a.database_id) AS DatabaseName,
       mf.name AS LogicalFileName, mf.type_desc,
       a.sample_ms - b.sample_ms AS SampleMilliseconds,
       a.num_of_reads - b.num_of_reads AS Reads,
       a.num_of_writes - b.num_of_writes AS Writes,
       CAST(1.0 * (a.io_stall_read_ms - b.io_stall_read_ms)
            / NULLIF(a.num_of_reads - b.num_of_reads, 0)
            AS decimal(12,2)) AS AverageReadMs,
       CAST(1.0 * (a.io_stall_write_ms - b.io_stall_write_ms)
            / NULLIF(a.num_of_writes - b.num_of_writes, 0)
            AS decimal(12,2)) AS AverageWriteMs
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS a
JOIN #IoBefore AS b
  ON b.database_id = a.database_id AND b.file_id = a.file_id
JOIN sys.master_files AS mf
  ON mf.database_id = a.database_id AND mf.file_id = a.file_id
WHERE a.sample_ms > b.sample_ms
  AND a.num_of_reads >= b.num_of_reads
  AND a.num_of_writes >= b.num_of_writes
  AND a.io_stall_read_ms >= b.io_stall_read_ms
  AND a.io_stall_write_ms >= b.io_stall_write_ms
ORDER BY DatabaseName, mf.type_desc, LogicalFileName;
DROP TABLE #IoBefore;

AverageReadMs teilt die zusätzlichen Lesewartezeiten durch die zusätzlichen abgeschlossenen Leseoperationen. AverageWriteMs tut dies für Schreibzugriffe. Die Multiplikation mit 1.0 verhindert Ganzzahldivision. NULL bedeutet, dass keine entsprechenden Operationen gemessen wurden, nicht eine Latenz von null Millisekunden.

Lesen Sie immer auch die Operationsanzahl. Hundert Millisekunden bei einer einzigen Operation sind anders zu bewerten als derselbe Mittelwert bei Tausenden. Fünf Sekunden sind eine kurze Demonstration, keine allgemein ausreichende Stichprobe. Erfassen Sie mehrere Intervalle während der Störung und einer vergleichbaren normalen Phase.

Die Filter verwerfen erkennbare Zählerrückgänge und ungültige Zeitabstände. Sie machen Vergleiche über Neustarts, Dateiaustausch oder Datenbankänderungen hinweg nicht zuverlässig. Ein dauerhafter Sammler sollte Startzeit und Dateiidentität erfassen und solche Grenzübergänge verwerfen. Setzen Sie gemeinsam verwendete Diagnosezähler nicht bloß zur Rechenvereinfachung zurück.

Mittelwerte verbergen außerdem die Verteilung. Viele schnelle und wenige extrem langsame Operationen können denselben Durchschnitt haben wie gleichmäßig mittelmäßige Zugriffe. Dateizähler liefern weder genaue Perzentile noch die verantwortliche Abfrage für jeden einzelnen Stillstand.

Latenz mit Symptomen verbinden

Langsame Datenlesevorgänge können PAGEIOLATCH-Wartezeiten begleiten. Viele physische Lesezugriffe können aber durch Abfrageentwurf oder fehlende Cache-Abdeckung entstehen. Ein unnötig großer Scan belastet auch eine gesunde Speicherplattform. Prüfen Sie Zugriffspfad und Datenmenge vor der Entscheidung für schnellere Datenträger.

Langsame Protokollschreibvorgänge können COMMIT verzögern und mit WRITELOG auftreten. Viele kleine Transaktionen reagieren unter Umständen empfindlich auf einzelne Schreibverzögerungen, obwohl der Gesamtdurchsatz gering ist. Datendateischreiben steht anders mit Checkpoints und Hintergrundarbeit in Verbindung. Vermischen Sie diese Wege nicht zu einer einzigen Speicherkennzahl.

Ergänzen Sie Operationsrate, übertragene Datenmenge und Anwendungsdauer. Ein ausgeschöpftes Durchsatzlimit zeigt ein anderes Muster als wenige langsame Einzeloperationen. Vergleichen Sie Betriebssystem- und Plattformwerte für genau dasselbe Zeitfenster.

Unterscheiden Sie PAGEIOLATCH von PAGELATCH. Letzteres betrifft eine Sperre im Arbeitsspeicher, keinen Datenträgerzugriff. Ähnliche Namen bedeuten keine identische Ursache. Schnellere Datenträger beheben keine überlastete speicherinterne Zuordnungsseite.

Den nächsten Schritt aus den Daten ableiten

Identifizieren Sie Dateien und Lasten, die sich zu Beginn der Störung verändert haben. Neu überlappende Sicherungen, Indexarbeiten, Importe oder Berichte können Konkurrenz erzeugen. Gemeinsame virtuelle Infrastruktur kann zusätzlich außerhalb der Engine begrenzen.

Vermeiden Sie eine universelle akzeptable Millisekundengrenze. Dienstanforderung, Operationsgröße, Warteschlangen, Architektur und Protokollpfad beeinflussen die Bewertung. Vergleichen Sie mit einer eigenen gesunden Ausgangsmessung und den erwarteten Plattformfähigkeiten.

Verringert eine Abfrageänderung die Leseanzahl, messen Sie denselben Bericht erneut einschließlich Gesamtarbeit und Dauer. Nach einer Speicheränderung prüfen Sie auch COMMIT- und Anwendungsverhalten. Ein besserer DMV-Wert sollte einen nachvollziehbaren Nutzen erklären.

Bewahren Sie Messpunkte, Zeitstempel, Lastkontext und Maßnahme auf. So entsteht eine wiederholbare Diagnose, statt aus einem alten kumulativen Mittelwert ein dauerhaftes Urteil über die Speicherplattform abzuleiten.

Technische Referenzen: Microsoft Learn: File I/O statistics · Microsoft Learn: Troubleshoot slow I/O.