Udostępnij za pośrednictwem

Optymalizowanie konserwacji indeksu w celu zwiększenia wydajności zapytań i zmniejszenia zużycia zasobów

  • Artykuł

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAnalytics Platform System (PDW)SQL Database w usłudze Microsoft Fabric

Ten artykuł ułatwia określenie, kiedy i jak przeprowadzić utrzymanie indeksu. Obejmuje ona pojęcia, takie jak fragmentacja indeksu i gęstość stron, oraz ich wpływ na wydajność zapytań i zużycie zasobów. Opisuje metody konserwacji indeksu, reorganizacji indeksu i odbudowywaniaindeksu i sugeruje strategię konserwacji indeksu, która równoważy potencjalne ulepszenia wydajności w stosunku do zużycia zasobów wymaganych do konserwacji.

Notatka

Ten artykuł nie dotyczy dedykowanej puli SQL w usłudze Azure Synapse Analytics. Aby uzyskać informacje na temat konserwacji indeksu dedykowanej puli SQL w usłudze Azure Synapse Analytics, zobacz Indeksowanie dedykowanych tabel puli SQL w usłudze Azure Synapse Analytics.

Pojęcia: fragmentacja indeksu i gęstość stron

Co to jest fragmentacja indeksu i jak ma to wpływ na wydajność:

  • W indeksach B-tree (rowstore) fragmentacja istnieje, gdy indeksy mają strony, na których kolejność logiczna w indeksie, na podstawie wartości klucza indeksu, nie jest zgodna z fizyczną kolejnością stron indeksu.

    Notatka

    W dokumentacji jest zwykle używany termin B-tree w odniesieniu do indeksów. W indeksach magazynujących wiersze silnik bazy danych implementuje drzewo B+. Nie dotyczy to indeksów magazynu kolumn ani indeksów w tabelach zoptymalizowanych pod kątem pamięci. Aby uzyskać więcej informacji, zobacz architektura usługi SQL Server i architektura indeksu usługi Azure SQL oraz przewodnik projektowania.

  • Aparat bazy danych automatycznie modyfikuje indeksy za każdym razem, gdy operacje wstawiania, aktualizowania lub usuwania są wykonywane na danych bazowych. Na przykład dodanie wierszy w tabeli może spowodować podzielenie istniejących stron w indeksach magazynu wierszy, co umożliwia wstawienie nowych wierszy. W miarę upływu czasu te modyfikacje mogą spowodować rozproszenie danych w indeksie (pofragmentowanych).

  • W przypadku zapytań, które odczytują wiele stron przy użyciu skanowania pełnego lub zakresu indeksów, wysoce pofragmentowane indeksy mogą obniżyć wydajność zapytań, gdy wymagane jest dodatkowe operacje we/wy do odczytu danych. Zamiast niewielkiej liczby dużych żądań we/wy zapytanie wymagałoby większej liczby małych żądań we/wy do odczytu tej samej ilości danych.

  • Gdy podsystem magazynowania zapewnia lepszą wydajność operacji we/wy sekwencyjnych niż losowa wydajność we/wy, fragmentacja indeksu może obniżyć wydajność, ponieważ do odczytu fragmentowanych indeksów jest wymagana większa liczba losowych operacji we/wy.

Co to jest gęstość stron (nazywana również pełnią strony) i jak ma to wpływ na wydajność:

  • Każda strona w bazie danych może zawierać zmienną liczbę wierszy. Jeśli wiersze zajmują całe miejsce na stronie, gęstość strony wynosi 100%. Jeśli strona jest pusta, gęstość strony wynosi 0%. Jeśli strona o gęstości 100% jest podzielona na dwie strony, aby pomieścić nowy wiersz, gęstość dwóch nowych stron wynosi około 50%.
  • Gdy gęstość stron jest niska, do przechowywania tej samej ilości danych jest wymagana większa liczba stron. Oznacza to, że do odczytu i zapisu tych danych jest niezbędna większa liczba operacji we/wy, a do buforowania tych danych jest niezbędna większa ilość pamięci. Jeśli pamięć jest ograniczona, liczba stron wymaganych przez zapytanie jest buforowana, co powoduje jeszcze więcej operacji we/wy dysku. W związku z tym niska gęstość stron negatywnie wpływa na wydajność.
  • Gdy aparat bazy danych dodaje wiersze do strony podczas tworzenia, przebudowy lub reorganizacji indeksu, nie wypełni strony w pełni, jeśli współczynnik wypełnienia indeksu jest ustawiony na wartość inną niż 100 (lub 0, która jest równoważna w tym kontekście). Powoduje to mniejszą gęstość stron, co również zwiększa obciążenie operacji we/wy i negatywnie wpływa na wydajność.
  • Niska gęstość stron może zwiększyć liczbę pośrednich poziomów drzewa B. To umiarkowanie zwiększa koszt procesora CPU i operacji wejścia/wyjścia przy indeksowych skanach i wyszukiwaniach stron liściowych.
  • Gdy optymalizator zapytań kompiluje plan zapytania, uwzględnia koszt operacji we/wy potrzebny do odczytania danych wymaganych przez zapytanie. Przy niskiej gęstości stron jest więcej stron do odczytania, dlatego koszt operacji we/wy jest wyższy. Może to mieć wpływ na wybór planu zapytania. Na przykład, gdy gęstość stron zmniejsza się wraz z upływem czasu z powodu podziałów stron, optymalizator może skompilować inny plan dla tego samego zapytania z innym profilem wydajności i zużycia zasobów.

Napiwek

W wielu obciążeniach zwiększenie gęstości stron powoduje większy pozytywny wpływ na wydajność niż zmniejszenie fragmentacji.

Aby uniknąć niepotrzebnego obniżenia gęstości stron, firma Microsoft nie zaleca ustawiania współczynnika wypełnienia na wartości inne niż 100 lub 0, z wyjątkiem niektórych przypadków w przypadku indeksów, w których występuje duża liczba podziałów stron , na przykład często modyfikowane indeksy z wiodącymi kolumnami zawierającymi nie sekwencyjne wartości identyfikatora GUID.

Mierzenie fragmentacji indeksu i gęstości strony

Zarówno fragmentacja, jak i gęstość stron są jednymi z czynników, które należy wziąć pod uwagę podczas podejmowania decyzji, czy przeprowadzić konserwację indeksu, oraz która metoda konserwacji ma być używana.

Fragmentacja jest definiowana inaczej dla indeksów typu rowstore oraz columnstore . W przypadku indeksów typu rowstore funkcja sys.dm_db_index_physical_stats() pozwala określić fragmentację i gęstość stron w określonym indeksie, w wszystkich indeksach na tabeli lub indeksowanym widoku, wszystkich indeksach w bazie danych, lub wszystkich indeksach we wszystkich bazach danych. W przypadku indeksów partycjonowanych sys.dm_db_index_physical_stats() zawiera te informacje dla każdej partycji.

Zestaw wyników zwrócony przez sys.dm_db_index_physical_stats zawiera następujące kolumny:

Kolumna Opis
avg_fragmentation_in_percent Fragmentacja logiczna (strony poza kolejnością w indeksie).
avg_page_space_used_in_percent Średnia gęstość stron.

W przypadku skompresowanych grup wierszy w indeksach magazynu kolumn fragmentacja jest definiowana jako stosunek usuniętych wierszy do łącznej liczby wierszy wyrażonych jako wartość procentowa. sys.dm_db_column_store_row_group_physical_stats umożliwia określenie liczby wierszy ogółem oraz usuniętych w każdej grupie wierszy danego indeksu, wszystkich indeksów w tabeli lub wszystkich indeksów w bazie danych.

Zestaw wyników zwrócony przez sys.dm_db_column_store_row_group_physical_stats zawiera następujące kolumny:

Kolumna Opis
total_rows Liczba wierszy przechowywanych fizycznie w grupie wierszy. W przypadku skompresowanych grup wierszy obejmuje to wiersze oznaczone jako usunięte.
deleted_rows Liczba wierszy przechowywanych fizycznie w skompresowanej grupie wierszy, które są oznaczone do usunięcia. 0 dla grup wierszy, które znajdują się w magazynie różnicowym.

Skompresowana fragmentacja grup wierszy w indeksie magazynu kolumn można obliczyć przy użyciu tej formuły:

100.0*(ISNULL(deleted_rows,0))/NULLIF(total_rows,0)

Napiwek

W przypadku indeksów wierszowych i kolumnowych należy przejrzeć fragmentację sterty i gęstość strony po usunięciu lub zaktualizowaniu dużej liczby wierszy. W przypadku stert, jeśli występują częste aktualizacje, okresowo sprawdzaj fragmentację, aby uniknąć rozprzestrzeniania się rekordów przekierowujących. Aby uzyskać więcej informacji na temat Heaps, zobacz Heaps (Tabele bez indeksów klastrowanych).

Zobacz Przykłady, aby uzyskać przykładowe zapytania w celu określenia fragmentacji i gęstości strony.

Metody konserwacji indeksu: reorganizacja i ponowne kompilowanie

Fragmentację indeksu można zmniejszyć i zwiększyć gęstość stron przy użyciu jednej z następujących metod:

  • Reorganizacja indeksu
  • Ponowne kompilowanie indeksu

Notatka

W przypadku partycjonowanych indeksów można użyć jednej z następujących metod na wszystkich partycjach lub jednej partycji indeksu.

Reorganizacja indeksu

Reorganizacja indeksu jest mniej zasobożerna niż jego odbudowa. Z tego powodu powinna to być preferowana metoda konserwacji indeksu, chyba że istnieje określona przyczyna użycia ponownego kompilowania indeksu. Reorganizacja jest zawsze operacją online. Oznacza to, że długoterminowe blokady na poziomie obiektu nie są przechowywane, a zapytania lub aktualizacje tabeli bazowej mogą być kontynuowane podczas operacji ALTER INDEX ... REORGANIZE.

  • W przypadku indeksów rowstoreSilnik bazy danych defragmentuje tylko poziom liściowy klastrowanych i nieklastrowanych indeksów w tabelach i widokach, zmieniając fizycznie kolejność stron na poziomie liści, aby dopasować je do logicznej kolejności węzłów liściowych (od lewej do prawej). Reorganizacja kompaktuje również strony indeksu, aby gęstość stron była równa współczynnikowi wypełnienia indeksu . Aby wyświetlić ustawienie współczynnika wypełnienia, użyj sys.indexes. Aby zapoznać się z przykładami składni, zobacz przykłady — Rowstore reorganize.
  • W przypadku korzystania z indeksów magazynu kolumnmagazyn różnicowy może mieć wiele małych grup wierszy po wstawieniu, zaktualizowaniu i usunięciu danych z upływem czasu. Reorganizacja indeksu magazynu kolumnowego wymusza przekształcenie grup wierszy z magazynu różnicowego w skompresowane grupy w magazynie kolumnowym oraz łączy mniejsze skompresowane grupy wierszy w większe grupy. Operacja reorganizacji również fizycznie usuwa wiersze oznaczone jako usunięte w magazynie kolumn. Reorganizacja indeksu magazynu kolumn może wymagać dodatkowych zasobów procesora CPU w celu skompresowania danych. Gdy operacja jest uruchomiona, wydajność może spowolnić. Jednak po skompresowaniu danych wydajność zapytań się poprawia. Aby zapoznać się z przykładami składni, zobacz: przykłady — reorganizacja Columnstore.

Notatka

Począwszy od programu SQL Server 2019 (15.x), usługi Azure SQL Database i usługi Azure SQL Managed Instance, proces tuple-mover jest wspomagany przez zadanie scalania w tle, które automatycznie kompresuje mniejsze otwarte grupy wierszy delta, które istniały przez pewien czas zgodnie z wewnętrznym progiem, lub scala skompresowane grupy wierszy, z których usunięto dużą liczbę wierszy. Poprawia to jakość indeksu magazynu kolumn z upływem czasu. W większości przypadków odrzuca to potrzebę wydawania poleceń ALTER INDEX ... REORGANIZE.

Napiwek

Jeśli anulujesz operację reorganizacji lub jeśli zostanie ona przerwana, postępy dokonane do tego momentu zostaną utrwalone w bazie danych. Aby zreorganizować duże indeksy, można uruchomić i zatrzymać operację wiele razy, dopóki nie zostanie ukończona.

Ponowne kompilowanie indeksu

Odbudowa indeksu usuwa i tworzy indeks na nowo. W zależności od typu indeksu i wersji aparatu bazy danych można wykonać operację ponownego kompilowania w trybie offline lub w trybie online. Ponowne kompilowanie indeksu w trybie offline zwykle trwa krócej niż ponowne kompilowanie w trybie online, ale przechowuje blokady na poziomie obiektu na czas trwania operacji ponownej kompilacji, co blokuje dostęp do tabeli lub widoku zapytań.

Ponowne kompilowanie indeksu online nie wymaga blokad na poziomie obiektu do końca operacji, gdy blokada musi być przechowywana przez krótki czas, aby zakończyć ponowną kompilację. W zależności od wersji silnika bazy danych, można uruchomić przebudowę indeksu online jako operację możliwą do wznowienia. Można wstrzymać ponowne odbudowywanie indeksu, które można wznowić, zachowując dotychczasowy postęp. Można wznowić operację ponownej kompilacji po jej wstrzymaniu lub przerwaniu, lub anulować, jeśli ukończenie ponownej kompilacji stanie się niepotrzebne.

Aby zapoznać się z Transact-SQL składnią, zobacz ALTER INDEX REBUILD. Aby uzyskać więcej informacji na temat ponownego kompilowania indeksu online, zobacz Perform Index Operations Online.

Notatka

Podczas odbudowy indeksu w trybie online każda modyfikacja danych w indeksowanych kolumnach musi zaktualizować dodatkową kopię indeksu. Może to spowodować niewielkie obniżenie wydajności instrukcji modyfikacji danych podczas ponownego kompilowania online.

Jeśli operacja indeksu z możliwością wznowienia w trybie online zostanie wstrzymana, ten wpływ na wydajność będzie nadal występować do momentu zakończenia lub przerwania operacji możliwej do wznowienia. Jeśli nie zamierzasz ukończyć wznawialnej operacji indeksowania, przerwij ją zamiast ją wstrzymywać.

Napiwek

W zależności od dostępnych zasobów i wzorców obciążeń określenie większej niż domyślna wartość MAXDOP w instrukcji ALTER INDEX REBUILD może skrócić czas odbudowy kosztem wyższego użycia procesora CPU.

  • W przypadku indeksów rowstoreponowne kompilowanie usuwa fragmentację na wszystkich poziomach indeksu i kompakuje strony na podstawie określonego lub bieżącego współczynnika wypełnienia. Po określeniu ALL wszystkie indeksy w tabeli zostaną porzucone i ponownie utworzone w ramach jednej operacji. Gdy indeksy z 128 lub większą liczbą zakresów są ponownie przebudowywane, silnik bazy danych odkłada dezalokację stron i nabywanie skojarzonych blokad do momentu zakończenia przebudowy. Aby zapoznać się z przykładami składni, zobacz przykłady — ponowne kompilowanie magazynu wierszy.

  • W przypadku indeksów magazynu kolumnodbudowa usuwa fragmentację, przenosi wszelkie wiersze z magazynu delta do magazynu kolumn i fizycznie usuwa wiersze oznaczone jako do usunięcia. Aby zapoznać się z przykładami składni, zobacz przykłady — ponowne kompilowanie magazynu kolumn.

    Napiwek

    Począwszy od programu SQL Server 2016 (13.x), ponowne kompilowanie indeksu magazynu kolumn zwykle nie jest potrzebne, ponieważ REORGANIZE wykonuje podstawowe elementy kompilacji jako operację online.

Odzyskiwanie po uszkodzeniach danych przy użyciu ponownego kompilowania indeksu

Przed programem SQL Server 2008 (10.0.x) czasami można ponownie skompilować indeks nieklastrowany w magazynie wierszy w celu skorygowania niespójności z powodu uszkodzenia danych w indeksie.

Nadal można naprawić takie niespójności w indeksie nieklastrowanym przez ponowne skompilowanie indeksu nieklastrowanego w trybie offline. Nie można jednak naprawić niespójności indeksu nieklastrowanego przez ponowne skompilowanie indeksu w trybie online, ponieważ mechanizm ponownego kompilowania online używa istniejącego indeksu nieklastrowanego jako podstawy ponownej kompilacji, a tym samym prowadzi do niespójności. Ponowne tworzenie indeksu w trybie offline może czasami wymusić skanowanie klastrowanego indeksu (lub sterty), a tym samym zastąpić niespójne dane w indeksie nieklastrowanym danymi z klastrowanego indeksu lub sterty.

Aby upewnić się, że indeks klastrowany lub sterta jest używane jako źródło danych, usuń i ponownie utwórz indeks nieklastrowany zamiast go przebudowywać. Podobnie jak w przypadku wcześniejszych wersji, można naprawić niespójności, przywracając uszkodzone dane z kopii zapasowej. Można jednak naprawić niespójności indeksu nieklastrowanego przez ponowne skompilowanie go w trybie offline lub ponowne utworzenie go. Aby uzyskać więcej informacji, zobacz DBCC CHECKDB (Transact-SQL).

Automatyczne zarządzanie indeksami i statystykami

Użyj rozwiązań, takich jak Adaptive Index Defrag, aby automatycznie zarządzać fragmentacją indeksu i aktualizacjami statystyk dla jednej lub więcej baz danych. Ta procedura automatycznie wybiera, czy należy ponownie skompilować lub zreorganizować indeks zgodnie z poziomem fragmentacji, między innymi parametrami, oraz zaktualizować statystyki z progiem liniowym.

Zagadnienia dotyczące ponownego kompilowania i reorganizacji indeksów magazynu wierszy

Następujące scenariusze powodują automatyczne ponowne utworzenie wszystkich indeksów nieklastrowanych rowstore w tabeli:

  • Tworzenie indeksu klastrowanego w tabeli, w tym ponowne utworzenie indeksu klastrowanego przy użyciu innego klucza przy użyciu CREATE CLUSTERED INDEX ... WITH (DROP_EXISTING = ON)
  • Usuwanie indeksu klastrowanego, co powoduje, że tabela jest przechowywana jako sterta.

Następujące scenariusze nie kompilują ponownie automatycznie wszystkich indeksów nieklastrowanych wierszowych w tej samej tabeli:

  • Ponowne kompilowanie indeksu klastrowanego
  • Zmiana klastrowanego magazynu indeksów, takiego jak zastosowanie schematu partycjonowania lub przeniesienie indeksu klastrowanego do innej grupy plików

Ważny

Nie można zreorganizować ani ponownie skompilować indeksu, jeśli grupa plików, na której się znajduje, jest w trybie offline lub tylko do odczytu. Gdy słowo kluczowe ALL jest określone i co najmniej jeden indeks znajduje się w grupie plików w trybie offline lub tylko do odczytu, instrukcja kończy się niepowodzeniem.

Podczas ponownego kompilowania indeksu nośnik fizyczny musi mieć wystarczającą ilość miejsca do przechowywania dwóch kopii indeksu. Po zakończeniu ponownego kompilowania aparat bazy danych usuwa oryginalny indeks.

Po określeniu ALL za pomocą instrukcji ALTER INDEX ... REORGANIZE klastrowane, nieklastrowane i indeksy XML w tabeli zostaną zreorganizowane.

Przebudowa lub reorganizacja małych indeksów rowstore zwykle nie zmniejsza fragmentacji. Do wersji SQL Server 2014 (12.x) włącznie, aparat bazy danych SQL Server przydziela miejsce przy użyciu zakresów mieszanych. W związku z tym strony małych indeksów są czasami przechowywane w różnych zakresach, co niejawnie sprawia, że takie indeksy są fragmentowane. Mieszane zakresy są współużytkowane przez maksymalnie osiem obiektów, więc fragmentacja w małym indeksie może nie zostać zmniejszona po reorganizacji lub odbudowaniu.

Szczegóły dotyczące odbudowywania indeksu kolumnowego

Podczas odbudowy indeksu magazynu kolumn aparat bazy danych odczytuje wszystkie dane z oryginalnego indeksu magazynu kolumn, w tym magazyn różnicowy. Łączy dane w nowe grupy wierszy i kompresuje wszystkie grupy wierszy do columnstore. Aparat bazy danych defragmentuje magazyn kolumn przez fizyczne usuwanie wierszy oznaczonych jako usunięte.

Notatka

Począwszy od programu SQL Server 2019 (15.x), przesuwacz krotek jest wspomagany przez zadanie scalania w tle działania, które automatycznie kompresuje mniejsze otwarte grupy wierszy magazynu różnicowego, które istniały przez pewien czas, co ustala próg wewnętrzny, lub scala skompresowane grupy wierszy, w których usunięto dużą liczbę wierszy. Poprawia to jakość indeksu kolumnowego w miarę upływu czasu. Aby uzyskać więcej informacji na temat terminów i pojęć związanych z columnstore, zobacz także Indeksy columnstore: Omówienie.

Odbudowa partycji zamiast całej tabeli

Ponowne kompilowanie całej tabeli trwa długo, jeśli indeks jest duży i wymaga wystarczającej ilości miejsca na dysku, aby przechowywać dodatkową kopię całego indeksu podczas odbudowy.

W przypadku tabel partycjonowanych nie trzeba ponownie kompilować całego indeksu magazynu kolumn, jeśli fragmentacja występuje tylko w niektórych partycjach, na przykład w partycjach, w których UPDATE, DELETElub instrukcje MERGE miały wpływ na dużą liczbę wierszy.

Ponowne skompilowanie partycji po załadowaniu lub zmodyfikowaniu danych gwarantuje, że wszystkie dane są przechowywane w skompresowanych grupach wierszy w magazynie kolumn. Gdy proces ładowania danych wstawia dane do partycji przy użyciu partii mniejszych niż 102 400 wierszy, partycja może prowadzić do powstania wielu otwartych grup wierszy w przechowalni delta. Ponowne kompilowanie powoduje przeniesienie wszystkich wierszy magazynu różnicowego do skompresowanych grup wierszy w magazynie kolumn.

Zagadnienia dotyczące reorganizacji indeksu columnstore

Podczas reorganizacji indeksu magazynu kolumnowego aparat bazy danych kompresuje każdą zamkniętą grupę wierszy w magazynie delta do magazynu kolumnowego jako skompresowaną grupę wierszy. Począwszy od programu SQL Server 2016 (13.x) i usługi Azure SQL Database, polecenie REORGANIZE wykonuje następujące dodatkowe optymalizacje defragmentacji w trybie online:

  • Fizycznie usuwa wiersze z grupy wierszy, gdy 10% lub więcej wierszy zostało logicznie usuniętych. Jeśli na przykład skompresowana grupa wierszy z 1 milionami wierszy zawiera 100 000 wierszy usuniętych, aparat bazy danych usunie usunięte wiersze i ponownie skompresuje grupę wierszy z 900 000 wierszy, zmniejszając zużycie miejsca do magazynowania.
  • Łączy co najmniej jedną skompresowaną grupę wierszy, aby zwiększyć liczbę wierszy na grupę wierszy, maksymalnie 1048 576 wierszy. Jeśli na przykład zbiorczo wstawisz pięć partii z 102 400 wierszy, otrzymasz pięć skompresowanych grup wierszy. Jeśli uruchomisz REORGANIZE, te grupy wierszy zostaną scalone do jednej skompresowanej grupy wierszy zawierającej 512 000 wierszy. Zakłada się, że nie było ograniczeń rozmiaru słownika ani pamięci.
  • Silnik bazy danych próbuje połączyć grupy wierszy, w których 10% lub więcej wierszy zostało oznaczonych jako usunięte, i połączyć z innymi grupami wierszy. Na przykład grupa wierszy 1 jest kompresowana i zawiera 500 000 wierszy, podczas gdy grupa wierszy 21 jest kompresowana i zawiera 1048 576 wierszy. Grupa wierszy 21 ma 60% wierszy oznaczonych jako usunięte, co pozostawia 409 830 wierszy. Aparat bazy danych faworyzuje łączenie tych dwóch grup wierszy w celu skompresowania nowej grupy wierszy zawierającej 909 830 wierszy.

Po załadowaniu danych, może powstać wiele małych grup wierszy w sklepie delta. Za pomocą ALTER INDEX REORGANIZE można wymusić te grupy wierszy do magazynu kolumn, a następnie połączyć mniejsze skompresowane grupy wierszy w większe skompresowane grupy wierszy. Operacja reorganizacji spowoduje również usunięcie wierszy oznaczonych jako skasowane ze sklepu kolumnowego.

Notatka

Reorganizacja indeksu magazynu kolumn przy użyciu programu Management Studio łączy skompresowane grupy wierszy ze sobą, ale nie wymusza kompresji wszystkich grup wierszy do magazynu kolumn. Zamknięte grupy wierszy zostaną skompresowane, ale otwarte grupy wierszy nie zostaną skompresowane do formatu magazynu kolumn. Aby wymusić kompresję wszystkich grup wierszy, użyj przykładu Transact-SQL zawierającego COMPRESS_ALL_ROW_GROUPS = ON.

Co należy wziąć pod uwagę przed przeprowadzeniem konserwacji indeksu

Konserwacja indeksu wykonywana przez reorganizację lub ponowne kompilowanie indeksu wymaga dużej ilości zasobów. Powoduje to znaczny wzrost wykorzystania CPU, używanej pamięci i operacji wejścia/wyjścia pamięci masowej. Jednak w zależności od obciążenia bazy danych i innych czynników korzyści, jakie zapewnia, wahają się od niezwykle ważnych do znikomej.

Aby uniknąć niepotrzebnego wykorzystania zasobów, należy unikać masowego przeprowadzania konserwacji indeksu. Zamiast tego korzyści z wydajności związane z konserwacją indeksu powinny być określane empirycznie dla każdego obciążenia przy użyciu zalecanej strategii i ważone kosztami zasobów i wpływem obciążenia potrzebnym do osiągnięcia tych korzyści.

Prawdopodobieństwo uzyskania korzyści z wydajności wynikającej z reorganizacji lub ponownego kompilowania indeksu jest wyższe, gdy indeks jest mocno rozdrobniony lub gdy gęstość strony jest niska. Jednak nie są to jedyne rzeczy, które należy wziąć pod uwagę. Czynniki takie jak wzorce zapytań (przetwarzanie transakcji a analiza i raportowanie), zachowanie podsystemu magazynowania, dostępne ulepszenia pamięci i aparatu bazy danych w czasie odgrywają rolę.

Ważny

Decyzje dotyczące konserwacji indeksu należy podjąć po uwzględnieniu wielu czynników w określonym kontekście każdego obciążenia, w tym kosztu konserwacji zasobów. Nie powinny one być oparte tylko na stałych progach fragmentacji ani gęstości strony.

Pozytywny efekt uboczny odbudowy indeksu

Klienci często obserwują ulepszenia wydajności po odbudowaniu indeksów. Jednak w wielu przypadkach te ulepszenia nie są związane z zmniejszeniem fragmentacji lub zwiększeniem gęstości strony.

Odbudowa indeksu ma ważną korzyść: aktualizuje statystyki na kluczowych kolumnach indeksu, przeskanowując wszystkie wiersze w indeksie. Jest to odpowiednik wykonywania UPDATE STATISTICS ... WITH FULLSCAN, co sprawia, że statystyki są aktualne, a czasami poprawia ich jakość w porównaniu z domyślną aktualizacją statystyk próbkowanych. Po zaktualizowaniu statystyk plany zapytań odwołujące się do nich zostaną ponownie skompilowane. Jeśli poprzedni plan zapytania nie był optymalny ze względu na nieaktualne statystyki, niewystarczający współczynnik próbkowania statystyk lub z innych powodów, ponownie skompilowany plan często będzie działać lepiej.

Klienci często niepoprawnie przypisują tę poprawę odbudowie indeksu, sądząc, że jest to wynik zmniejszenia fragmentacji i zwiększonej gęstości stron. W rzeczywistości tę samą korzyść można często osiągnąć przy znacznie mniejszym kosztem zasobów przez aktualizowanie statystyk zamiast odbudowy indeksów.

Napiwek

Koszt zasobów związany z aktualizacją statystyk jest niewielki w porównaniu z rekonstrukcją indeksu, a operacja zwykle kończy się w ciągu kilku minut. Ponowne kompilowanie indeksu może potrwać kilka godzin.

Strategia konserwacji indeksu

Firma Microsoft zaleca, aby klienci rozważyli i zastosować następującą strategię konserwacji indeksu:

  • Nie zakładaj, że utrzymanie indeksu zawsze znacznie poprawi Twoje obciążenie robocze.
  • Zmierz konkretny wpływ reorganizacji lub odbudowywania indeksów na wydajność zapytań w twoim obciążeniu. Magazyn zapytań to dobry sposób na mierzenie wydajności "przed konserwacją" i "po konserwacji" przy użyciu techniki testowania A/B.
  • Jeśli zauważysz, że ponowne kompilowanie indeksów poprawia wydajność, spróbuj zastąpić go zaktualizowanymi statystykami. Może to spowodować podobną poprawę. W takim przypadku może nie być konieczne odbudowywanie indeksów tak często, a może wcale, a zamiast tego można wykonywać okresowe aktualizacje statystyk. W przypadku niektórych statystyk może być konieczne zwiększenie współczynnika próbkowania przy użyciu klauzul WITH SAMPLE ... PERCENT lub WITH FULLSCAN (nie jest to powszechne).
  • Monitoruj fragmentację indeksu i gęstość stron w czasie, aby sprawdzić, czy istnieje korelacja między wzrostem lub spadkiem tych wartości a wydajnością zapytań. Jeśli większa fragmentacja lub niższa gęstość stron obniży wydajność do nieakceptowalnego poziomu, zreorganizuj lub odbuduj indeksy. Często wystarczy tylko zreorganizować lub ponownie kompilować określone indeksy używane przez zapytania z obniżoną wydajnością. Pozwala to uniknąć wyższego kosztu utrzymania każdego indeksu w bazie danych.
  • Ustanowienie korelacji między fragmentacją/gęstością strony a wydajnością umożliwia również określenie częstotliwości konserwacji indeksu. Nie należy zakładać, że konserwacja musi być wykonywana zgodnie z ustalonym harmonogramem. Lepszą strategią jest monitorowanie fragmentacji i gęstości stron oraz uruchamianie konserwacji indeksu zgodnie z potrzebami, zanim wydajność obniży się niedopuszczalnie.
  • Jeśli ustalono, że wymagana jest konserwacja indeksu, a jego koszt zasobów jest akceptowalny, wykonaj konserwację podczas niskich czasów użycia zasobów, jeśli to możliwe.
  • Okresowo testuj, ponieważ wzorce użycia zasobów mogą się zmieniać w czasie.

Konserwacja indeksu w usługach Azure SQL Database i Azure SQL Managed Instance

Oprócz powyższych zagadnień i strategii w usługach Azure SQL Database i Azure SQL Managed Instance szczególnie ważne jest rozważenie kosztów i korzyści związanych z konserwacją indeksu. Klienci powinni wykonać je tylko wtedy, gdy istnieje wykazana potrzeba, i biorąc pod uwagę następujące kwestie.

  • Usługi Azure SQL Database i Azure SQL Managed Instance implementują zarządzanie zasobami w celu ustawienia granic użycia CPU, pamięci i wejścia/wyjścia zgodnie z przydzieloną warstwą cenową. Te ograniczenia dotyczą wszystkich obciążeń użytkowników, w tym konserwacji indeksu. Jeśli skumulowane użycie zasobów przez wszystkie obciążenia zbliża się do granic zasobów, operacja ponownej kompilacji lub reorganizacji może obniżyć wydajność innych obciążeń z powodu rywalizacji o zasoby. Na przykład zbiorcze ładowanie danych może stać się wolniejsze, ponieważ operacje we/wy dziennika transakcji są na poziomie 100% ze względu na współbieżną ponowną kompilację indeksu. W usłudze Azure SQL Managed Instance ten wpływ można zmniejszyć, uruchamiając konserwację indeksu w oddzielnej grupie obciążeń Zarządca zasobów z ograniczoną alokacją zasobów kosztem wydłużenia czasu trwania konserwacji indeksu.
  • Dla oszczędności kosztów klienci często konfigurują bazy danych, elastyczne pule i zarządzane wystąpienia z minimalnym zapasem zasobów. Wybrano poziom cenowy, który jest wystarczający dla obciążeń aplikacji. Aby obsłużyć znaczny wzrost użycia zasobów ze względu na konserwację indeksu bez obniżania wydajności aplikacji, klienci mogą wymagać aprowizacji większej ilości zasobów i zwiększenia kosztów bez konieczności poprawy wydajności aplikacji.
  • W pulach elastycznych zasoby są współużytkowane we wszystkich bazach danych w puli. Nawet jeśli określona baza danych jest bezczynna, wykonywanie konserwacji indeksu w tej bazie danych może mieć wpływ na obciążenia aplikacji uruchomione współbieżnie w innych bazach danych w tej samej puli. Aby uzyskać więcej informacji, zobacz Zarządzanie zasobami w gęstych elastycznych pulach.
  • W przypadku większości typów pamięci masowej używanej w usługach Azure SQL Database i Azure SQL Managed Instance nie ma różnicy w wydajności pomiędzy sekwencyjnymi i losowymi operacjami wejścia/wyjścia. Zmniejsza to wpływ fragmentacji indeksu na wydajność zapytań.
  • Podczas korzystania z skalowania odczytu lub replik geograficznych, opóźnienia danych w replikach często zwiększają się podczas wykonywania konserwacji indeksu na replice podstawowej. Jeśli replika geograficzna została przydzielona z niewystarczającymi zasobami, aby utrzymać wzrost generowania dziennika transakcji spowodowany konserwacją indeksu, może znacząco opóźniać się względem głównej repliki, co powoduje konieczność jej ponownej synchronizacji. Sprawia to, że replika jest niedostępna do momentu ukończenia ponownego zasiewania. Ponadto w warstwach usług Premium i Biznes Krytyczny repliki używane do wysokiej dostępności mogą podobnie pozostawać w tyle za głównym podczas konserwacji indeksu. Jeśli przełączenie awaryjne jest wymagane podczas lub wkrótce po konserwacji indeksu, może potrwać to dłużej niż oczekiwano.
  • Jeśli rekonstrukcja indeksu działa na replice podstawowej, a jednocześnie na replice z możliwością odczytu wykonywane jest długotrwałe zapytanie, zapytanie to może zostać automatycznie zakończone, aby zapobiec zablokowaniu wątku ponownej synchronizacji (redo) na replice.

Istnieją konkretne, ale nietypowe scenariusze, w których może być wymagana jednorazowa lub okresowa konserwacja indeksu w usługach Azure SQL Database i Azure SQL Managed Instance:

Napiwek

Jeśli ustalono, że konserwacja indeksu jest niezbędna dla obciążeń usług Azure SQL Database i Azure SQL Managed Instance, należy zreorganizować indeksy lub ponownie skompilować indeks online. Umożliwia to obciążeniom zapytań dostęp do tabel podczas odbudowy indeksów.

Ponadto możliwość wznowienia operacji pozwala uniknąć ponownego uruchamiania jej od początku, jeśli zostanie przerwana przez planowane lub nieplanowane przejście bazy danych w tryb failover. Korzystanie z operacji indeksowania z możliwością wznowienia jest szczególnie ważne, gdy indeksy są duże.

Napiwek

Operacje indeksowania w trybie offline zwykle są wykonywane szybciej niż operacje online. Powinny one być używane, gdy tabele nie będą uzyskiwane przez zapytania podczas operacji, na przykład po załadowaniu danych do tabel przejściowych w ramach sekwencyjnego procesu ETL.

Ograniczenia i ograniczenia

Indeksy Rowstore z ponad 128 rozszerzeniami są przebudowywane w dwóch oddzielnych fazach: logicznej i fizycznej. W fazie logicznej istniejące jednostki alokacji używane przez indeks są oznaczone do dealokacji, wiersze danych są kopiowane i sortowane, a następnie przenoszone do nowych jednostek alokacji utworzonych w celu przechowywania przebudowanego indeksu. W fazie fizycznej jednostki alokacji oznaczone wcześniej do alokacji transakcji są fizycznie porzucane w krótkich transakcjach, które występują w tle i nie wymagają wielu blokad. Aby uzyskać więcej informacji na temat jednostek alokacji, zobacz Pages and Extents Architecture Guide (Przewodnik po architekturze stron i zakresów).

Instrukcja ALTER INDEX REORGANIZE wymaga, aby plik danych zawierający indeks miał dostępne miejsce, ponieważ operacja może przydzielić tylko tymczasowe strony robocze w tym samym pliku, a nie w innym pliku w tej samej grupie plików. Mimo że grupa plików ma dostępne wolne miejsce, użytkownik nadal może napotkać błąd 1105: Could not allocate space for object '###' in database '###' because the '###' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup podczas operacji reorganizacji, jeśli plik danych nie ma miejsca.

Nie można zreorganizować indeksu, gdy ALLOW_PAGE_LOCKS jest ustawiona na wartość WYŁĄCZONE.

Do programu SQL Server 2017 (14.x) ponowne kompilowanie klastrowanego indeksu magazynu kolumn jest operacją offline. Silnik bazy danych musi uzyskać wyłączną blokadę na tabeli lub partycji podczas przebudowy. Dane są w trybie offline i niedostępne podczas ponownej kompilacji, nawet w przypadku używania NOLOCK, izolacji migawki zatwierdzonej do odczytu (RCSI) lub izolacji migawki. Począwszy od programu SQL Server 2019 (15.x), można ponownie skompilować indeks klastrowanego magazynu kolumn przy użyciu opcji ONLINE = ON.

Ostrzeżenie

Tworzenie i odbudowa niezrównoważonych indeksów w tabeli z więcej niż 1000 partycjami jest możliwe, ale nie jest wspierane. Może to spowodować obniżenie wydajności lub nadmierne zużycie pamięci podczas tych operacji. Firma Microsoft zaleca używanie wyłącznie indeksów zgodnych z , gdy liczba partycji przekracza 1000.

Ograniczenia statystyk

  • Gdy indeks jest utworzony lub odbudowany, statystyki są tworzone lub aktualizowane przez przeskanowanie wszystkich wierszy w tabeli, co jest równoważne użyciu klauzuli FULLSCAN w CREATE STATISTICS lub UPDATE STATISTICS. Jednak począwszy od programu SQL Server 2012 (11.x), gdy indeks partycjonowany jest tworzony lub odbudowywany, statystyki nie są tworzone ani aktualizowane przez skanowanie wszystkich wierszy w tabeli. Zamiast tego jest używany domyślny współczynnik próbkowania. Aby utworzyć lub zaktualizować statystyki dotyczące partycjonowanych indeksów, przeskanując wszystkie wiersze w tabeli, użyj CREATE STATISTICS lub UPDATE STATISTICS z klauzulą FULLSCAN.
  • Podobnie, gdy operacja tworzenia lub ponownego kompilowania indeksu jest wznawiana, statystyki są tworzone lub aktualizowane przy użyciu domyślnego współczynnika próbkowania. Jeśli statystyki zostały utworzone lub ostatnio zaktualizowane przy użyciu klauzuli PERSIST_SAMPLE_PERCENT ustawionej na ON, operacje indeksu z możliwością wznowienia używają utrwalonego współczynnika próbkowania w celu utworzenia lub zaktualizowania statystyk.
  • Gdy indeks jest zreorganizowany, statystyki nie są aktualizowane.

Przykłady

Sprawdź fragmentację i gęstość strony indeksu magazynu wierszy przy użyciu Transact-SQL

Poniższy przykład określa średnią fragmentację i gęstość stron dla wszystkich indeksów typu rowstore w bieżącej bazie danych. Używa trybu SAMPLED do szybkiego zwracania wyników z możliwością działania. Aby uzyskać dokładniejsze wyniki, użyj trybu DETAILED. Wymaga to skanowania wszystkich stron indeksu i może zająć dużo czasu.

SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       ips.avg_fragmentation_in_percent,
       ips.avg_page_space_used_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc
FROM sys.dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i
ON ips.object_id = i.object_id
   AND
   ips.index_id = i.index_id
ORDER BY page_count DESC;

Poprzednia instrukcja zwraca zestaw wyników podobny do następującego:

schema_name  object_name           index_name                               index_type    avg_fragmentation_in_percent avg_page_space_used_in_percent page_count  alloc_unit_type_desc
------------ --------------------- ---------------------------------------- ------------- ---------------------------- ------------------------------ ----------- --------------------
dbo          FactProductInventory  PK_FactProductInventory                  CLUSTERED     0.390015600624025            99.7244625648629               3846        IN_ROW_DATA
dbo          DimProduct            PK_DimProduct_ProductKey                 CLUSTERED     0                            89.6839757845318               497         LOB_DATA
dbo          DimProduct            PK_DimProduct_ProductKey                 CLUSTERED     0                            80.7132814430442               251         IN_ROW_DATA
dbo          FactFinance           NULL                                     HEAP          0                            99.7982456140351               239         IN_ROW_DATA
dbo          ProspectiveBuyer      PK_ProspectiveBuyer_ProspectiveBuyerKey  CLUSTERED     0                            98.1086236718557               79          IN_ROW_DATA
dbo          DimCustomer           IX_DimCustomer_CustomerAlternateKey      NONCLUSTERED  0                            99.5197553743514               78          IN_ROW_DATA

Aby uzyskać więcej informacji, zobacz sys.dm_db_index_physical_stats.

Sprawdź fragmentację indeksu columnstore przy użyciu Transact-SQL

Poniższy przykład oblicza średnią fragmentację wszystkich indeksów kolumnowych z kompresowanymi grupami wierszy w bieżącej bazie danych.

SELECT OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
       OBJECT_NAME(i.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       100.0 * (ISNULL(SUM(rgs.deleted_rows), 0)) / NULLIF(SUM(rgs.total_rows), 0) AS avg_fragmentation_in_percent
FROM sys.indexes AS i
INNER JOIN sys.dm_db_column_store_row_group_physical_stats AS rgs
ON i.object_id = rgs.object_id
   AND
   i.index_id = rgs.index_id
WHERE rgs.state_desc = 'COMPRESSED'
GROUP BY i.object_id, i.index_id, i.name, i.type_desc
ORDER BY schema_name, object_name, index_name, index_type;

Poprzednia instrukcja zwraca zestaw wyników podobny do następującego:

schema_name  object_name            index_name                           index_type                avg_fragmentation_in_percent
------------ ---------------------- ------------------------------------ ------------------------- ----------------------------
Sales        InvoiceLines           NCCX_Sales_InvoiceLines              NONCLUSTERED COLUMNSTORE  0.000000000000000
Sales        OrderLines             NCCX_Sales_OrderLines                NONCLUSTERED COLUMNSTORE  0.000000000000000
Warehouse    StockItemTransactions  CCX_Warehouse_StockItemTransactions  CLUSTERED COLUMNSTORE     4.225346161484279

Obsługa indeksów przy użyciu programu SQL Server Management Studio

Zreorganizowanie lub ponowne kompilowanie indeksu

  1. W Eksplorator obiektówrozwiń bazę danych zawierającą tabelę, na której chcesz zreorganizować indeks.
  2. Rozwiń folder Tables.
  3. Rozwiń tabelę, w której chcesz zreorganizować indeks.
  4. Rozwiń folder indeksy.
  5. Kliknij prawym przyciskiem myszy indeks, który chcesz zreorganizować, a następnie wybierz pozycję Reorganize.
  6. W oknie dialogowym Reorganizacja indeksów sprawdź, czy prawidłowy indeks znajduje się w siatce Indeksy do reorganizacji i wybierz OK.
  7. Zaznacz pole wyboru Kompaktowanie dużych danych kolumn obiektów, aby określić, że wszystkie strony zawierające dane obiektów dużych (LOB) są również kompaktowane.
  8. Wybierz OK.

Zreorganizowanie wszystkich indeksów w tabeli

  1. W Eksplorator obiektówrozwiń bazę danych zawierającą tabelę, na której chcesz zreorganizować indeksy.
  2. Rozwiń folder Tables.
  3. Rozwiń tabelę, w której chcesz zreorganizować indeksy.
  4. Kliknij prawym przyciskiem myszy folder Indexes i wybierz pozycję Reorganize All.
  5. W oknie dialogowym Zreorganizuj indeksy sprawdź, czy prawidłowe indeksy znajdują się w sekcji Indeksy do zreorganizowania. Aby usunąć indeks z indeksów do reorganizacji siatki, wybierz indeks, a następnie naciśnij Delete.
  6. Zaznacz pole wyboru Kompaktowanie dużych danych kolumn obiektów, aby określić, że wszystkie strony zawierające dane obiektów dużych (LOB) są również kompaktowane.
  7. Wybierz przycisk OK.

Obsługa indeksów przy użyciu Transact-SQL

Notatka

Aby uzyskać więcej przykładów użycia Transact-SQL do ponownego kompilowania lub reorganizacji indeksów, zobacz ALTER INDEX Examples — Rowstore Indexes and ALTER INDEX Examples — Columnstore Indexes.

Reorganizacja indeksu

Poniższy przykład zreorganizuje indeks IX_Employee_OrganizationalLevel_OrganizationalNode w tabeli HumanResources.Employee w bazie danych AdventureWorks2022.

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode
    ON HumanResources.Employee
    REORGANIZE;

W poniższym przykładzie następuje reorganizacja indeksu magazynu kolumn IndFactResellerSalesXL_CCI w tabeli dbo.FactResellerSalesXL_CCI w bazie danych AdventureWorksDW2022. To polecenie wymusza przeniesienie wszystkich zamkniętych i otwartych grup wierszy do kolumnowego magazynu danych.

-- This command forces all closed and open row groups into columnstore.
ALTER INDEX IndFactResellerSalesXL_CCI
    ON FactResellerSalesXL_CCI
    REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

Zreorganizowanie wszystkich indeksów w tabeli

Poniższy przykład zreorganizuje wszystkie indeksy w tabeli HumanResources.Employee w bazie danych AdventureWorks2022.

ALTER INDEX ALL ON HumanResources.Employee
   REORGANIZE;

Ponowne kompilowanie indeksu

Poniższy przykład ponownie kompiluje pojedynczy indeks w tabeli Employee w bazie danych AdventureWorks2022.

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD
;

Ponowne kompilowanie wszystkich indeksów w tabeli

Poniższy przykład ponownie kompiluje wszystkie indeksy skojarzone z tabelą w bazie danych AdventureWorks2022 przy użyciu słowa kluczowego ALL. Określono trzy opcje.

ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON)
;

Aby uzyskać więcej informacji, zobacz ALTER INDEX.

Powiązana zawartość