Udostępnij za pośrednictwem

Wskazówki dotyczące operacji indeksowania w trybie online

  • Artykuł

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL Database w usłudze Microsoft Fabric

Podczas wykonywania operacji indeksowania w trybie online obowiązują następujące wskazówki:

  • Indeksy klastrowane muszą być tworzone, przebudowywane lub usuwane w trybie offline, gdy tabela bazowa zawiera następujące typy danych dużych obiektów (LOB): image, ntexti text.
  • Indeksy nieklastrowane można tworzyć w trybie online, gdy tabela zawiera kolumny używające typów danych LOB, ale żadna z tych kolumn nie jest używana w definicji indeksu jako klucz lub kolumny dołączone.
  • Indeksy w lokalnych tabelach tymczasowych nie mogą być tworzone, odbudowywane ani porzucane w trybie online. To ograniczenie nie ma zastosowania do indeksów w globalnych tabelach tymczasowych.
  • Możesz uruchomić operację indeksu online jako operację wznawianą, używając klauzuli RESUMABLECREATE INDEX lub ALTER INDEX. Można wznowić operację indeksu po nieoczekiwanym niepowodzeniu, przełączeniu bazy danych w tryb awaryjny lub poleceniu ALTER INDEX PAUSE i kontynuować od miejsca, w którym została przerwana.

Notatka

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.

W poniższej tabeli przedstawiono operacje indeksu, które można wykonać w trybie online, indeksy wykluczone z tych operacji online i ograniczenia indeksu z możliwością wznowienia. Dostępne są również dodatkowe ograniczenia.

Operacja indeksu online Wykluczone indeksy Inne ograniczenia
ALTER INDEX REBUILD Wyłączony indeks klastrowany lub wyłączony widok indeksowany

Indeks XML

Indeksowanie w lokalnej tabeli tymczasowej		 Określenie słowa kluczowego ALL może spowodować niepowodzenie operacji, gdy tabela zawiera wykluczony indeks.

Obowiązują dodatkowe ograniczenia dotyczące ponownego kompilowania wyłączonych indeksów. Aby uzyskać więcej informacji, zobacz Wyłącz indeksy i ograniczenia.
CREATE INDEX Indeks XML

Początkowy unikatowy indeks klastrowany w widoku

Indeks w lokalnej tabeli tymczasowej
CREATE INDEX WITH DROP_EXISTING Wyłączony indeks klastrowany lub wyłączony widok indeksowany

Indeksowanie w lokalnej tabeli tymczasowej

Indeks XML
DROP INDEX Indeks wyłączony

Indeks XML

Indeks nieklastrowany

Indeksowanie w lokalnej tabeli tymczasowej		 Nie można określić wielu indeksów w ramach jednej instrukcji.
ALTER TABLE ADD CONSTRAINT (PRIMARY KEY lub UNIQUE) Indeksowanie w lokalnej tabeli tymczasowej

Indeks klastrowany		 Tylko jedna podklauzula jest dozwolona jednocześnie. Na przykład nie można dodawać ani usuwać ograniczeń PRIMARY KEY albo UNIQUE w tej samej instrukcji ALTER TABLE.
ALTER TABLE DROP CONSTRAINT (PRIMARY KEY lub UNIQUE) Indeks klastrowany

Nie można modyfikować, obcinać ani porzucać tabeli bazowej, gdy trwa operacja indeksu online.

Ustawienie opcji online (ON lub OFF) określone podczas tworzenia lub porzucania indeksu klastrowanego jest stosowane do wszystkich indeksów nieklastrowanych, które należy ponownie skompilować. Jeśli na przykład indeks klastrowany jest kompilowany w trybie online przy użyciu CREATE INDEX WITH DROP_EXISTING, ONLINE=ON, wszystkie skojarzone indeksy nieklastrowane są również ponownie tworzone w trybie online.

Podczas tworzenia lub odbudowywania indeksu UNIQUE w trybie online konstruktor indeksu i współbieżna transakcja użytkownika mogą próbować wstawić ten sam klucz, co narusza unikatowość. Jeśli wiersz wprowadzony przez użytkownika zostanie wstawiony do nowego indeksu (docelowego), zanim oryginalny wiersz z tabeli źródłowej zostanie przeniesiony do nowego indeksu, operacja indeksu online zakończy się niepowodzeniem.

Chociaż nie jest to powszechne, operacja indeksowania online może spowodować zakleszczenie podczas interakcji z aktualizacjami bazy danych z powodu aktywności użytkownika lub działania aplikacji. W takich rzadkich przypadkach działanie użytkownika lub aplikacji jest wybierane jako ofiara zakleszczenia.

Możesz wykonywać współbieżne operacje DDL indeksu online w tej samej tabeli lub widoku tylko wtedy, gdy tworzysz wiele nowych indeksów nieklastrowanych lub reorganizujesz indeksy nieklastrowane. Wszystkie inne operacje indeksowania online wykonywane w tym samym czasie kończą się niepowodzeniem. Na przykład nie można utworzyć nowego indeksu w trybie online podczas ponownego kompilowania istniejącego indeksu w trybie online w tej samej tabeli.

Nie można wykonać operacji online, gdy indeks zawiera kolumnę dużego typu obiektu, a ta sama transakcja wprowadza modyfikacje danych przed rozpoczęciem operacji indeksu online. Aby obejść ten problem, przenieś operację indeksu online poza transakcję lub przenieś ją przed wszelkimi modyfikacjami danych w tej samej transakcji.

Zagadnienia dotyczące miejsca na dysku

Operacje indeksowania w trybie online wymagają więcej miejsca na dysku niż operacje indeksowania w trybie offline.

  • Podczas operacji tworzenia indeksu i ponownego kompilowania indeksu wymagane jest dodatkowe miejsce na skompilowanie indeksu (lub jego odbudowanie). Zazwyczaj ta dodatkowa przestrzeń jest taka sama jak bieżące miejsce zajmowane przez indeks, ale może być większa lub mniejsza w zależności od kompresji używanej w bieżącym lub przebudowanym indeksie.
  • Ponadto miejsce na dysku jest wymagane dla indeksu mapowania tymczasowego. Ten indeks tymczasowy jest używany w operacjach indeksowania online, które tworzą, kompilują lub usuwają indeks klastrowany.
  • Usunięcie klastrowanego indeksu w trybie online wymaga tak dużej ilości miejsca, jak tworzenie (lub ponowne kompilowanie) indeksu klastrowanego w trybie online.

Aby uzyskać więcej informacji, zobacz Wymagania dotyczące przestrzeni dyskowej dla operacji DDL na indeksach.

Zagadnienia dotyczące wydajności

Mimo że operacje indeksowania online zezwalają na współbieżną aktualizację przez użytkowników, operacje indeksowania mogą trwać dłużej, jeśli aktualizacja jest intensywna. Zazwyczaj operacje indeksowania online są wolniejsze niż równoważne operacje indeksowania w trybie offline, niezależnie od współbieżnego poziomu działania aktualizacji.

Ponieważ zarówno struktury źródłowe, jak i docelowe są utrzymywane podczas operacji indeksu online, użycie zasobów dla transakcji wstawiania, aktualizowania i usuwania jest zwiększane, potencjalnie dwukrotnie. Może to spowodować spadek wydajności i większe użycie zasobów, zwłaszcza czas procesora CPU, podczas operacji indeksowania. Operacje indeksowania online są w pełni rejestrowane.

Mimo że zalecamy wykonywanie operacji online, należy ocenić środowisko i określone wymagania. Może to być optymalne do uruchamiania operacji indeksowania w trybie offline. W ten sposób dostęp użytkownika do danych jest ograniczony podczas operacji, ale operacja kończy się szybciej i używa mniejszej liczby zasobów.

Na komputerach wieloprocesorowych z uruchomionym programem SQL Server 2016 (13.x) i nowszymi wersjami operacje indeksowania mogą używać równoległości do wykonywania operacji skanowania i sortowania skojarzonych z instrukcją indeksu. Możesz użyć opcji indeksu MAXDOP, aby kontrolować stopień równoległości operacji indeksu online. W ten sposób można zrównoważyć zasoby używane przez operację indeksowania z zasobami współbieżnych użytkowników. Aby uzyskać więcej informacji, zobacz Konfigurowanie operacji indeksowania równoległego. Aby uzyskać więcej informacji na temat wersji programu SQL Server obsługujących operacje indeksowania równoległego, zobacz Editions i obsługiwane funkcje programu SQL Server 2022.

Ponieważ w ostatniej fazie operacji indeksowania jest utrzymywana blokada współdzielona (S) lub blokada modyfikacji schematu (Sch-M), należy zachować ostrożność, gdy uruchamiasz operację indeksowania online wewnątrz jawnej transakcji użytkownika, takiej jak blok BEGIN TRANSACTION ... COMMIT. W ten sposób blokady będą przechowywane do końca transakcji, co potencjalnie blokuje inne obciążenia.

Jeśli blokady strony indeksu są wyłączone przy użyciu ALLOW_PAGE_LOCKS=OFF, ponowne kompilowanie indeksu online może zwiększyć fragmentację indeksu, gdy jest uruchamiany z MAXDOP większym niż 1. Aby uzyskać więcej informacji, zobacz Jak to działa: ponowne kompilowanie indeksu online — może spowodować zwiększenie fragmentacji.

Zagadnienia dotyczące dziennika transakcji

Operacje indeksowania na dużą skalę wykonywane w trybie offline lub online mogą generować duże ilości dziennika transakcji. Dzieje się tak, ponieważ operacje ponownego kompilowania indeksu w trybie offline i online są w pełni rejestrowane. Aby upewnić się, że można wycofać operację indeksu, dziennik transakcji nie może zostać obcięty do momentu zakończenia operacji indeksu; można jednak utworzyć kopię zapasową dziennika podczas operacji indeksu.

W związku z tym dziennik transakcji musi mieć wystarczającą ilość miejsca do przechowywania zarówno transakcji operacji indeksowania, jak i wszystkich współbieżnych transakcji użytkownika podczas operacji indeksowania. Aby uzyskać więcej informacji, zobacz Miejsce na dysku dziennika transakcji dla operacji indeksowania.

Operacje indeksów online nie powodują znacznego wzrostu rozmiaru dziennika transakcji, jeśli przyspieszone odzyskiwanie bazy danych (ADR) jest włączone.

Zagadnienia dotyczące trwałego magazynu wersji

Jeśli ADR jest włączona, tworzenie lub odbudowa dużego indeksu w trybie online może znacznie zwiększyć rozmiar Magazynu Trwałych Wersji (PVS), podczas gdy operacja indeksu jest w toku. Upewnij się, że baza danych ma wystarczającą ilość wolnego miejsca, aby PVS mogła się rozwijać. Aby uzyskać więcej informacji, zobacz Monitorowanie i rozwiązywanie problemów z przyspieszonym odzyskiwaniem danych bazy danych.

Zagadnienia dotyczące wznawialnego indeksu

Opcja indeksu RESUMABLE dla CREATE INDEX i ALTER INDEX dotyczy programu SQL Server (ALTER INDEX począwszy od programu SQL Server 2017 (14.x) i CREATE INDEX począwszy od programu SQL Server 2019 (15.x)), usługi Azure SQL Database i usługi Azure SQL Managed Instance. Aby uzyskać więcej informacji, zobacz CREATE INDEX i ALTER INDEX.

Aby użyć opcji RESUMABLE, należy również użyć opcji ONLINE. Podczas tworzenia lub ponownego kompilowania indeksu z możliwością wznowienia obowiązują następujące wskazówki:

  • Masz lepszą kontrolę nad zarządzaniem, planowaniem i rozszerzaniem okien obsługi indeksów. Możesz wstrzymać i ponownie uruchomić operację tworzenia lub odbudowy indeksu wiele razy, aby dopasować okna serwisowe.
  • Możesz odzyskać dane po błędach tworzenia lub odbudowy indeksu (na przykład w przypadku trybu awaryjnego bazy danych lub braku miejsca na dysku) bez konieczności ponownego uruchamiania operacji indeksowania od początku.
  • Po wstrzymaniu operacji indeksu zarówno oryginalny indeks, jak i nowo utworzony wymagają miejsca na dysku i muszą zostać zaktualizowane podczas operacji DML.
  • Opcja SORT_IN_TEMPDB=ON nie jest obsługiwana.
  • Wyłączone indeksy nie są obsługiwane.

Napiwek

Operacje na indeksach z możliwością wznowienia nie wymagają dużej transakcji, co pozwala na częste skracanie dziennika podczas tej operacji i unikanie dużego wzrostu dziennika. Dane wymagane do wznowienia i ukończenia operacji indeksu są przechowywane w plikach danych bazy danych.

Ogólnie rzecz biorąc, nie ma różnicy w wydajności między wznowialnymi i niewznowialnymi działaniami indeksowania online. W przypadku wznawianych CREATE INDEXistnieje stałe obciążenie, które może spowodować zauważalnie wolniejsze operacje dla mniejszych tabel.

Po wstrzymaniu operacji indeksowania, którą można wznowić:

  • W przypadku większości obciążeń odczytu spadek wydajności jest nieznaczny.
  • W przypadku obciążeń z dużą liczbą aktualizacji może wystąpić pewne obniżenie przepływności w zależności od specyfiki obciążenia.

Ogólnie rzecz biorąc, nie ma różnicy w jakości defragmentacji między wznawianym a niewznawianym indeksem online podczas tworzenia lub odbudowy.

Uwaga

Podczas gdy operacja indeksu online jest wstrzymana, każda transakcja, która wymaga wyłącznego poziomu tabeli (X) blokady w tabeli zawierającej wstrzymany indeks kończy się niepowodzeniem. Na przykład może to wystąpić w przypadku operacji INSERT ... WITH (TABLOCK). W takim przypadku zostanie wyświetlony błąd 10637:

Cannot perform this operation on '<object name>' with ID (<object ID>) as one or more indexes are currently in resumable index rebuild state. Please refer to sys.index_resumable_operations for more details.

Aby rozwiązać problem z błędem 10637, usuń wskazówkę TABLOCK z transakcji lub usuń operację indeksu i poczekaj na jej zakończenie przed ponownym podjęciem próby wykonania transakcji.

Opcje domyślne online

Ustawiając konfiguracje ELEVATE_ONLINE lub ELEVATE_RESUMABLE w zakresie bazy danych, możesz ustawić operacje indeksowania w trybie online oraz możliwość wznawiania jako domyślne opcje na poziomie bazy danych. Dzięki tym opcjom domyślnym można uniknąć przypadkowego uruchomienia operacji indeksu w trybie offline, która sprawia, że tabela lub indeks są niedostępne podczas jej działania. Obie opcje powodują, że aparat bazy danych automatycznie przenosi niektóre operacje indeksu do trybu online lub wykonywania wznawialnego.

Możesz ustawić opcję FAIL_UNSUPPORTED, WHEN_SUPPORTEDlub OFF. Można ustawić różne wartości dla ELEVATE_ONLINE i ELEVATE_RESUMABLE. Aby uzyskać więcej informacji, zajrzyj do ALTER DATABASE SCOPED CONFIGURATION.

Zarówno ELEVATE_ONLINE, jak i ELEVATE_RESUMABLE mają zastosowanie tylko do instrukcji DDL, które obsługują składnię online oraz wznawianą. Jeśli na przykład spróbujesz utworzyć indeks XML z ELEVATE_ONLINE=FAIL_UNSUPPORTED, operacja zostanie uruchomiona w trybie offline, ponieważ indeksy XML nie obsługują opcji ONLINE. Opcje wpływają tylko na instrukcje DDL, które są przesyłane bez określenia opcji ONLINE lub RESUMABLE. Na przykład, przesyłając instrukcję z ONLINE=OFF lub RESUMABLE=OFF, użytkownik może zastąpić ustawienie FAIL_UNSUPPORTED i uruchomić instrukcję w trybie offline i/lub nieresumownie.

Notatka

ELEVATE_ONLINE i ELEVATE_RESUMABLE nie mają zastosowania do operacji indeksowania XML.

Powiązana zawartość