Kiedy powinienem odbudować indeksy?

Kiedy powinienem odbudować indeksy w mojej relacyjnej bazie danych (SQL Server)?

Czy istnieje uzasadnienie dla regularnego przebudowywania indeksów?

Ryzykując, że w mojej odpowiedzi będę zbyt ogólny, powiem, że powinieneś regularnie przeprowadzać proces konserwacji indeksu. Jednak proces konserwacji indeksu powinien jedynie odbudowywać / reorganizować indeksy, które tego wymagają.

Rodzi to pytanie: kiedy indeks wymaga przebudowy lub reorganizacji? Rolando ładnie to poruszył. Znowu ryzykuję, że będę bardzo szeroki. Indeks wymaga konserwacji, gdy poziom fragmentacji niekorzystnie wpływa na wydajność. Ten poziom fragmentacji może się różnić w zależności od wielkości i składu wskaźnika.

Mówiąc o SQL Server, mam tendencję do wybierania rozmiaru indeksu i poziomu fragmentacji indeksu, w którym to momencie rozpoczynam konserwację indeksu. Jeśli indeks zawiera mniej niż 100 stron, nie będę wykonywać żadnych czynności konserwacyjnych.

Jeśli indeks jest podzielony między 10% a 30%, zrobię REORGANIZEindeks i UPDATEstatystyki. Jeśli indeks jest podzielony na ponad 30%, zrobię REBUILDindeks - nie UPDATE STATISTICS, ponieważ zajmuje się tym REBUILD. Pamiętaj jednak, że przebudowa aktualizuje tylko obiekt statystyk bezpośrednio powiązany z indeksem. Pozostałe statystyki kolumn będą musiały być utrzymywane osobno.

Ta odpowiedź to naprawdę długa droga do powiedzenia: tak, powinieneś rutynowo konserwować indeksy, ale tylko na indeksach, które tego potrzebują.

Kiedy powinienem odbudować indeksy w mojej relacyjnej bazie danych (np. SQL Server)?

Powinieneś odbudować indeksy, gdy staną się bardzo rozdrobnione przez zdarzenia specjalne. Na przykład wykonujesz duże, masowe ładowanie danych do indeksowanej tabeli.

Czy istnieje uzasadnienie dla regularnego przebudowywania indeksów?

A co jeśli twoje indeksy ulegają fragmentacji z powodu regularnej aktywności? Czy powinieneś planować regularne przebudowy? Jak często powinni biegać?

Tom Kyte , w tym klasycznym wątku Ask Tom , zaleca:

Odstęp czasu między odbudowaniami indeksu powinien wynosić w przybliżeniu NA ZAWSZE.

...

Nie wiem, jak to powiedzieć lepiej - indeks chce być duży i gruby z dodatkową przestrzenią. Jest w kolumnie, którą aktualizujesz - przenosząc pozycję indeksu z miejsca na miejsce w indeksie. Pewnego dnia wiersz ma kod „A”, następnego dnia kod to „G”, następnie „Z”, następnie „H” i tak dalej. Tak więc wpis indeksu dla wiersza przesuwa się z miejsca na miejsce w indeksie. Gdy tak się dzieje, potrzebuje miejsca - jeśli nie będzie miejsca, podzielimy blok na dwie części - i zrobimy miejsce. Teraz indeks staje się gruby. Z czasem indeks ma 2-3 razy większy rozmiar niż na początku i jest „w połowie lub więcej pusty” Ale to jest OK, ponieważ przenosisz wiersze. Teraz, gdy przesuwamy rzędy, nie musimy już dzielić bloków, aby zrobić miejsce - pokój jest już dostępny.

Następnie przychodzisz, przebudowujesz lub upuszczasz i ponownie tworzysz indeks (które mają te same efekty - tylko odbudowanie jest „bezpieczniejsze” - nie ma szansy na utratę indeksu i może być szybsze, ponieważ indeks można odbudować przez skanowanie istniejącego indeksu zamiast skanowania tabeli oraz sortowanie i budowanie nowego indeksu). Teraz cała ta ładna przestrzeń zniknęła. Ponownie rozpoczynamy proces dzielenia bloków - wracając do miejsca, w którym zaczęliśmy.

Nie zaoszczędziłeś miejsca.

Indeks wrócił do poprzedniego stanu.

Po prostu marnowałbyś czas na jego odbudowę, powodując powtarzanie się tego błędnego cyklu.

Logika tutaj jest zdrowa, ale jest tendencyjna w stosunku do profilu obciążenia dużego do odczytu.

Indeks „gruby” (tj. Z dużą ilością przerw) rzeczywiście zapewnia sporo miejsca na nowe i przeniesione wiersze, zmniejszając w ten sposób podziały stron i przyspieszając pisanie. Jednak gdy czytasz z tego indeksu tłuszczu, musisz przeczytać więcej stron, aby uzyskać te same dane, ponieważ teraz przesiewasz więcej pustej przestrzeni. Spowalnia to twoje odczyty.

Tak więc w bazach danych z dużą ilością danych chcesz regularnie odbudowywać lub reorganizować swoje indeksy. (Jak często i pod jakimi warunkami? Matt M ma już konkretną odpowiedź na to pytanie.) W bazach danych, które wykazują w przybliżeniu równoważną aktywność odczytu i zapisu, lub w bazach danych, które są obciążone zapisem, prawdopodobnie szkodzisz wydajności bazy danych poprzez odbudowę indeksów regularnie.

Większość ludzi przebudowuje je regularnie, aby nigdy się nie rozpadły. Kiedy trzeba je odbudować, zależy to od tego, jak szybko ulegną fragmentacji. Niektóre indeksy będą musiały być często przebudowywane, inne w zasadzie nigdy. Sprawdź skrypt, który przygotował SQLFool, który radzi sobie z rozwiązywaniem tych problemów .

Jak zauważono w przyjętej odpowiedzi Matta M., powszechną zasadą jest, że indeksy, które są podzielone na ponad 30%, powinny zostać odbudowane.

To zapytanie pomoże ci ustalić, ile indeksów masz podzielonych na ponad 30% (jeśli masz, powinieneś je odbudować):

SELECT DB_NAME() AS DBName,
       OBJECT_NAME(ind.object_id) AS TableName,
       ind.name AS IndexName,
       indexstats.index_type_desc AS IndexType,
       indexstats.avg_fragmentation_in_percent,
       indexstats.fragment_count,
       indexstats.avg_fragment_size_in_pages,
       SUM(p.rows) AS Rows 
  FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
         INNER JOIN sys.indexes AS ind ON (    ind.object_id = indexstats.object_id
                                           AND ind.index_id = indexstats.index_id)
         INNER JOIN sys.partitions AS p ON (    ind.object_id = p.object_id
                                            AND ind.index_id = p.index_id)
 WHERE indexstats.avg_fragmentation_in_percent > 30
 GROUP BY
       OBJECT_NAME(ind.object_id),
       ind.name,
       indexstats.index_type_desc,
       indexstats.avg_fragmentation_in_percent,
       indexstats.fragment_count,
       indexstats.avg_fragment_size_in_pages 
 ORDER BY indexstats.avg_fragmentation_in_percent DESC

Kiedy powinienem odbudować indeksy?

Gdy procent fragmentacji indeksu jest większy niż 30%.

Czy istnieje uzasadnienie dla regularnego przebudowywania indeksów?

Nie ma takiego przypadku, ale ogólnie rzecz biorąc, wykonywanie indeksu konserwacji raz w tygodniu, w weekend jest najlepszą praktyką w celu utrzymania stabilności środowiska.

Polecam używanie skryptów konserwacyjnych Ola Hallengren (najlepsze skrypty konserwacyjne), dostosowywanie skryptów w zależności od środowiska i planowanie ich uruchamiania w weekend.

https://ola.hallengren.com/

Uwaga: nie zapomnij zaktualizować statystyk po przebudowaniu indeksów, ponieważ przebudowanie indeksów nie aktualizuje wszystkich statystyk.

Tak jak w przypadku większości rzeczy w IT, to zależy. Jaki problem próbujesz rozwiązać poprzez przebudowanie indeksów? Czy możesz pokazać, że to naprawia problem? Jeśli tak, zmieniaj liczby, aż znajdziesz najmniejszą liczbę czynności konserwacyjnych, które musisz zrobić, aby rozwiązać problem.

Jeśli to nie rozwiąże problemu, lub powodem, dla którego to robisz, jest po prostu złagodzenie niektórych wskaźników, które monitorujesz, ponieważ może to poprawić, wtedy wszystko, co robisz, to spalanie procesora i operacji we / wy i prawdopodobnie pogorszenie problemu.

Istnieje argument, że naprawienie fragmentacji nie zrobi żadnej różnicy na twoim serwerze, więc czy w ogóle warto to robić regularnie?

https://www.brentozar.com/archive/2017/12/index-maintenance-madness/

http://brentozar.com/go/defrag