Delen via

Hervatbare tabelbeperkingen toevoegen

Van toepassing op: SQL Server 2022 (16.x) en latere versies Van Azure SQL DatabaseAzure SQL Managed InstanceSQL Database in Microsoft Fabric

De hervatbare bewerking voor het maken en opnieuw bouwen van online indexen wordt al ondersteund voor SQL Server 2019, Azure SQL Database en Azure SQL Managed Instance. Met de hervatbare bewerkingen kunnen indexbewerkingen worden uitgevoerd terwijl de tabel online is (ONLINE=ON) en ook:

  • Pauzeer en hervat het creëren of herbouwen van een index meerdere keren om tegemoet te komen aan een onderhoudsvenster.

  • Herstel van fouten bij het aanmaken of herbouwen van indexen, zoals bij databasefailovers of onvoldoende schijfruimte.

  • Schakel het afkappen van transactielogboeken in tijdens het maken of herbouwen van een index.

  • Wanneer een indexbewerking is onderbroken, vereisen zowel de oorspronkelijke index als de zojuist gemaakte index schijfruimte en moeten ze worden bijgewerkt tijdens DML-bewerkingen (Data Manipulation Language ).

De nieuwe extensies voor SQL Server 2022, SQL Database en SQL Managed Instance maken een hervatbare bewerking mogelijk voor de DDL-opdrachtALTER TABLE ADD CONSTRAINT en het toevoegen van een primaire of unieke sleutel. Zie ALTER TABLE table_constraint voor meer informatie over het toevoegen van een primaire of unieke sleutel.

Opmerking

Hervatbare tabelbeperkingen zijn alleen van toepassing op de beperkingen PRIMARY KEY en UNIQUE KEY. Het hervatten van het toevoegen van tabelbeperkingen wordt niet ondersteund voor beperkingen van vreemde sleutels.

Hervatbare bewerkingen

In eerdere versies van SQL Server kan de ALTER TABLE ADD CONSTRAINT bewerking worden uitgevoerd met de ONLINE=ON optie. Het kan echter veel uren duren voordat een grote tabel is voltooid en kan een groot aantal resources verbruiken. Er is ook de mogelijkheid van fouten of onderbrekingen tijdens een dergelijke uitvoering. We hebben hervatbare mogelijkheden ALTER TABLE ADD CONSTRAINT geïntroduceerd voor gebruikers om de bewerking tijdens een onderhoudsvenster te onderbreken of om deze opnieuw te starten vanaf de locatie waar de bewerking is onderbroken tijdens een uitvoeringsfout, zonder de bewerking vanaf het begin opnieuw te starten.

Ondersteunde scenario’s

De nieuwe hervatbare functie van ALTER TABLE ADD CONSTRAINT ondersteunt de volgende klantscenario’s:

  • Pauzeer of hervat de actieve ALTER TABLE ADD CONSTRAINT bewerking, zoals het onderbreken voor een onderhoudsvenster en het hervatten van de bewerking zodra het onderhoudsvenster is voltooid.

  • Hervat ALTER TABLE ADD CONSTRAINT de bewerking na failovers en systeemfouten.

  • Bewerking uitvoeren ALTER TABLE ADD CONSTRAINT op een grote tabel ondanks de kleine logboekgrootte die beschikbaar is.

Opmerking

Voor de hervatbare bewerking ALTER TABLE ADD CONSTRAINT moet de ALTER opdracht online (WITH ONLINE = ON) worden uitgevoerd.

Deze functie is vooral handig voor grote tabellen.

T-SQL-syntaxis voor ALTER TABLE

Zie de syntaxis en opties in ALTER TABLE (Transact-SQL) voor informatie over de syntaxis die wordt gebruikt om hervatbare bewerkingen in een tabelbeperking in te schakelen.

Opmerkingen voor ALTER TABLE

  • Er is een nieuwe component WITH <resumable_options toegevoegd aan de huidige T-SQL-syntaxis in ALTER TABLE (Transact-SQL).

  • De optie HERVATTEN is nieuw en is toegevoegd aan de bestaande syntaxis van ALTER TABLE (Transact-SQL).

  • MAX_DURATION = tijd [MINUTEN] gebruikt met RESUMABLE = ON (vereist ONLINE = ON). MAX_DURATION geeft de tijd aan (een geheel getal dat is opgegeven in minuten) dat een hervatbare online bewerking voor het toevoegen van beperkingen wordt uitgevoerd voordat deze wordt onderbroken. Als dit niet is opgegeven, wordt de bewerking voortgezet totdat deze is voltooid.

T-SQL-syntaxis voor ALTER INDEX

Als u de hervatbare tabelbeperkingsbewerking ALTER TABLE ADD CONSTRAINTwilt onderbreken, hervatten of afbreken, gebruikt u de T-SQL-syntaxis ALTER INDEX (Transact-SQL).

Voor hervatbare beperkingen wordt de bestaande opdracht ALTER INDEX ALL gebruikt.

ALTER INDEX ALL ON <table_name>  
      { RESUME [WITH (<resumable_index_options>,[...n])]
        | PAUSE
        | ABORT
      }
<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 } )  
}

Opmerkingen voor ALTER INDEX

ALTER INDEX ALL ON <Table> PAUSE

  • Een hervatbare en online bewerking voor het toevoegen van tabelbeperkingen onderbreken die wordt uitgevoerd

ALTER INDEX ALL ON <Table> RESUME [WITH (<resumable_index_options>,[...n])]

  • Hervat een bewerking voor het toevoegen van tabelbeperkingen die handmatig is onderbroken of vanwege een fout.

MAX_DURATION gebruikt met RESUMABLE=ON

  • De tijd (een geheel getal dat is opgegeven in minuten) dat de hervatbare bewerking voor het toevoegen van tabelbeperkingen wordt uitgevoerd nadat deze is hervat. Zodra de tijd is verlopen, wordt de hervatbare bewerking onderbroken als deze nog actief is.

WAIT_AT_LOW_PRIORITY gebruikt met RESUMABLE=ON en ONLINE = ON

  • Het hervatten van een online bewerking voor het toevoegen van tabelbeperkingen na een pauze moet wachten op blokkeringsbewerkingen in deze tabel. WAIT_AT_LOW_PRIORITY geeft aan dat de bewerking tabelbeperking toevoegen wacht op vergrendelingen met een lage prioriteit, zodat andere bewerkingen kunnen worden voortgezet terwijl de hervatbare bewerking wacht. Het weglaten van de optie WAIT_AT_LOW_PRIORITY is gelijk aan WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE). Zie WAIT_AT_LOW_PRIORITY voor meer informatie.

ALTER INDEX ALL ON <Table> ABORT

  • Een lopende of gepauzeerde operatie voor het toevoegen van een tabelbeperking die als hervatbaar is verklaard, afbreken. De afgebroken bewerking moet expliciet worden uitgevoerd als een ABORT opdracht om een hervattbare beperkingsbewerking te beëindigen. Als u een hervatbare tabelbeperkingsbewerking onderbreekt, wordt de uitvoering niet beëindigd. In plaats daarvan blijft de bewerking voor onbepaalde tijd onderbroken.

Zie PAUSE voor meer informatie overRESUME, ABORTen opties die beschikbaar zijn voor hervatbare bewerkingen.

De status voor hervatbare bewerking weergeven

Als u de status van de hervatbare tabelbeperkingsbewerking wilt weergeven, gebruikt u de weergave sys.index_resumable_operations.

Permissions

Vereist ALTER autorisatie op de tabel.

Er zijn geen nieuwe machtigingen vereist voor hervatbare ALTER TABLE ADD CONSTRAINT machtigingen.

Voorbeelden

Hier zijn enkele voorbeelden van het gebruik van hervatbare operaties voor het toevoegen van tabelbeperkingen.

Voorbeeld 1

Hervatbare ALTER TABLE bewerking voor het toevoegen van een primaire sleutel die is geclusterd op kolom (a) met MAX_DURATION van 240 minuten.

ALTER TABLE table1
ADD CONSTRAINT PK_Constrain PRIMARY KEY CLUSTERED (a)
WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 240);

Voorbeeld 2

Hervatbare ALTER TABLE operatie voor het toevoegen van een unieke constraint op twee kolommen (a en b) met een MAX_DURATION van 240 minuten.

ALTER TABLE table2
ADD CONSTRAINT PK_Constrain UNIQUE CLUSTERED (a,b)
WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 240);

Voorbeeld 3

ALTER TABLE bewerking voor het toevoegen van een geclusterde primaire sleutel die is onderbroken en hervat.

In de onderstaande tabel ziet u twee sessies (Session #1 en Session #2) die chronologisch worden uitgevoerd met behulp van de volgende T-SQL-instructies. Session #1 voert een hervatbare ALTER TABLE ADD CONSTRAINT bewerking uit waarmee een primaire sleutel in de kolom wordt gemaakt Col1. Session #2 controleert de uitvoeringsstatus voor de lopende beperking. Na enige tijd onderbreekt het de herbruikbare bewerking. Session #2 controleert de status van de gepauzeerde beperking. Ten slotte hervat Session #1 de onderbroken beperking en controleert Session #2 de status opnieuw.

Sessie 1 Sessie 2
Hervatbare toevoegbeperking uitvoeren

ALTER TABLE TestConstraint
ADD CONSTRAINT PK_TestConstraint PRIMARY KEY (Col1)
WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 30);
De beperkingsstatus controleren

SELECT sql_text, state_desc, percent_complete
FROM sys.index_resumable_operations;
Uitvoer die de bewerking toont

sql_text state_desc percent_complete
ALTER TABLE TestConstraint (...)LOPEND43.552
De hervatbare beperking onderbreken

ALTER INDEX ALL ON TestConstraint PAUSE;
Error

Msg 1219, Level 16, State 1, Line 6
Your session has been disconnected because of a high priority DDL operation.

Msg 1750, Level 16, State 1, Line 6
Could not create constraint or index. See previous errors.

Msg 0, Level 20, State 0, Line 5
A severe error occurred on the current command.
The results, if any, should be discarded.
De beperkingsstatus controleren

SELECT sql_text, state_desc, percent_complete
FROM sys.index_resumable_operations;
Uitvoer die de bewerking weergeeft

sql_text state_desc percent_complete
ALTER TABLE TestConstraint (...)GESTOPT65.339
ALTER INDEX ALL ON TestConstraint RESUME;
De beperkingsstatus controleren

SELECT sql_text, state_desc, percent_complete
FROM sys.index_resumable_operations;
Uitvoer die de bewerking toont

sql_text state_desc percent_complete
ALTER TABLE TestConstraint (...)LOPEND90.238

Zodra de bewerking is voltooid, voert u de volgende T-SQL-instructie uit om de beperking te controleren:

SELECT constraint_name, table_name, constraint_type 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='PRIMARY KEY';
GO

Dit is de resultatenset:

constraint_name tabelnaam beperkingstype
PK_Constraint TestConstraint PRIMAIRE SLEUTEL

Zie ook

  • Last updated on