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

Aplica-se a: SQL Server 2022 (16.x) e versões posteriores Banco de dados SQL do AzureInstância Gerenciada de SQL do AzureBanco de dados SQL no Microsoft Fabric

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

• Recupere-se de falhas na criação ou reconstrução de índice, como failovers do banco de dados ou esgotamento do 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 para SQL Server 2022, Banco de Dados SQL e Instância Gerenciada de SQL permitem uma operação retomável para o comando DDL (Linguagem de Definição de Dados)ADD CONSTRAINT e a adição de uma Chave Primária ou Exclusiva.ALTER TABLE Para obter mais informações sobre como adicionar uma chave primária ou exclusiva, consulte ALTER TABLE table_constraint.

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 em uma tabela grande pode levar muitas horas para ser concluída e pode consumir muitos 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 uma operação em execução ALTER TABLE ADD CONSTRAINT, por exemplo, pausando-a durante uma janela de manutenção e retomando-a quando a janela de manutenção terminar.

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

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

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 para ALTER TABLE

• Uma nova cláusula WITH <resumable_options foi adicionada à sintaxe T-SQL atual 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) 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 T-SQL para ALTER INDEX

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

Para restrições retomáveis, o comando ALL existente ALTER INDEX é 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 online e retomável de adicionar uma restrição à tabela que está sendo executada

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

• Retome uma operação de adicionar uma restrição de tabela que esteja pausada manualmente ou devido a uma falha.

MAX_DURATION usado com RESUMABLE=ON

• O tempo (um valor inteiro especificado em minutos) após o qual a operação retomável de adição de restrição à tabela é executada depois de 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

• Retomar uma operação online de adição de restrição à tabela após uma pausa deve aguardar a conclusão das operações de bloqueio nessa tabela. WAIT_AT_LOW_PRIORITY indica que a operação de adicionar uma restrição à tabela aguardará bloqueios de baixa prioridade, permitindo que outras operações prossigam 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 PAUSE, RESUMEe ABORT opções disponíveis para operações retomáveis, consulte 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 para a tabela.

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

Exemplos

Veja alguns exemplos de uso de operações retomáveis para adicionar restrições de tabela.

Exemplo 1

Operação reiniciável ALTER TABLE para adicionar uma chave primária clusterizada na coluna (a), com MAX_DURATION definido para 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 de adição de 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 estado da restrição pausada. Por fim, Session #1 retoma a restrição pausada e Session #2 verifica o status novamente.

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:

Confira também

• Diretrizes para operações de índice online
• CREATE INDEX (Transact-SQL)
• ALTER INDEX (Transact-SQL)
• sys.index_resumable_operations
• WAIT_AT_LOW_PRIORITY
• ALTER TABLE (Transact-SQL)
• ALTER TABLE index_option