Adição retomável de restrições de tabela

  • Artigo

Aplica-se a: SQL Server 2022 (16.x) Banco de Dados SQL do AzureInstância Gerenciada de SQL do Azure

A operação retomável para criação e reconstrução de índice online já é compatível com o SQL Server 2019, o Banco de Dados SQL do Azure e a Instância Gerenciada de SQL do Azure. As operações retomáveis permitem que as operações de índice sejam executadas enquanto a tabela estiver online (ONLINE=ON) e também:

  • Pausar e reiniciar uma operação de criação ou recompilação de índice várias vezes para adequar-se a uma janela de manutenção

  • Recuperar-se de falhas de criação ou recompilação de índice, tais como failovers de banco de dados ou ficar sem espaço em disco.

  • Habilitar o truncamento de logs de transações durante uma operação de criação ou recompilação de índice.

  • Quando uma operação de índice está em pausa, tanto o índice original quanto o recém-criado exigem espaço em disco e precisam ser atualizados durante as operações da DML (linguagem de manipulação de dados).

As novas extensões do SQL Server 2022, banco de dados SQL e Instância Gerenciada de SQL permitem uma operação retomável para o comando ALTER TABLE ADD CONSTRAINT da DDL (Linguagem de Definição de Dados) e a adição de uma Chave Primária ou Exclusiva. Para obter mais informações sobre como adicionar uma Chave Primária ou Exclusiva, confira ALTER TABLE table_constraint.

Observação

As restrições de tabela de adição retomável aplicam-se somente às restrições PRIMARY KEY e UNIQUE KEY. Não há suporte para restrições de tabela de adição retomável para restrições de FOREIGN KEY.

Operações retomáveis

Em versões anteriores do SQL Server, a operação ALTER TABLE ADD CONSTRAINT pode ser executada com a opção ONLINE=ON. No entanto, a operação pode levar muitas horas para conclusão em uma tabela grande e pode consumir uma grande quantidade de recursos. Há também a possibilidade de falhas ou interrupção durante essa execução. Introduzimos funcionalidades retomáveis para o ALTER TABLE ADD CONSTRAINT para que os usuários pausem a operação durante uma janela de manutenção ou reiniciem do ponto em que ela foi interrompida durante uma falha de execução, sem reiniciar a operação do início.

Cenários com suporte

A nova funcionalidade retomável do ALTER TABLE ADD CONSTRAINT dá suporte aos seguintes cenários de cliente:

  • Pausar ou retomar a operação em execução ALTER TABLE ADD CONSTRAINT, como pausar para uma janela de manutenção e retomar a operação assim que a janela de manutenção for concluída.

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

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

Observação

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

Esse recurso é especialmente útil para tabelas grandes.

Sintaxe T-SQL para ALTER TABLE

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

Comentários de ALTER TABLE

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

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

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

Sintaxe do T-SQL para ALTER INDEX

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

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

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

Comentários para ALTER INDEX

ALTER INDEX ALL ON <Table> PAUSE

  • Pausar uma operação de adição de restrição de tabela retomável online que está sendo executada

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

  • Retome uma operação de adição de restrição de tabela que seja esteja em pausa manualmente ou devido a uma falha.

MAX_DURATION usado com RESUMABLE=ON

  • O tempo (um valor inteiro especificado em minutos) no qual a operação adicionar restrição de tabela é executada após ser retomada. Quando o tempo expirar, a operação retomável será colocada em pausa se ainda estiver em execução.

WAIT_AT_LOW_PRIORITY usado com RESUMABLE=ON e ONLINE = ON

  • A retomada de uma operação de adição de restrição de tabela online após uma pausa deve aguardar as operações de bloqueio nesta tabela. WAIT_AT_LOW_PRIORITY indica que a operação de adição de restrição de tabela aguardará bloqueios de baixa prioridade, permitindo que outras operações continuem enquanto a operação retomável estiver aguardando. Omitir a opção WAIT_AT_LOW_PRIORITY é equivalente a WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE). Para obter mais informações, consulte WAIT_AT_LOW_PRIORITY.

ALTER INDEX ALL ON <Table> ABORT

  • Anule uma operação de adição de restrição de tabela em execução ou em pausa declarada como retomável. A operação de anulação deve ser executada explicitamente como um comando ABORT para encerrar uma operação de restrição retomável. A falha ou pausa de uma operação de restrição de tabela retomável não encerra sua execução. Em vez disso, deixa a operação em um estado indefinido de pausa.

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

Exibir o status da operação retomável

Para exibir o status da operação de restrição de tabela retomável, use a exibição sys.index_resumable_operations.

Permissões

Requer a permissão ALTER na tabela.

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

Exemplos

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

Exemplo 1

Operação retomável ALTER TABLE para adicionar uma chave primária clusterizada 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 retomável ALTER TABLE para adicionar uma restrição exclusiva em duas colunas (a e b) com MAX_DURATION de 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

Operação ALTER TABLE para adicionar uma chave primária clusterizada sendo pausada e retomada.

A tabela a seguir mostra duas sessões (Session #1 e Session #2) sendo executadas cronologicamente usando as instruções T-SQL a seguir. Session #1 executa uma operação retomável ALTER TABLE ADD CONSTRAINT que cria uma chave primária na coluna Col1. Session #2 verifica o status de execução da restrição em execução. Depois de algum tempo, ela pausa a operação reutilizável. Session #2 verifica o status da restrição pausada. Por fim, Session #1 retoma a restrição pausada e Session #2 verifica o status novamente.

Sessão nº 1 Sessão nº 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);
Verificar o status da restrição

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

sql_textstate_descpercent_complete
ALTER TABLE TestConstraint (...)RUNNING43,552
Pausar a restrição retomável

ALTER INDEX ALL ON TestConstraint PAUSE;
Erro

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.
Verificar o status da restrição

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

sql_textstate_descpercent_complete
ALTER TABLE TestConstraint (...)PAUSED65,339
ALTER INDEX ALL ON TestConstraint RESUME;
Verificar o status da restrição

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

sql_textstate_descpercent_complete
ALTER TABLE TestConstraint (...)RUNNING90,238

Depois que a operação for concluída, 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

Este é o conjunto de resultados:

constraint_name table_name constraint_type
PK_Constraint TestConstraint PRIMARY KEY

Confira também