Delen via

Richtlijnen voor online indexbewerkingen

  • Artikel

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL-database in Microsoft Fabric

Wanneer u online indexbewerkingen uitvoert, zijn de volgende richtlijnen van toepassing:

  • Geclusterde indexen moeten offline worden gemaakt, opnieuw opgebouwd of offline worden verwijderd wanneer de onderliggende tabel de volgende gegevenstypen voor grote objecten (LOB) bevat: afbeelding, nteksten tekst.
  • Niet-geclusterde indexen kunnen online worden gemaakt wanneer de tabel kolommen bevat met behulp van de LOB-gegevenstypen, maar geen van deze kolommen wordt gebruikt in de indexdefinitie als sleutel- of opgenomen kolommen.
  • Indexen voor lokale tijdelijke tabellen kunnen niet online worden gemaakt, opnieuw opgebouwd of verwijderd. Deze beperking geldt niet voor indexen in globale tijdelijke tabellen.
  • U kunt een online indexbewerking starten als een hervatbare bewerking met behulp van de RESUMABLE component van CREATE INDEX of ALTER INDEX. Een hervatbare indexbewerking kan opnieuw worden gestart na een onverwachte fout, databasefailover of een ALTER INDEX PAUSE opdracht en doorgaan vanaf de locatie waar deze is onderbroken.

Notitie

Online indexbewerkingen zijn niet beschikbaar in elke editie van Microsoft SQL Server. Zie -edities en ondersteunde functies van SQL Server 2022voor een lijst met functies die worden ondersteund door de edities van SQL Server.

In de volgende tabel ziet u de indexbewerkingen die online kunnen worden uitgevoerd, de indexen die zijn uitgesloten van deze onlinebewerkingen en hervatbare indexbeperkingen. Er zijn ook extra beperkingen inbegrepen.

Online indexbewerking Uitgesloten indexen Andere beperkingen
ALTER INDEX REBUILD Uitgeschakelde geclusterde index of uitgeschakelde geïndexeerde weergave

XML-index

Indexeren op een lokale tijdelijke tabel		 Als u het trefwoord opgeeft ALL kan de bewerking mislukken wanneer de tabel een uitgesloten index bevat.

Aanvullende beperkingen voor het opnieuw samenstellen van uitgeschakelde indexen zijn van toepassing. Zie Indexen en beperkingen uitschakelenvoor meer informatie.
CREATE INDEX XML-index

Initiële unieke geclusterde index in een weergave

Indexeren op een lokale tijdelijke tabel
CREATE INDEX WITH DROP_EXISTING Uitgeschakelde geclusterde index of uitgeschakelde geïndexeerde weergave

Indexeren op een lokale tijdelijke tabel

XML index
DROP INDEX Uitgeschakelde index

XML-index

Niet-geclusterde index

Indexeren op een lokale tijdelijke tabel		 Meerdere indexen kunnen niet binnen één instructie worden opgegeven.
ALTER TABLE ADD CONSTRAINT (PRIMARY KEY of UNIQUE) Indexeren op een lokale tijdelijke tabel

Geclusterde index		 Er is slechts één subclause tegelijk toegestaan. U kunt bijvoorbeeld geen beperkingen voor PRIMARY KEY of UNIQUE toevoegen en verwijderen in dezelfde ALTER TABLE instructie.
ALTER TABLE DROP CONSTRAINT (PRIMARY KEY of UNIQUE) Geclusterde index

De onderliggende tabel kan niet worden gewijzigd, afgekapt of verwijderd terwijl er een onlineindexbewerking wordt uitgevoerd.

De onlineoptie-instelling (ON of OFF) die is opgegeven wanneer u een geclusterde index maakt of neer zet, wordt toegepast op niet-geclusterde indexen die opnieuw moeten worden opgebouwd. Als de geclusterde index bijvoorbeeld online wordt gemaakt met behulp van CREATE INDEX WITH DROP_EXISTING, ONLINE=ON, worden alle gekoppelde niet-geclusterde indexen ook online gemaakt.

Wanneer u een UNIQUE-index online maakt of herbouwt, proberen de indexbouwer en een gelijktijdige gebruikerstransactie dezelfde sleutel in te voegen, waardoor de uniekheid wordt geschonden. Als een rij die door een gebruiker wordt ingevoerd, wordt ingevoegd in de nieuwe index (doel) voordat de oorspronkelijke rij uit de brontabel naar de nieuwe index wordt verplaatst, mislukt de online indexbewerking.

Hoewel niet gebruikelijk, kan de online indexbewerking een impasse veroorzaken wanneer deze communiceert met database-updates vanwege gebruikers- of toepassingsactiviteiten. In deze zeldzame gevallen wordt de activiteit van de gebruiker of toepassing geselecteerd als het slachtoffer van een doodlopende kringloop.

U kunt gelijktijdige DDL-bewerkingen voor online indexen uitvoeren in dezelfde tabel of weergave alleen wanneer u meerdere nieuwe niet-geclusterde indexen maakt of niet-geclusterde indexen opnieuw ordenen. Alle andere online indexbewerkingen die tegelijkertijd worden uitgevoerd, mislukken. U kunt bijvoorbeeld geen nieuwe index online maken tijdens het opnieuw samenstellen van een bestaande index in dezelfde tabel.

Een onlinebewerking kan niet worden uitgevoerd wanneer een index een kolom van het grote objecttype bevat en dezelfde transactie gegevenswijzigingen aanbrengt voordat de online indexbewerking wordt gestart. Als u dit probleem wilt omzeilen, verplaatst u de onlineindexbewerking buiten de transactie of verplaatst u deze vóór gegevenswijzigingen in dezelfde transactie.

Overwegingen voor schijfruimte

Online indexbewerkingen vereisen meer schijfruimte dan offline indexbewerkingen.

  • Tijdens het maken van de index en het opnieuw opbouwen van indexen is extra ruimte vereist voor de index die wordt gebouwd (of opnieuw wordt opgebouwd). Deze extra ruimte is meestal hetzelfde als de huidige ruimte die door de index wordt bezet, maar deze kan groter of kleiner zijn, afhankelijk van de compressie die wordt gebruikt in de huidige of herbouwde index.
  • Daarnaast is schijfruimte vereist voor de tijdelijke mappingindex. Deze tijdelijke index wordt gebruikt in online indexbewerkingen die een geclusterde index maken, herbouwen of verwijderen.
  • Het online verwijderen van een geclusterde index vereist net zoveel ruimte als het online maken (of herbouwen) van een geclusterde index.

Zie Schijfruimtevereisten voor DDL-indexbewerkingenvoor meer informatie.

Prestatieoverwegingen

Hoewel online indexbewerkingen gelijktijdige activiteit van gebruikersupdates toestaan, kunnen de indexbewerkingen langer duren als de updateactiviteit zwaar is. Online indexbewerkingen zijn doorgaans trager dan gelijkwaardige offline indexbewerkingen, ongeacht het niveau van de gelijktijdige updateactiviteit.

Omdat zowel de bron- als doelstructuren worden onderhouden tijdens de onlineindexbewerking, wordt het resourcegebruik voor het invoegen, bijwerken en verwijderen van transacties verhoogd, mogelijk verdubbeld. Dit kan leiden tot een afname van de prestaties en een groter resourcegebruik, met name cpu-tijd, tijdens de indexbewerking. Online indexbewerkingen worden volledig geregistreerd.

Hoewel we onlinebewerkingen aanbevelen, moet u uw omgeving en specifieke vereisten evalueren. Het kan optimaal zijn om indexbewerkingen offline uit te voeren. Hierdoor wordt de gebruikerstoegang tot de gegevens tijdens de bewerking beperkt, maar de bewerking wordt sneller voltooid en gebruikt minder resources.

Op computers met meerdere processors waarop SQL Server 2016 (13.x) en latere versies worden uitgevoerd, kunnen indexbewerkingen parallellisme gebruiken om de scan- en sorteerbewerkingen uit te voeren die zijn gekoppeld aan de indexinstructie. U kunt de MAXDOP indexoptie gebruiken om de mate van parallelle uitvoering van de online indexbewerking te bepalen. Op deze manier kunt u de resources verdelen die door indexbewerking worden gebruikt met resources van de gelijktijdige gebruikers. Zie Parallelle indexbewerkingen configurerenvoor meer informatie. Zie -edities en ondersteunde functies van SQL Server 2022voor meer informatie over de edities van SQL Server die parallelle indexbewerkingen ondersteunen.

Omdat een gedeelde (S) vergrendeling of schemawijzigingsvergrendeling (Sch-M) wordt behouden in de laatste fase van de indexbewerking, moet u voorzichtig zijn bij het uitvoeren van een online indexbewerking binnen een expliciete gebruikerstransactie, zoals BEGIN TRANSACTION ... COMMIT blok. Hierdoor worden de vergrendelingen bewaard tot het einde van de transactie, waardoor andere workloads mogelijk worden geblokkeerd.

Als indexpaginavergrendelingen zijn uitgeschakeld met ALLOW_PAGE_LOCKS=OFF, kan het herbouwen van online indexen de fragmentatie van de index verhogen wanneer deze wordt uitgevoerd met MAXDOP groter dan 1. Zie How It Works: Online Index Rebuild - Can Cause Increased Fragmenting(Online index herbouwen - Kan verhoogde fragmentatie veroorzaken) voor meer informatie.

Overwegingen voor transactielogboeken

Grootschalige indexbewerkingen die offline of online worden uitgevoerd, kunnen grote hoeveelheden transactielogboeken genereren. Dit komt doordat zowel offline- als online herbouwbewerkingen voor indexen volledig zijn vastgelegd. Om ervoor te zorgen dat de indexbewerking kan worden teruggedraaid, kan het transactielogboek pas worden afgekapt als de indexbewerking is voltooid; Er kan echter een back-up van het logboek worden gemaakt tijdens de indexbewerking.

Daarom moet het transactielogboek voldoende ruimte hebben om zowel de indexbewerkingstransacties als gelijktijdige gebruikerstransacties tijdens de indexbewerking op te slaan. Zie Schijfruimte voor transactielogboeken voor indexbewerkingenvoor meer informatie.

Online indexbewerkingen veroorzaken geen hoge groei van transactielogboeken als versneld databaseherstel (ADR) is ingeschakeld.

Overwegingen voor permanente versieopslag

Als ADR is ingeschakeld, kan het online maken of herbouwen van een grote index de grootte van permanente versieopslag (PVS) aanzienlijk vergroten terwijl de indexbewerking wordt uitgevoerd. Zorg ervoor dat de database voldoende vrije ruimte heeft om PVS te laten groeien. Zie Bewaken en problemen met versneld databaseherstel oplossenvoor meer informatie.

Overwegingen voor hervatting van indexen

De RESUMABLE-indexoptie voor CREATE INDEX en ALTER INDEX is van toepassing op SQL Server (ALTER INDEX vanaf SQL Server 2017 (14.x) en CREATE INDEX te beginnen met SQL Server 2019 (15.x)), Azure SQL Database en Azure SQL Managed Instance. Zie CREATE INDEX en ALTER INDEXvoor meer informatie.

Als u de optie RESUMABLE wilt gebruiken, moet u ook de optie ONLINE gebruiken. Wanneer u hervatbare index maakt of herbouwt, zijn de volgende richtlijnen van toepassing:

  • U hebt betere controle over het beheren, plannen en uitbreiden van onderhoudsvensters voor indexen. U kunt een indexbewerking meerdere keren onderbreken en opnieuw starten, zodat deze past bij uw onderhoudsvensters.
  • U kunt herstellen na het maken of opnieuw opbouwen van indexfouten (zoals databasefailovers of onvoldoende schijfruimte) zonder dat u de indexbewerking opnieuw hoeft op te starten vanaf het begin.
  • Wanneer een indexbewerking is onderbroken, vereisen zowel de oorspronkelijke index als de zojuist gemaakte index schijfruimte en moet deze worden bijgewerkt tijdens DML-bewerkingen.
  • De optie SORT_IN_TEMPDB=ON wordt niet ondersteund.
  • Uitgeschakelde indexen worden niet ondersteund.

Tip

Voor hervatbare indexbewerkingen is geen grote transactie vereist, waardoor regelmatige afkapping van logboeken tijdens deze bewerking wordt toegestaan en grote logboekgroei wordt voorkomen. De gegevens die nodig zijn om een indexbewerking te hervatten en te voltooien, worden opgeslagen in de gegevensbestanden van een database.

Over het algemeen is er geen prestatieverschil tussen hervatbare en niet-onresumerbare online indexbewerkingen. Voor hervatbare CREATE INDEXis er een constante overhead die aanzienlijk tragere bewerkingen voor kleinere tabellen kan veroorzaken.

Wanneer een hervatbare indexbewerking wordt onderbroken:

  • Voor voornamelijk leesworkloads is de prestatievermindering onbelangrijk.
  • Voor werkbelastingen met veel updates kan er enige vermindering van prestaties optreden, afhankelijk van de specifieke werkbelastingen.

Over het algemeen is er geen verschil in de defragmentatiekwaliteit tussen hervatbare en niet-onresumerbare online indexen maken of herbouwen.

Notitie

Hoewel een online indexbewerking is onderbroken, mislukt elke transactie die een exclusieve tabelniveau (X) vergrendeling vereist op de tabel die de onderbroken index bevat. Dit kan bijvoorbeeld gebeuren met INSERT ... WITH (TABLOCK)-bewerkingen. In dit geval krijgt u fout 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.

Als u fout 10637 wilt oplossen, verwijdert u de TABLOCK hint uit uw transactie of maakt u de indexbewerking ongedaan en wacht u totdat deze is voltooid voordat u de transactie opnieuw probeert uit te voeren.

Online standaardopties

U kunt online- en hervatbare indexbewerkingen instellen als de standaardopties op databaseniveau door de configuraties voor ELEVATE_ONLINE of ELEVATE_RESUMABLE databasebereik in te stellen. Met deze standaardopties kunt u voorkomen dat u per ongeluk een offline indexbewerking start waardoor een tabel of index niet toegankelijk is terwijl deze wordt uitgevoerd. Beide opties zorgen ervoor dat de database-engine bepaalde indexbewerkingen automatisch verhoogt tot online of hervatbare uitvoering.

U kunt de optie instellen als FAIL_UNSUPPORTED, WHEN_SUPPORTEDof OFF. U kunt verschillende waarden instellen voor ELEVATE_ONLINE en ELEVATE_RESUMABLE. Zie ALTER DATABASE SCOPED CONFIGURATIONvoor meer informatie.

Zowel ELEVATE_ONLINE als ELEVATE_RESUMABLE zijn alleen van toepassing op DDL-instructies die respectievelijk de online- en hervatbare syntaxis ondersteunen. Als u bijvoorbeeld probeert een XML-index te maken met ELEVATE_ONLINE=FAIL_UNSUPPORTED, wordt de bewerking offline uitgevoerd omdat XML-indexen de optie ONLINE niet ondersteunen. De opties zijn alleen van invloed op DDL-instructies die worden verzonden zonder een ONLINE of RESUMABLE optie op te geven. Door bijvoorbeeld een instructie met ONLINE=OFF of RESUMABLE=OFFin te dienen, kan de gebruiker een FAIL_UNSUPPORTED instelling overschrijven en een instructie offline en/of niet-onherstelbaar uitvoeren.

Notitie

ELEVATE_ONLINE en ELEVATE_RESUMABLE zijn niet van toepassing op XML-indexbewerkingen.

Verwante inhoud