Nie byłem w stanie znaleźć żadnych dobrych zasobów online, więc przeprowadziłem więcej praktycznych badań i pomyślałem, że użyteczne byłoby opublikowanie wynikowego planu konserwacji pełnego tekstu, który wdrażamy na podstawie tych badań.
Nasza heurystyka określa, kiedy konieczna jest konserwacja
Naszym głównym celem jest utrzymanie stałej wydajności zapytań pełnotekstowych w miarę ewolucji danych w tabelach leżących u ich podstaw. Jednak z różnych powodów trudno byłoby nam każdego wieczora uruchamiać reprezentatywny zestaw zapytań pełnotekstowych dla każdej z naszych baz danych i wykorzystywać wydajność tych zapytań, aby określić, kiedy konieczna jest konserwacja. Dlatego chcieliśmy stworzyć praktyczne reguły, które można bardzo szybko obliczyć i wykorzystać jako heurystykę, aby wskazać, że utrzymanie indeksu pełnotekstowego może być uzasadnione.
W trakcie tej eksploracji stwierdziliśmy, że katalog systemowy zawiera wiele informacji na temat tego, w jaki sposób dany indeks pełnotekstowy jest dzielony na fragmenty. Jednak nie ma obliczonego oficjalnego „fragmentacji%” (tak jak w przypadku indeksów b-drzewa za pośrednictwem sys.dm_db_index_physical_stats ). Na podstawie informacji o fragmentach pełnego tekstu postanowiliśmy obliczyć własne „fragmentację pełnego tekstu%”. Następnie użyliśmy serwera deweloperów, aby wielokrotnie dokonywać losowych aktualizacji w dowolnym miejscu od 100 do 25 000 wierszy na raz do 10 milionów wierszy kopii danych produkcyjnych, rejestrować fragmentację pełnego tekstu i wykonywać wzorcowe zapytanie pełnotekstowe przy użyciu CONTAINSTABLE.
Wyniki, jak widać na wykresach powyżej i poniżej, były bardzo pouczające i pokazały, że stworzona przez nas miara fragmentacji jest bardzo silnie skorelowana z obserwowaną wydajnością. Ponieważ ma to również związek z naszymi jakościowymi obserwacjami podczas produkcji, wystarczy, że wygodnie jest nam używać procent fragmentacji jako naszej heurystyki przy podejmowaniu decyzji, kiedy nasze indeksy pełnotekstowe wymagają konserwacji.
Plan konserwacji
Zdecydowaliśmy się użyć następującego kodu do obliczenia% fragmentacji dla każdego indeksu pełnotekstowego. Wszelkie indeksy pełnotekstowe o nieistotnych rozmiarach z fragmentacją co najmniej 10% zostaną oflagowane, aby zostały odbudowane przez naszą nocną konserwację.
-- Compute fragmentation information for all full-text indexes on the database
SELECT c.fulltext_catalog_id, c.name AS fulltext_catalog_name, i.change_tracking_state,
i.object_id, OBJECT_SCHEMA_NAME(i.object_id) + '.' + OBJECT_NAME(i.object_id) AS object_name,
f.num_fragments, f.fulltext_mb, f.largest_fragment_mb,
100.0 * (f.fulltext_mb - f.largest_fragment_mb) / NULLIF(f.fulltext_mb, 0) AS fulltext_fragmentation_in_percent
INTO #fulltextFragmentationDetails
FROM sys.fulltext_catalogs c
JOIN sys.fulltext_indexes i
ON i.fulltext_catalog_id = c.fulltext_catalog_id
JOIN (
-- Compute fragment data for each table with a full-text index
SELECT table_id,
COUNT(*) AS num_fragments,
CONVERT(DECIMAL(9,2), SUM(data_size/(1024.*1024.))) AS fulltext_mb,
CONVERT(DECIMAL(9,2), MAX(data_size/(1024.*1024.))) AS largest_fragment_mb
FROM sys.fulltext_index_fragments
GROUP BY table_id
) f
ON f.table_id = i.object_id
-- Apply a basic heuristic to determine any full-text indexes that are "too fragmented"
-- We have chosen the 10% threshold based on performance benchmarking on our own data
-- Our over-night maintenance will then drop and re-create any such indexes
SELECT *
FROM #fulltextFragmentationDetails
WHERE fulltext_fragmentation_in_percent >= 10
AND fulltext_mb >= 1 -- No need to bother with indexes of trivial size
Te zapytania dają wyniki takie jak poniżej, w tym przypadku wiersze 1, 6 i 9 zostałyby oznaczone jako zbyt pofragmentowane dla optymalnej wydajności, ponieważ indeks pełnotekstowy ma ponad 1 MB i co najmniej 10% fragmentacji.
Utrzymanie rytmu
Mamy już okno konserwacji nocnej, a obliczanie fragmentacji jest bardzo tanie. Będziemy więc przeprowadzać tę kontrolę co noc, a następnie wykonamy tylko droższą operację polegającą na przebudowaniu indeksu pełnotekstowego, gdy będzie to konieczne, w oparciu o próg fragmentacji 10%.
ODBUDOWAĆ vs REORGANIZACJA vs. ZROBIĆ / UTWÓRZ
Oferty REBUILDi REORGANIZEopcje programu SQL Server , ale są one dostępne tylko dla katalogu pełnotekstowego (który może zawierać dowolną liczbę indeksów pełnotekstowych) w całości. Ze względów starszych mamy jeden katalog pełnotekstowy, który zawiera wszystkie nasze indeksy pełnotekstowe. Dlatego zdecydowaliśmy się na drop ( DROP FULLTEXT INDEX), a następnie odtworzenie ( CREATE FULLTEXT INDEX) na poziomie indeksu pełnotekstowego.
Bardziej idealne może być podzielenie indeksów pełnotekstowych na osobne katalogi w logiczny sposób i wykonanie REBUILDzamiast nich, ale rozwiązanie drop / create będzie dla nas w międzyczasie działać.
