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:

USE master
GO

CREATE DATABASE FillFactorDemo
GO

USE FillFactorDemo
GO

CREATE TABLE Employees
(
id int identity,
name nvarchar(50),
surname nvarchar(100),
birth_date date,
description nvarchar(500)
)
GO

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:

CREATE CLUSTERED INDEX CIX_Employees ON dbo.Employees(id)
WITH FILLFACTOR=100
GO

Następnie wstawmy 150 przykładowych wierszy:

INSERT INTO Employees
(
name,
surname,
birth_date,
description
)
VALUES
(N'Jan',N'Kowalski',N'19900101',Replicate(N' ',500))
GO 150

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:

SELECT 
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) AS TotalPages, 
    SUM(a.used_pages) AS UsedPages, 
    (SUM(a.total_pages) - SUM(a.used_pages)) AS UnusedPages
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
      t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, p.Rows
ORDER BY 
    t.Name

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:

ALTER INDEX [CIX_Employees] ON [dbo].[Employees] 
REBUILD PARTITION = ALL WITH (FILLFACTOR = 30)

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:

SET STATISTICS IO ON

SELECT * FROM Employees

(150 row(s) affected)
Table 'Employees'. Scan count 1, 
logical reads 52, 
physical reads 0, 
read-ahead reads 0, 
lob logical reads 0, 
lob physical reads 0, 
lob read-ahead reads 0.

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:

(150 row(s) affected)
Table 'Employees'. Scan count 1, 
logical reads 24, 
physical reads 0, 
read-ahead reads 0, 
lob logical reads 0, 
lob physical reads 0, 
lob read-ahead reads 0.

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:

SELECT 
 OBJECT_NAME(OBJECT_ID), 
 index_id,index_type_desc,
 index_level,
 avg_fragmentation_in_percent,
 avg_page_space_used_in_percent,
 page_count
FROM 
 sys.dm_db_index_physical_stats(
DB_ID(N'FillFactorDemo'), 
OBJECT_ID('dbo.Employees'), 
NULL, 
NULL , 
'DETAILED')
ORDER BY 
   avg_fragmentation_in_percent DESC

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 = OFF FILLFACTOR odnosi się tylko do poziomu liści) – ustawienie to jednak jest użyteczne bardzo rzadko – ja osobiście ustawiam go na ON 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.