Partilhar via

Adicionar restrições retomáveis à tabela

Aplica-se a: SQL Server 2022 (16.x) e versões posteriores Azure SQL Database AzureSQL Managed InstanceSQL database in Microsoft Fabric

A operação retomável para criação e reconstrução de índices online já é suportada para SQL Server 2019, Azure SQL Database e Azure SQL Managed Instance. As operações retomáveis permitem executar operações de índice enquanto a tabela está online (ONLINE=ON) e também:

  • Pause e reinicie várias vezes uma operação de criação ou reconstrução de índice para se adequar a uma janela de manutenção.

  • Recuperar de falhas na criação ou reconstrução de índices, como failovers de base de dados ou falta de espaço em disco.

  • Permitir o truncamento dos registos de transações durante uma operação de criação ou reconstrução de índice.

  • Quando uma operação de índice é pausada, tanto o índice original como o recém-criado requerem espaço em disco e precisam de ser atualizados durante operações de Linguagem de Manipulação de Dados (DML ).

As novas extensões para SQL Server 2022, SQL Database e SQL Managed Instance permitem retomar a operação do comando Data Definition Language (DDL), como ALTER TABLE ADD CONSTRAINT, e adicionar uma Chave Primária ou Única. Para mais informações sobre a adição de uma Chave Primária ou Única, consulte ALTER TABLE table_constraint.

Observação

As restrições da tabela de adição retomáveis aplicam-se apenas às restrições de CHAVE PRIMÁRIA e CHAVE ÚNICA. As restrições de adição retomável de tabela não são suportadas para as restrições de chave estrangeira.

Operações retomáveis

Em versões anteriores do SQL Server, a ALTER TABLE ADD CONSTRAINT operação podia ser executada com a ONLINE=ON opção. No entanto, a operação pode demorar muitas horas para ser concluída para uma tabela grande e pode consumir uma grande quantidade de recursos. Existe também a possibilidade de falhas ou interrupções durante essa execução. Introduzimos capacidades retomáveis para ALTER TABLE ADD CONSTRAINT que os utilizadores possam pausar a operação durante uma janela de manutenção, ou reiniciá-la a partir do local onde foi interrompida durante uma falha de execução, sem reiniciar a operação desde o início.

Cenários suportados

A nova capacidade retomável para ALTER TABLE ADD CONSTRAINT suporta os seguintes cenários para os clientes:

  • Pause ou retome a execução ALTER TABLE ADD CONSTRAINT da operação, como pausar durante uma janela de manutenção, e retomar a operação assim que a janela de manutenção estiver concluída.

  • Retomar ALTER TABLE ADD CONSTRAINT a operação após failovers e falhas do sistema.

  • Executar a operação ALTER TABLE ADD CONSTRAINT numa tabela grande, apesar do pequeno tamanho de log disponível.

Observação

A operação retomável para ALTER TABLE ADD CONSTRAINT exige que o ALTER comando seja executado online (WITH ONLINE = ON).

Esta funcionalidade é especialmente útil para tabelas grandes.

Sintaxe T-SQL para ALTER TABLE

Para informações sobre a sintaxe usada para permitir operações retomáveis numa restrição de tabela, consulte a sintaxe e as opções em ALTER TABLE (Transact-SQL).

Observações para ALTER TABLE

  • Uma nova cláusula WITH <resumable_options foi adicionada à sintaxe atual do T-SQL em ALTER TABLE (Transact-SQL).

  • A opção RESUMABLE é nova e foi adicionada à sintaxe existente ALTER TABLE (Transact-SQL).

  • MAX_DURATION = tempo [MINUTOS] usado com RESUMABLE = ON (requer ONLINE = ON). MAX_DURATION indica o tempo (um valor inteiro especificado em minutos) em que uma operação de restrição de adição online retomável é executada antes de ser pausada. Se não for especificado, a operação continua até a conclusão.

Sintaxe T-SQL para ALTER INDEX

Para pausar, retomar ou abortar a operação de restrição de tabela retomável para ALTER TABLE ADD CONSTRAINT, use a sintaxe T-SQL ALTER INDEX (Transact-SQL).

Para restrições retomáveis é utilizado o comando ALTER INDEX ALL existente.

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 } )  
}

Observações para o ALTER INDEX

ALTER INDEX ALL ON <Table> PAUSE

  • Pausar uma operação de adicionar restrição à tabela, que é retomável e online, que está a ser executada

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

  • Retomar uma operação de adição de restrições de tabela que foi pausada manualmente ou devido a uma falha.

MAX_DURATION Usado com RESUMABLE=ON

  • O tempo (um valor inteiro especificado em minutos) em que a operação de restrição de adição resumível à tabela é executada uma vez retomada. Quando o tempo limite termina, a operação retomável é pausada se ainda estiver em execução.

WAIT_AT_LOW_PRIORITY usado com RESUMABLE=ON e ONLINE = ON

  • Retomar uma operação online para adicionar uma restrição à tabela após uma pausa deve esperar por operações que bloqueiam essa tabela. WAIT_AT_LOW_PRIORITY indica que a operação de adicionar restrições à tabela esperará por bloqueios de baixa prioridade, permitindo que outras operações prossigam enquanto a operação que pode ser retomada está à espera. Omitir a opção WAIT_AT_LOW_PRIORITY é equivalente a WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE). Para mais informações, consulte WAIT_AT_LOW_PRIORITY.

ALTER INDEX ALL ON <Table> ABORT

  • Abortar uma operação de adição de restrição de tabela em execução ou pausada que foi declarada como retomável. A operação de abortar deve ser executada explicitamente como um ABORT comando para terminar uma operação de restrição retomável. A falha ou a pausa de uma operação de restrição de tabela retomável não termina a sua execução. Pelo contrário, deixa a operação num estado pausado indefinido.

Para mais informações sobre PAUSE, RESUME, e ABORT opções disponíveis para operações retomáveis, veja ALTER INDEX (Transact-SQL).

Ver o estado da operação retomável

Para visualizar o estado da operação de restrição de tabela retomável, use a visualização sys.index_resumable_operations.

Permissions

É necessária a permissão ALTER na tabela.

Não são necessárias novas permissões para o retomável ALTER TABLE ADD CONSTRAINT .

Examples

Aqui estão alguns exemplos de como usar operações de restrição de tabela de adição retomáveis.

Exemplo 1

Operação ALTER TABLE retomável para adicionar uma chave primária agrupada na coluna (a) com MAX_DURATION de 240 minutos.

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

Exemplo 2

Operação ALTER TABLE retomável para adicionar uma restrição única em duas colunas (a e b) que dura MAX_DURATION 240 minutos.

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

Exemplo 3

ALTER TABLE A operação para adicionar uma chave primária clusterizada foi pausada e retomada.

A tabela abaixo mostra duas sessões (Session #1 e Session #2) a serem executadas cronologicamente usando as seguintes instruções T-SQL. Session #1 executa uma operação retomável ALTER TABLE ADD CONSTRAINT criando uma chave primária na coluna Col1. Session #2 verifica o estado de execução da restrição de execução. Após algum tempo, a operação reutilizável é pausada. Session #2 verifica o estado da restrição pausada. Finalmente, Session #1 retoma a restrição pausada e Session #2 verifica novamente o estado.

Sessão #1 Sessão #2
Executar restrição de adição retomável

ALTER TABLE TestConstraint
ADD CONSTRAINT PK_TestConstraint PRIMARY KEY (Col1)
WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 30);
Verifique o estado da restrição

SELECT sql_text, state_desc, percent_complete
FROM sys.index_resumable_operations;
Saída que mostra a operação

sql_text state_desc percentagem_concluída
ALTER TABLE TestConstraint (...)CORRIDA43.552
Pausar a restrição retomável

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.
Verifique o estado da restrição

SELECT sql_text, state_desc, percent_complete
FROM sys.index_resumable_operations;
Saída que mostra a operação

sql_text state_desc percentagem_completa
ALTER TABLE TestConstraint (...)PAUSADO65.339
ALTER INDEX ALL ON TestConstraint RESUME;
Verifique o estado da restrição

SELECT sql_text, state_desc, percent_complete
FROM sys.index_resumable_operations;
Saída que mostra a operação

sql_text state_desc percentagem_completa
ALTER TABLE TestConstraint (...)CORRIDA90.238

Uma vez concluída a operação, execute a seguinte instrução T-SQL para verificar a restrição:

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

Aqui está o conjunto de resultados:

constraint_name nome_da_tabela tipo_de_restrição
Restrição_PK TestConstraint CHAVE PRIMÁRIA

Consulte também

  • Last updated on