IO Stalls

IO Stalls to wskaźnik SQL Server mierzący łączny czas oczekiwania na operacje odczytu i zapisu na dysku.

IO Stalls w SQL Server — co dokładnie mierzy ten wskaźnik

IO Stalls w Microsoft SQL Server oznacza czas oczekiwania na zakończenie operacji wejścia i wyjścia wykonywanych przez pliki bazy danych. W praktyce chodzi przede wszystkim o odczyty i zapisy realizowane dla plików danych oraz plików dziennika transakcji. SQL Server wysyła żądanie do systemu operacyjnego i magazynu danych, a następnie oczekuje na jego wykonanie. Czas takiego oczekiwania jest rejestrowany jako opóźnienie operacji dyskowej. Wysoka wartość IO Stalls oznacza, że operacje I/O zajmują serwerowi dużo czasu, ale sama wartość nie wskazuje jeszcze, czy przyczyną jest dysk, przeciążenie, niewłaściwa konfiguracja, duża liczba żądań czy sposób działania konkretnego zapytania.

W SQL Server dane dotyczące opóźnień plików można odczytać między innymi z dynamicznego widoku zarządzania sys.dm_io_virtual_file_stats. Widok ten zwraca statystyki dla poszczególnych plików baz danych. Kolumny io_stall_read_ms i io_stall_write_ms zawierają łączny czas oczekiwania odpowiednio na odczyty i zapisy, wyrażony w milisekundach. Kolumna io_stall przedstawia łączny czas oczekiwania dla obu rodzajów operacji. Są to wartości skumulowane, dlatego ich interpretacja wymaga odniesienia do liczby operacji oraz długości okresu, w którym statystyki były zbierane.

Odczyt statystyk plików i obliczanie średniego opóźnienia

Sam łączny czas IO Stalls jest niewystarczający do oceny wydajności. Należy zestawić go z liczbą odczytów i zapisów. W sys.dm_io_virtual_file_stats służą do tego między innymi kolumny num_of_reads i num_of_writes. Średnie opóźnienie odczytu można obliczyć jako io_stall_read_ms / num_of_reads, a średnie opóźnienie zapisu jako io_stall_write_ms / num_of_writes. Wynik jest wyrażony w milisekundach na operację. Trzeba przy tym uwzględnić dzielenie przez zero, ponieważ plik może nie mieć jeszcze żadnych odczytów albo zapisów.

Przykładowe zapytanie dla SQL Server może wyglądać następująco:

SELECT DB_NAME(vfs.database_id) AS database_name, mf.type_desc, mf.physical_name, vfs.num_of_reads, vfs.num_of_writes, vfs.io_stall_read_ms, vfs.io_stall_write_ms, CASE WHEN vfs.num_of_reads = 0 THEN 0 ELSE vfs.io_stall_read_ms * 1.0 / vfs.num_of_reads END AS avg_read_ms, CASE WHEN vfs.num_of_writes = 0 THEN 0 ELSE vfs.io_stall_write_ms * 1.0 / vfs.num_of_writes END AS avg_write_ms FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id ORDER BY vfs.io_stall DESC; Zapytanie rozróżnia pliki danych i pliki dziennika przez kolumnę type_desc, pokazuje ścieżkę pliku oraz sortuje wyniki według skumulowanego czasu oczekiwania. Statystyki są naliczane od uruchomienia instancji SQL Server albo od momentu udostępnienia odpowiednich statystyk, więc warto zapisać wynik, odczekać określony czas i porównać różnice.

Jak łączyć IO Stalls z objawami przeciążenia I/O

Wysokie IO Stalls może wynikać z opóźnień urządzenia pamięci masowej, ale również z dużej liczby operacji generowanych przez SQL Server. Plik danych może wykonywać wiele odczytów losowych, gdy zapytania nie korzystają skutecznie z indeksów albo gdy potrzebne strony nie znajdują się w pamięci. Z kolei dziennik transakcji jest obsługiwany w sposób wymagający trwałego zapisu przy zatwierdzaniu transakcji. Jeżeli zapis dziennika jest wolny, może to ograniczać czas realizacji operacji COMMIT, nawet gdy odczyty z plików danych mają niewielkie opóźnienia.

Do uzupełnienia analizy używa się statystyk oczekiwań SQL Server. Oczekiwania typu PAGEIOLATCH_* dotyczą oczekiwania na odczyt stron danych z dysku do pamięci, natomiast WRITELOG wskazuje oczekiwanie na zapis rekordów dziennika na dysku. Oczekiwanie IO_COMPLETION może oznaczać oczekiwanie na zakończenie operacji I/O, które nie jest związane wyłącznie z odczytem stron. Nazwa oczekiwania nie zastępuje analizy plików, ponieważ statystyki oczekiwań pokazują perspektywę zadań SQL Server, a sys.dm_io_virtual_file_stats pokazuje skumulowane dane dla plików.

Ważne jest rozróżnienie wartości skumulowanej od bieżącego pomiaru. Przykładowo plik, który działał przez wiele dni i wykonał miliony operacji, może mieć większy łączny IO Stalls niż plik z krótkim, ale bardzo wolnym epizodem. Dlatego podczas diagnostyki należy wykonywać pomiary w porównywalnych przedziałach czasu oraz obserwować osobno odczyty, zapisy, pliki danych i pliki dziennika. Przydatne jest również zestawienie wyników z aktywnością zapytań, obciążeniem procesora, ilością wolnej pamięci i monitorowaniem urządzenia przechowującego pliki SQL Server.

Postępowanie diagnostyczne przy wysokim IO Stalls

Analizę należy rozpocząć od ustalenia, który plik generuje największy czas oczekiwania oraz czy problem dotyczy odczytów, zapisów, czy obu rodzajów operacji. Następnie trzeba sprawdzić średnie opóźnienie pojedynczej operacji i liczbę operacji w badanym okresie. Jeżeli opóźnienie jest wysokie tylko dla jednego pliku, podejrzenie może dotyczyć konkretnego woluminu, ścieżki, macierzy lub konfiguracji warstwy przechowywania. Jeżeli podobne wartości występują dla wielu baz i plików, bardziej prawdopodobne jest przeciążenie wspólnego urządzenia albo wspólnego kanału I/O.

Wysokie IO Stalls nie powinno być automatycznie leczone przez samo przeniesienie plików lub dodanie indeksów. Najpierw trzeba ustalić źródło żądań. Należy przejrzeć zapytania wykonujące pełne skany dużych tabel, operacje sortowania i agregacje wymagające zapisu do tempdb, a także transakcje generujące duży dziennik. W przypadku PAGEIOLATCH_* trzeba sprawdzić, czy zapytania odczytują więcej stron, niż jest to konieczne. W przypadku WRITELOG należy przeanalizować wielkość i częstotliwość transakcji oraz opóźnienia pliku dziennika. Dopiero po takim rozpoznaniu można dobrać działanie, takie jak korekta zapytania, indeksu, rozmiaru pliku, rozmieszczenia plików albo infrastruktury dyskowej.

Podczas serwisowania komputera z narzędziami administracyjnymi SQL Server można zebrać wyniki DMV, sprawdzić ścieżki plików i porównać je z obserwowanym obciążeniem. Sama obecność wysokiego IO Stalls nie jest dowodem uszkodzenia dysku ani bazy danych. Jest sygnałem, że warto połączyć pomiary SQL Server z testami stanu nośnika, systemu operacyjnego i aplikacji generującej ruch. Takie podejście pozwala oddzielić rzeczywiste opóźnienia infrastruktury od sytuacji, w której wolne działanie wynika głównie z nadmiernej liczby operacji wykonywanych przez zapytania.