ALTER INDEX (Transact-SQL)

Dotyczy:sql ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL Database w usłudze Microsoft Fabric

Modyfikuje istniejącą tabelę lub indeks widoku (rowstore, columnstore lub XML), wyłączając, ponownie kompilując lub reorganizując indeks; lub przez ustawienie opcji w indeksie.

Transact-SQL konwencje składni

Składnia

Składnia dla programu SQL Server, usługi Azure SQL Database i usługi Azure SQL Managed Instance.

ALTER INDEX { index_name | ALL } ON <object>
{
      REBUILD {
            [ PARTITION = ALL [ WITH ( <rebuild_index_option> [ , ...n ] ) ] ]
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> [ , ...n ] ) ] ]
      }
    | DISABLE
    | REORGANIZE  [ PARTITION = partition_number ] [ WITH ( <reorganize_option>  ) ]
    | SET ( <set_index_option> [ , ...n ] )
    | RESUME [ WITH (<resumable_index_option> [ , ...n ] ) ]
    | PAUSE
    | ABORT
}
[ ; ]

<object> ::=
{
    { database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }
}

<rebuild_index_option> ::=
{
      PAD_INDEX = { ON | OFF }
    | FILLFACTOR = fillfactor
    | SORT_IN_TEMPDB = { ON | OFF }
    | IGNORE_DUP_KEY = { ON | OFF }
    | STATISTICS_NORECOMPUTE = { ON | OFF }
    | STATISTICS_INCREMENTAL = { ON | OFF }
    | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
    | RESUMABLE = { ON | OFF }
    | MAX_DURATION = <time> [ MINUTES ]
    | ALLOW_ROW_LOCKS = { ON | OFF }
    | ALLOW_PAGE_LOCKS = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
        [ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ]
    | XML_COMPRESSION = { ON | OFF }
        [ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ] }

<single_partition_rebuild_index_option> ::=
{
      SORT_IN_TEMPDB = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | RESUMABLE = { ON | OFF }
    | MAX_DURATION = <time> [ MINUTES ]
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
    | XML_COMPRESSION = { ON | OFF }
    | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
}

<reorganize_option> ::=
{
       LOB_COMPACTION = { ON | OFF }
    |  COMPRESS_ALL_ROW_GROUPS =  { ON | OFF }
}

<set_index_option> ::=
{
      ALLOW_ROW_LOCKS = { ON | OFF }
    | ALLOW_PAGE_LOCKS = { ON | OFF }
    | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
    | IGNORE_DUP_KEY = { ON | OFF }
    | STATISTICS_NORECOMPUTE = { ON | OFF }
    | COMPRESSION_DELAY = { 0 | delay [ Minutes ] }
}

<resumable_index_option> ::=
 {
    MAXDOP = max_degree_of_parallelism
    | MAX_DURATION = <time> [ MINUTES ]
    | <low_priority_lock_wait>
 }

<low_priority_lock_wait> ::=
{
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] ,
                          ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
}

Składnia dla usług Azure Synapse Analytics i Analytics Platform System (PDW).

ALTER INDEX { index_name | ALL }
    ON [ schema_name. ] table_name
{
      REBUILD {
            [ PARTITION = ALL [ WITH ( <rebuild_index_option> ) ] ]
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> ) ] ]
      }
    | DISABLE
    | REORGANIZE [ PARTITION = partition_number ]
}
[ ; ]

<rebuild_index_option> ::=
{
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
        [ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ]
    | XML_COMPRESSION = { ON | OFF }
        [ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ]
}

<single_partition_rebuild_index_option> ::=
{
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
    | XML_COMPRESSION = { ON | OFF }
}

Argumenty

index_name

Nazwa indeksu. Nazwy indeksów muszą być unikatowe w tabeli lub widoku, ale nie muszą być unikatowe w bazie danych. Nazwy indeksów muszą być zgodne z regułami identyfikatorów .

CAŁY

Określa wszystkie indeksy skojarzone z tabelą lub widokiem niezależnie od typu indeksu. Określenie ALL powoduje niepowodzenie instrukcji, jeśli co najmniej jeden indeks znajduje się w grupie plików w trybie offline lub tylko do odczytu albo określona operacja nie jest dozwolona w co najmniej jednym typie indeksu. W poniższej tabeli wymieniono operacje indeksów i niedozwolone typy indeksów.

Używanie ALL słowa kluczowego z tą operacją	Niepowodzenie, jeśli tabela ma co najmniej jedną
REBUILD WITH ONLINE = ON	Indeks XML Indeks przestrzenny Indeks magazynu kolumn w programie SQL Server 2017 (14.x) i starszych wersjach. Nowsze wersje obsługują ponowne kompilowanie w trybie online indeksów magazynu kolumn.
REBUILD PARTITION = <partition_number>	Indeks niepartycyjny, indeks XML, indeks przestrzenny lub indeks wyłączony
REORGANIZE	Indeksy z ALLOW_PAGE_LOCKS ustawione na OFF
REORGANIZE PARTITION = <partition_number>	Indeks niepartycyjny, indeks XML, indeks przestrzenny lub indeks wyłączony
IGNORE_DUP_KEY = ON	Indeks XML Indeks przestrzenny Indeks magazynu kolumn 1
ONLINE = ON	Indeks XML Indeks przestrzenny Indeks magazynu kolumn 1
RESUMABLE = ON	Indeksy z możliwością wznowienia nie są obsługiwane za pomocą słowa kluczowego ALL

Jeśli ALL zostanie określony z PARTITION = <partition_number>, wszystkie indeksy muszą być wyrównane. Oznacza to, że są one partycjonowane na podstawie równoważnych funkcji partycji. Użycie ALL z PARTITION powoduje ponowne skompilowanie lub reorganizację wszystkich partycji indeksu z tą samą <partition_number>. Aby uzyskać więcej informacji na temat indeksów partycjonowanych, zobacz Partycjonowane tabele i indeksy.

Aby uzyskać więcej informacji na temat operacji indeksowania online, zobacz wskazówki dotyczące operacji indeksowania online.

database_name

Nazwa bazy danych.

schema_name

Nazwa schematu, do którego należy tabela lub widok.

table_or_view_name

Nazwa tabeli lub widoku skojarzonego z indeksem. Aby wyświetlić szczegóły indeksu dla tabeli lub widoku, użyj widoku katalogu sys.indexes.

Usługa Azure SQL Database obsługuje trzyczęściowy format nazwy <database_name>.<schema_name>.<object_name>, gdy <database_name> jest bieżącą nazwą bazy danych lub <database_name> jest tempdb i <object_name> rozpoczyna się od # lub ##. Jeśli nazwa schematu jest dbo, można pominąć <schema_name>.

REBUILD [ WITH ( <rebuild_index_option> [ ,... n ] ]

Dotyczy: SQL Server 2012 (11.x) i nowsze wersje, Azure SQL Database i Azure SQL Managed Instance

Określa, że indeks jest przebudowany przy użyciu tych samych kolumn, typu indeksu, atrybutu unikatowości i kolejności sortowania. REBUILD włącza indeks wyłączony. Ponowne kompilowanie indeksu klastrowanego nie powoduje ponownego kompilowania skojarzonych indeksów nieklastrowanych, chyba że określono słowo kluczowe ALL. Jeśli nie określono opcji indeksu, zostaną zastosowane istniejące wartości opcji indeksu w sys.indexes. W przypadku każdej opcji indeksu, której wartość nie jest wyświetlana w sys.indexes, ma zastosowanie domyślna definicja argumentu opcji.

Jeśli ALL jest określona, a tabela bazowa jest stertą, operacja ponownego kompilowania nie ma wpływu na stertę. Wszystkie indeksy nieklastrowane skojarzone z tabelą zostaną ponownie skompilowane.

Operacja REBUILD może być minimalnie rejestrowana, jeśli model odzyskiwania bazy danych jest rejestrowany zbiorczo lub prosty.

Podczas ponownego kompilowania podstawowego indeksu XML podstawowa tabela użytkownika jest niedostępna przez czas trwania operacji indeksowania.

W przypadku indeksów magazynu kolumn operacja ponownego kompilowania:

• Rekompresuje wszystkie dane do magazynu kolumn. Istnieją dwie kopie indeksu magazynu kolumn, gdy trwa operacja ponownego kompilowania. Po zakończeniu odbudowy aparat bazy danych usuwa oryginalny indeks magazynu kolumn.
• Nie zachowuje kolejności sortowania, jeśli istnieje. Aby ponownie skompilować indeks magazynu kolumn i zachować lub wprowadzić kolejność sortowania, użyj instrukcji CREATE [CLUSTERED] COLUMNSTORE INDEX ... ORDER (...) ... WITH (DROP_EXISTING = ON).

Aby uzyskać więcej informacji, zobacz Optymalizowanie konserwacji indeksu w celu zwiększenia wydajności zapytań i zmniejszenia zużycia zasobów.

PARTYCJA

Określa, że tylko jedna partycja indeksu jest przebudowana lub zreorganizowana. PARTITION nie można określić, jeśli index_name nie jest indeksem partycjonowanym.

PARTITION = ALL ponownie kompiluje wszystkie partycje.

Ostrzeżenie

Tworzenie i ponowne kompilowanie nieprzywiązanych indeksów w tabeli z ponad 1000 partycjami jest możliwe, ale nie jest obsługiwane. Może to spowodować obniżenie wydajności lub nadmierne zużycie pamięci podczas tych operacji. Firma Microsoft zaleca używanie indeksów wyrównanych tylko wtedy, gdy liczba partycji przekracza 1000.

• partition_number

  Numer partycji indeksu partycjonowanego, który ma zostać przebudowany lub zreorganizowany. partition_number jest wyrażeniem stałym, które może odwoływać się do zmiennych. Obejmują one zmienne typu zdefiniowane przez użytkownika lub funkcje i funkcje zdefiniowane przez użytkownika, ale nie mogą odwoływać się do instrukcji Transact-SQL. partition_number musi istnieć lub instrukcja kończy się niepowodzeniem.

• WITH ( <single_partition_rebuild_index_option> )

  SORT_IN_TEMPDB, MAXDOP, DATA_COMPRESSIONi XML_COMPRESSION to opcje, które można określić podczas ponownego kompilowania pojedynczej partycji przy użyciu składni (PARTITION = partition_number). Nie można określić indeksów XML w jednej operacji ponownego kompilowania partycji.

WYŁĄCZAĆ

Oznacza indeks jako wyłączony i niedostępny do użycia przez aparat bazy danych. Każdy indeks można wyłączyć. Definicja indeksu wyłączonego pozostaje w katalogu systemowym bez bazowych danych indeksu. Wyłączenie indeksu klastrowanego uniemożliwia użytkownikowi dostęp do bazowych danych tabeli. Aby włączyć indeks, użyj ALTER INDEX REBUILD lub CREATE INDEX WITH DROP_EXISTING. Aby uzyskać więcej informacji, zobacz Wyłącz indeksy i ograniczenia oraz Włączanie indeksów i ograniczeń.

REORGANIZACJA indeksu magazynu wierszy

W przypadku indeksów magazynu wierszy REORGANIZE określa, aby zreorganizować poziom liścia indeksu. Operacja REORGANIZE to:

• Zawsze wykonywane w trybie online. Oznacza to, że długoterminowe blokady tabeli blokującej nie są przechowywane, a zapytania lub aktualizacje danych w tabeli bazowej mogą być kontynuowane podczas transakcji ALTER INDEX REORGANIZE.
• Niedozwolone dla indeksu wyłączonego.
• Niedozwolone, gdy ALLOW_PAGE_LOCKS jest ustawiona na wartość OFF.
• Nie można wycofać podczas wykonywania transakcji i transakcja jest cofana.

Nuta

Jeśli ALTER INDEX REORGANIZE używa jawnych transakcji (na przykład ALTER INDEX wewnątrz BEGIN TRAN ... COMMIT/ROLLBACK) zamiast domyślnego niejawnego trybu transakcji, zachowanie blokowania REORGANIZE staje się bardziej restrykcyjne, potencjalnie powodując blokowanie. Aby uzyskać więcej informacji na temat niejawnych transakcji, zobacz SET IMPLICIT_TRANSACTIONS.

Aby uzyskać więcej informacji, zobacz Optymalizowanie konserwacji indeksu w celu zwiększenia wydajności zapytań i zmniejszenia zużycia zasobów.

ZREORGANIZOWANIE ZA POMOCĄ ( LOB_COMPACTION = { ON | OFF } )

Dotyczy indeksów magazynu wierszy.

• NA

  • Określa kompaktowanie wszystkich stron zawierających dane tych dużych typów danych obiektów (LOB): obraz, tekst, ntext, varchar(max), nvarchar(max), varbinary(max)i xml. Kompaktowanie tych danych może zmniejszyć rozmiar danych na dysku.
  • W przypadku indeksu klastrowanego ta funkcja kompaktuje wszystkie kolumny BIZNESOWE, które znajdują się w tabeli.
  • W przypadku indeksu nieklastrowanego ta funkcja kompaktuje wszystkie kolumny LOB, które są kolumnami niekluczowymi (dołączonymi) w indeksie.
  • REORGANIZE ALL wykonuje kompaktowanie LOB na wszystkich indeksach. Dla każdego indeksu kompaktuje wszystkie kolumny LOB w indeksie klastrowanym, tabeli bazowej lub dołączone kolumny w indeksie nieklastrowanym.

• OD

  • Strony zawierające duże dane obiektów nie są kompaktowane.
  • OFF nie ma wpływu na stertę.

REORGANIZACJA indeksu magazynu kolumn

W przypadku indeksów magazynu kolumn REORGANIZE kompresuje każdą zamkniętą grupę wierszy różnicowych do magazynu kolumn jako skompresowaną grupę wierszy. Operacja REORGANIZE jest zawsze wykonywana w trybie online. Oznacza to, że długoterminowe blokady tabeli blokującej nie są przechowywane, a zapytania lub aktualizacje tabeli bazowej mogą być kontynuowane podczas transakcji ALTER INDEX REORGANIZE.

Aby uzyskać więcej informacji, zobacz Optymalizowanie konserwacji indeksu w celu zwiększenia wydajności zapytań i zmniejszenia zużycia zasobów.

• REORGANIZE nie jest wymagane w celu przeniesienia zamkniętych grup wierszy różnicowych do skompresowanych grup wierszy. Proces krotki tła (TM) jest okresowo wznawiany w celu skompresowania zamkniętych grup wierszy różnicowych. Zalecamy używanie REORGANIZE, gdy krotka-mover spada. REORGANIZE może bardziej agresywnie kompresować grupy wierszy.
• Aby skompresować wszystkie otwarte i zamknięte grupy wierszy, zobacz REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS).

W przypadku indeksów magazynu kolumn w programie SQL Server 2016 (13.x) i nowszych wersjach usługi Azure SQL Database i azure SQL Managed Instance REORGANIZE wykonuje następujące dodatkowe optymalizacje defragmentacji w trybie online:

• Fizycznie usuwa usunięte wiersze z grupy wierszy, gdy 10% lub więcej wierszy zostało logicznie usuniętych. Usunięte bajty są odzyskiwane na nośniku fizycznym. Jeśli na przykład skompresowana grupa wierszy zawierająca 1 milion wierszy zawiera 100 000 wierszy usuniętych, aparat bazy danych usuwa usunięte wiersze i ponownie komkompresuje grupę wierszy z 900 000 wierszy.

• Łączy co najmniej jedną skompresowaną grupę wierszy, aby zwiększyć liczbę wierszy na grupę wierszy maksymalnie 1048 576 wierszy. Na przykład w przypadku zbiorczego importowania 5 partii 102 400 wierszy otrzymasz 5 skompresowanych grup wierszy. Jeśli uruchomisz REORGANIZE, te grupy wierszy zostaną scalone z 1 skompresowaną grupą wierszy z 512 000 wierszami. Zakłada się, że nie ma ograniczeń rozmiaru słownika ani pamięci.

• W przypadku grup wierszy, w których 10% lub więcej wierszy zostało logicznie usuniętych, aparat bazy danych próbuje połączyć tę grupę wierszy z co najmniej jedną grupą wierszy. Na przykład grupa wierszy 1 jest kompresowana z 500 000 wierszy, a grupa wierszy 21 jest kompresowana z maksymalnie 1048 576 wierszy. Grupa wierszy 21 ma 60% usuniętych wierszy, 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.

ZREORGANIZOWANIE ZA POMOCĄ ( COMPRESS_ALL_ROW_GROUPS = { ON | OFF } )

Dotyczy indeksów magazynu kolumn.

Dotyczy: SQL Server 2016 (13.x) i nowsze wersje, Azure SQL Database i Azure SQL Managed Instance

COMPRESS_ALL_ROW_GROUPS umożliwia wymuszenie otwarcia lub zamknięcia grup wierszy różnicowych w magazynie kolumn. W przypadku tej opcji nie jest konieczne ponowne skompilowanie indeksu magazynu kolumn w celu opróżniania grup wierszy różnicowych. W połączeniu z innymi funkcjami usuwania i scalania defragmentacji nie jest już konieczne ponowne kompilowanie indeksu magazynu kolumn w większości sytuacji.

• NA

  Wymusza wszystkie grupy wierszy do magazynu kolumn, niezależnie od rozmiaru i stanu (zamknięte lub otwarte).

• OD

  Wymusza wszystkie zamknięte grupy wierszy do magazynu kolumn.

Aby uzyskać więcej informacji, zobacz Optymalizowanie konserwacji indeksu w celu zwiększenia wydajności zapytań i zmniejszenia zużycia zasobów.

SET ( opcja <set_index> [ ,... n ] )

Modyfikuje opcje indeksu bez ponownego kompilowania lub reorganizacji indeksu. SET nie można określić dla indeksu wyłączonego.

PAD_INDEX = { ON | WYŁ. }

Określa dopełnienie indeksu. Wartość domyślna to OFF.

• NA

  Procent wolnego miejsca określonego przez współczynnik wypełnienia jest stosowany do stron na poziomie pośrednim indeksu. Jeśli FILLFACTOR nie zostanie określona w tym samym czasie, PAD_INDEX zostanie ustawiona na ON, zostanie użyta wartość współczynnika wypełnienia w sys.indexes.

• OD

  Strony na poziomie pośrednim są wypełniane w pobliżu pojemności, pozostawiając wystarczającą ilość miejsca dla co najmniej jednego wiersza maksymalnego rozmiaru indeksu, biorąc pod uwagę zestaw kluczy na stronach pośrednich. Dzieje się tak również, jeśli PAD_INDEX jest ustawiona na ON, ale nie określono współczynnika wypełnienia.

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

FILLFACTOR = fillfactor

Określa wartość procentową wskazującą, jak pełny aparat bazy danych powinien ustawić poziom liścia każdej strony indeksu podczas tworzenia lub modyfikowania indeksu. Wartość fillfactor musi być wartością całkowitą z zakresu od 1 do 100. Wartość domyślna to 0. Wartości współczynnika wypełnienia 0 i 100 są takie same we wszystkich aspektach.

Jawne ustawienie FILLFACTOR ma zastosowanie tylko wtedy, gdy indeks zostanie utworzony lub ponownie utworzony. Aparat bazy danych nie zachowuje dynamicznie określonego procentu pustego miejsca na stronach. Aby uzyskać więcej informacji, zobacz CREATE INDEX.

Aby wyświetlić ustawienie współczynnika wypełnienia, użyj fill_factor w sys.indexes.

Ważny

Utworzenie indeksu z FILLFACTOR mniejszym niż 100 zwiększa ilość miejsca do magazynowania zajmowanego przez aparat bazy danych, ponieważ aparat bazy danych redystrybuuje dane zgodnie z współczynnikiem wypełnienia podczas tworzenia lub odbudowy indeksu.

SORT_IN_TEMPDB = { ON | WYŁ. }

Określa, czy mają być przechowywane tymczasowe wyniki sortowania w tempdb. Wartość domyślna to OFF z wyjątkiem warstwy Hiperskala usługi Azure SQL Database. W przypadku wszystkich operacji kompilacji indeksu w warstwie Hiperskala SORT_IN_TEMPDB jest zawsze ON, chyba że jest używana kompilacja indeksu z możliwością wznowienia. W przypadku wznawianych kompilacji indeksu SORT_IN_TEMPDB jest zawsze OFF.

• NA

  Wyniki sortowania pośredniego używane do kompilowania indeksu są przechowywane w tempdb. Może to skrócić czas wymagany do utworzenia indeksu. Zwiększa to jednak ilość miejsca na dysku używanego podczas kompilacji indeksu.

• OD

  Wyniki sortowania pośredniego są przechowywane w tej samej bazie danych co indeks.

Jeśli operacja sortowania nie jest wymagana lub jeśli sortowanie można wykonać w pamięci, opcja SORT_IN_TEMPDB zostanie zignorowana.

Aby uzyskać więcej informacji, zobacz SORT_IN_TEMPDB opcja indeksów.

IGNORE_DUP_KEY = { ON | WYŁ. }

Określa odpowiedź na błąd, gdy operacja wstawiania próbuje wstawić zduplikowane wartości klucza do unikatowego indeksu. Opcja IGNORE_DUP_KEY dotyczy tylko operacji wstawiania po utworzeniu lub ponownym utworzeniu indeksu. Wartość domyślna to OFF.

• NA

  Komunikat ostrzegawczy występuje, gdy zduplikowane wartości klucza są wstawione do unikatowego indeksu. Tylko wiersze naruszające ograniczenie unikatowości nie są wstawione.

• OD

  Komunikat o błędzie występuje, gdy zduplikowane wartości klucza są wstawione do unikatowego indeksu. Cała operacja INSERT jest cofana.

IGNORE_DUP_KEY nie można ustawić na ON dla indeksów utworzonych w widoku, indeksach innych niż unikatowe, indeksach XML, indeksach przestrzennych i filtrowanych indeksach.

Aby wyświetlić ustawienie IGNORE_DUP_KEY dla indeksu, użyj kolumny ignore_dup_key w widoku katalogu sys.indexes.

W składni zgodnej z poprzednimi wersjami WITH IGNORE_DUP_KEY jest równoważna WITH IGNORE_DUP_KEY = ON.

STATISTICS_NORECOMPUTE = { ON | WYŁ. }

Wyłącz lub włącz opcję automatycznej aktualizacji statystyk, AUTO_STATISTICS_UPDATE, dla statystyk dotyczących indeksu. Wartość domyślna to OFF.

• NA

  Automatyczne aktualizacje statystyk są wyłączone po ponownym skompilowania indeksu.

• OD

  Automatyczne aktualizacje statystyk są włączone po ponownym skompilowania indeksu.

Aby przywrócić automatyczne aktualizowanie statystyk, ustaw STATISTICS_NORECOMPUTE na OFFlub wykonaj UPDATE STATISTICS bez klauzuli NORECOMPUTE.

Ostrzeżenie

Jeśli wyłączysz automatyczną ponowną kompilację statystyk przez ustawienie STATISTICS_NORECOMPUTE = ON, możesz uniemożliwić optymalizatorowi zapytań wybranie optymalnych planów wykonywania zapytań dotyczących tabeli.

Ustawienie STATISTICS_NORECOMPUTE na wartość ON nie uniemożliwia aktualizacji statystyk indeksu występujących podczas operacji ponownego kompilowania indeksu.

STATISTICS_INCREMENTAL = { ON | WYŁ. }

Dotyczy: SQL Server 2014 (12.x) i nowsze wersje, Azure SQL Database i Azure SQL Managed Instance

W przypadku ONstatystyki utworzone na indeksie są statystykami partycji. Gdy OFF, istniejące statystyki zostaną usunięte, a aparat bazy danych ponownie skompiluje statystyki. Wartość domyślna to OFF.

Jeśli statystyki poszczególnych partycji nie są obsługiwane, opcja zostanie zignorowana i zostanie wygenerowane ostrzeżenie. Statystyki przyrostowe nie są obsługiwane w następujących przypadkach:

• Statystyki utworzone za pomocą indeksów, które nie są dostosowane do partycji z tabelą podstawową
• Statystyki utworzone w przypadku pomocniczych baz danych z możliwością odczytu grupy dostępności
• Statystyki utworzone w bazach danych tylko do odczytu
• Statystyki utworzone dla filtrowanych indeksów
• Statystyki utworzone w widokach
• Statystyki utworzone w tabelach wewnętrznych
• Statystyki utworzone za pomocą indeksów przestrzennych lub indeksów XML

ONLINE = { ON | WYŁ. }

Określa, czy tabele bazowe i skojarzone indeksy są dostępne dla zapytań i modyfikacji danych podczas operacji indeksu. Wartość domyślna to OFF.

W przypadku indeksu XML lub indeksu przestrzennego obsługiwana jest tylko ONLINE = OFF, a jeśli ONLINE jest ustawiona na ON zostanie zgłoszony błąd.

Ważny

Operacje indeksowania online nie są dostępne w każdej wersji programu Microsoft SQL Server. Aby uzyskać listę funkcji obsługiwanych przez wersje programu SQL Server, zobacz Editions i obsługiwane funkcje programu SQL Server 2022.

• NA

  Długoterminowe blokady tabeli nie są przechowywane przez czas trwania operacji indeksu. W fazie głównej operacji indeksowania blokada intencji udostępnionej (IS) jest przechowywana w tabeli źródłowej. Umożliwia to kontynuowanie zapytań lub aktualizacji podstawowej tabeli i indeksów. Na początku operacji blokada współużytkowanego (S) jest przechowywana w obiekcie źródłowym przez krótki czas. Na końcu operacji przez krótki czas nabyty jest blokada współużytkowana (S) na obiekcie, jeśli tworzony jest indeks nieklastrowany. Modyfikacja schematu (Sch-M) jest uzyskiwana podczas tworzenia lub porzucania klastrowanego indeksu w trybie online i odbudowy indeksu klastrowanego lub nieklastrowanego. ONLINE nie można ustawić na ON podczas tworzenia indeksu w lokalnej tabeli tymczasowej.

  Nuta

  Możesz użyć opcji WAIT_AT_LOW_PRIORITY, aby zmniejszyć lub uniknąć blokowania podczas operacji indeksowania online. Aby uzyskać więcej informacji, zobacz WAIT_AT_LOW_PRIORITY z operacjami indeksowania online.

• OD

  Blokady tabeli są stosowane do czasu trwania operacji indeksu. Operacja indeksu w trybie offline, która tworzy, kompiluje lub usuwa indeks klastrowany, przestrzenny lub XML albo ponownie kompiluje lub usuwa indeks nieklastrowany, uzyskuje modyfikację schematu (Sch-M) blokady w tabeli. Zapobiega to dostępowi wszystkich użytkowników do tabeli bazowej przez czas trwania operacji. Operacja indeksu w trybie offline, która tworzy indeks nieklastrowany, początkowo uzyskuje blokadę współużytkowaną (S) w tabeli. Zapobiega to modyfikacjom podstawowej definicji tabeli, ale umożliwia odczytywanie i modyfikowanie danych w tabeli, gdy kompilacja indeksu jest w toku.

Aby uzyskać więcej informacji, zobacz Wykonywanie operacji indeksowania w trybie online i wytyczne dotyczące operacji indeksowania online.

Indeksy, w tym indeksy w globalnych tabelach tymczasowych, można odtworzyć w trybie online, z wyjątkiem następujących przypadków:

• Indeks XML
• Indeksowanie w lokalnej tabeli tymczasowej
• Początkowy unikatowy indeks klastrowany w widoku
• Wyłączone indeksy klastrowane
• Klastrowane indeksy magazynu kolumn w programie SQL Server 2017 (14.x)) i starszych wersjach
• Nieklastrowane indeksy magazynu kolumn w programie SQL Server 2016 (13.x)) i starszych wersjach
• Indeks klastrowany, jeśli tabela bazowa zawiera typy danych BIZNESOWYCH (obraz, ntext, tekst) i typy danych przestrzennych
• varchar(max) i kolumny varbinary(max) nie mogą być częścią klucza indeksu. W programie SQL Server (począwszy od programu SQL Server 2012 (11.x)) w usłudze Azure SQL Database i w usłudze Azure SQL Managed Instance, gdy tabela zawiera varchar(max) lub varbinary(max) kolumny, indeks klastrowany zawierający inne kolumny można skompilować lub skompilować przy użyciu opcji ONLINE.

Aby uzyskać więcej informacji, zobacz Jak działają operacje indeksowania online.

WZNAWIANIE = { WŁ. | WYŁ.}

Dotyczy: SQL Server 2017 (14.x) i nowsze wersje, Azure SQL Database i Azure SQL Managed Instance

Określa, czy operacja indeksu online jest wznawiana.

• NA

  Operacja indeksu jest wznawiana.

• OD

  Operacja indeksu nie jest wznawiana.

MAX_DURATION = czas [ MINUT ] używany z RESUMABLE = ON (wymaga ONLINE = ON)

Dotyczy: SQL Server 2017 (14.x) i nowsze wersje, Azure SQL Database i Azure SQL Managed Instance

Określa, jak długo w minutach całkowitych wykonywana jest operacja indeksu z możliwością wznowienia, zanim zostanie wstrzymana.

ALLOW_ROW_LOCKS = { ON | WYŁ. }

Określa, czy blokady wierszy są dozwolone. Wartość domyślna to ON.

• NA

  Blokady wierszy są dozwolone podczas uzyskiwania dostępu do indeksu. Aparat bazy danych określa, kiedy są używane blokady wierszy.

• OD

  Blokady wierszy nie są używane.

ALLOW_PAGE_LOCKS = { ON | WYŁ. }

Określa, czy blokady strony są dozwolone. Wartość domyślna to ON.

• NA

  Blokady stron są dozwolone podczas uzyskiwania dostępu do indeksu. Aparat bazy danych określa, kiedy są używane blokady strony.

• OD

  Blokady stron nie są używane.

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | WYŁ. }

Dotyczy: SQL Server 2019 (15.x) i nowsze wersje, Azure SQL Database i Azure SQL Managed Instance

Określa, czy należy zoptymalizować, aby uniknąć rywalizacji o wstawianie ostatniej strony. Wartość domyślna to OFF. Aby uzyskać więcej informacji, zobacz sekwencyjne klucze.

MAXDOP = max_degree_of_parallelism

Zastępuje maksymalny stopień równoległości opcji konfiguracji dla operacji indeksowania. Aby uzyskać więcej informacji, zobacz Configure the max degree of parallelism Server Configuration Option. Użyj MAXDOP, aby ograniczyć stopień równoległości i wynikowe użycie zasobów dla operacji kompilacji indeksu.

Mimo że opcja MAXDOP jest syntaktycznie obsługiwana dla wszystkich indeksów XML i indeksów przestrzennych, ALTER INDEX obecnie używa tylko jednego procesora.

max_degree_of_parallelism może to być:

• 1

  Pomija generowanie planu równoległego.

• >1

  Ogranicza maksymalny stopień równoległości używany w operacji indeksowania równoległego do określonej liczby lub mniejszej na podstawie bieżącego obciążenia systemu.

• 0 (wartość domyślna)

  Używa stopnia równoległości określonego na poziomie serwera, bazy danych lub grupy obciążenia, chyba że zostanie zmniejszona na podstawie bieżącego obciążenia systemu.

Aby uzyskać więcej informacji, zobacz Konfigurowanie operacji indeksowania równoległego.

Nuta

Operacje indeksowania równoległego nie są dostępne w każdej wersji programu SQL Server. Aby uzyskać listę funkcji obsługiwanych przez wersje programu SQL Server, zobacz Editions i obsługiwane funkcje programu SQL Server 2022.

COMPRESSION_DELAY = { 0 | czas trwania [ min ] }

Dotyczy: PROGRAMU SQL Server (począwszy od programu SQL Server 2016 (13.x)), usługi Azure SQL Database i usługi Azure SQL Managed Instance

W przypadku tabeli opartej na dysku z indeksem magazynu kolumn określa minimalną liczbę minut, przez które grupa wierszy różnicowych w stanie zamkniętym musi pozostać w magazynie różnicowym, zanim aparat bazy danych będzie mógł skompresować go do skompresowanej grupy wierszy. Ponieważ tabele oparte na dyskach nie śledzą czasów wstawiania i aktualizowania poszczególnych wierszy, aparat bazy danych stosuje to opóźnienie tylko do grup wierszy magazynu różnicowego w stanie zamkniętym.

Wartość domyślna to 0 minut.

Aby uzyskać zalecenia dotyczące używania COMPRESSION_DELAY, zobacz Wprowadzenie do magazynu kolumn na potrzeby analizy operacyjnej w czasie rzeczywistym.

DATA_COMPRESSION

Określa opcję kompresji danych dla określonego indeksu, numeru partycji lub zakresu partycji. Opcje są następujące:

• ŻADEN

  Indeks lub określone partycje nie są kompresowane. Nie dotyczy to indeksów magazynu kolumn.

• SZEREG

  Indeks lub określone partycje są kompresowane przy użyciu kompresji wierszy. Nie dotyczy to indeksów magazynu kolumn.

• STRONA

  Indeks lub określone partycje są kompresowane przy użyciu kompresji strony. Nie dotyczy to indeksów magazynu kolumn.

• MAGAZYN KOLUMN

  Dotyczy: SQL Server 2014 (12.x) i nowsze wersje, Azure SQL Database i Azure SQL Managed Instance

  Dotyczy tylko indeksów magazynu kolumn, w tym zarówno nieklastrowanych magazynów kolumn, jak i klastrowanych indeksów magazynu kolumn. Określenie COLUMNSTORE usuwa wszystkie inne kompresje danych, w tym COLUMNSTORE_ARCHIVE.

• COLUMNSTORE_ARCHIVE

  Dotyczy: SQL Server 2014 (12.x) i nowsze wersje, Azure SQL Database i Azure SQL Managed Instance

  Dotyczy tylko indeksów magazynu kolumn, w tym zarówno nieklastrowanych magazynów kolumn, jak i klastrowanych indeksów magazynu kolumn. COLUMNSTORE_ARCHIVE dodatkowo kompresuje określoną partycję do mniejszego rozmiaru. Może to być używane do archiwizacji lub w innych sytuacjach, które wymagają mniejszego rozmiaru magazynu i mogą pozwolić na więcej czasu na przechowywanie i pobieranie.

Aby uzyskać więcej informacji na temat kompresji, zobacz Kompresja danych.

XML_COMPRESSION

Dotyczy: SQL Server 2022 (16.x) i nowsze wersje, Azure SQL Database i Azure SQL Managed Instance

Określa opcję kompresji XML dla określonego indeksu, który zawiera co najmniej jedną kolumnę xml typu danych. Opcje są następujące:

• NA

  Indeks lub określone partycje są kompresowane przy użyciu kompresji XML.

• OD

  Indeks lub określone partycje nie są kompresowane.

ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,... n ] )

Określa partycje, do których mają zastosowanie ustawienia DATA_COMPRESSION lub XML_COMPRESSION. Jeśli indeks nie jest partycjonowany, argument ON PARTITIONS generuje błąd. Jeśli nie podano klauzuli ON PARTITIONS, opcja DATA_COMPRESSION lub XML_COMPRESSION dotyczy wszystkich partycji indeksu partycjonowanego.

<partition_number_expression> można określić w następujący sposób:

• Podaj liczbę partycji, na przykład: ON PARTITIONS (2).
• Podaj numery partycji dla kilku pojedynczych partycji rozdzielonych przecinkami, na przykład: ON PARTITIONS (1, 5).
• Podaj zarówno zakresy, jak i poszczególne partycje: ON PARTITIONS (2, 4, 6 TO 8).

<range> można określić jako numery partycji oddzielone wyrazem TO, na przykład: ON PARTITIONS (6 TO 8).

Aby ustawić różne typy kompresji danych dla różnych partycji, określ opcję DATA_COMPRESSION więcej niż raz, na przykład:

REBUILD WITH
(
DATA_COMPRESSION = NONE ON PARTITIONS (1),
DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8),
DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5)
);

Można również określić opcję XML_COMPRESSION więcej niż raz, na przykład:

REBUILD WITH
(
XML_COMPRESSION = OFF ON PARTITIONS (1),
XML_COMPRESSION = ON ON PARTITIONS (2, 4, 6 TO 8),
XML_COMPRESSION = OFF ON PARTITIONS (3, 5)
);

WZNAWIAĆ

Dotyczy: SQL Server 2017 (14.x) i nowsze wersje, Azure SQL Database i Azure SQL Managed Instance

Wznawia operację indeksu wstrzymaną ręcznie, ponieważ osiągnięto maksymalny czas trwania lub z powodu awarii.

• MAX_DURATION

  Określa, jak długo, w minutach całkowitych, wznawiana operacja indeksu jest wykonywana po wznowieniu przed ponownym wstrzymaniem.

• WAIT_AT_LOW_PRIORITY

  Wznawianie operacji kompilacji indeksu po wstrzymaniu musi uzyskać niezbędne blokady. WAIT_AT_LOW_PRIORITY wskazuje, że operacja kompilacji indeksu uzyskuje blokady o niskim priorytecie, co umożliwia kontynuowanie innych operacji podczas oczekiwania operacji kompilacji indeksu. Pominięcie opcji WAIT_AT_LOW_PRIORITY jest równoważne WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE). Aby uzyskać więcej informacji, zobacz WAIT_AT_LOW_PRIORITY.

PAUZA

Dotyczy: SQL Server 2017 (14.x) i nowsze wersje, Azure SQL Database i Azure SQL Managed Instance

Wstrzymuje operację kompilacji indeksu z możliwością wznowienia.

PRZERWAĆ

Dotyczy: SQL Server 2017 (14.x) i nowsze wersje, Azure SQL Database i Azure SQL Managed Instance

Przerywa uruchomioną lub wstrzymaną operację kompilacji indeksu, która została uruchomiona jako wznawiana. Aby zakończyć operację kompilacji indeksu możliwego do wznowienia, należy jawnie wykonać polecenie ABORT. Niepowodzenie lub wstrzymanie w operacji indeksu z możliwością wznowienia nie kończy wykonywania; zamiast tego pozostawia operację w stanie nieokreślonym wstrzymania.

Uwagi

ALTER INDEX nie można użyć do ponownego partycjonowania indeksu ani przeniesienia go do innej grupy plików. Tej instrukcji nie można użyć do modyfikowania definicji indeksu, takiej jak dodawanie lub usuwanie kolumn lub zmienianie kolejności kolumn. Użyj CREATE INDEX z klauzulą DROP_EXISTING, aby wykonać te operacje.

Jeśli opcja nie zostanie jawnie określona, zostanie zastosowane bieżące ustawienie. Jeśli na przykład ustawienie FILLFACTOR nie zostanie określone w klauzuli REBUILD, wartość współczynnika wypełnienia przechowywana w katalogu systemowym jest używana podczas procesu odbudowy. Aby wyświetlić bieżące ustawienia opcji indeksu, użyj sys.indexes.

Wartości ONLINE, MAXDOPi SORT_IN_TEMPDB nie są przechowywane w katalogu systemowym. Jeśli nie zostanie określona w instrukcji indeksu, zostanie użyta wartość domyślna dla opcji.

Na komputerach wieloprocesorowych, podobnie jak w przypadku innych zapytań, ALTER INDEX REBUILD automatycznie używa większej liczby procesorów do wykonywania operacji skanowania i sortowania skojarzonych z modyfikowaniem indeksu. Z drugiej strony ALTER INDEX REORGANIZE jest operacją jednowątkową. Aby uzyskać więcej informacji, zobacz Konfigurowanie operacji indeksowania równoległego.

W usłudze SQL Database w usłudze Microsoft Fabric ALTER INDEX ALL nie jest obsługiwana, ale ALTER INDEX <index name>.

Ponowne kompilowanie indeksów

Ponowne kompilowanie indeksu spada i ponowne tworzenie indeksu. Spowoduje to usunięcie fragmentacji, odzyskanie miejsca na dysku przez skompaktowanie stron na podstawie określonego lub istniejącego ustawienia współczynnika wypełnienia i zmianę kolejności wierszy indeksu na ciągłych stronach. Po określeniu ALL wszystkie indeksy w tabeli zostaną porzucone i ponownie utworzone w jednej transakcji. Ograniczenia klucza obcego nie muszą być usuwane z wyprzedzeniem. Gdy indeksy o rozmiarze co najmniej 128 zakresów zostaną ponownie skompilowane, aparat bazy danych wyzywając rzeczywiste przydziały strony i skojarzone z nimi blokady do momentu zatwierdzenia transakcji. Aby uzyskać więcej informacji, zobacz odroczonej alokacji transakcji.

Aby uzyskać więcej informacji, zobacz Optymalizowanie konserwacji indeksu w celu zwiększenia wydajności zapytań i zmniejszenia zużycia zasobów.

Zreorganizowanie indeksów

Reorganizacja indeksu używa minimalnych zasobów systemowych. Defragmentuje poziom liści klastrowanych i nieklastrowanych indeksów w tabelach i widokach, fizycznie zmieniając kolejność stron na poziomie liścia, aby dopasować logiczne, od lewej do prawej, kolejność węzłów liścia. Reorganizacja kompaktuje również strony indeksu. Kompaktowanie jest oparte na istniejącej wartości współczynnika wypełnienia.

Po określeniu ALL indeksy relacyjne, zarówno klastrowane, jak i nieklastrowane, a indeksy XML w tabeli są reorganizowane. Niektóre ograniczenia mają zastosowanie podczas określania ALL.

Aby uzyskać więcej informacji, zobacz Optymalizowanie konserwacji indeksu w celu zwiększenia wydajności zapytań i zmniejszenia zużycia zasobów.

Nuta

W przypadku tabeli ze uporządkowanym indeksem magazynu kolumn ALTER INDEX REORGANIZE nie sortuje ponownie danych. Aby skorzystać z danych, użyj ALTER INDEX REBUILD.

Wyłączanie indeksów

Wyłączenie indeksu uniemożliwia użytkownikowi dostęp do indeksu i dla indeksów klastrowanych do danych tabeli bazowej. Definicja indeksu pozostaje w katalogu systemowym. Wyłączenie indeksu nieklastrowanego lub indeksu klastrowanego w widoku fizycznie usuwa dane indeksu. Wyłączenie indeksu klastrowanego uniemożliwia dostęp do danych, ale dane pozostają niezamierzone w drzewie B do momentu usunięcia lub ponownego skompilowania indeksu. Aby sprawdzić, czy indeks jest wyłączony, użyj kolumny is_disabled w widoku wykazu sys.indexes.

Nuta

W dokumentacji jest zwykle używany termin B-tree w odniesieniu do indeksów. W indeksach magazynu wierszy aparat 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.

Jeśli tabela znajduje się w publikacji replikacji transakcyjnej, nie można wyłączyć indeksu skojarzonego z ograniczeniem klucza podstawowego. Te indeksy są wymagane przez replikację. Aby wyłączyć taki indeks, należy najpierw usunąć tabelę z publikacji. Aby uzyskać więcej informacji, zobacz Publikowanie danych i obiektów bazy danych.

Użyj instrukcji ALTER INDEX REBUILD lub instrukcji CREATE INDEX WITH DROP_EXISTING, aby włączyć indeks. Nie można ponownie skompilować wyłączonego indeksu klastrowanego przy użyciu opcji ONLINE ustawionej na ON. Aby uzyskać więcej informacji, zobacz Wyłącz indeksy i ograniczenia.

Ustawianie opcji

Można ustawić opcje ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, OPTIMIZE_FOR_SEQUENTIAL_KEY, IGNORE_DUP_KEYi STATISTICS_NORECOMPUTE dla określonego indeksu bez odbudowy lub reorganizacji tego indeksu. Zmodyfikowane wartości są natychmiast stosowane do indeksu. Aby wyświetlić te ustawienia, użyj sys.indexes. Aby uzyskać więcej informacji, zobacz Ustawianie opcji indeksu.

Opcje blokad wierszy i stron

Gdy ALLOW_ROW_LOCKS = ON i ALLOW_PAGE_LOCK = ON, blokady na poziomie wiersza, na poziomie strony i na poziomie tabeli są dozwolone podczas uzyskiwania dostępu do indeksu. Aparat bazy danych wybiera odpowiednią blokadę i może eskalować blokadę z wiersza lub blokady strony do blokady tabeli.

W przypadku ALLOW_ROW_LOCKS = OFF i ALLOW_PAGE_LOCK = OFFtylko blokada na poziomie tabeli jest dozwolona podczas uzyskiwania dostępu do indeksu.

Jeśli ALL zostanie określona, gdy zostaną ustawione opcje blokowania wiersza lub strony, ustawienia zostaną zastosowane do wszystkich indeksów. Gdy tabela bazowa jest stertą, ustawienia są stosowane w następujący sposób:

Opcja	Dotyczy
ALLOW_ROW_LOCKS = ON lub OFF	Sterta i wszystkie skojarzone indeksy nieklastrowane.
ALLOW_PAGE_LOCKS = ON	Sterta i wszystkie skojarzone indeksy nieklastrowane.
ALLOW_PAGE_LOCKS = OFF	Indeksy nieklastrowane, w których wszystkie blokady stron nie są dozwolone. W stercie tylko udostępnione (S), aktualizacja (U) i wyłączne (X) blokady stron nie są dozwolone. Aparat bazy danych może nadal uzyskiwać blokady stron intencji (IS, IUlub IX) do celów wewnętrznych.

Ostrzeżenie

Nie zaleca się wyłączania blokad wierszy lub stron w indeksie. Mogą wystąpić problemy związane ze współbieżnością, a niektóre funkcje mogą być niedostępne. Na przykład nie można zreorganizować indeksu, gdy ALLOW_PAGE_LOCKS jest ustawiona na OFF.

Operacje indeksowania online

Podczas ponownego kompilowania indeksu i opcji ONLINE jest ustawiona wartość ON, dane w indeksie, skojarzona z nią tabela i inne indeksy w tej samej tabeli są dostępne dla zapytań i modyfikacji. Możesz również ponownie skompilować część indeksu znajdującego się w jednej partycji w trybie online. Ekskluzywne blokady tabel są przechowywane tylko przez krótki czas na końcu odbudowy indeksu.

Reorganizacja indeksu jest zawsze wykonywana w trybie online. Proces przechowuje blokady tylko przez krótki czas i jest mało prawdopodobne, aby zablokować zapytania lub aktualizacje.

Równoczesne operacje indeksowania online można wykonywać na tej samej partycji tabeli lub tabeli tylko podczas wykonywania następujących operacji:

• Tworzenie wielu indeksów nieklastrowanych.
• Reorganizacja różnych indeksów w tej samej tabeli.
• Reorganizacja różnych indeksów podczas ponownego kompilowania indeksów bez zmian w tej samej tabeli.

Wszystkie inne operacje indeksowania online wykonywane w tym samym czasie kończą się niepowodzeniem. Na przykład nie można ponownie skompilować co najmniej dwóch indeksów w tej samej tabeli jednocześnie ani utworzyć nowego indeksu podczas ponownego kompilowania istniejącego indeksu w tej samej tabeli.

Aby uzyskać więcej informacji, zobacz Wykonywanie operacji indeksowania w trybie online.

Operacje indeksu z możliwością wznowienia

Dotyczy: SQL Server 2017 (14.x) i nowsze wersje, Azure SQL Database i Azure SQL Managed Instance

Można ponownie skompilować indeks online. Oznacza to, że ponowne kompilowanie indeksu można zatrzymać, a następnie ponownie uruchomić z punktu, w którym został zatrzymany. Aby uruchomić ponowne kompilowanie indeksu jako możliwe do wznowienia, określ opcję RESUMABLE = ON.

Poniższe wskazówki dotyczą operacji indeksowania z możliwością wznowienia:

• Aby użyć opcji RESUMABLE, należy również użyć opcji ONLINE.
• Opcja RESUMABLE nie jest utrwalana w metadanych dla danego indeksu i ma zastosowanie tylko do czasu trwania bieżącej instrukcji DDL. Dlatego należy jawnie określić klauzulę RESUMABLE = ON, aby umożliwić wznawianie.
• Opcję MAX_DURATION można określić w dwóch kontekstach:
  • MAX_DURATION dla opcji RESUMABLE określa przedział czasu dla tworzonego indeksu. Po upływie tego czasu i jeśli kompilacja indeksu jest nadal uruchomiona, zostanie wstrzymana. Decydujesz, kiedy można wznowić kompilację dla wstrzymanego indeksu. Czas w minutach dla MAX_DURATION musi być dłuższy niż 0 minut i krótszy niż lub równy jeden tydzień (7 * 24 * 60 = 10080 minut). Długa przerwa w operacji indeksu może znacząco wpłynąć na wydajność DML w określonej tabeli, a także pojemność dysku bazy danych, ponieważ zarówno oryginalny indeks, jak i nowo utworzony indeks wymagają miejsca na dysku i muszą zostać zaktualizowane przez operacje DML. Jeśli opcja MAX_DURATION zostanie pominięta, operacja indeksu będzie kontynuowana do momentu ukończenia lub do momentu wystąpienia błędu.
  • MAX_DURATION dla opcji WAIT_AT_LOW_PRIORITY określa czas oczekiwania przy użyciu blokad o niskim priorytekcie, jeśli operacja indeksu jest zablokowana, przed podjęciem akcji. Aby uzyskać więcej informacji, zobacz WAIT_AT_LOW_PRIORITY z operacjami indeksowania online.
• Aby natychmiast wstrzymać operację indeksu, możesz wykonać polecenie ALTER INDEX PAUSE lub wykonać polecenie KILL <session_id>.
• Ponowne wykonanie oryginalnej instrukcji ALTER INDEX REBUILD z tymi samymi parametrami wznawia wstrzymaną operację ponownego kompilowania indeksu. Można również wznowić wstrzymaną operację ponownego kompilowania indeksu, wykonując instrukcję ALTER INDEX RESUME.
• Polecenie ABORT zabija sesję uruchamiającą kompilację indeksu i anuluje operację indeksu. Nie można wznowić operacji indeksowania, która została przerwana.
• Podczas wznawiania operacji ponownego kompilowania indeksu, która jest wstrzymana, można zmienić wartość MAXDOP na nową wartość. Jeśli MAXDOP nie zostanie określona podczas wznawiania operacji indeksowania, która jest wstrzymana, zostanie użyta wartość MAXDOP użyta do ostatniego wznowienia. Jeśli w ogóle nie określono opcji MAXDOP dla operacji ponownego kompilowania indeksu, zostanie użyta wartość domyślna.

Operacja indeksu z możliwością wznowienia jest uruchamiana do momentu ukończenia, wstrzymania lub niepowodzenia. Jeśli operacja zostanie wstrzymana, zostanie wyświetlony błąd wskazujący, że operacja została wstrzymana i że ponowna kompilacja indeksu nie została ukończona. Jeśli operacja zakończy się niepowodzeniem, zostanie również wyświetlony błąd.

Aby sprawdzić, czy operacja indeksu jest wykonywana jako operacja z możliwością wznowienia i sprawdzić jego bieżący stan wykonania, użyj widoku wykazu sys.index_resumable_operations.

Zasoby

Następujące zasoby są wymagane do wznowienia operacji indeksowania:

• Dodatkowe miejsce wymagane do zachowania kompilowania indeksu, w tym czas wstrzymania kompilacji.
• Dodatkowa przepływność dziennika w fazie sortowania. Ogólne użycie miejsca w dzienniku dla indeksu z możliwością wznowienia jest mniejsze w porównaniu do regularnego ponownego kompilowania indeksu online i umożliwia obcinanie dzienników podczas tej operacji.
• Instrukcje DDL próbujące zmodyfikować indeks, który jest kompilowany lub skojarzona z nią tabela, podczas gdy operacja indeksu jest wstrzymana, nie są dozwolone.
• Czyszczenie duchów jest blokowane w indeksie kompilacji przez czas trwania operacji zarówno podczas wstrzymania, jak i podczas wykonywania operacji.
• Jeśli tabela zawiera kolumny LOB, wznawiana kompilacja indeksu klastrowanego wymaga modyfikacji schematu (Sch-M) blokady na początku operacji.

Bieżące ograniczenia funkcjonalności

Operacje ponownego kompilowania indeksu z możliwością wznowienia mają następujące ograniczenia:

• Opcja SORT_IN_TEMPDB = ON nie jest obsługiwana w przypadku operacji indeksowania z możliwością wznowienia.
• Nie można wykonać polecenia DDL z RESUMABLE = ON wewnątrz jawnej transakcji.
• Nie można utworzyć indeksu, który zawiera:
  • Obliczone lub znacznik czasu/kolumny rowversion jako kolumny klucza.
  • Kolumna LOB jako dołączona kolumna.
• Operacje indeksu z możliwością wznowienia nie są obsługiwane w następujących celach:
  • Polecenie ALTER INDEX REBUILD ALL
  • Polecenie ALTER TABLE REBUILD
  • Indeksy magazynu kolumn
  • Przefiltrowane indeksy
  • Wyłączone indeksy

WAIT_AT_LOW_PRIORITY z operacjami indeksowania online

Dotyczy: SQL Server 2014 (12.x) i nowsze wersje, Azure SQL Database i Azure SQL Managed Instance

Jeśli nie używasz opcji WAIT_AT_LOW_PRIORITY, wszystkie aktywne transakcje blokujące przechowujące blokady w tabeli lub indeksie muszą zostać ukończone, aby operacja ponownego kompilowania indeksu została uruchomiona i zakończona. Po rozpoczęciu i zakończeniu operacji indeksu online należy uzyskać udostępniony (S) lub modyfikację schematu (Sch-M) zablokować tabelę i przechowywać ją przez krótki czas. Mimo że blokada jest przechowywana tylko przez krótki czas, może ona znacząco wpłynąć na przepływność obciążenia, zwiększyć opóźnienie zapytań lub spowodować przekroczenie limitu czasu wykonywania.

Aby uniknąć tych problemów, opcja WAIT_AT_LOW_PRIORITY umożliwia zarządzanie zachowaniem S lub Sch-M blokad wymaganych do rozpoczęcia i zakończenia operacji indeksowania online, wybierając spośród trzech opcji. We wszystkich przypadkach, jeśli w czasie oczekiwania określonym przez MAX_DURATION = n [minutes] nie ma blokady, która obejmuje operację indeksu, operacja indeksu jest wykonywana natychmiast.

WAIT_AT_LOW_PRIORITY sprawia, że operacja indeksu online czeka przy użyciu blokad o niskim priorytekcie, umożliwiając innym operacjom korzystanie z normalnych blokad priorytetu w międzyczasie. Pominięcie opcji WAIT_AT_LOW_PRIORITY jest równoważne WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = czas [MINUTES]

Czas oczekiwania (wartość całkowita określona w minutach) oczekiwania operacji indeksu online przy użyciu blokad o niskim priorytekcie. Jeśli operacja zostanie zablokowana przez MAX_DURATION czasu, zostanie wykonana określona akcja ABORT_AFTER_WAIT. MAX_DURATION czas jest zawsze w minutach, a słowo MINUTES można pominąć.

ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS ]

• NONE: Kontynuuj oczekiwanie na blokadę z normalnym priorytetem.
• SELF: Zakończ obecnie wykonywaną operację indeksu online bez podejmowania żadnych akcji. Nie można użyć opcji SELF, gdy MAX_DURATION wynosi 0.
• BLOCKERS: zabij wszystkie transakcje użytkownika, które blokują operację indeksu online, aby operacja mogła kontynuować. Opcja BLOCKERS wymaga, aby podmiot zabezpieczeń wykonujący instrukcję CREATE INDEX lub ALTER INDEX miał uprawnienie ALTER ANY CONNECTION.

Za pomocą następujących zdarzeń rozszerzonych można monitorować operacje indeksowania oczekujące na blokady o niskim priorytekcie:

• lock_request_priority_state
• process_killed_by_abort_blockers
• ddl_with_wait_at_low_priority

Ograniczenia indeksu przestrzennego

Podczas ponownego kompilowania indeksu przestrzennego podstawowa tabela użytkownika jest niedostępna podczas operacji indeksu.

Nie można zmodyfikować ograniczenia PRIMARY KEY w tabeli użytkownika, gdy indeks przestrzenny jest zdefiniowany w kolumnie tej tabeli. Aby zmienić ograniczenie PRIMARY KEY, najpierw upuść każdy indeks przestrzenny tabeli. Po zmodyfikowaniu ograniczenia PRIMARY KEY można ponownie utworzyć każdy z indeksów przestrzennych.

W operacji ponownej kompilacji pojedynczej partycji nie można określić żadnych indeksów przestrzennych. Można jednak określić indeksy przestrzenne w odbudowywaniu tabeli.

Aby zmienić opcje specyficzne dla indeksu przestrzennego, takie jak BOUNDING_BOX lub GRID, możesz użyć instrukcji CREATE SPATIAL INDEX, która określa DROP_EXISTING = ONlub upuść indeks przestrzenny i utworzyć nowy. Aby zapoznać się z przykładem, zobacz CREATE SPATIAL INDEX.

Kompresja danych

Aby uzyskać więcej informacji na temat kompresji danych, zobacz Kompresja danych.

Poniżej przedstawiono kluczowe kwestie, które należy wziąć pod uwagę w kontekście operacji kompilacji indeksu, gdy jest używana kompresja danych:

• Kompresja może zezwalać na przechowywanie większej liczby wierszy na stronie, ale nie zmienia maksymalnego rozmiaru wiersza.
• Strony inne niż liścia indeksu nie są kompresowane na stronie, ale mogą być kompresowane wierszami.
• Każdy indeks nieklastrowany ma indywidualne ustawienie kompresji i nie dziedziczy ustawienia kompresji bazowej tabeli.
• Po utworzeniu klastrowanego indeksu na stercie indeks klastrowany dziedziczy stan kompresji sterta, chyba że określono alternatywny stan kompresji.

Następujące zagadnienia dotyczą ponownego kompilowania indeksów partycjonowanych:

• Nie można zmienić ustawienia kompresji pojedynczej partycji, jeśli tabela ma nieprzywiązane indeksy.
• Składnia ALTER INDEX <index> ... REBUILD PARTITION ... WITH DATA_COMPRESSION = ... ponownie kompiluje określoną partycję indeksu z określoną opcją kompresji. Jeśli klauzula WITH DATA_COMPRESSION zostanie pominięta, zostanie użyta istniejąca opcja kompresji.
• Składnia ALTER INDEX <index> ... REBUILD PARTITION = ALL ponownie kompiluje wszystkie partycje indeksu przy użyciu istniejących opcji kompresji.
• Składnia ALTER INDEX <index> ... REBUILD PARTITION = ALL (WITH ...) ponownie kompiluje wszystkie partycje indeksu. Możesz wybrać inną kompresję dla różnych partycji przy użyciu klauzuli DATA_COMPRESSION = ... ON PARTITIONS ( ...).

Aby ocenić, jak zmiana PAGE i ROW kompresji wpływa na tabelę, indeks lub partycję, użyj procedury składowanej sp_estimate_data_compression_savings.

Statystyka

Podczas ponownego kompilowania indeksu statystyki indeksu są aktualizowane przy użyciu pełnego skanowania dla indeksów bez partycjonowania oraz z domyślnym współczynnikiem próbkowania dla indeksów partycjonowanych. Żadne inne statystyki tabeli nie są aktualizowane w ramach odbudowy indeksu.

Uprawnienia

Wymagane jest uprawnienie ALTER w tabeli lub widoku.

Informacje o wersji

• Usługa Azure SQL Database nie obsługuje grup plików innych niż PRIMARY.
• Usługi Azure SQL Database i Azure SQL Managed Instance nie obsługują opcji FILESTREAM.
• Indeksy magazynu kolumn nie są dostępne przed programem SQL Server 2012 (11.x).
• Operacje indeksowania możliwe do wznowienia są dostępne w programie SQL Server 2017 (14.x) i nowszych wersjach, usłudze Azure SQL Database i usłudze Azure SQL Managed Instance.

Podstawowy przykład składni

ALTER INDEX index1 ON table1 REBUILD;
ALTER INDEX ALL ON table1 REBUILD;
ALTER INDEX ALL ON dbo.table1 REBUILD;

Przykłady: indeksy magazynu kolumn

Te przykłady dotyczą indeksów magazynu kolumn.

A. POKAZ REORGANIZE

W tym przykładzie pokazano, jak działa polecenie ALTER INDEX REORGANIZE. Tworzy tabelę zawierającą wiele grup wierszy, a następnie pokazuje, jak REORGANIZE scala grupy wierszy.

-- Create a database
CREATE DATABASE [columnstore];
GO

-- Create a rowstore staging table
CREATE TABLE [staging] (
    AccountKey INT NOT NULL,
    AccountDescription NVARCHAR(50),
    AccountType NVARCHAR(50),
    AccountCodeAlternateKey INT
);

-- Insert 10 million rows into the staging table.
DECLARE @loop INT;
DECLARE @AccountDescription VARCHAR(50);
DECLARE @AccountKey INT;
DECLARE @AccountType VARCHAR(50);
DECLARE @AccountCode INT;

SELECT @loop = 0

BEGIN TRANSACTION

WHILE (@loop < 300000)
BEGIN
    SELECT @AccountKey = CAST(RAND() * 10000000 AS INT);
    SELECT @AccountDescription = 'accountdesc ' + CONVERT(VARCHAR(20), @AccountKey);
    SELECT @AccountType = 'AccountType ' + CONVERT(VARCHAR(20), @AccountKey);
    SELECT @AccountCode = CAST(RAND() * 10000000 AS INT);

    INSERT INTO staging
    VALUES (
        @AccountKey,
        @AccountDescription,
        @AccountType,
        @AccountCode
     );

    SELECT @loop = @loop + 1;
END

COMMIT

-- Create a table for the clustered columnstore index
CREATE TABLE cci_target (
    AccountKey INT NOT NULL,
    AccountDescription NVARCHAR(50),
    AccountType NVARCHAR(50),
    AccountCodeAlternateKey INT
);

-- Convert the table to a clustered columnstore index named inxcci_cci_target;
CREATE CLUSTERED COLUMNSTORE INDEX idxcci_cci_target ON cci_target;

Użyj opcji TABLOCK, aby wstawić wiersze równolegle. Począwszy od programu SQL Server 2016 (13.x), operacja INSERT INTO może działać równolegle, gdy jest używana TABLOCK.

INSERT INTO cci_target WITH (TABLOCK)
SELECT TOP 300000 * FROM staging;

Uruchom to polecenie, aby wyświetlić OPEN grupy wierszy różnicowych. Liczba grup wierszy zależy od stopnia równoległości.

SELECT *
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id  = object_id('cci_target');

Uruchom to polecenie, aby wymusić wszystkie CLOSED i OPEN grupy wierszy do magazynu kolumn.

ALTER INDEX idxcci_cci_target ON cci_target REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

Uruchom to polecenie ponownie i zobaczysz, że mniejsze grupy wierszy są scalane w jedną skompresowaną grupę wierszy.

ALTER INDEX idxcci_cci_target ON cci_target REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

B. Kompresowanie zamkniętych grup wierszy różnicowych do magazynu kolumn

W tym przykładzie użyto opcji REORGANIZE, aby skompresować poszczególne CLOSED grupy wierszy różnicowych do magazynu kolumn jako skompresowanej grupy wierszy. Nie jest to konieczne, ale jest przydatne, gdy krotka-mover nie kompresuje CLOSED grupy wierszy wystarczająco szybko.

Możesz uruchomić oba przykłady w przykładowej bazie danych AdventureWorksDW2022.

Ten przykład uruchamia REORGANIZE na wszystkich partycjach.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE;

Ten przykład uruchamia REORGANIZE na określonej partycji.

-- REORGANIZE a specific partition
ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE PARTITION = 0;

C. Kompresuj wszystkie otwarte i zamknięte grupy wierszy różnicowych do magazynu kolumn

Dotyczy: SQL Server 2016 (13.x) i nowsze wersje, Azure SQL Database i Azure SQL Managed Instance

Polecenie REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON) kompresuje poszczególne OPEN i CLOSED grupę wierszy różnicowych do magazynu kolumn jako skompresowaną grupę wierszy. Spowoduje to opróżnienie magazynu delty i wymusi kompresowanie wszystkich wierszy do magazynu kolumn. Jest to przydatne szczególnie po wykonaniu wielu operacji wstawiania, ponieważ te operacje przechowują wiersze w co najmniej jednej grupie wierszy różnicowych.

REORGANIZE łączy grupy wierszy w celu wypełnienia grup wierszy do maksymalnej liczby wierszy <= 1024 576. Dlatego kompresowanie wszystkich OPEN i CLOSED grup wierszy nie kończy się na wielu skompresowanych grupach wierszy, które mają tylko kilka wierszy. Chcesz, aby grupy wierszy było tak pełne, jak to możliwe, aby zmniejszyć skompresowany rozmiar i zwiększyć wydajność zapytań.

W poniższych przykładach użyto bazy danych AdventureWorksDW2022.

W tym przykładzie wszystkie OPEN i CLOSED grupy wierszy różnicowych do indeksu magazynu kolumn.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

W tym przykładzie wszystkie OPEN i CLOSED grupy wierszy różnicowych do indeksu magazynu kolumn dla określonej partycji.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE PARTITION = 0 WITH (COMPRESS_ALL_ROW_GROUPS = ON);

D. Defragmentacja indeksu magazynu kolumn w trybie online

nie ma zastosowania do: SQL Server 2012 (11.x) i SQL Server 2014 (12.x).

Począwszy od programu SQL Server 2016 (13.x), REORGANIZE wykonuje więcej niż kompresowanie grup wierszy różnicowych do magazynu kolumn. Wykonuje również defragmentację online. Najpierw zmniejsza rozmiar magazynu kolumn przez fizyczne usunięcie usuniętych wierszy, gdy usunięto 10% lub więcej wierszy w grupie wierszy. Następnie łączy ze sobą grupy wierszy w celu utworzenia większych grup wierszy, które mają maksymalnie 1024 576 wierszy na grupy wierszy. Wszystkie zmienione grupy wierszy zostaną ponownie skompresowane.

Nuta

Począwszy od programu SQL Server 2016 (13.x), ponowne kompilowanie indeksu magazynu kolumn nie jest już konieczne w większości sytuacji, ponieważ REORGANIZE fizycznie usuwa usunięte wiersze i scala grupy wierszy. Opcja COMPRESS_ALL_ROW_GROUPS wymusza wszystkie OPEN lub CLOSED grupy wierszy różnicowych do magazynu kolumn, który wcześniej można było wykonać tylko z ponowną kompilacją. REORGANIZE jest w trybie online i występuje w tle, aby zapytania mogły kontynuować operację.

Poniższy przykład wykonuje REORGANIZE w celu defragmentowania indeksu przez fizyczne usuwanie wierszy, które zostały logicznie usunięte z tabeli i scalanie grup wierszy.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE;

E. Ponowne kompilowanie klastrowanego indeksu magazynu kolumn w trybie offline

Dotyczy: SQL Server, Azure SQL Database i Azure SQL Managed Instance

Napiwek

Począwszy od programu SQL Server 2016 (13.x) i usługi Azure SQL Database, zalecamy używanie ALTER INDEX REORGANIZE zamiast ALTER INDEX REBUILD dla indeksów magazynu kolumn.

Nuta

W programach SQL Server 2012 (11.x) i SQL Server 2014 (12.x) REORGANIZE są używane tylko do kompresowania CLOSED grup wierszy do magazynu kolumn. Jedynym sposobem wykonania operacji defragmentacji i wymuszenia wszystkich grup wierszy różnicowych do magazynu kolumn jest ponowne skompilowanie indeksu.

W tym przykładzie pokazano, jak ponownie skompilować indeks klastrowanego magazynu kolumn i wymusić wszystkie grupy wierszy różnicowych do magazynu kolumn. Ten pierwszy krok przygotowuje tabelę FactInternetSales2 w bazie danych AdventureWorksDW2022 z klastrowanym indeksem magazynu kolumn i wstawia dane z pierwszych czterech kolumn.

CREATE TABLE dbo.FactInternetSales2 (
    ProductKey [int] NOT NULL,
    OrderDateKey [int] NOT NULL,
    DueDateKey [int] NOT NULL,
    ShipDateKey [int] NOT NULL);

CREATE CLUSTERED COLUMNSTORE INDEX cci_FactInternetSales2
ON dbo.FactInternetSales2;

INSERT INTO dbo.FactInternetSales2
SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey
FROM dbo.FactInternetSales;

SELECT * FROM sys.column_store_row_groups;

Wyniki pokazują jedną OPEN grupę wierszy, co oznacza, że program SQL Server czeka na dodanie większej liczby wierszy, zanim zamknie grupę wierszy i przeniesie dane do magazynu kolumn. Ta następna instrukcja ponownie kompiluje indeks klastrowanego magazynu kolumn, który wymusza wszystkie wiersze do magazynu kolumn.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REBUILD;
SELECT * FROM sys.column_store_row_groups;

Wyniki instrukcji SELECT pokazują, że grupa wierszy jest COMPRESSED, co oznacza, że segmenty kolumn grupy wierszy są teraz kompresowane i przechowywane w magazynie kolumn.

F. Ponowne kompilowanie partycji klastrowanego indeksu magazynu kolumn w trybie offline

Dotyczy: SQL Server 2012 (11.x) i nowsze wersje, Azure SQL Database i Azure SQL Managed Instance

Aby ponownie skompilować partycję dużego klastrowanego indeksu magazynu kolumn, użyj ALTER INDEX REBUILD z opcją partycji. W tym przykładzie ponownie skompiluje się partycja 12. Począwszy od programu SQL Server 2016 (13.x), zalecamy zastąpienie REBUILDREORGANIZE.

ALTER INDEX cci_fact3
ON fact3
REBUILD PARTITION = 12;

G. Zmienianie klastrowanego indeksu magazynu kolumn w celu korzystania z kompresji archiwalnej

nie ma zastosowania do: SQL Server 2012 (11.x)

Możesz jeszcze bardziej zmniejszyć rozmiar klastrowanego indeksu magazynu kolumn, korzystając z opcji kompresji danych COLUMNSTORE_ARCHIVE. Jest to praktyczne rozwiązanie w przypadku starszych danych, które chcesz przechowywać w tańszej pamięci masowej. Zalecamy używanie tej funkcji tylko w przypadku danych, do których nie uzyskuje się dostępu często, ponieważ dekompresja jest wolniejsza niż w przypadku normalnej kompresji COLUMNSTORE.

Poniższy przykład ponownie kompiluje indeks klastrowanego magazynu kolumn w celu korzystania z kompresji archiwalnej, a następnie pokazuje, jak usunąć kompresję archiwalną. Wynik końcowy używa tylko kompresji magazynu kolumn.

Najpierw przygotuj przykład, tworząc tabelę z klastrowanym indeksem magazynu kolumn. Następnie skompresuj tabelę dalej przy użyciu kompresji archiwalnej.

--Prepare the example by creating a table with a clustered columnstore index.
CREATE TABLE SimpleTable (
    ProductKey [int] NOT NULL,
    OrderDateKey [int] NOT NULL,
    DueDateKey [int] NOT NULL,
    ShipDateKey [int] NOT NULL
);

CREATE CLUSTERED INDEX cci_SimpleTable ON SimpleTable (ProductKey);

CREATE CLUSTERED COLUMNSTORE INDEX cci_SimpleTable
ON SimpleTable
WITH (DROP_EXISTING = ON);

--Compress the table further by using archival compression.
ALTER INDEX cci_SimpleTable ON SimpleTable
REBUILD
WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);
GO

Ten przykład usuwa kompresję archiwum i używa tylko kompresji magazynu kolumn.

ALTER INDEX cci_SimpleTable ON SimpleTable
REBUILD
WITH (DATA_COMPRESSION = COLUMNSTORE);
GO

Przykłady: indeksy magazynu wierszy

A. Ponowne kompilowanie indeksu

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

ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee REBUILD;

B. Ponowne kompilowanie wszystkich indeksów w tabeli i określanie opcji

Poniższy przykład określa słowo kluczowe ALL. Spowoduje to ponowne skompilowanie wszystkich indeksów skojarzonych z tabelą Production.Product w bazie danych AdventureWorks2022. Określono trzy opcje.

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

Poniższy przykład dodaje opcję ONLINE, w tym opcję blokady o niskim priorytcie, i dodaje opcję kompresji wiersza.

Dotyczy: SQL Server 2014 (12.x) i nowsze wersje, Azure SQL Database i Azure SQL Managed Instance

ALTER INDEX ALL ON Production.Product
REBUILD WITH
(
    FILLFACTOR = 80,
    SORT_IN_TEMPDB = ON,
    STATISTICS_NORECOMPUTE = ON,
    ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS ) ),
    DATA_COMPRESSION = ROW
);

C. Reorganizacja indeksu za pomocą kompaktowania LOB

W poniższym przykładzie zreorganizuje pojedynczy indeks klastrowany w bazie danych AdventureWorks2022. Ponieważ indeks zawiera typ danych LOB na poziomie liścia, instrukcja kompaktuje również wszystkie strony zawierające duże dane obiektu. Określanie opcji WITH (LOB_COMPACTION = ON) nie jest wymagane, ponieważ wartość domyślna jest włączona.

ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto REORGANIZE WITH (LOB_COMPACTION = ON);

D. Ustawianie opcji w indeksie

W poniższym przykładzie ustawiono kilka opcji AK_SalesOrderHeader_SalesOrderNumber indeksu w bazie danych AdventureWorks2022.

ALTER INDEX AK_SalesOrderHeader_SalesOrderNumber ON
    Sales.SalesOrderHeader
SET (
    STATISTICS_NORECOMPUTE = ON,
    IGNORE_DUP_KEY = ON,
    ALLOW_PAGE_LOCKS = ON
    ) ;
GO

E. Wyłączanie indeksu

Poniższy przykład wyłącza indeks nieklastrowany w tabeli Employee w bazie danych AdventureWorks2022.

ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee DISABLE;

F. Wyłączanie ograniczeń

Poniższy przykład wyłącza ograniczenie PRIMARY KEY przez wyłączenie indeksu PRIMARY KEY w bazie danych AdventureWorks2022. Ograniczenie FOREIGN KEY w tabeli bazowej jest automatycznie wyłączone i zostanie wyświetlony komunikat ostrzegawczy.

ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department DISABLE;

Zestaw wyników zwraca ten komunikat ostrzegawczy.

Warning: Foreign key 'FK_EmployeeDepartmentHistory_Department_DepartmentID'
on table 'EmployeeDepartmentHistory' referencing table 'Department'
was disabled as a result of disabling the index 'PK_Department_DepartmentID'.

G. Włączanie ograniczeń

Poniższy przykład umożliwia PRIMARY KEY i FOREIGN KEY ograniczenia, które zostały wyłączone w przykładzie F.

Ograniczenie PRIMARY KEY jest włączone przez ponowne skompilowanie indeksu PRIMARY KEY.

ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department REBUILD;

Następnie włączono ograniczenie FOREIGN KEY.

ALTER TABLE HumanResources.EmployeeDepartmentHistory
CHECK CONSTRAINT FK_EmployeeDepartmentHistory_Department_DepartmentID;
GO

H. Ponowne kompilowanie indeksu partycjonowanego

Poniższy przykład ponownie kompiluje pojedynczą partycję, numer partycji 5, z partycjonowanego indeksu IX_TransactionHistory_TransactionDate w bazie danych AdventureWorks2022. Partycja 5 jest odbudowywana przy użyciu ONLINE=ON, a 10 minut oczekiwania na blokadę o niskim priorytecie ma zastosowanie oddzielnie do każdej blokady uzyskanej przez operację ponownego kompilowania indeksu. Jeśli w tym czasie nie można uzyskać blokady w celu ukończenia ponownego kompilowania indeksu, instrukcja operacji ponownej kompilacji zostanie przerwana z powodu ABORT_AFTER_WAIT = SELF.

Dotyczy: SQL Server 2014 (12.x) i nowsze wersje, Azure SQL Database i Azure SQL Managed Instance

-- Verify the partitioned indexes.
SELECT *
FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID(N'Production.TransactionHistory'), NULL , NULL, NULL);
GO
--Rebuild only partition 5.
ALTER INDEX IX_TransactionHistory_TransactionDate
ON Production.TransactionHistory
REBUILD Partition = 5
   WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 minutes, ABORT_AFTER_WAIT = SELF)));
GO

Ja. Zmienianie ustawienia kompresji indeksu

Poniższy przykład ponownie kompiluje indeks w niepartycyjnej tabeli magazynu wierszy.

ALTER INDEX IX_INDEX1
ON T1
REBUILD
WITH (DATA_COMPRESSION = PAGE);
GO

J. Zmienianie ustawienia indeksu za pomocą kompresji XML

Dotyczy: SQL Server 2022 (16.x) i nowsze wersje, Azure SQL Database i Azure SQL Managed Instance.

Poniższy przykład ponownie kompiluje indeks w niepartycyjnej tabeli magazynu wierszy.

ALTER INDEX IX_INDEX1
ON T1
REBUILD
WITH (XML_COMPRESSION = ON);
GO

Aby uzyskać więcej przykładów kompresji danych, zobacz Kompresja danych.

K. Ponowne kompilowanie indeksu możliwego do wznowienia w trybie online

Dotyczy: SQL Server 2017 (14.x) i nowsze wersje, Azure SQL Database i Azure SQL Managed Instance

W poniższych przykładach pokazano, jak używać ponownego kompilowania indeksu z możliwością wznowienia online.

Wykonaj ponowną kompilację indeksu online jako operację wznawianą za pomocą MAXDOP = 1. Wykonanie tego samego polecenia ponownie po wstrzymaniu operacji indeksu automatycznie wznawia operację ponownego kompilowania indeksu.

ALTER INDEX test_idx on test_table REBUILD WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

Wykonaj ponowną kompilację indeksu online jako operację wznawianą z MAX_DURATION ustawioną na 240 minut.

ALTER INDEX test_idx on test_table REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

Wstrzymywanie uruchomionej ponownej kompilacji indeksu online z możliwością wznowienia.

ALTER INDEX test_idx on test_table PAUSE;

Wznów ponowne kompilowanie indeksu online dla ponownego kompilowania indeksu, który został wykonany jako operacja wznawiana, określając nową wartość dla MAXDOP ustawioną na 4.

ALTER INDEX test_idx on test_table RESUME WITH (MAXDOP = 4);

Wznów operację ponownego kompilowania indeksu online dla ponownego kompilowania indeksu w trybie online, która została wykonana jako wznawiana. Ustaw wartość MAXDOP na 2, ustaw czas wykonywania dla indeksu, który jest uruchamiany jako możliwy do wznowienia do 240 minut, a jeśli indeks jest blokowany w blokadzie, poczekaj 10 minut i po tym zabij wszystkie blokady.

ALTER INDEX test_idx on test_table
    RESUME WITH (MAXDOP = 2, MAX_DURATION = 240 MINUTES,
    WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10, ABORT_AFTER_WAIT = BLOCKERS));

Przerwanie operacji ponownego kompilowania indeksu, która jest uruchomiona lub wstrzymana.

ALTER INDEX test_idx on test_table ABORT;

Powiązana zawartość

• architektury i architektury indeksu usługi Azure SQL Server oraz przewodnika projektowania
• Wykonywanie operacji indeksowania w trybie online
• CREATE INDEX (Transact-SQL)
• CREATE SPATIAL INDEX (Transact-SQL)
• CREATE XML INDEX (Transact-SQL)
• DROP INDEX (Transact-SQL)
• Wyłącz indeksy i ograniczenia
• indeksów XML (SQL Server)
• Optymalizowanie konserwacji indeksu w celu zwiększenia wydajności zapytań i zmniejszenia zużycia zasobów
• sys.dm_db_index_physical_stats (Transact-SQL)
• EVENTDATA (Transact-SQL)