SYS.DM_IO_VIRTUAL_FILE_STATS

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

SYS.DM_IO_VIRTUAL_FILE_STATS — czym jest ten widok diagnostyczny

SYS.DM_IO_VIRTUAL_FILE_STATS to dynamiczny widok zarządzania w Microsoft SQL Server, wykorzystywany do obserwowania operacji wejścia i wyjścia wykonywanych przez pliki baz danych. Analiza zwracanych danych pozwala ustalić, jak intensywnie używany jest konkretny plik danych lub plik dziennika transakcji, ile operacji odczytu i zapisu zostało wykonanych oraz jak długo trwała obsługa tych żądań. Widok nie opisuje zawartości tabel ani indeksów. Dostarcza informacji o zachowaniu warstwy plikowej, czyli o tym, jak silnik bazy korzysta z plików zapisanych na określonych woluminach.

Wyniki są zwracane dla kombinacji identyfikatora bazy danych, identyfikatora pliku oraz punktu, z którego pobrano dane. Dzięki temu możliwe jest rozróżnienie między plikiem podstawowym, dodatkowymi plikami danych i dziennikiem transakcji. W praktyce DMV służy przede wszystkim do porównań: między plikami jednej bazy, między różnymi bazami, między okresami obserwacji albo między obciążeniem odczytowym i zapisywalnym. Sam odczyt widoku nie stanowi jeszcze diagnozy. Jest punktem wyjścia do połączenia statystyk z informacjami o strukturze bazy, obciążeniu zapytaniami oraz stanie nośników.

Znaczenie tego widoku jest szczególnie duże wtedy, gdy pojawia się podejrzenie, że opóźnienia nie wynikają z samego kodu zapytań, lecz z oczekiwania na operacje dyskowe. Wysoka liczba odczytów może oznaczać intensywne skanowanie danych, niewystarczające wykorzystanie pamięci podręcznej albo zapytania wymagające dużego zakresu danych. Duża liczba zapisów w dzienniku może towarzyszyć transakcjom modyfikującym wiele wierszy. Dopiero zestawienie kilku pól i obserwacja ich zmian w czasie pozwalają odróżnić stały charakter obciążenia od krótkotrwałego skoku.

SYS.DM_IO_VIRTUAL_FILE_STATS — kontekst techniczny i porównanie miar

Wśród najczęściej analizowanych kolumn znajdują się między innymi liczba odczytów, liczba zapisów, liczba bajtów odczytanych i zapisanych oraz czas oczekiwania związany z odczytem albo zapisem. W zależności od wersji SQL Server i rodzaju operacji dostępne są także informacje o liczbie operacji oczekujących. Nazwy pól są istotne, ponieważ liczba operacji nie mówi tego samego co liczba przetworzonych bajtów. Dwa pliki mogą mieć podobną liczbę odczytów, lecz zupełnie inną wielkość transferu. Z kolei podobna liczba bajtów może zostać obsłużona przez niewiele większych operacji albo przez bardzo dużą liczbę mniejszych żądań.

Warto rozróżnić czas pracy operacji od czasu, który można bezpośrednio przypisać pojedynczemu zapytaniu. Statystyki DMV dotyczą poziomu pliku i są agregowane od momentu uruchomienia instancji albo od chwili wyzerowania odpowiednich liczników. Nie powinno się więc traktować ich jako bieżącego pomiaru pojedynczej transakcji. Czas zgromadzony dla pliku może obejmować wiele równoległych żądań, dlatego proste dzielenie całkowitego czasu przez liczbę operacji daje jedynie przybliżoną wartość średnią. Taki wynik powinien być opisywany ostrożnie i porównywany z innymi źródłami diagnostycznymi.

Przydatnym sposobem analizy jest pobranie pierwszego zestawu danych, odczekanie ustalonego okresu pracy i wykonanie drugiego odczytu. Różnica między pomiarami pokazuje aktywność w badanym przedziale, a nie sumę zdarzeń od początku działania instancji. Takie podejście ułatwia porównanie obciążenia w okresie spokojnym i w okresie występowania problemu. Należy zachować te same warunki filtrowania, uwzględnić restart instancji oraz pamiętać, że wyzerowanie liczników zmienia punkt odniesienia dla dalszych porównań.

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

SELECT
    vfs.database_id,
    vfs.file_id,
    DB_NAME(vfs.database_id) AS database_name,
    mf.type_desc,
    mf.physical_name,
    vfs.num_of_reads,
    vfs.num_of_writes,
    vfs.num_of_bytes_read,
    vfs.num_of_bytes_written,
    vfs.io_stall_read_ms,
    vfs.io_stall_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf
  ON mf.database_id = vfs.database_id
 AND mf.file_id = vfs.file_id
ORDER BY vfs.io_stall_read_ms + vfs.io_stall_write_ms DESC;

Parametry NULL powodują objęcie pomiarem wszystkich dostępnych baz i plików, natomiast wskazanie identyfikatora bazy lub pliku pozwala ograniczyć zakres. Do interpretacji potrzebna jest ostrożność: wysoki czas oczekiwania nie zawsze oznacza uszkodzenie pliku, a niski czas nie wyklucza problemów z zapytaniami, blokadami lub pamięcią.

SYS.DM_IO_VIRTUAL_FILE_STATS — typowe problemy ujawniane przez analizę

Jednym z częstych przypadków jest nierównomierne obciążenie plików danych. Jeżeli jeden plik przyjmuje znacznie więcej operacji niż pozostałe, można sprawdzić sposób rozmieszczenia obiektów, konfigurację grupy plików, wzorzec wzrostu danych oraz charakter zapytań. Sama różnica nie stanowi dowodu, że rozmieszczenie jest błędne. W niektórych bazach określone obiekty są celowo używane częściej. W innych przypadkach rozbieżność może wskazywać na nieplanowany wzrost jednego pliku, niewłaściwy dobór struktury albo obciążenie skoncentrowane wokół konkretnych tabel.

Osobną grupę stanowią problemy związane z dziennikiem transakcji. Duża liczba zapisów i rosnący czas oczekiwania mogą towarzyszyć masowym modyfikacjom, długim transakcjom, częstym operacjom zatwierdzania lub niewłaściwie dobranym procedurom przetwarzania. Nie należy jednak zmniejszać pliku dziennika wyłącznie na podstawie jednego odczytu DMV. Najpierw powinno się ustalić przyczynę wzrostu, sprawdzić wykorzystanie dziennika, model odzyskiwania oraz częstotliwość operacji zabezpieczających. Mechaniczne zmniejszanie i ponowne powiększanie pliku może prowadzić do niepotrzebnej fragmentacji i kolejnych opóźnień.

Inny problem może dotyczyć wysokiej wartości czasu oczekiwania przy niewielkiej liczbie operacji. Taki obraz często sugeruje, że pojedyncze żądania były obsługiwane długo, lecz wymaga potwierdzenia w systemie operacyjnym i warstwie sprzętowej. Przyczyną może być przeciążenie nośnika, konkurencja o zasoby, opóźnienia połączenia z magazynem danych, działanie oprogramowania zabezpieczającego albo chwilowe zdarzenie. Z kolei duża liczba operacji przy umiarkowanym czasie oczekiwania może oznaczać intensywny, ale sprawnie obsługiwany ruch. W tym przypadku trzeba ocenić, czy sama liczba operacji przekłada się na odczuwalne opóźnienia.

Warto również sprawdzić, czy odczyty dotyczą plików danych, czy dziennika. Plik dziennika jest zwykle obciążany sekwencyjnymi zapisami, natomiast pliki danych mogą obsługiwać zarówno odczyty, jak i zapisy w bardziej zróżnicowany sposób. Porównywanie tych typów bez uwzględnienia ich roli może prowadzić do nieprawidłowych wniosków. Ważna jest także lokalizacja pliku, jego rozmiar, sposób powiększania oraz dostępna przestrzeń, ponieważ ograniczenia pojemnościowe potrafią nasilać problemy niezależnie od samej konstrukcji zapytań.

SYS.DM_IO_VIRTUAL_FILE_STATS — praktyczne wskazówki interpretacyjne

Najbezpieczniej prowadzić analizę w kilku krokach. Najpierw powinno się zapisać stan początkowy liczników wraz z datą, nazwą bazy, identyfikatorem pliku i ścieżką. Następnie należy ustalić przedział obserwacji odpowiadający rzeczywistemu problemowi. Krótki pomiar może uchwycić pojedynczy skok, a zbyt długi może rozmyć różnicę między okresami. Po drugim odczycie oblicza się przyrosty liczników, a potem porównuje je z czasem trwania testu, aktywnością użytkowników i logami systemowymi.

Nie powinno się wyciągać wniosków na podstawie jednej kolumny. Liczba odczytów, liczba bajtów oraz czas oczekiwania powinny być analizowane razem. Przydatne jest także sprawdzenie średniej wielkości operacji, choć wynik należy traktować jako wskaźnik orientacyjny. Duży transfer przy małej liczbie operacji może wskazywać na inne zachowanie niż małe porcje danych obsługiwane bardzo często. Różne wzorce obciążenia wymagają odmiennych działań, dlatego nie istnieje jedna uniwersalna wartość graniczna, po której można bez dodatkowych danych uznać środowisko za wadliwe.

Statystyki DMV dobrze uzupełnia się danymi o oczekiwaniach, blokadach, czasie wykonywania zapytań, planach wykonania, pamięci i kondycji nośników. Należy sprawdzić również, czy w badanym okresie występowały operacje kopii zapasowej, przebudowy indeksów, importy, eksporty albo zadania utrzymaniowe. Każda z tych czynności może zmienić profil odczytów i zapisów. Jeżeli pomiar wykonano po restarcie instancji, trzeba zaznaczyć ten fakt w dokumentacji, ponieważ liczniki nie opisują wcześniejszej historii.

• Porównywać przyrosty liczników, a nie tylko wartości skumulowane.
• Rozdzielać analizę plików danych od analizy dziennika transakcji.
• Łączyć dane z DMV z czasem wystąpienia objawów i obciążeniem użytkowym.
• Zapisywać nazwę bazy, identyfikator pliku oraz ścieżkę fizyczną.
• Unikać zmian konfiguracyjnych przed potwierdzeniem przyczyny.

SYS.DM_IO_VIRTUAL_FILE_STATS — kiedy zgłosić się do serwisu

Do serwisu warto zgłosić się wtedy, gdy opóźnienia operacji na bazie powtarzają się, pojawiają się błędy odczytu lub zapisu, system długo reaguje na podstawowe działania albo analiza wskazuje na niestabilne zachowanie nośnika. Pomoc może być potrzebna również wtedy, gdy statystyki plikowe są trudne do powiązania z objawami użytkowymi i konieczne staje się sprawdzenie całego komputera, pamięci masowej, systemu operacyjnego oraz konfiguracji SQL Server. Przy przekazywaniu zgłoszenia dobrze przygotować przybliżony czas wystąpienia problemu, nazwę bazy, wyniki dwóch pomiarów DMV, komunikaty błędów i informację o wykonywanych wówczas zadaniach.

W Warszawie takie zgłoszenia przyjmuje Warszawskie Pogotowie Komputerowe 24/7. Przed rozpoczęciem prac można przekazać opis objawów oraz zebrane wyniki, aby ułatwić uporządkowanie diagnostyki. Szczególnie użyteczne są informacje o tym, czy problem dotyczy wyłącznie jednej bazy, wszystkich aplikacji korzystających z komputera, określonego pliku czy tylko wybranych okresów. Pozwala to lepiej rozdzielić kwestie związane z zapytaniami i konfiguracją SQL Server od problemów dotyczących systemu lub pamięci masowej.

SYS.DM_IO_VIRTUAL_FILE_STATS nie zastępuje pełnej diagnostyki, lecz dostarcza konkretnego obrazu aktywności plików. Prawidłowo wykorzystane statystyki pomagają ustalić, gdzie szukać przyczyny: w intensywności odczytu, liczbie zapisów, czasie oczekiwania, rozłożeniu obciążenia albo zachowaniu dziennika transakcji. Największą wartość uzyskuje się wtedy, gdy pomiar jest powtarzalny, opisany w czasie i zestawiony z innymi obserwacjami technicznymi.