Fillfactor – stopień wypełnienia indeksu

O tym jak działają klasyczne indeksy rowstore zarówno zgrupowane jak niezgrupowane powiedziano już niemal wszystko – przynajmniej tak myślałem przez bardzo długi czas. Jak to zwykle bywa z błędnego osądu wyprowadzili mnie klienci u których oprócz lepszej czy gorszej strategii indeksowania bardzo często widzę kompletny brak zrozumienia jednego z kluczowych ustawień indeksów jakim bez wątpienia jest FILLFACTOR, o którym postanowiłem napisać parę słów – mam nadzieję, że informacje te będą dla Was zrozumiałe i przydatne.

Jak to zwykle bywa na samym początku stwórzmy sobie obiekty na których będzie się opierać artykuł tj. bazę danych o nazwie FillFactorDemo oraz tabelę Employees:

FILLFACTOR jest ustawieniem indeksów i stanowi miarę wypełnienia indeksu danymi czy też rezerwowania miejsca na stronie danych dla przyszłego użycia. Właściwość ta przyjmuje wartości od 0 do 100 gdzie np. ustawienie 60 oznacza, iż 40% strony danych zostanie pozostawione puste na przyszłe potrzeby,a 60 będzie przeznaczone na dane. Warto zauważyć, że wartość 0 i 100 oznacza dokładnie to samo i powoduje to, iż miejsce nie będzie rezerwowane na przyszłe potrzeby. Poniższa grafika pokazuje w uproszczony sposób przykładowe 3 strony danych z odpowiednimi ustawieniami FILLFACTOR (biała część oznacza zarezerwowane puste miejsce):

Stwórzmy sobie zatem indeks (w tym wypadku zgrupowany) na którym będziemy działać – FILLFACTOR ustawmy na 100:

Następnie wstawmy 150 przykładowych wierszy:

Sprawdźmy ile stron zajmują nasze dane używając złączenia kilku kluczowych widoków systemowych takich jak sys.allocation_units czy też sys.partitions:

Dane w tabeli zajmują 24 strony – czy to dużo? Oczywiście nie, zobaczmy ile stron będzie użytych gdy zmienimy współczynnik FILLFACTOR na mniejszą wartość – można to zrobić przebudowując indeks składnią TSQL ALTER INDEX … REBUILD:

Liczba używanych stron wzrosła do 52! Dosyć duża różnica przy tak małej tabeli, możecie wyobrazić sobie jak wynik powyższego zapytania będzie wyglądał w przypadku większych obiektów. W tym miejscu możecie zapytać czy powinno nas to interesować, że coś jest przechowywane na większej ilości stron – odpowiedź na to pytanie znajduje się poniżej:

Tych którzy nie widzą co oznaczają powyższe liczby odsyłam do mojego artykułu na ten temat, który znajduje się tutaj. Ogólnie rzecz biorąc aby wyświetlić rezultat zapytania trzeba było odczytać 52 strony – czyli dokładnie tyle ile wcześniej udało nam się odczytać z widoków systemowych. Poniżej zamieszczam analogiczny odczyt statystyk IO dla FILLFACTOR ustawionego na 100:

Logiczne jest to, że im mniej stron do odczytania tym szybciej zwrócony zostanie pożądany rezultat. W przypadku FILLFACTOR mniejszego niż 100 każde nasze zapytanie odczytuje dane oraz określoną ilość “powietrza”. Po co w takim razie pozostawiać puste miejsce? Ponieważ nasza operacja wstawiania danych może powodować, że wiersz musi być wstawiony pomiędzy istniejące wartości -SQL Server aby zapewnić właściwe uporządkowanie danych musi część z nich “przerzucić” na inną stronę – podział taki modelowo wynosi 50% tj. 50% wierszy jest przenoszone na nową stronę, a 50% zostaje na tej samej – jednakże nie jest to reguła. Działanie takie nazywa się podziałem stron (ang. page split) i gdy występuje może powodować problemy wydajnościowe. Przykładowy schemat działania podziału stron dla operacji INSERT został podany poniżej:

W celu radzenia sobie z podziałem stron istnieje właśnie ustawienie FILLFACTOR. Jaka jest jego najlepsza wartość? Oczywiście takowej nie ma – Paul Randall (czyli niezaprzeczalny autorytet w dziedzinie SQL Server) poleca aby w systemach transakcyjnych zacząć od ustawienia 70% i testowania czy występują podziały stron. Jednakże istnieje złota reguła – w przypadku hurtowni danych dobrym ustawieniem jest 100 (lub 0) gdyż w większości są to systemy nastawione na wydajność odczytów, a jest to idealne ustawienie w tego typu scenariuszach. Dodatkowo jeżeli mamy pewność, że nasza tabela jest statyczna lub wstawiana będzie zawsze wartość o kolejnym(następnym) kluczu indeksu ( np. kolumny z autoinkrementacją) możemy pokusić się o ustawienie wysokiego współczynnika tj. 90 albo 100, jeżeli natomiast wiemy, że wartości wstawiane mogą być niższe niż istniejące testujmy wartości od 70 do 90.

W tym miejscu warto również zaznaczyć, że ilość wolnego miejsca na pojedynczej stronie danych nazywane jest fragmentacją wewnętrzną (ang. Internal fragmentation) i może być również mierzona za pomocą funkcji systemowej sys.dm_db_index_physical_stats:

Funkcja ta przyjmuje ID bazy, obiektu, indeksu, partycji oraz tryb działania tj:

  • DETAILED – wszystkie strony będą przebadane aby zwrócić rezultat
  • SAMPLED – wybrane strony będą egzaminowane
  • LIMITED – najszybsza opcja – skanowane są tylko strony nie będące liśćmi indeksu

Dla dużych tabel/indeksów najlepiej wybrać LIMITED lub SAMPLED aby nasze zapytanie nie obciążyło zbytnie serwera. Poniżej przedstawiony został rezultat naszego zapytania:

Zapytanie zwróciło dwa wiersze – po jednej dla każdego poziomu indeksu. Pole avg_fragmentation_in_percent mówi nam o fragmentacji zewnętrznej (o której opowiemy innym razem) z kolei avg_page_space_used_in_percent mówi nam właśnie o gęstości strony (ang. Page density) czyli ile miejsca na stronie zajmują dane. Ze względu na fakt, iż ustawiliśmy FILLFACTOR na 100 to na stronie pojedynczej stronie można było zapełnić około 88%. Dlaczego nie 100? Ponieważ pozostałe 12% nie było w stanie pomieścić żadnego dodatkowego wiersza dlatego też pozostało ono puste.

Oprócz ustawienia FILLFACTOR istnieje ustawienie PAD_INDEX, które pozwala zdefiniować czy wartość podana w FILLFACTOR ma być również stosowana dla wyższych poziomów indeksu (przy PAD_INDEX = FALSE FILLFACTOR odnosi się tylko do poziomu liści) – ustawienie to jednak jest użyteczne bardzo rzadko – ja osobiście ustawiam go na TRUE w hurtowniach danych z FILLFACTOR ustawionym na 100.

Oczywiście niniejszy artykuł nie wyczerpuje całego tematu fragmentacji i podziału stron – postaram się go uzupełnić następnymi wpisami. Na ten moment mam nadzieję, że udało się Wam zrozumieć ten temat i nie będziecie pozostawiać domyślnie ustawionego FILLFACTOR w swoich indeksach. Dodatkowo polecam Wam sesję Indexes – How it works oraz stronę Paula Randalla czyli sqlskills.com gdzie znajdziecie ogromną ilość informacji na ten inne tematy związane z SQL Server DB.

Adrian Chodkowski
Follow me

Adrian Chodkowski

SQL geek, Data enthusiast, Consultant & Developer
Adrian Chodkowski
Follow me

4 Comments

  1. Gość

    Pisałeś o klientach u których są problemy z indeksowaniem. Ale jaki jest profil zawodowy tych klientów? Kim oni są, i jaka jest ich znajomość języka SQL oraz optymalizacji zapytań, w tym tworzenia i zarządzania indeksami? Bo jeżeli tym przykładowym klientem jest pani Zosia z księgowości, która ma dostęp do bazy danych z fakturami i płatnościami, to prawdopodobnie nie ma pojęcia co to jest SQL. Jeżeli klientem jest jakiś inny pracownik biurowy, przyuczony do obsługi bazy w SQL Server, to założę się że nawet nie słyszał o indeksach. Jeżeli klientem jest programista SQL, to powinien coś wiedzieć o indeksach.

    Reply
    1. Adrian Chodkowski (Post author)

      Od ludzi nie zajmujących się technicznymi aspektami pracy z bazami danych takowej wiedzy bym raczej nie wymagał 🙂 Zdarza się, że deweloperzy traktują bazę trochę po “macoszemu”, administratorzy często zajmują się administracją nie tylko SQL Server ale też innych baz i systemów dlatego też nie przywiązują aż takiej wagi do tych ustawień – dlatego też pojawiają się różne tego typu sprawy na blogu 🙂

      Reply
      1. Gość

        Niedawno byłem na szkoleniu dotyczącym pisania zoptymalizowanych zapytań do SQL Server. Z tego co mówili i o co się pytali słuchacze wynikało, że chyba wszyscy z nich są zawodowo związani z SQL Server, a na pewno z bazami relacyjnymi. Z ich rozmów wywnioskowałem że są raczej programistami SQL. A było widać że na szkolenie nie przyszli po to aby je odbębnić, ukradkiem oglądając filmy na YouTube, lecz byli na serio zainteresowani tym co przerabialiśmy (to były zajęcia praktyczne). I pomimo iż chyba byli programistami SQL (a może administratorami, nie upieram się), to było widać że raczej nie za bardzo orientują się w sprawach optymalizowania zapytań. No, powiedzmy, średnio się orientują. Zatem moje obserwacje pokrywają się z tym co napisałeś w komentarzu. Bardzo dobrze że poruszasz różne sprawy na tym blogu, w tym i związane z optymalizacją.

        Reply
        1. Adrian Chodkowski (Post author)

          Dokładnie tak! Ludzie nawet zawodowo związani z SQL Server często nie interesują się optymalizacją czy zagadnieniami administracyjnymi mając w głowie myśl “ważne żeby działało” 🙂

          Reply

Leave a Comment

Your email address will not be published. Required fields are marked *