Ola Hallengren: IndexOptimize

Procedura składowana IndexOptimize

Poniżej za pomocą skryptu można wylistować wszystkie parametry procedury składowanej IndexOptimize. Na chwilę obecną można doliczyć się 35 parametrów, które można wykorzystać do konfiguracji procedury.

SELECT 'Parameter_name' = name,
       'Type' = TYPE_NAME(user_type_id),
       'Length' = max_length,
       'Prec' = CASE
                    WHEN TYPE_NAME(system_type_id) = 'uniqueidentifier'
                    THEN precision
                    ELSE OdbcPrec(system_type_id, max_length, precision)
                END,
       'Scale' = OdbcScale(system_type_id, scale),
       'Param_order' = parameter_id,
       'Collation' = CONVERT(SYSNAME,
                             CASE
                                 WHEN system_type_id IN(35, 99, 167, 175, 231, 239)
                                 THEN SERVERPROPERTY('collation')
                             END)
FROM sys.parameters
WHERE object_id = OBJECT_ID('dbo.IndexOptimize');

W odpowiedzi dostajemy następującą listę:

Poniżej opiszę większość parametrów. Pozwoli to na lepsze zrozumienie zasad działania procedury IndexOptymazie i stworzenie własnego rozwiązania.

Istnieje możliwość zmiany wartości domyślnych dla każdego parametru wewnątrz procedury, ale nie jest to zalecane, ponieważ wpłynie to na każde wywołanie joba, który korzysta z tej procedury (np. IndexOptimize – USER_DATABASES).

Powszechną praktyką jest przekazywanie wszystkich niezbędnych parametrów do procedury składowanej za pomocą skryptu T-SQL lub CmdExec z własnymi parametrami co daje niestandardowe, w pełni zautomatyzowane rozwiązanie do obsługi indeksów.

• Databases

Parametr określa jedną lub więcej baz danych, na które skrypt ma wpływ. Aby dołączyć wiele baz danych, po prostu oddziel nazwy baz danych przecinkami (,). Aby wykluczyć bazę danych, użyj znaku łącznika (-) przed nazwą bazy danych. Aby wybrać symbol wieloznaczny, użyj znaku procentu (%). Lista wszystkich akceptowanych parametrów przedstawiona jest w tabeli.

• Indexes

Parametr określa wybór indeksu. Znaki przecinków, myślników i procentów są używane w taki sam sposób, jak w przypadku parametru Databases. Jeśli nie określono żadnej innej wartości, wybór będzie obejmował wszystkie indeksy.

• AvailabilityGroups

Te same reguły dotyczą wyboru grup dostępności, który jest kontrolowany przez parametr AvailabilityGroups.

• FragmentationLevel1 i FragmentationLevel2

Procedura składowana grupuje wszystkie indeksy w 3 kategorie, w zależności od wartości średniej fragmentacji indeksu w procentach. Ta wartość jest uzyskiwana dla wszystkich indeksów należących do bazy danych określonej przez parametr DATABASES. Procedura IndexOptimize uruchamia funkcję sys.dm_db_index_physical_stats w celu uzyskania średnich wartości fragmentacji dla indeksów, a następnie grupuje je w 3 kategorie:

• Indeksy o niskiej fragmentacji
• Indeksy o średniej fragmentacji
• Indeksy o dużej fragmentacji

Parametr FragmentationLevel1 jest wartością całkowitą, która przedstawia maksymalny limit (w %) dla indeksów o niskiej fragmentacji oraz dolny limit dla indeksów o średniej fragmentacji.

Podobnie parametr FragmentationLevel2 przedstawia maksymalny limit procentowy dla indeksów ze średnią grupą fragmentacji i punkt wyjścia dla indeksów o wysokiej fragmentacji.

Ustawiając wartości parametrów

Procedura uruchamia sprawdzanie sys.dm_db_index_physical_stats i grupuje indeksy w kategorie. Wszystkie indeksy, które mają średnią wartość fragmentacji od 0 do 5%, są uważane za indeksy o niskiej fragmentacji. Średnie wartości fragmentacji dla indeksów o średniej fragmentacji wynoszą od 5 do 25%. Jeżeli fragmentacja indeksu przekracza 25%, jest uważana za indeks wysoce rozdrobniony.

Każdy z parametrów przedstawia odpowiednią grupę indeksów na podstawie procentów fragmentacji zdefiniowanych przez parametry FragmentationLevel1 i FragmentationLevel2. Wartość każdego parametru określa akcję konserwacyjną, która zostanie wykonana na grupie indeksów.

Chociaż możliwe jest uruchamianie operacji reorganizacji i odbudowy w trybie offline na dowolnym indeksie, wiadomo, że odbudowa w trybie online nie jest obsługiwana dla wszystkich indeksów. Na przykład przebudowa online jest dostępna tylko w wersji Enterprise SQL Server 2005, podczas gdy SQL Server 2008 obsługuje operacje w wersjach Evaluation, Developer i Enterprise. Wszystkie nowsze wersje programu SQL Server obsługują odbudowę online we wszystkich wersjach. Odbudowa indeksu w trybie online nie jest również obsługiwana w przypadku indeksów XML, indeksów Spatial i indeksów w lokalnej tabeli tymczasowej.

Biorąc to pod uwagę, można zauważyć, że odbudowa w trybie online może się nie powieść z powodu wersji, edycji lub typu indeksu programu SQL Server. Ola Hallengren zaprojektował rozwiązanie również dla tych przypadków: możliwe jest określenie wielu operacji na grupie indeksów. Jeśli pierwsza określona operacja nie może zostać wykonana z jakiegokolwiek powodu, zostanie wykonana druga operacja. Oto przykład:

@FragmentationLow = NULL
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'

W powyższym przypadku indeksy o małej fragmentacji zostaną pominięte. Indeksy o średniej fragmentacji w pierwszej kolejności będą reorganizowane. Jeśli którykolwiek z tych indeksów nie może zostać zreorganizowany, zostanie wykonana przebudowa w trybie online. Jeśli odbudowa w trybie online również nie powiedzie się w przypadku indeksu, zostanie on odbudowany w trybie offline. Ta sama logika jest używana w przypadku bardzo pofragmentowanych indeksów: procedura początkowo próbuje odbudować indeksy w trybie online, a jeśli z jakiegoś powodu to się nie powiedzie, wykonywana jest odbudowa w trybie offline.

• PageCountLevel

Parametr przyjmuje tylko wartości całkowite. Wartość przedstawia rozmiar indeksu w stronach (pamiętajmy, że każda strona pamięci zajmuje 8KB danych). Wszystkie indeksy, które zajmują mniej stron niż określona wartość, zostaną pominięte przez operacje konserwacyjne. Wartość domyślna PageCountLevel to 1000, co oznacza, że ​​wszystkie indeksy mniejsze niż 1000 stron zostaną pominięte, jeśli nie określono inaczej.

• SortInTempdb

SortInTempdb określa, czy baza danych tempdb będzie używana do operacji sortowania podczas uruchamiania odbudowy indeksu. Dostępne wartości tego parametru to Y (Tak) i N (Nie), które później są wartościami domyślnymi.

• MaxDOP

MaxDOP to liczba całkowita, która przedstawia liczbę procesorów, które powinny być użyte do operacji odbudowy indeksu. Jeśli nie określono inaczej, używany jest globalny maksymalny stopień równoległości.

• FillFactor

Ustawienie FillFactor w procentach dla operacji odbudowy indeksu. Jeśli nie określono inaczej, procedura używa współczynnika wypełnienia, który jest już ustawiony dla każdego indeksu. Domyślna wartość SQL Server to 0 lub 100%, co oznacza, że ​​podczas przebudowy indeksu na każdej stronie nie powinno być wolnego miejsca. Pamiętaj, że zmiana tej wartości na dowolną inną niż 0 lub 100 powoduje wzrost rozmiaru indeksu.

• PadIndex

Parametr PadIndex stosuje procent wolnego miejsca, określony przez współczynnik wypełnienia (tak jak fillfactor) , do stron pośredniego poziomu indeksu. Dostępne wartości to Y (tak) i N (nie), przy czym N jest wartością domyślną.

• LOBCompaction

Parametr określa, czy podczas reorganizacji indeksów należy kompaktować strony zawierające duże kolumny obiektów. Dostępne wartości to Y (tak) i N (nie). Wartość domyślna to Y.

• UpdateStatistics

Procedura wykorzystuje komendę T-SQL UPDATE STATISTICS do wykonania aktualizacji statystyk. Wartość parametru UpdateStatistics określa typy statystyk, które będą aktualizowane. Wartości parametrów przedstawiono w tabeli:

Wartość domyślna parametru UpdateStatistics to NULL.

• OnlyModifiedStatistics

Procedura aktualizuje tylko te statystyki, dla których rekordy zostały zmodyfikowane od ostatniej aktualizacji statystyk. Dostępne wartości to Y (tak) i N (nie). Parametr jest brany pod uwagę tylko wtedy, gdy wartość UpdateStatistics jest inna niż NULL.

• StatisticsSample

Parametr określa procent wszystkich wierszy, które są skanowane podczas aktualizacji statystyk. Jeżeli do procedury zostanie przekazana wartość 100, zostanie wykonane pełne skanowanie. Jeśli nie zostanie podana żadna wartość, SQL Server automatycznie pobierze próbkę.

• StatisticsResample

Parametr StatisticsResample aktualizuje statystyki o najnowszą próbkę. Dostępne wartości to Y (tak) i N (nie), później są wartościami domyślnymi. Jeśli używana jest wartość domyślna, SQL Server automatycznie obliczy wymaganą próbkę

• PartitionLevel

Określa, czy zachowywać indeksy partycjonowane na poziomie partycji. Procedura akceptuje wartości Y (tak) i N (nie), przy czym pierwsza jest wartością domyślną.

• MSShippedObjects

Określa, czy zachowywać indeksy i statystyki dotyczące obiektów utworzonych przez wewnętrzne komponenty SQL Server. Procedura akceptuje wartości Y (tak) i N (nie), które później są wartościami domyślnymi.

• TimeLimit

Użyj parametru TimeLimit, aby ograniczyć czas trwania zadań konserwacji indeksu. Wartość przedstawia czas w sekundach, po którym wszystkie komendy są zatrzymywane. Domyślnie ten parametr nie jest brany pod uwagę, a procedura ma nieograniczony czas na wykonanie wszystkich poleceń.

• Delay

Parametr przedstawia opóźnienie czasowe (w sekundach) pomiędzy poleceniami indeksu. Wartość domyślna to 0 i nie ma opóźnienia między poleceniami indeksu.

• WaitAtLowPriorityMaxDuration

Wartością tego parametru jest czas w minutach, przez który procedura będzie czekać na blokady o niskim priorytecie, gdy wykonywana jest operacja odbudowy indeksu w trybie online.

• WaitAtLowPriorityAbortAfterWait

Parametr określa akcję, która zostanie podjęta po upływie czasu określonego przez parametr WaitAtLowPriorityMaxDuration. Dostępne wartości przedstawiono w tabeli.

• LockTimeout

Parametr LockTimeout określa czas (w sekundach), przez który procedura będzie czekać do momentu zwolnienia blokady. Jeśli nie określono inaczej, procedura będzie czekać na dowolne blokady w nieskończoność.

Tutaj jeszcze uwaga, jeśli użyjesz tego parametru i polecenie zostanie uruchomione jako krok w jobie to w przypadku wystąpienia limitu zostanie wygenerowany błąd wykonania, ale procedura będzie indeksować kolejne elementy. Aby pozyskać więcej szczegółów należy zobaczyć wpisy w tabeli master.dbo.CommandLog (pod warunkiem włączenia opcji LogToTable logowania do tabeli)

• LogToTable

Parametr określa, czy komendy mają być rejestrowane w tabeli dbo.CommandLog. Dostępne wartości to Y (tak) i N (nie). Wartość domyślna to N.

• Execute

Określa, czy wykonać polecenia procedury, czy po prostu je wylistować. Dostępne wartości to Y (tak) i N (nie), przy czym pierwsza jest wartością domyślną.