Fragmentacja indeksów

To jest nie blog o SQL Server, ale myślę, że podstawowe informacje o bazach danych są przydatne dla każdego programisty.

Wszyscy używamy indeksów i wiemy, że dzielą się one m.in. na clustered index oraz non-clustered index. Odpowiednie stworzenie ich może okazać się dość trudnym zadaniem. Niestety nasza rola nie ogranicza się wyłącznie do ich utworzenia ponieważ wcześniej czy później będziemy mieli do czynienia z fragmentacją indeksów.

Co to jest fragmentacja? W informatyce jest to znane pojęcie i oznacza, że dane nie są rozmieszczone równomiernie w pamięci. Indeks jest reprezentowany jako drzewo. Jeśli jego węzły nie są rozmieszczone równomiernie (w spójnych blokach), wtedy odczytywanie danych jest trudniejsze ponieważ musimy skakać z jednego adresu na drugi.

Tak na prawdę w bazach danych musimy być świadomi dwóch typów fragmentacji. Pierwszy z nich to fragmentacja fizyczna dysku. Jeśli dane na dysku nie są spójne, to naturalnie może zdarzyć się, że plik bazy danych również zostanie podzielony na różne bloki w pamięci. Ten typ fragmentacji nie ma nic wspólnego z SQL Server, ale jeśli ma miejsce, spowoduje spowolnienie wszystkich zapytań. Wyobraźmy sobie, że plik bazy danych jest umieszczony na tym samym dysku, gdzie umieszczone są jakieś inne pliki. Jeśli tylko jest tam dużo operacji Input\Output, wtedy fragmentacja może mieć miejsce dosyć często. Rozwiązaniem jest zwykła defragmentacja dysku wykonywana przez Windows. Dodatkowo warto umieszczać plik bazodanowy na osobnym, niezależnym dysku.

Drugi typ fragmentacji, to fragmentacja indeksu i jest ściśle związana z silnikiem baz danych. Wchodząc szczegóły, wyróżnia się fragmentację wewnętrzną oraz zewnętrzna. Jeśli usuniemy dane ze środka tabeli, wtedy pojawią się puste fragmenty na stronach pamięci. Wykonując DELETE wielokrotnie, możemy doprowadzić zatem do fragmentacji wewnętrznej, czyli pustych fragmentów (“dziur”) na stronach pamięci.

Z kolei zewnętrzna fragmentacja ma miejsce, gdy logiczna kolejność stron nie odpowiada fizycznej kolejności. Przytrafia się to, gdy wiersze są dodawane do środkowych stron, w których nie ma już miejsca. Załóżmy, że mamy strony Page1, Page2, Page3, które odpowiadają fizycznej kolejności Page1->Page2->Page3. Załóżmy, że wykonujemy INSERT, który powinien dodać dane na końcu strony Page1. Jeśli nie będzie tam wystarczającao miejsca, wtedy zostanie dodana nowa strona (nazwijmy ją Page1A) i fizyczny porządek będzie wyglądał Page1->Page2->Page3->Page1A, a logiczny z kolei Page1->Page1a->Page2->Page3. Innymi słowy, zewnętrzna fragmentacja to “dziury” pomiędzy stronami, a wewnętrzna to “dziury” w obrębie pojedynczych stron.

Wniosek taki, że indeksy trzeba przebudowywać od czasu do czasu. W  SQL Management Studio można wybrać z menu kontekstowego opcję Rebuild:

1

Na ekranie pojawi się informacja jak bardzo dany indeks jest sfragmentowany:

2

Oczywiście w praktyce automatyzuje się takie zadania. Administratorzy baz danych zwykle definiują skrypty, które wykonają się, gdy poziom fragmentacji osiągnie dany próg.

Na przykład, podany skrypt wyświetli informacje o wszystkich indeksach wraz z ich fragmentacją (źródło):

SELECT dbschemas.[name] as 'Schema', 
dbtables.[name] as 'Table', 
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

Leave a Reply

Your email address will not be published.