Adição retomável de restrições de tabela
Aplica-se a: SQL Server 2022 (16.x)
Banco de Dados SQL do Azure
Instâ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 comRESUMABLE = ON
(requer oONLINE = 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çãoWAIT_AT_LOW_PRIORITY
é equivalente aWAIT_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ávelALTER TABLE TestConstraint ADD CONSTRAINT PK_TestConstraint PRIMARY KEY (Col1) WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 30); |
|||||||
Verificar o status da restriçãoSELECT sql_text, state_desc, percent_complete FROM sys.index_resumable_operations; |
|||||||
Saída mostrando a operação
|
|||||||
Pausar a restrição retomávelALTER 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çãoSELECT sql_text, state_desc, percent_complete FROM sys.index_resumable_operations; |
|||||||
Saída mostrando a operação
|
|||||||
ALTER INDEX ALL ON TestConstraint RESUME; |
|||||||
Verificar o status da restriçãoSELECT sql_text, state_desc, percent_complete FROM sys.index_resumable_operations; |
|||||||
Saída mostrando a operação
|
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 |