sys.dm_db_index_physical_stats zwraca poziom fragmentacji indeksów, pomocny przy planowaniu ich konserwacji.
Rola widoku sys.dm_db_index_physical_stats w SQL Server
sys.dm_db_index_physical_stats to funkcja zarządzania dynamicznego dostępna w Microsoft SQL Server, służąca do odczytu informacji o fizycznym stanie indeksów i stosów tabel. Zwraca między innymi poziom fragmentacji, liczbę stron zajmowanych przez obiekt, średni rozmiar wypełnienia stron oraz liczbę fragmentów. Dane te pozwalają ocenić, czy sposób ułożenia stron indeksu może zwiększać koszt odczytu zakresowego albo powodować niepotrzebne operacje wejścia-wyjścia. Funkcja nie naprawia indeksu i nie zmienia danych. Jest narzędziem diagnostycznym używanym przed zaplanowaniem przebudowy lub reorganizacji.
Fragmentacja analizowana przez tę funkcję dotyczy fizycznego porządku stron liściowych indeksu. W przypadku indeksu B-tree strony powinny być ułożone w kolejności odpowiadającej kluczowi indeksu. Gdy strony logicznie sąsiadujące znajdują się w różnych miejscach pliku danych, rośnie fragmentacja zewnętrzna. Osobno można analizować stopień zapełnienia stron, ponieważ częste modyfikacje rekordów lub celowo pozostawione miejsce mogą wpływać na liczbę stron oraz rozmiar operacji odczytu. Sam wysoki procent fragmentacji nie zawsze oznacza konieczność natychmiastowej konserwacji; znaczenie wyniku zależy także od rozmiaru indeksu, rodzaju zapytań i aktualnego obciążenia serwera.
Zakres analizy i tryby skanowania indeksów
Funkcję można wywołać z identyfikatorami bazy danych, obiektu, indeksu oraz partycji. Przykładowe zapytanie analizujące wszystkie indeksy bieżącej bazy ma postać SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED');. Pierwszy argument wskazuje bazę danych, drugi obiekt, trzeci konkretny indeks, a czwarty partycję. Wartość NULL oznacza brak ograniczenia w danym wymiarze. Parametr trybu skanowania przyjmuje wartości LIMITED, SAMPLED albo DETAILED; w poprawnym zapytaniu należy użyć wartości bez przypadkowego odstępu, czyli 'SAMPLED'.
Tryb LIMITED wykonuje najmniej pracy i korzysta głównie z informacji znajdujących się na poziomach wyższych niż poziom liściowy indeksu. Jest przydatny do szybkiego przeglądu dużej liczby obiektów, ale wynik może być mniej szczegółowy. Tryb SAMPLED analizuje próbkę stron liściowych; dla indeksów mających mniej niż 10 000 stron zachowuje się jak DETAILED. Tryb DETAILED skanuje wszystkie strony i dostarcza najbardziej szczegółowych danych, lecz może generować większy ruch odczytu oraz obciążenie. Dobór trybu powinien uwzględniać rozmiar bazy i okno przeznaczone na diagnostykę.
Funkcja zwraca osobny wiersz dla zakresu określonego przez bazę, obiekt, indeks i partycję. Dla obiektów niepartycjonowanych wartość partition_number wynosi 1. Przy tabelach partycjonowanych ocena całej tabeli może ukryć różnice między partycjami, dlatego często trzeba analizować partycje oddzielnie. Identyfikatory obiektu i indeksu można uzyskać z katalogowych widoków sys.tables oraz sys.indexes, a wyniki filtrować po object_id, index_id i partition_number.
Najważniejsze kolumny i interpretacja wyników
Jedną z podstawowych kolumn jest avg_fragmentation_in_percent, określająca średnią fragmentację logiczną w procentach. Wartość page_count pokazuje liczbę stron zajętych przez analizowany poziom indeksu, a fragment_count liczbę fizycznie nieciągłych fragmentów. avg_page_space_used_in_percent informuje o średnim wykorzystaniu przestrzeni na stronach liściowych. Przy indeksach rowstore pomocne są również index_type_desc, alloc_unit_type_desc, record_count oraz compressed_page_count, ponieważ pozwalają ustalić typ struktury i skalę analizowanego obszaru.
Przykładowe zapytanie ograniczające wyniki do indeksów o co najmniej 1000 stron może wyglądać następująco: SELECT object_id, index_id, partition_number, index_type_desc, page_count, avg_fragmentation_in_percent, avg_page_space_used_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') WHERE page_count >= 1000;. Taki filtr nie jest uniwersalną regułą konserwacji, lecz ogranicza szum wynikający z bardzo małych indeksów. W małym indeksie nawet duży procent może dotyczyć niewielkiej liczby stron i nie przynosić zauważalnej korzyści po reorganizacji.
Decyzję o dalszym działaniu należy podejmować na podstawie kilku sygnałów. ALTER INDEX ... REORGANIZE wykonuje reorganizację online i jest operacją przyrostową, natomiast ALTER INDEX ... REBUILD tworzy indeks ponownie. Konkretne możliwości przebudowy zależą od edycji SQL Server, wersji silnika, typu indeksu, partycjonowania oraz ustawień operacji online. Procent fragmentacji nie powinien być traktowany jako samodzielny wyzwalacz. Należy uwzględnić rzeczywisty czas zapytań, plany wykonania, częstotliwość zmian danych, dostępne miejsce w pliku danych i dzienniku transakcji oraz wymagania dotyczące blokowania.
Bezpieczne użycie podczas przeglądu bazy
Wywołanie funkcji może wymagać odpowiednich uprawnień do odczytu metadanych i danych dotyczących analizowanej bazy. Zakres widocznych informacji zależy od uprawnień użytkownika oraz konfiguracji SQL Server. Analiza szczegółowa dużej tabeli odczytuje znaczną liczbę stron, dlatego powinna być planowana poza najbardziej obciążonym okresem albo ograniczona do konkretnego obiektu. W systemie produkcyjnym warto najpierw wykonać szybki przegląd w trybie LIMITED, a dopiero potem użyć SAMPLED lub DETAILED dla indeksów, których stan wymaga dokładniejszego potwierdzenia.
Wyniku funkcji nie należy mylić z bieżącym pomiarem wydajności zapytań. Widok nie pokazuje czasu wykonania instrukcji, liczby odczytów logicznych ani tego, czy optymalizator wybrał dany indeks. Do pełnej oceny potrzebne są także statystyki zapytań, plany wykonania oraz informacje o obciążeniu. Istotne jest również odróżnienie indeksów rowstore od columnstore. Kolumny i sposób interpretacji fragmentacji mogą różnić się zależnie od typu struktury, dlatego przed zastosowaniem procedury konserwacyjnej trzeba sprawdzić index_type_desc i dokumentację właściwą dla używanego typu indeksu.
Praktyczny przegląd powinien zakończyć się zapisaniem daty pomiaru, identyfikatora bazy, nazwy obiektu, indeksu, partycji, trybu skanowania i wartości page_count. Porównywanie takich pomiarów w czasie pomaga ustalić, czy fragmentacja narasta systematycznie, czy jest tylko chwilowym skutkiem większej operacji ładowania danych. Dopiero na tej podstawie można ustalić harmonogram reorganizacji albo przebudowy i zweryfikować po operacji, czy zmieniły się zarówno parametry fizyczne indeksu, jak i rzeczywista wydajność zapytań.
