Widok sys.dm_io_virtual_file_stats

sys.dm_io_virtual_file_stats pokazuje statystyki odczytu i zapisu dla poszczególnych plików bazy.

Widok sys.dm_io_virtual_file_stats jako źródło informacji o pracy plików

Widok sys.dm_io_virtual_file_stats służy do obserwowania, jak silnik SQL Server wykonuje operacje wejścia i wyjścia związane z plikami baz danych. Analizowane są osobno pliki danych oraz pliki dziennika transakcji, dzięki czemu można rozdzielić obciążenie wynikające z odczytu danych od obciążenia związanego z zapisem zmian. Informacje są prezentowane dla konkretnej bazy i konkretnego pliku, a nie wyłącznie dla całego środowiska. Pozwala to ustalić, które elementy składowania są najczęściej używane, gdzie pojawiają się opóźnienia oraz czy problem dotyczy odczytu, zapisu, czy obu rodzajów operacji.

Nie jest to klasyczny raport historyczny zapisujący każdy pojedynczy odczyt lub zapis. Zwracane wartości mają charakter liczników narastających od momentu uruchomienia instancji, odłączenia bazy albo wyzerowania odpowiednich danych diagnostycznych. W praktyce przydatne jest wykonanie dwóch pomiarów w określonym odstępie czasu i porównanie różnic między nimi. Sam wysoki licznik nie musi oznaczać awarii, ponieważ może wynikać z długiej pracy bazy i dużej liczby prawidłowych zapytań. Znaczenie ma tempo przyrostu, rozmiar przetwarzanych danych oraz czas oczekiwania przypisany do operacji.

Wynik zawiera między innymi identyfikator bazy, identyfikator pliku, liczbę odczytów, liczbę zapisów, liczbę bajtów odczytanych i zapisanych oraz łączny czas oczekiwania na zakończenie operacji. Dostępne są również wartości dotyczące oczekiwania na zapis dziennika transakcji. Po połączeniu tych danych z widokami katalogowymi można uzyskać nazwę pliku, jego typ oraz pełną ścieżkę. Dopiero takie zestawienie daje kontekst potrzebny do prawidłowej interpretacji wyników.

sys.dm_io_virtual_file_stats w kontekście odczytu, zapisu i opóźnień

Najważniejsze rozróżnienie dotyczy liczby operacji oraz ilości przesłanych danych. Dwa pliki mogą mieć podobną liczbę odczytów, ale jeden może obsługiwać małe porcje danych, a drugi znacznie większe. Z tego powodu sama liczba operacji nie wystarcza do oceny obciążenia. Warto analizować zarówno num_of_reads i num_of_writes, jak i num_of_bytes_read oraz num_of_bytes_written. Na tej podstawie można wyliczyć średni rozmiar operacji i sprawdzić, czy dominują częste, niewielkie odwołania, czy rzadsze transfery dużych bloków.

Istotne są również pola określające czas oczekiwania. io_stall_read_ms opisuje łączny czas oczekiwania na zakończenie odczytów, a io_stall_write_ms odnosi się do zapisów. Dla dziennika transakcji dostępne jest osobne pole io_stall_queued_write_ms, które może pomóc w ocenie oczekiwania związanego z kolejkowaniem zapisów. Wartości te należy odnosić do liczby operacji. Przykładowo, duży łączny czas przy milionach odczytów może oznaczać inne zjawisko niż ten sam czas przy kilkuset operacjach.

Przydatnym wskaźnikiem jest średni czas pojedynczej operacji, obliczany jako czas oczekiwania podzielony przez liczbę odpowiednich odczytów albo zapisów. Taki wynik nie jest dokładnym pomiarem opóźnienia każdego żądania, lecz uśrednioną wskazówką diagnostyczną. Należy pamiętać, że licznik czasu może obejmować różne etapy obsługi żądania, a jego interpretacja zależy od wersji SQL Server, rodzaju nośnika, konfiguracji systemu oraz współbieżności zapytań. Nie powinno się na podstawie jednej wartości automatycznie uznawać, że winny jest konkretny komponent.

Ważne jest także odróżnienie plików danych od dziennika transakcji. Pliki danych są często obciążane odczytami stron i zapisami zmodyfikowanych stron, natomiast dziennik wymaga przede wszystkim sekwencyjnego zapisu zmian przed zatwierdzeniem transakcji. Wysokie oczekiwanie przy pliku dziennika może wpływać na czas zatwierdzania operacji, ale nie należy wyciągać wniosków bez sprawdzenia wielkości transakcji, częstotliwości zatwierdzeń i pozostałych oczekiwań systemu.

Typowe problemy wykrywane przez widok sys.dm_io_virtual_file_stats

Jednym z częstych zastosowań jest wykrywanie pliku, który obsługuje nieproporcjonalnie dużą część ruchu. Może to wynikać z układu obiektów, niekorzystnego rozmieszczenia danych, nierównomiernego rozrostu plików albo zapytań skanujących duże obszary tabel. Sam widok nie wskazuje jednak, które zapytanie spowodowało ruch. Do dalszej analizy potrzebne są informacje z planów wykonania, statystyk zapytań, konfiguracji baz oraz harmonogramów zadań.

Inny przypadek to wysoki czas oczekiwania przy stosunkowo małej liczbie operacji. Taki wzorzec może wskazywać na chwilowe przeciążenie podsystemu wejścia i wyjścia, problemy z warstwą przechowywania, konkurencję między obciążeniami albo opóźnienia poza samym silnikiem bazy. Pomocne bywa porównanie kilku plików znajdujących się na tym samym nośniku oraz plików rozmieszczonych w innych lokalizacjach. Jeśli opóźnienie jest podobne dla całej grupy plików, przyczyna może być wspólna. Jeżeli problem dotyczy jednego pliku, należy sprawdzić jego otoczenie i sposób użycia.

Wyniki mogą również ujawnić, że dziennik transakcji jest intensywnie zapisywany, mimo że odczytów jest niewiele. Jest to zgodne z charakterem dziennika i nie oznacza samo w sobie nieprawidłowości. Powodem zwiększonej aktywności mogą być duże transakcje, częste modyfikacje, operacje zbiorcze albo procesy utrzymaniowe. W analizie powinno się uwzględnić model odzyskiwania, sposób wykonywania kopii dziennika oraz to, czy występują długotrwałe transakcje blokujące możliwość ponownego użycia jego przestrzeni.

Problemem diagnostycznym bywa także nieprawidłowe odczytanie liczników jako danych z ostatniej minuty lub godziny. Bez punktu odniesienia licznik narastający nie mówi, kiedy wystąpiła konkretna operacja. Dlatego zaleca się zapisanie stanu początkowego, odczekanie reprezentatywnego okresu i ponowne pobranie danych. Okres powinien obejmować typowe obciążenie, a w razie potrzeby osobno godziny szczytu, zadania cykliczne i czas wykonywania kopii zapasowych.

Praktyczne wskazówki dla analizy sys.dm_io_virtual_file_stats

Najczytelniejszy raport powstaje po połączeniu funkcji z widokami sys.master_files albo sys.database_files. Dzięki temu identyfikator pliku zostaje uzupełniony o nazwę logiczną, nazwę fizyczną i informację, czy chodzi o plik danych, czy dziennika. Przy analizie wielu baz wygodne jest użycie funkcji z argumentami wskazującymi konkretną bazę albo wartościami umożliwiającymi pobranie danych dla wszystkich baz widocznych w danym kontekście. Uprawnienia wymagane do odczytu zależą od wersji SQL Server i zakresu informacji, dlatego w razie braku wyników należy sprawdzić uprawnienia diagnostyczne.

Dobrym rozwiązaniem jest zapisywanie kolejnych próbek w tabeli pomocniczej razem z czasem pomiaru, identyfikatorem instancji i informacją o bazie. Następnie oblicza się różnice liczników pomiędzy próbkami. Takie podejście pozwala obserwować zmiany w czasie, tworzyć zestawienia dzienne oraz porównywać okresy o podobnym profilu pracy. Należy zachować ostrożność po restarcie instancji, odtworzeniu bazy lub innym zdarzeniu powodującym zmianę punktu początkowego, ponieważ różnica może wtedy nie mieć znaczenia.

Warto zestawiać dane z sys.dm_io_virtual_file_stats z obserwacjami dotyczącymi oczekiwań, aktywnych zapytań, rozmiaru pamięci podręcznej, czasu procesora i wykorzystania przestrzeni. Na przykład duży ruch odczytu przy niskim czasie oczekiwania może być prawidłowym skutkiem wydajnej pracy nośnika, a mały ruch przy wysokim czasie oczekiwania może wskazywać na problem chwilowy. Dopiero zgodność kilku niezależnych sygnałów pozwala sformułować wiarygodną hipotezę.

Nie zaleca się mechanicznego przenoszenia plików tylko dlatego, że jeden z nich ma więcej operacji. Przed zmianą konfiguracji należy ustalić, czy obciążenie jest stałe, czy wynika z konkretnego procesu, oraz czy przeniesienie pliku rzeczywiście zmieni warunki pracy. Przydatne jest również sprawdzenie wolnego miejsca, wzorca powiększania plików, fragmentacji systemu plików i innych procesów korzystających z tego samego zasobu.

Kiedy zgłosić się z widokiem sys.dm_io_virtual_file_stats do serwisu

Do serwisu warto zgłosić się wtedy, gdy wyniki wskazują na powtarzalne opóźnienia wejścia i wyjścia, wyraźne różnice między plikami albo nagłe pogorszenie działania aplikacji korzystającej z bazy. Przydatne jest przekazanie kilku próbek pomiarowych, informacji o czasie występowania problemu oraz zestawienia nazw plików z ich ścieżkami. Tak przygotowany materiał ułatwia odróżnienie problemu z konfiguracją bazy od problemu sprzętowego lub systemowego.

Warszawskie Pogotowie Komputerowe 24/7 może pomóc w uporządkowaniu wyników diagnostycznych dotyczących komputera, nośnika danych i środowiska pracy SQL Server. Przed przekazaniem sprzętu należy zabezpieczyć kopie zapasowe oraz nie usuwać plików dziennika i danych na podstawie pojedynczego odczytu. W przypadku podejrzenia awarii nośnika ważniejsze od dalszych testów obciążeniowych jest ograniczenie pracy urządzenia i zachowanie danych do analizy. Ostateczna ocena powinna uwzględniać zarówno statystyki z widoku, jak i stan całego systemu, sposób użytkowania bazy oraz powtarzalność zaobserwowanych objawów.