ALTER INDEX (Transact-SQL)
Aplica-se a: SQL Server
Banco de Dados
SQL do Azure Instância Gerenciada de SQL
do Azure PDW (Sistema de Plataforma de Análise) do Azure Synapse Analytics
Modifica uma tabela ou índice de exibição existente (rowstore, columnstore ou XML) desabilitando, recriando ou reorganizando o índice, ou definindo opções no índice.
Convenções de sintaxe de Transact-SQL
Sintaxe
Sintaxe para SQL Server, Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure.
ALTER INDEX { index_name | ALL } ON <object>
{
REBUILD {
[ PARTITION = ALL [ WITH ( <rebuild_index_option> [ , ...n ] ) ] ]
| [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> [ , ...n ] ) ] ]
}
| DISABLE
| REORGANIZE [ PARTITION = partition_number ] [ WITH ( <reorganize_option> ) ]
| SET ( <set_index_option> [ , ...n ] )
| RESUME [ WITH (<resumable_index_option> [ , ...n ] ) ]
| PAUSE
| ABORT
}
[ ; ]
<object> ::=
{
{ database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }
}
<rebuild_index_option> ::=
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB = { ON | OFF }
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| STATISTICS_INCREMENTAL = { ON | OFF }
| ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
| RESUMABLE = { ON | OFF }
| MAX_DURATION = <time> [ MINUTES ]
| ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| MAXDOP = max_degree_of_parallelism
| DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
[ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ]
| XML_COMPRESSION = { ON | OFF }
[ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ] }
<single_partition_rebuild_index_option> ::=
{
SORT_IN_TEMPDB = { ON | OFF }
| MAXDOP = max_degree_of_parallelism
| RESUMABLE = { ON | OFF }
| MAX_DURATION = <time> [ MINUTES ]
| DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
| XML_COMPRESSION = { ON | OFF }
| ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
}
<reorganize_option> ::=
{
LOB_COMPACTION = { ON | OFF }
| COMPRESS_ALL_ROW_GROUPS = { ON | OFF }
}
<set_index_option> ::=
{
ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| COMPRESSION_DELAY = { 0 | delay [ Minutes ] }
}
<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 } )
}
Sintaxe para Azure Synapse Analytics e PDW (Analytics Platform System).
ALTER INDEX { index_name | ALL }
ON [ schema_name. ] table_name
{
REBUILD {
[ PARTITION = ALL [ WITH ( <rebuild_index_option> ) ] ]
| [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> ) ] ]
}
| DISABLE
| REORGANIZE [ PARTITION = partition_number ]
}
[ ; ]
<rebuild_index_option> ::=
{
DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
[ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ]
| XML_COMPRESSION = { ON | OFF }
[ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ]
}
<single_partition_rebuild_index_option> ::=
{
DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
| XML_COMPRESSION = { ON | OFF }
}
Observação
Para exibir a sintaxe do Transact-SQL para o SQL Server 2014 (12.x) e versões anteriores, confira a Documentação das versões anteriores.
Argumentos
index_name
O nome do índice. Os nomes de índice devem ser exclusivos em uma tabela ou exibição, mas não precisam ser exclusivos no banco de dados. Os nomes de índice precisam seguir as regras para identificadores.
ALL
Especifica todos os índices associados à tabela ou exibição, independentemente do tipo de índice. A especificação ALL
fará com que a instrução falhe se um ou mais índices estiverem em um grupo de arquivos offline ou somente leitura ou se a operação especificada não for permitida em um ou mais tipos de índice. A tabela a seguir lista as operações de índice e os tipos de índice não permitidos.
Usando a palavra-chave ALL com esta operação | Falhará se a tabela tiver um ou mais |
---|---|
REBUILD WITH ONLINE = ON |
Índice XML Índice espacial Índice columnstore 1 |
REBUILD PARTITION = <partition_number> |
Índice não particionado, índice XML, índice espacial ou índice desabilitado |
REORGANIZE |
Índices com ALLOW_PAGE_LOCKS definido como OFF |
REORGANIZE PARTITION = <partition_number> |
Índice não particionado, índice XML, índice espacial ou índice desabilitado |
IGNORE_DUP_KEY = ON |
Índice XML Índice espacial Índice columnstore 1 |
ONLINE = ON |
Índice XML Índice espacial Índice columnstore 1 |
RESUMABLE = ON 2 |
Índices retomáveis não suportados com ALL palavra-chave |
1 Aplica-se ao SQL Server 2012 (11.x) e versões posteriores e ao Banco de Dados SQL do Azure.
2 Aplica-se ao SQL Server 2017 (14.x) e versões posteriores e ao Banco de Dados SQL do Azure
Se ALL
for especificado com PARTITION = <partition_number>
, todos os índices devem ser alinhados. Isso significa que eles serão particionados com base nas funções de partições equivalentes. Usar ALL
with PARTITION
faz com que todas as partições de índice com o mesmo <partition_number>
sejam reconstruídas ou reorganizadas. Para obter mais informações sobre índices particionados, consulte Tabelas e índices particionados.
Para obter informações mais detalhadas sobre operações de índice que podem ser executadas online, consulte Diretrizes para operações de índice online.
database_name
O nome do banco de dados.
schema_name
O nome do esquema ao qual a tabela ou exibição pertence.
table_or_view_name
É o nome da tabela ou exibição associada ao índice. Para exibir um relatório dos índices em um objeto, use a exibição do catálogo sys.indexes.
O Banco de Dados SQL oferece suporte ao formato <database_name>.[schema_name].<table_or_view_name>
de nome de três partes quando o database_name é o banco de dados atual ou o database_name é tempdb
e o table_or_view_name começa com #
.
RECONSTRUIR [ COM ( <rebuild_index_option> [ ,... n ] ) ]
Aplica-se a: SQL Server 2012 (11.x) e versões posteriores e Banco de Dados SQL do Azure
Especifica que o índice é recriado usando as mesmas colunas, tipo de índice, atributo de exclusividade e ordem de classificação. Essa cláusula é equivalente a DBCC DBREINDEX. REBUILD
habilita um índice desabilitado. A recriação de um índice clusterizado não recria índices não clusterizados associados, a menos que a palavra-chave ALL
seja especificada. Se as opções de índice não forem especificadas, os valores de opção de índice existentes armazenados em sys.indexes serão aplicados. Para qualquer opção de índice cujo valor não seja armazenado em sys.indexes
, o padrão indicado na definição de argumento da opção será aplicado.
Se ALL
for especificado e a tabela subjacente for um heap, a REBUILD
operação não terá efeito na tabela. Quaisquer índices não clusterizados associados à tabela serão recriados.
A operação REBUILD
poderá ser registrada minimamente se o modelo de recuperação de banco de dados for definido como bulk-logged ou simples.
Observação
Ao recriar um índice XML primário, a tabela de usuário subjacente não estará disponível durante a operação de índice.
Para índices columnstore, a operação REBUILD
:
- Não usa a ordem de classificação.
- Obtém um bloqueio exclusivo na tabela ou na partição durante a
REBUILD
. Os dados ficam "offline" e indisponíveis durante o , mesmo ao usarNOLOCK
o RCSI (Read Committed Snapshot Isolation) ou oREBUILD
SI (Snapshot Isolation, isolamento de snapshot confirmado). - Compacta novamente todos os dados no columnstore. Há duas cópias do índice columnstore durante a
REBUILD
. Quando aREBUILD
é concluída, o SQL Server exclui o índice columnstore original.
Para obter mais informações, consulte Otimizar a manutenção do índice para melhorar o desempenho da consulta e reduzir o consumo de recursos.
PARTITION
Especifica que apenas uma partição de um índice é recriada ou reorganizada. PARTITION
não poderá ser especificado se index_name não for um índice particionado.
PARTITION = ALL
reconstrói todas as partições.
Aviso
É possível criar e recompilar índices não alinhados em uma tabela com mais de 1.000 partições, mas não há suporte. Isso pode causar desempenho degradado ou consumo excessivo de memória durante essas operações. A Microsoft recomenda usar índices alinhados apenas quando o número de partições for maior que 1.000.
partition_number
É o número de partição de um índice particionado que será recompilado ou reorganizado. partition_number é uma expressão de constante que pode fazer referência a variáveis. Isso inclui variáveis de tipo definido pelo usuário ou funções e funções definidas pelo usuário, mas não é possível fazer referência a uma instrução Transact-SQL. partition_number deve existir ou a instrução falhará.
COM ( <single_partition_rebuild_index_option> )
SORT_IN_TEMPDB
,MAXDOP
,DATA_COMPRESSION
eXML_COMPRESSION
são as opções que podem ser especificadas ao executarREBUILD
em uma só partição(PARTITION = partition_number)
. Índices XML não podem ser especificados em uma operaçãoREBUILD
de partição.
DISABLE
Marca o índice como desabilitado e indisponível para uso pelo Mecanismo de Banco de Dados. Qualquer índice pode ser desabilitado. A definição de um índice desabilitado permanece no catálogo do sistema sem nenhum dado de índice subjacente. Desabilitar um índice clusterizado evita que o usuário acesse os dados da tabela subjacente. Para habilitar um índice, use ALTER INDEX REBUILD
ou CREATE INDEX WITH DROP_EXISTING
. Para obter mais informações, consulte Desabilitar índices e restrições e Habilitar índices e restrições.
REORGANIZE um índice rowstore
Para índices rowstore, REORGANIZE
especifica a reorganização do nível folha do índice. A REORGANIZE
operação é:
- sempre executada online. Isso significa que os bloqueios de tabela de longo prazo não são mantidos e que as consultas ou atualizações da tabela subjacente podem continuar durante a transação
ALTER INDEX REORGANIZE
. - Não é permitida para um índice desabilitado.
- Não permitido quando
ALLOW_PAGE_LOCKS
está definido comoOFF
. - Não é revertido quando executado em uma transação e a transação é revertida.
Observação
Quando ALTER INDEX REORGANIZE
usa transações explícitas (por exemplo, ALTER INDEX
dentro de uma BEGIN TRAN ... COMMIT/ROLLBACK
) em vez do modo de transação implícita padrão, o comportamento de bloqueio de REORGANIZE
se torna mais restritivo, potencialmente causando bloqueio. Para obter mais informações sobre transações implícitas, consulte SET IMPLICIT_TRANSACTIONS.
Para obter mais informações, consulte Otimizar a manutenção do índice para melhorar o desempenho da consulta e reduzir o consumo de recursos.
REORGANIZE WITH ( LOB_COMPACTION = { ON | OFF } )
Aplica-se a índices rowstore.
LOB_COMPACTION = ON
- Especifica compactar todas as páginas que contêm dados destes tipos de dados de LOB (objeto grande): image, text, ntext, varchar(max), nvarchar(max), varbinary(max) e xml. A compactação desses dados pode reduzir o tamanho dos dados no disco.
- Para um índice clusterizado, isso compacta todas as colunas LOB contidas na tabela.
- Reorganizar um índice não clusterizado, compacta todas as colunas LOB não chave (incluídas) no índice.
REORGANIZE ALL
executa LOB_COMPACTION em todos os índices. Para cada índice, isso compacta todas as colunas LOB no índice clusterizado, na tabela subjacente ou nas colunas incluídas em um índice não clusterizado.
LOB_COMPACTION = OFF
- Páginas que contêm dados de objeto grande não são compactadas.
OFF
não tem efeito em um heap.
REORGANIZE um índice columnstore
Para índices columnstore, REORGANIZE
compacta cada CLOSED
rowgroup delta no columnstore como um rowgroup compactado. A operação REORGANIZE
é sempre executada online. Isso significa que os bloqueios de tabela de longo prazo não são mantidos e que as consultas ou atualizações da tabela subjacente podem continuar durante a transação ALTER INDEX REORGANIZE
. Para obter mais informações, consulte Otimizar a manutenção do índice para melhorar o desempenho da consulta e reduzir o consumo de recursos.
REORGANIZE
não é necessário para moverCLOSED
rowgroups delta para rowgroups compactados. O processo TM (movedor de tupla) em segundo plano é ativado periodicamente para compactarCLOSED
grupos de linhas delta. Recomendamos usarREORGANIZE
quando o movimentador de tupla estiver ficando para trás.REORGANIZE
pode compactar rowgroups de forma mais agressiva.- Para compactar todos e
OPEN
CLOSED
rowgroups, consulte aREORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS)
opção nesta seção.
Para índices columnstore no SQL Server (começando com SQL Server 2016 (13.x)) e Banco de Dados SQL do Azure, REORGANIZE
executa as seguintes otimizações extras de desfragmentação online:
Remove fisicamente linhas de um grupo de linhas quando 10% ou mais linhas foram excluídas logicamente. Os bytes excluídos são recuperados na mídia física. Por exemplo, se um grupo de linhas compactado de 1 milhão de linhas tiver 100.000 linhas excluídas, o SQL Server removerá as linhas excluídas e recompactará o grupo de linhas com 900 mil linhas. Ele salva no armazenamento removendo as linhas excluídas.
Combina um ou mais rowgroups compactados para aumentar linhas por rowgroup até o máximo de 1.048.576 linhas. Por exemplo, se você importar em massa 5 lotes de 102.400 linhas, obterá 5 rowgroups compactados. Se você executar
REORGANIZE
o , esses rowgroups serão mesclados em 1 rowgroup compactado de tamanho 512.000 linhas. Isso pressupõe que não havia nenhuma limitação de tamanho ou memória de dicionário.Para rowgroups nos quais 10% ou mais das linhas foram excluídas logicamente, o SQL Server tenta combinar esse rowgroup com um ou mais rowgroups. Por exemplo, o rowgroup 1 é compactado com 500 mil linhas e o rowgroup 21 é compactado com o máximo de 1.048.576 linhas. O rowgroup 21 tem 60% das linhas excluídas, o que deixa 409.830 linhas. O SQL Server favorece a combinação desses dois rowgroups para compactar um novo rowgroup com 909.830 linhas.
REORGANIZE WITH ( COMPRESS_ALL_ROW_GROUPS = { ON | OFF } )
Aplica-se em índices columnstore.
Aplica-se a: SQL Server (Começando pelo SQL Server 2016 (13.x)) e Banco de Dados SQL do Azure
COMPRESS_ALL_ROW_GROUPS
Fornece uma maneira de forçar OPEN
ou CLOSED
delta rowgroups no columnstore. Com essa opção, não é necessário recompilar o índice columnstore para esvaziar os rowgroups delta. Isso, combinado a outros recursos de desfragmentação de remover e mesclar, faz com que não seja mais necessário recompilar o índice na maioria das situações.
ON
Força todos os rowgroups no columnstore, independentemente do tamanho e do estado (CLOSED
ouOPEN
).OFF
Força todos osCLOSED
rowgroups no columnstore.
Para obter mais informações, consulte Otimizar a manutenção do índice para melhorar o desempenho da consulta e reduzir o consumo de recursos.
SET ( <set_index opção> [ ,... n ] )
Especifica opções de índice sem recriar ou reorganizar o índice. SET
não pode ser especificado para um índice desabilitado.
PAD_INDEX = { ON | OFF }
Especifica o preenchimento do índice. O padrão é OFF
.
ATIVADO
O percentual de espaço livre especificada por
FILLFACTOR
é aplicado às páginas de nível intermediário do índice. SeFILLFACTOR
não for especificado ao mesmo tempoPAD_INDEX
for definido comoON
, o valor do fator de preenchimento armazenado em sys.indexes será usado.OFF ou fillfactor não está especificado
As páginas do nível intermediário são preenchidas até próximo à capacidade máxima. Isso deixa espaço suficiente para pelo menos uma linha do tamanho máximo que o índice pode ter, com base no conjunto de chaves das páginas intermediárias.
Para obter mais informações, confira CREATE INDEX.
FILLFACTOR = fillfactor
Especifica uma porcentagem que indica quanto Mecanismo de Banco de Dados deve preencher o nível folha de cada página de índice durante a criação ou alteração do índice. O valor de fillfactor deve ser um valor inteiro de 1 a 100. O padrão é 0. Os valores de fator de preenchimento 0 e 100 são iguais em todos os aspectos.
Uma configuração FILLFACTOR
explícita é usada somente quando o índice é criado pela primeira vez ou recriado. O Mecanismo de Banco de Dados não mantém dinamicamente o percentual especificado de espaço vazio nas páginas. Para obter mais informações, confira CREATE INDEX.
Para ver a configuração do fator de preenchimento, use fill_factor
em sys.indexes
.
Importante
A criação ou alteração de um índice clusterizado com um valor FILLFACTOR
afeta a quantidade de espaço de armazenamento que os dados ocupam, pois o Mecanismo de Banco de Dados redistribui os dados quando cria o índice clusterizado.
SORT_IN_TEMPDB = { ON | OFF }
Especifica se os resultados de classificação devem ser armazenados em tempdb
. O padrão é OFF
exceto para a Hiperescala do Banco de Dados SQL do Azure. Para todas as operações de recompilação de índice em Hiperescala, SORT_IN_TEMPDB
está sempre ativado, independentemente da opção especificada, a menos que a recompilação de índice retomável seja usada.
ATIVADO
Os resultados de classificação intermediários usados para criar o índice são armazenados em
tempdb
. Setempdb
estiver em um conjunto de discos diferente do banco de dados do usuário, isso poderá reduzir o tempo necessário para criar um índice. Entretanto, isso aumenta o espaço em disco usado durante a criação do índice.OFF
Os resultados intermediários de classificação são armazenados no mesmo banco de dados que o índice.
Se uma operação de classificação não for necessária ou se a classificação puder ser executada na memória, a opção SORT_IN_TEMPDB
será ignorada.
Para obter mais informações, consulte a Opção SORT_IN_TEMPDB para índices.
IGNORE_DUP_KEY = { ON | OFF }
Especifica a resposta de erro quando uma operação de inserção tenta inserir valores da chave duplicada em um índice exclusivo. A opção IGNORE_DUP_KEY
aplica-se apenas a operações de inserção depois que o índice é criado ou recompilado. O padrão é OFF
.
ATIVADO
Uma mensagem de aviso será exibida quando valores de chave duplicados são inseridos em um índice exclusivo. Somente as linhas que violam a restrição de exclusividade falham.
OFF
Uma mensagem de erro ocorre quando valores de chave duplicados são inseridos em um índice exclusivo. Toda a
INSERT
operação é revertida.
IGNORE_DUP_KEY
não pode ser definido como ON
para índices criados em uma exibição, índices não exclusivos, índices XML, índices espaciais e índices filtrados.
Para exibir IGNORE_DUP_KEY
, use sys.indexes.
Na sintaxe compatível com versões anteriores, WITH IGNORE_DUP_KEY
é equivalente a WITH IGNORE_DUP_KEY = ON
.
STATISTICS_NORECOMPUTE = { ON | OFF }
Desative ou ative a opção de atualização automática de estatísticas, AUTO_STATISTICS_UPDATE
, para as estatísticas relacionadas aos índices especificados. O padrão é OFF
.
ATIVADO
As atualizações automáticas de estatísticas são desabilitadas depois que o índice é recriado.
OFF
As atualizações automáticas de estatísticas são habilitadas depois que o índice é recriado.
Para restaurar a atualização automática de estatísticas, defina como STATISTICS_NORECOMPUTE
OFF
ou execute UPDATE STATISTICS
sem a NORECOMPUTE
cláusula.
Aviso
Se você desabilitar a atualização automática de estatísticas, isso poderá impedir que o Otimizador de Consulta escolha planos de execução ideais para consultas que envolvem a tabela. Você deve usar essa opção com moderação e somente por um administrador de banco de dados qualificado.
Essa configuração não impede uma atualização automática com verificação completa das estatísticas relacionadas ao índice, durante a operação de recompilação.
STATISTICS_INCREMENTAL = { ON | OFF }
Aplica-se a: SQL Server 2014 (12.x) e versões posteriores e Banco de Dados SQL do Azure
Quando ON
, as estatísticas criadas são estatísticas por partição. Quando OFF
, a árvore de estatísticas é descartada e o SQL Server recalcula as estatísticas. O padrão é OFF
.
Se não houver suporte para estatísticas por partição, a opção será ignorada e um aviso será gerado. As estatísticas incrementais não têm suporte para os seguintes tipos de estatísticas:
- Estatísticas criadas com os índices que não estejam alinhados por partição à tabela base
- Estatísticas criadas sobre bancos de dados que são réplicas secundárias para leitura de grupo de disponibilidade
- Estatísticas criadas em bancos de dados somente leitura
- Estatísticas criadas em índices filtrados
- Estatísticas criadas em exibições
- Estatísticas criadas em tabelas internas
- Estatísticas criadas com índices espaciais ou índices XML
ONLINE = { ON | OFF } <conforme se aplica a rebuild_index_option>
Especifica se as tabelas subjacentes e os índices associados estão disponíveis para consultas e modificação de dados durante a operação de índice. O padrão é OFF
.
Para um índice XML ou índice espacial, somente ONLINE = OFF
é suportado e, se ONLINE
for definido como ON
um erro, é gerado.
Importante
As operações de índice online não estão disponíveis em todas as edições de Microsoft SQL Server. Para obter uma lista de recursos com suporte nas edições do SQL Server, confira Edições e recursos com suporte no SQL Server 2022.
ATIVADO
Bloqueios de tabela de longa duração não são mantidos durante a operação do índice. Durante a fase principal da operação de índice, apenas um bloqueio IS (Tentativa Compartilhada) é mantido na tabela de origem. Isso permite que as consultas ou atualizações na tabela e nos índices subjacentes continuem. No início da operação, um bloqueio Compartilhado (S) é mantido brevemente no objeto de origem. No final da operação, um bloqueio S é mantido brevemente na origem se um índice não clusterizado estiver sendo criado. Um bloqueio de modificação de esquema (Sch-M) é adquirido quando um índice clusterizado é criado ou descartado online e quando um índice clusterizado ou não clusterizado está sendo recriado.
ONLINE
não pode ser definido comoON
quando um índice está sendo criado em uma tabela temporária local.OFF
Os bloqueios de tabela são aplicados durante a operação de índice. Uma operação de índice offline que cria, recria ou descarta um índice clusterizado, espacial ou XML, ou que recria ou descarta um índice não clusterizado, adquire um bloqueio Sch-M na tabela. Isso evita o acesso de todos os usuários à tabela subjacente enquanto durar a operação. Uma operação de índice offline que cria um índice não clusterizado adquire um bloqueio Compartilhado (S) na tabela. Isso impede atualizações na tabela subjacente, mas permite operações de leitura, como
SELECT
instruções.
Para obter mais informações, consulte Executar operações de índice online.
Índices, inclusive aqueles em tabelas temporárias globais, podem ser recriados online exceto nos seguintes casos:
- Índice XML
- Índice em uma tabela temporária local
- Índice clusterizado exclusivo inicial em uma exibição
- Índices columnstore
- Índice clusterizado se a tabela subjacente contiver tipos de dados LOB (image, ntext, text) e tipos de dados espaciais
- As colunas varchar(max) e varbinary(max) não podem fazer parte de um índice. Em SQL Server (começando com o SQL Server 2012 (11.x)) e Banco de Dados SQL do Azure, quando uma tabela contém as colunas varchar(max) ou varbinary(max) , um índice clusterizado contendo outras colunas pode ser criado ou recriado usando a opção
ONLINE
. Banco de Dados SQL do Azure não permite a opçãoONLINE
quando a tabela base contém as colunas varchar(max) ou varbinary(max)
Para obter mais informações, consulte Como funcionam as operações de índice online.
Os seguintes XEvents são relacionados a ALTER TABLE ... SWITCH PARTITION
e à recompilação de índice online.
- lock_request_priority_state
- process_killed_by_abort_blockers
- ddl_with_wait_at_low_priority
O XEvent progress_report_online_index_operation
existente para operações de índice on-line inclui partition_number
e partition_id
.
RESUMABLE = { ON | OFF}
Aplica-se a: SQL Server 2017 (14.x) e versões posteriores e Banco de Dados SQL do Azure
Especifica se uma operação de índice online é retomável.
ATIVADO
A operação do índice é retomável.
OFF
A operação do índice não é retomável.
MAX_DURATION = tempo [ MINUTOS ] usado com RESUMABLE = ON
(requer ONLINE = ON
)
Aplica-se a: SQL Server 2017 (14.x) e versões posteriores e Banco de Dados SQL do Azure
Indica o tempo (um valor inteiro especificado em minutos) pelo qual um uma operação de índice online retomável é executada antes de ser colocada em pausa.
Importante
Para obter informações mais detalhadas sobre operações de índice que podem ser executadas online, consulte Diretrizes para operações de índice online.
Observação
Não há suporte para recompilações de índice online retomáveis em índices columnstore.
ALLOW_ROW_LOCKS = { ON | OFF }
Especifica se bloqueios de linha são permitidos. O padrão é ON
.
ATIVADO
Bloqueios de linha são permitidos ao acessar o índice. O Mecanismo de Banco de Dados determina quando os bloqueios de linha são usados.
OFF
Bloqueios de linha não são usados.
ALLOW_PAGE_LOCKS = { ON | OFF }
Especifica se bloqueios de página são permitidos. O padrão é ON
.
ATIVADO
Bloqueios de página são permitidos ao acessar o índice. O Mecanismo de Banco de Dados determina quando os bloqueios de página são usados.
OFF
Bloqueios de página não serão usados.
Observação
Um índice não pode ser reorganizado quando ALLOW_PAGE_LOCKS
é definido como OFF
.
OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
Aplica-se a: SQL Server 2019 (15.x) e versões posteriores e Banco de Dados SQL do Azure
Especifica se a contenção de inserção de última página será ou não otimizada. O padrão é OFF
. Para saber mais, confira as Chaves sequenciais.
MAXDOP = max_degree_of_parallelism
Substitui a opção de configuração max degree of parallelism durante a operação do índice. Para obter mais informações, consulte Configurar o grau máximo de paralelismo (opção de configuração do servidor). Use MAXDOP
para limitar o número de processadores usados em uma execução de plano paralelo. O máximo é de 64 processadores.
Importante
Embora a MAXDOP
opção tenha suporte sintaticamente para todos os índices XML, para um índice espacial ou um índice XML primário, ALTER INDEX
atualmente usa apenas um único processador.
max_degree_of_parallelism pode ser:
1
: Suprime a geração de planos paralelos.>1
: Restringe o número máximo de processadores usados em uma operação de índice paralelo ao número especificado.0
(padrão): usa o número real de processadores ou menos com base na carga de trabalho atual do sistema.
Para obter mais informações, consulte Configurar operações de índice paralelo.
Observação
As operações de índice paralelas não estão disponíveis em todas as edições do SQL Server. Para obter uma lista de recursos com suporte nas edições do SQL Server, confira Edições e recursos com suporte no SQL Server 2022.
COMPRESSION_DELAY = { 0 | duração [ minutos ] }
Aplica-se a: SQL Server (Começando pelo SQL Server 2016 (13.x))
Para uma tabela baseada em disco, delay especifica o número mínimo de minutos que um grupo de linhas delta no CLOSED
estado deve permanecer no grupo de linhas delta antes que o SQL Server possa compactá-lo no grupo de linhas compactado. Como as tabelas baseadas em disco não controlam os tempos de inserção e atualização em linhas individuais, o CLOSED
SQL Server aplica o atraso aos rowgroups delta no estado.
O padrão é 0 minuto.
Para obter recomendações sobre quando usar COMPRESSION_DELAY
o , consulte Introdução ao Columnstore para análise operacional em tempo real.
DATA_COMPRESSION
Especifica a opção de compactação de dados para o índice, número de partição ou intervalo de partições especificado. As opções são as descritas a seguir:
Nenhuma
A tabela ou as partições especificadas não são compactadas. Não se aplica a índices columnstore.
ROW
O índice ou as partições especificadas são compactados com o uso da compactação de linha. Não se aplica a índices columnstore.
PAGE
O índice ou as partições especificadas são compactados com o uso da compactação de página. Não se aplica a índices columnstore.
COLUMNSTORE
Aplica-se a: SQL Server 2014 (12.x) e versões posteriores e Banco de Dados SQL do Azure
Aplica-se somente a índices columnstore, incluindo índices columnstore não clusterizados e clusterizados.
COLUMNSTORE
especifica a descompactação do índice ou das partições especificadas que são compactadas com aCOLUMNSTORE_ARCHIVE
opção. Quando os dados são restaurados, eles continuam a ser compactados com a compactação columnstore usada para todos os índices columnstore.COLUMNSTORE_ARCHIVE
Aplica-se a: SQL Server 2014 (12.x) e versões posteriores e Banco de Dados SQL do Azure
Aplica-se somente a índices columnstore, incluindo índices columnstore não clusterizados e clusterizados.
COLUMNSTORE_ARCHIVE
compacta ainda mais a partição especificada para um tamanho menor. Isso pode ser usado para fins de arquivamento, ou em outras situações que exijam menos armazenamento e possam dispensar mais tempo para armazenamento e recuperação.
Para obter mais informações sobre compactação, consulte Compactação de dados.
XML_COMPRESSION
Aplicável a: SQL Server 2022 (16.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure.
Especifica a opção de compactação XML para o índice especificado que contém uma ou mais colunas de tipo de dados xml. As opções são as descritas a seguir:
ATIVADO
O índice ou as partições especificadas são compactados com a compactação XML.
OFF
A tabela ou as partições especificadas não são compactadas.
EM PARTIÇÕES ( { <partition_number_expression> | <intervalo> } [ ,... n ] )
Especifica as partições às quais as configurações DATA_COMPRESSION
e XML_COMPRESSION
se aplicam. Se o índice não for particionado, o argumento gerará ON PARTITIONS
um erro. Se a cláusula ON PARTITIONS
não for fornecida, a opção DATA_COMPRESSION
ou XML_COMPRESSION
será aplicada a todas as partições de um índice particionado.
<partition_number_expression>
pode ser especificado das seguintes maneiras:
Forneça o número de uma partição, por exemplo:
ON PARTITIONS (2)
.Forneça os números de várias partições individuais separados por vírgulas, por exemplo:
ON PARTITIONS (1, 5)
.Forneça os intervalos e as partições individuais:
ON PARTITIONS (2, 4, 6 TO 8)
.
<range>
pode ser especificado como números de partição separados pela palavra TO, por exemplo: ON PARTITIONS (6 TO 8)
.
Para definir tipos diferentes de compactação de dados para partições diferentes, especifique a opção DATA_COMPRESSION
mais de uma vez, por exemplo:
REBUILD WITH
(
DATA_COMPRESSION = NONE ON PARTITIONS (1),
DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8),
DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5)
);
Você também pode especificar a opção XML_COMPRESSION
mais de uma vez, por exemplo:
REBUILD WITH
(
XML_COMPRESSION = OFF ON PARTITIONS (1),
XML_COMPRESSION = ON ON PARTITIONS (2, 4, 6 TO 8),
XML_COMPRESSION = OFF ON PARTITIONS (3, 5)
);
ONLINE = { ON | OFF } <conforme se aplica a single_partition_rebuild_index_option>
Especifica se um índice ou partição do índice de uma tabela subjacente pode ser recriado online ou offline. Se REBUILD ... ONLINE = ON
for executada, os dados nessa tabela ficarão disponíveis para consultas e modificação de dados durante a operação de índice. O padrão é OFF
.
ATIVADO
Bloqueios de tabela de longa duração não são mantidos durante a operação do índice. Durante a fase principal da operação de índice, apenas um bloqueio IS (Tentativa Compartilhada) é mantido na tabela de origem. Um bloqueio de Sch-S (Estabilidade de Esquema) na tabela é necessário ao iniciar a recompilação do índice, já um bloqueio de Sch-M (Modificação de Esquema) é necessário na tabela no final da recompilação do índice online. Embora ambos os bloqueios de metadados sejam de curta duração, especialmente o bloqueio de Sch-M deve esperar que todas as transações de bloqueio sejam concluídas. Durante o tempo de espera, o bloqueio Sch-M bloqueia todas as transações restantes que esperam atrás desse bloqueio ao acessar a mesma tabela.
Observação
A recompilação de índice online pode definir as opções
low_priority_lock_wait
, confira WAIT_AT_LOW_PRIORITY com operações de índice online.OFF
Os bloqueios de tabela são aplicados durante a operação de índice. Isso evita o acesso de todos os usuários à tabela subjacente enquanto durar a operação.
RESUME
Aplica-se a: SQL Server 2017 (14.x) e versões posteriores e Banco de Dados SQL do Azure
Retome uma operação de índice que seja esteja em pausa manualmente ou devido a uma falha.
MAX_DURATION
usado comRESUMABLE = ON
O tempo (um valor de inteiro especificado em minutos) no qual a operação de índice online é 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 comRESUMABLE = ON
eONLINE = ON
.Retomar uma recompilação de índice online após uma pausa precisa esperar as operações de bloqueio nesta tabela.
WAIT_AT_LOW_PRIORITY
indica que a operação de recompilação de índice online aguarda bloqueios de baixa prioridade, permitindo que outras operações prossigam enquanto a operação de compilação de índice online está 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.
PAUSE
Aplica-se a: SQL Server 2017 (14.x) e versões posteriores e Banco de Dados SQL do Azure
Pause uma operação de recompilação de índice online retomável.
ABORT
Aplica-se a: SQL Server 2017 (14.x) e versões posteriores e Banco de Dados SQL do Azure
Anule uma operação de índice em execução ou em pausa declarada como retomável. Você precisa executar explicitamente uma operação ABORT
para terminar uma operação de recompilação de índice retomável. A falha ou pausa de uma operação de índice retomável não conclui a execução, mas deixa a operação em um estado de pausa indefinido.
Comentários
ALTER INDEX
não pode ser usado para reparticionar um índice ou movê-lo para um grupo de arquivos diferente. Essa instrução não pode ser usada para modificar a definição de índice, como adicionar ou excluir colunas ou alterar a ordem das colunas. Use CREATE INDEX
com a cláusula DROP_EXISTING
para executar essas operações.
Quando uma opção não for especificada explicitamente, a configuração atual será aplicada. Por exemplo, se uma FILLFACTOR
configuração não for especificada na REBUILD
cláusula, o valor do fator de preenchimento armazenado no catálogo do sistema será usado durante o processo de recompilação. Para exibir as configurações de opção de índice atuais, use sys.indexes.
Os valores para ONLINE
, MAXDOP
e SORT_IN_TEMPDB
não são armazenados no catálogo do sistema. A menos que especificado na instrução de índice, o valor padrão da opção será usado.
Em computadores multiprocessadores, assim como acontece em outras consultas, ALTER INDEX REBUILD
usa automaticamente mais processadores para executar operações de exame e classificação associadas à modificação do índice. Quando você executar ALTER INDEX REORGANIZE
, com ou sem LOB_COMPACTION
, o valor grau máximo de paralelismo será uma operação de thread único. Para obter mais informações, consulte Configurar operações de índice paralelo.
Importante
Um índice não poderá ser reorganizado ou recriado se o grupo de arquivos no qual ele está localizado estiver offline ou definido como somente leitura. Quando a palavra-chave ALL
for especificada e um ou mais índices estiver em um grupo de arquivos offline ou somente leitura, a instrução falhará.
Recriar índices
A recriação de um índice descarta e recria o índice. Isso remove a fragmentação, recupera espaço em disco ao compactar as páginas com base na configuração do fator de preenchimento especificada ou existente, e reclassifica as linhas do índice em páginas contíguas. Quando ALL
é especificado, todos os índices da tabela são descartados e recriados em uma única transação. As restrições de chave estrangeira não precisam ser removidas com antecedência. Quando índices com 128 extensões ou mais são recriados, o Mecanismo de Banco de Dados adia as desalocações de página atuais e seus bloqueios associados até depois da confirmação da transação.
Para obter mais informações, consulte Otimizar a manutenção do índice para melhorar o desempenho da consulta e reduzir o consumo de recursos.
Reorganizar índices
A reorganização de um índice utiliza recursos mínimos do sistema. Ela desfragmenta o nível folha de índices clusterizados e não clusterizados em tabelas e exibições, reordenando fisicamente as páginas de nível folha para que correspondam à ordem lógica, da esquerda para a direita, dos nós folha. A reorganização também compacta as páginas de índice. A compactação baseia-se no valor do fator de preenchimento existente.
Quando ALL
for especificado, os índices relacionais, clusterizados e não clusterizados e os índices XML da tabela serão reorganizados. Algumas restrições se aplicam quando ALL
é especificado; veja a definição de ALL
na seção Argumentos deste artigo.
Para obter mais informações, consulte Otimizar a manutenção do índice para melhorar o desempenho da consulta e reduzir o consumo de recursos.
Importante
Para uma tabela do Azure Synapse Analytics com um índice columnstore clusterizado ordenado, ALTER INDEX REORGANIZE
o não reclassifica os dados. Para reclassificar os dados, use ALTER INDEX REBUILD
.
Desabilitar índices
A desabilitação de um índice impede o acesso do usuário ao índice, e, para índices clusterizados, aos dados da tabela subjacente. A definição de índice permanece no catálogo do sistema. A desabilitação de um índice não clusterizado ou clusterizado em uma exibição exclui fisicamente os dados do índice. A desabilitação de um índice clusterizado impede o acesso aos dados, mas eles permanecem inalterados na árvore B até que o índice seja descartado ou recriado. Para exibir o status de um índice habilitado ou desabilitado, consulte a coluna is_disabled
na exibição do catálogo sys.indexes
.
Observação
A documentação do SQL Server usa o termo árvore B geralmente em referência a índices. Em índices de rowstore, o SQL Server implementa uma árvore B+. Isso não se aplica a índices columnstore ou armazenamentos de dados na memória. Para obter mais informações, confira o Guia de arquitetura e design do índice do SQL Server e SQL do Azure.
Se uma tabela estiver em uma publicação de replicação transacional, não será possível desabilitar nenhum índice associado a colunas de chave primária. Esses índices são necessários para a replicação. Para desabilitar um índice, você deve primeiramente descartar a tabela da publicação. Para obter mais informações, consulte Publicar dados e objetos de banco de dados.
Use a instrução ALTER INDEX REBUILD
ou CREATE INDEX WITH DROP_EXISTING
para habilitar o índice. A recriação de um índice clusterizado desabilitado não pode ser executada com a ONLINE
opção definida como ON
. Para obter mais informações, consulte Desabilitar índices e restrições.
Como definir as opções
Você pode definir as opções ALLOW_ROW_LOCKS
, ALLOW_PAGE_LOCKS
, OPTIMIZE_FOR_SEQUENTIAL_KEY
, IGNORE_DUP_KEY
e STATISTICS_NORECOMPUTE
para um índice especificado sem reconstruir ou reorganizar esse índice. Os valores modificados são aplicados imediatamente ao índice. Para exibir essas configurações, use sys.indexes
. Para obter mais informações sobre opções de índice, consulte Definir opções de índice.
Opções de bloqueios de linha e de página
Quando ALLOW_ROW_LOCKS = ON
e ALLOW_PAGE_LOCK = ON
, os bloqueios em nível de linha, página e tabela são permitidos quando você acessa o índice. O Mecanismo de Banco de Dados escolhe o bloqueio apropriado e pode escalar o bloqueio de uma linha ou página para um bloqueio de tabela.
Quando ALLOW_ROW_LOCKS = OFF
e ALLOW_PAGE_LOCK = OFF
, o bloqueio em nível de tabela é permitido quando você acessa o índice.
Se ALL
for especificado quando as opções de bloqueio de linha ou de página forem definidas, as configurações serão aplicadas a todos os índices. Quando a tabela subjacente é um heap, as configurações são aplicadas das seguintes maneiras:
Opção | Detalhes |
---|---|
ALLOW_ROW_LOCKS = ON or OFF |
Ao heap e a quaisquer índices não clusterizados associados. |
ALLOW_PAGE_LOCKS = ON |
Ao heap e a quaisquer índices não clusterizados associados. |
ALLOW_PAGE_LOCKS = OFF |
Totalmente aos índices não clusterizados. Isso significa que todos os bloqueios de página não são permitidos nos índices não clusterizados. No heap, somente os bloqueios S (compartilhados), U (atualizados) e X (exclusivos) da página não são permitidos. O Mecanismo de Banco de Dados ainda pode adquirir um bloqueio de página intencional (IS, IU ou IX) para fins internos. |
Operações de índice online
Ao recriar um índice e a ONLINE
opção é definida como ON
, os objetos subjacentes, as tabelas e os índices associados ficam disponíveis para consultas e modificação de dados. Você também pode recriar online uma parte de um índice que reside em uma única partição. As fechaduras de mesa exclusivas são mantidas apenas por um curto período de tempo durante o processo de alteração.
A reorganização de um índice sempre é executada online. O processo não mantém bloqueios de longo prazo e, portanto, não bloqueia consultas ou atualizações em execução.
Você pode executar operações de índice online simultâneas na mesma tabela ou partição de tabela somente ao executar as seguintes operações:
- Criar vários índices não clusterizados.
- Reorganizar índices diferentes na mesma tabela.
- Reorganizar índices diferentes ao recriar índices não sobrepostos na mesma tabela.
Todas as outras operações de índice online executadas ao mesmo tempo falham. Por exemplo, não é possível recompilar dois ou mais índices na mesma tabela ao mesmo tempo ou criar um índice ao recompilar um índice existente na mesma tabela.
Para obter mais informações, consulte Executar operações de índice online.
Operações de índice retomáveis
Aplica-se a: SQL Server 2017 (14.x) e versões posteriores e Banco de Dados SQL do Azure
A recriação de índice online é especificada como retomável usando a opção RESUMABLE = ON
.
A opção
RESUMABLE
não persiste nos metadados para um determinado índice e se aplica somente à duração de uma instrução DDL atual. Portanto, a cláusulaRESUMABLE = ON
deve ser especificada explicitamente para habilitar a capacidade de retomada.A opção
MAX_DURATION
é compatível com a opçãoRESUMABLE = ON
ou a opçãolow_priority_lock_wait
.MAX_DURATION
forRESUMABLE
especifica o intervalo de tempo para um índice que está sendo recompilado. Depois que esse tempo é usado, a recompilação do índice é pausada ou conclui sua execução. O usuário decide quando uma recompilação de um índice em pausa pode ser retomada. O tempo em minutos paraMAX_DURATION
deve ser superior a 0 minutos e inferior ou igual a uma semana (7 * 24 * 60 = 10.080 minutos). Ter uma longa pausa para uma operação de índice pode afetar o desempenho da DML em uma tabela específica, bem como a capacidade do disco do banco de dados, pois ambos os índices (o original e o recém-criado) exigem espaço em disco e precisam ser atualizados durante as operações DML. SeMAX_DURATION
a opção for omitida, a operação de índice continuará até sua conclusão ou até que ocorra uma falha.- A opção de argumento
low_priority_lock_wait
permite que você decida como a operação de índice pode continuar quando bloqueada no bloqueio Sch-M.
Executar novamente a instrução
ALTER INDEX REBUILD
original com os mesmos parâmetros retoma uma operação de recompilação de índice em pausa. Você também pode retomar uma operação de recompilação de índice em pausa executando a instruçãoALTER INDEX RESUME
.Não há suporte para a opção
SORT_IN_TEMPDB = ON
no índice retomávelO comando DDL com
RESUMABLE = ON
não pode ser executado em uma transação explícita (não pode fazer parte do blocoBEGIN TRAN ... COMMIT
).Apenas operações de índice em pausa estão retomáveis.
Ao retomar uma operação de índice que está pausada, você pode alterar o
MAXDOP
valor para um novo valor. SeMAXDOP
não for especificado ao retomar uma operação de índice pausada, o últimoMAXDOP
valor será usado. SE a opção não for especificada para aMAXDOP
operação de recompilação de índice, o valor padrão será usado.Para pausar imediatamente a operação de índice, você pode interromper o comando em andamento (Ctrl-C) ou executar o comando
ALTER INDEX PAUSE
ou o comandoKILL <session_id>
. Depois que o comando é pausado, ele pode ser retomado usandoRESUME
a opção.O comando
ABORT
elimina a sessão que hospedava a recompilação de índice original e elimina a operação de índiceNão são necessários recursos extras para recompilação de índice retomáveis exceto para
- Espaço extra necessário para manter o índice sendo criado, incluindo o tempo em que o índice está sendo pausado
- Um estado DDL que impede qualquer modificação de DDL
A limpeza fantasma é executada durante a fase de pausa do índice, mas é pausada durante a execução do índice. A seguinte funcionalidade está desabilitada para operações de recompilação do índice retomáveis
- Não há suporte para recompilar um índice desabilitado com
RESUMABLE = ON
- Comando
ALTER INDEX REBUILD ALL
ALTER TABLE
usando recompilação de índice- O comando DDL com
RESUMABLE = ON
não pode ser executado em uma transação explícita (não pode fazer parte do blocoBEGIN TRAN ... COMMIT
) - Recrie um índice que tenha colunas ou
TIMESTAMP
computadas como colunas de chave.
- Não há suporte para recompilar um índice desabilitado com
Caso a tabela base contenha colunas LOB, a recompilação de índice clusterizado retomável requer um bloqueio Sch-M no Iniciando esta operação
Observação
O comando DDL é executado até ser concluído, pausar ou falhar. Caso o comando seja pausado, um erro será emitido indicando que a operação foi pausada e que a criação do índice não foi concluída. Para obter mais informações sobre o status atual do índice, veja sys.index_resumable_operations. Como antes, no caso de uma falha, um erro será emitido também.
WAIT_AT_LOW_PRIORITY com operações de índice online
Aplica-se a: SQL Server 2014 (12.x) e versões posteriores e Banco de Dados SQL do Azure
A sintaxe low_priority_lock_wait
permite especificar o comportamento WAIT_AT_LOW_PRIORITY
. WAIT_AT_LOW_PRIORITY
pode ser usado apenas com ONLINE = ON
.
Para executar a instrução DDL de uma recompilação de índice online, todas as transações de bloqueio ativas em execução em uma tabela específica devem ser concluídas. Quando a recompilação de índice online for executada, ela bloqueará todas as novas transações que estão prontas para iniciar a execução nessa tabela. Embora a duração do bloqueio para recompilação de índice online seja curta, aguardar a conclusão de todas as transações abertas em uma determinada tabela e bloquear o início das novas transações pode afetar significativamente a taxa de transferência, causando lentidão ou tempo limite da carga de trabalho e limitando significativamente o acesso à tabela subjacente.
A opção WAIT_AT_LOW_PRIORITY
permite que os DBAs gerenciem os bloqueios de Sch-S (Estabilidade de Esquema) e os bloqueios de Sch-M (Modificação de Esquema) necessários às recompilações de índice online e que eles selecionem uma das duas opções. Nos dois casos, se, durante o tempo de espera MAX_DURATION = n [minutes]
, não houver nenhuma atividade de bloqueio, a recompilação de índice online será executada imediatamente sem aguardar e a instrução DDL será concluída.
WAIT_AT_LOW_PRIORITY
indica que a operação de recompilação de índice online aguarda bloqueios de baixa prioridade, permitindo que outras operações prossigam enquanto a operação de compilação de índice online está aguardando. Omitir a opção WAIT AT LOW PRIORITY
é equivalente a WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE)
.
MAX_DURATION = tempo [ MINUTOS ]
O tempo de espera (um valor inteiro especificado em minutos) que a recompilação do índice online bloqueia espera com baixa prioridade ao executar o comando DDL. Se a operação for bloqueada por MAX_DURATION
um tempo, a ação especificada ABORT_AFTER_WAIT
será executada. MAX_DURATION
O tempo é sempre em minutos, e a palavra MINUTES
pode ser omitida.
ABORT_AFTER_WAIT = [ NENHUM | AUTO | BLOQUEADORES ]
NONE
Continue aguardando o bloqueio com prioridade normal.
SELF
Saia da operação DDL de recompilação de índice online em execução no momento sem realizar a ação. A opção
SELF
não pode ser usada com umMAX_DURATION
de 0.BLOCKERS
Elimine todas as transações de usuário que bloqueiam a operação DDL de recompilação de índice online para que a operação possa continuar. A opção
BLOCKERS
requer que o logon tenha a permissãoALTER ANY CONNECTION
.
Restrições em índices espaciais
Quando você recompila um índice espacial, a tabela de usuário subjacente não está disponível durante a operação do índice porque o índice espacial mantém um bloqueio de esquema.
A PRIMARY KEY
restrição na tabela de usuário não pode ser modificada enquanto um índice espacial é definido em uma coluna dessa tabela. Para alterar a PRIMARY KEY
restrição, primeiro descarte todos os índices espaciais da tabela. Depois de modificar a PRIMARY KEY
restrição, você pode recriar cada um dos índices espaciais.
Em uma só operação de recompilação de partição, não é possível especificar nenhum índice espacial. Entretanto, você pode especificar índices espaciais em uma recriação de partição completa.
Para alterar as opções específicas de um índice espacial, como BOUNDING_BOX
ou GRID
, você pode usar uma instrução CREATE SPATIAL INDEX
que especifica DROP_EXISTING = ON
ou remover o índice espacial e criar outro índice. Para obter um exemplo, consulte CREATE SPATIAL INDEX.
Compactação de dados
Para obter mais informações sobre compactação de dados, consulte Compactação de dados.
Para avaliar como a alteração PAGE
e ROW
a compactação afetam uma tabela, um índice ou uma partição, use o procedimento armazenado sp_estimate_data_compression_savings .
As restrições a seguir se aplicam a índices particionados:
- Quando você usar
ALTER INDEX ALL ...
, não será possível alterar a configuração de compactação de uma só partição se a tabela tiver índices não alinhados. - A sintaxe
ALTER INDEX <index> ... REBUILD PARTITION ...
recompila a partição especificada do índice. - A sintaxe
ALTER INDEX <index> ... REBUILD WITH ...
recompila todas as partições do índice.
Estatísticas
Quando você executa ALTER INDEX ALL ...
em uma tabela, somente as estatísticas associadas a índices são atualizadas. As estatísticas automáticas ou manuais criadas na tabela (em vez de um índice) não são atualizadas.
Permissões
Para executar ALTER INDEX
, no mínimo, a permissão ALTER
na tabela ou exibição é necessária.
Notas de versão
- O Banco de Dados SQL não usa opções de grupo de arquivos nem de fluxo de arquivos.
- Os índices columnstore não estão disponíveis antes do SQL Server 2012 (11.x).
- Operações de índice retomáveis estão disponíveis começando com SQL Server 2017 (14.x) e Banco de Dados SQL do Azure.
Exemplo de sintaxe básica
ALTER INDEX index1 ON table1 REBUILD;
ALTER INDEX ALL ON table1 REBUILD;
ALTER INDEX ALL ON dbo.table1 REBUILD;
Exemplos: índices columnstore
Estes exemplos se aplicam a índices columnstore.
a. Demonstração de REORGANIZE
Este exemplo demonstra como o comando ALTER INDEX REORGANIZE
funciona. Ele cria uma tabela que tem vários rowgroups e demonstra como REORGANIZE
mescla os rowgroups.
-- Create a database
CREATE DATABASE [columnstore];
GO
-- Create a rowstore staging table
CREATE TABLE [staging] (
AccountKey INT NOT NULL,
AccountDescription NVARCHAR(50),
AccountType NVARCHAR(50),
AccountCodeAlternateKey INT
);
-- Insert 10 million rows into the staging table.
DECLARE @loop INT;
DECLARE @AccountDescription VARCHAR(50);
DECLARE @AccountKey INT;
DECLARE @AccountType VARCHAR(50);
DECLARE @AccountCode INT;
SELECT @loop = 0
BEGIN TRANSACTION
WHILE (@loop < 300000)
BEGIN
SELECT @AccountKey = CAST(RAND() * 10000000 AS INT);
SELECT @AccountDescription = 'accountdesc ' + CONVERT(VARCHAR(20), @AccountKey);
SELECT @AccountType = 'AccountType ' + CONVERT(VARCHAR(20), @AccountKey);
SELECT @AccountCode = CAST(RAND() * 10000000 AS INT);
INSERT INTO staging
VALUES (
@AccountKey,
@AccountDescription,
@AccountType,
@AccountCode
);
SELECT @loop = @loop + 1;
END
COMMIT
-- Create a table for the clustered columnstore index
CREATE TABLE cci_target (
AccountKey INT NOT NULL,
AccountDescription NVARCHAR(50),
AccountType NVARCHAR(50),
AccountCodeAlternateKey INT
);
-- Convert the table to a clustered columnstore index named inxcci_cci_target;
CREATE CLUSTERED COLUMNSTORE INDEX idxcci_cci_target ON cci_target;
Use a opção TABLOCK para inserir linhas em paralelo. A partir do SQL Server 2016 (13.x), a operação pode ser executada INSERT INTO
em paralelo quando TABLOCK
usada.
INSERT INTO cci_target WITH (TABLOCK)
SELECT TOP 300000 * FROM staging;
Execute este comando para ver os OPEN
rowgroups delta. O número de rowgroups depende do grau de paralelismo.
SELECT *
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = object_id('cci_target');
Execute este comando para forçar todos e CLOSED
OPEN
rowgroups no columnstore.
ALTER INDEX idxcci_cci_target ON cci_target REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);
Execute esse comando novamente e você verá que rowgroups menores são mesclados em um rowgroup compactado.
ALTER INDEX idxcci_cci_target ON cci_target REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);
B. Compactar rowgroups delta CLOSED para o columnstore
Este exemplo usa a REORGANIZE
opção de compactar cada CLOSED
rowgroup delta no columnstore como um rowgroup compactado. Isso não é necessário, mas é útil quando o motor de tupla não está compactando CLOSED
rowgroups rápido o suficiente.
Você pode executar ambos os exemplos no banco de dados de exemplo AdventureWorksDW2022
.
Este exemplo é executado REORGANIZE
em todas as partições.
ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE;
Este exemplo é executado REORGANIZE
em uma partição específica.
-- REORGANIZE a specific partition
ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE PARTITION = 0;
C. Compactar todos os rowgroups delta OPEN AND CLOSED para o columnstore
Aplica-se a: SQL Server (Começando pelo SQL Server 2016 (13.x)) e Banco de Dados SQL do Azure
O comando REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON)
compacta cada OPEN
rowgroup delta no CLOSED
columnstore como um rowgroup compactado. Isso esvazia o deltastore e força todas as linhas a serem compactadas no columnstore. Isso é útil principalmente depois de executar várias operações de inserção, já que essas operações armazenam as linhas em um ou mais rowgroups delta.
REORGANIZE
Combina rowgroups para preencher rowgroups até um número máximo de linhas <= 1.024.576. Portanto, quando você compacta todos e OPEN
CLOSED
rowgroups, você não acaba com muitos rowgroups compactados que têm apenas algumas linhas. Você deseja que rowgroups sejam tão completos quanto possível para reduzir o tamanho compactado e melhorar o desempenho da consulta.
O exemplo a seguir usa o banco de dados AdventureWorksDW2022
.
Este exemplo move todos os OPEN
rowgroups e CLOSED
delta para o índice columnstore.
ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);
Este exemplo move todos os OPEN
rowgroups e CLOSED
delta para o índice columnstore de uma partição específica.
ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE PARTITION = 0 WITH (COMPRESS_ALL_ROW_GROUPS = ON);
D. Desfragmentar um índice columnstore online
Não se aplica a: SQL Server 2012 (11.x) e SQL Server 2014 (12.x).
A partir do SQL Server 2016 (13.x), REORGANIZE
faz mais do que compactar rowgroups delta no columnstore. Ele também executa a desfragmentação online. Primeiro, reduz o tamanho do columnstore removendo fisicamente linhas excluídas quando 10% ou mais linhas em um grupo de linhas foram excluídos. Em seguida, ele combina rowgroups para formar rowgroups maiores que tenham até o máximo de 1.024.576 linhas por rowgroup. Todos os rowgroups alterados são recompactados.
Observação
A partir do SQL Server 2016 (13.x), a recriação de um índice columnstore não é mais necessária na maioria das situações, pois REORGANIZE
remove fisicamente as linhas excluídas e mescla os rowgroups. A COMPRESS_ALL_ROW_GROUPS
opção força todos os OPEN
rowgroups ou CLOSED
delta no columnstore, o que anteriormente só poderia ser feito com uma recompilação. REORGANIZE
está online e ocorre em segundo plano para que as consultas possam continuar à medida que a operação acontece.
O exemplo a seguir executa um REORGANIZE
para desfragmentar o índice removendo fisicamente as linhas que foram excluídas logicamente da tabela e mesclando rowgroups.
ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE;
E. Recompilar um índice columnstore clusterizado offline
Aplica-se a: SQL Server (Começando pelo SQL Server 2012 (11.x))
Dica
Começando com SQL Server 2016 (13.x) e no Banco de Dados SQL do Azure, é recomendável usar ALTER INDEX REORGANIZE
em vez de ALTER INDEX REBUILD
para índices columnstore.
Observação
No SQL Server 2012 (11.x) e no SQL Server 2014 (12.x), REORGANIZE
é usado apenas para compactar CLOSED
rowgroups no columnstore. É a única maneira de realizar operações de desfragmentação e forçar todos os rowgroups delta para o columnstore é recompilar o índice.
Este exemplo mostra como recompilar um índice columnstore clusterizado e forçar todos os rowgroups delta para o columnstore. A primeira etapa prepara uma tabela FactInternetSales2
no banco de dados AdventureWorksDW2022
com um índice columnstore clusterizado e insere dados das quatro primeiras colunas.
CREATE TABLE dbo.FactInternetSales2 (
ProductKey [int] NOT NULL,
OrderDateKey [int] NOT NULL,
DueDateKey [int] NOT NULL,
ShipDateKey [int] NOT NULL);
CREATE CLUSTERED COLUMNSTORE INDEX cci_FactInternetSales2
ON dbo.FactInternetSales2;
INSERT INTO dbo.FactInternetSales2
SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey
FROM dbo.FactInternetSales;
SELECT * FROM sys.column_store_row_groups;
Os resultados mostram um OPEN
rowgroup, o que significa que o SQL Server aguarda a adição de mais linhas antes de fechar o rowgroup e mover os dados para o columnstore. A próxima instrução recompila o índice columnstore clusterizado, o que força todas as linhas para o columnstore.
ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REBUILD;
SELECT * FROM sys.column_store_row_groups;
Os resultados da SELECT
instrução mostram que o rowgroup é COMPRESSED
, o que significa que os segmentos de coluna do rowgroup agora estão compactados e armazenados no columnstore.
F. Recompilar uma partição de um índice columnstore clusterizado offline
Aplica-se a: SQL Server 2012 (11.x) e versões posteriores
Para recompilar uma partição de um índice columnstore clusterizado grande, use ALTER INDEX REBUILD
com a opção de partição. Este exemplo recompila a partição 12. Começando com o SQL Server 2016 (13.x), é recomendável substituir REBUILD
por REORGANIZE
.
ALTER INDEX cci_fact3
ON fact3
REBUILD PARTITION = 12;
G. Alterar um índice columnstore clusterizado para usar a compactação de arquivamento
Não se aplica a: SQL Server 2012 (11.x)
Você pode optar por reduzir ainda mais o tamanho de um índice columnstore clusterizado usando a opção de compactação de COLUMNSTORE_ARCHIVE
dados. Isso é prático para dados mais antigos que você deseja manter em um armazenamento mais econômico. Recomendamos usar isso apenas em dados que não são acessados com frequência, pois a descompactação é mais lenta do que com a compactação normal COLUMNSTORE
.
O exemplo a seguir recompila um índice columnstore clusterizado para usar a compactação de arquivamento e, em seguida, mostra como remover essa compactação. O resultado final usa apenas a compactação columnstore.
Primeiro, prepare o exemplo criando uma tabela com um índice columnstore clusterizado. Em seguida, compacte ainda mais a tabela usando a compactação de arquivamento.
--Prepare the example by creating a table with a clustered columnstore index.
CREATE TABLE SimpleTable (
ProductKey [int] NOT NULL,
OrderDateKey [int] NOT NULL,
DueDateKey [int] NOT NULL,
ShipDateKey [int] NOT NULL
);
CREATE CLUSTERED INDEX cci_SimpleTable ON SimpleTable (ProductKey);
CREATE CLUSTERED COLUMNSTORE INDEX cci_SimpleTable
ON SimpleTable
WITH (DROP_EXISTING = ON);
--Compress the table further by using archival compression.
ALTER INDEX cci_SimpleTable ON SimpleTable
REBUILD
WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);
GO
Este exemplo remove a compactação de arquivos e usa apenas a compactação columnstore.
ALTER INDEX cci_SimpleTable ON SimpleTable
REBUILD
WITH (DATA_COMPRESSION = COLUMNSTORE);
GO
Exemplos: Índices Rowstore
a. Recompilar um índice
O exemplo a seguir recompila um único índice na tabela Employee
do banco de dados AdventureWorks2022
.
ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee REBUILD;
B. Recompilar todos os índices em uma tabela e especificar opções
O exemplo a seguir especifica a palavra-chave ALL
. Isso recompila todos os índices associados à tabela Production.Product
no banco de dados AdventureWorks2022
. Três opções são especificadas.
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);
O exemplo a seguir adiciona a opção ONLINE que inclui a opção de bloqueio de baixa prioridade e adiciona a opção de compactação de linha.
Aplica-se a: SQL Server 2014 (12.x) e versões posteriores e Banco de Dados SQL do Azure
ALTER INDEX ALL ON Production.Product
REBUILD WITH
(
FILLFACTOR = 80,
SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON,
ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS ) ),
DATA_COMPRESSION = ROW
);
C. Reorganizar um índice com compactação LOB
O exemplo a seguir reorganiza um único índice clusterizado no banco de dados AdventureWorks2022
. Como o índice contém um tipo de dados LOB no nível folha, a instrução também compacta todas as páginas que contêm dados de objeto grande. Não é necessário especificar a opção WITH (LOB_COMPACTION = ON)
porque o valor padrão é ON.
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto REORGANIZE WITH (LOB_COMPACTION = ON);
D. Definir opções em um índice
O exemplo a seguir define várias opções no índice AK_SalesOrderHeader_SalesOrderNumber
no banco de dados AdventureWorks2022
.
ALTER INDEX AK_SalesOrderHeader_SalesOrderNumber ON
Sales.SalesOrderHeader
SET (
STATISTICS_NORECOMPUTE = ON,
IGNORE_DUP_KEY = ON,
ALLOW_PAGE_LOCKS = ON
) ;
GO
E. Desabilitar um índice
O exemplo a seguir desabilita um índice não clusterizado na tabela Employee
do banco de dados AdventureWorks2022
.
ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee DISABLE;
F. Desabilitar restrições
O exemplo a seguir desabilita uma PRIMARY KEY
restrição desabilitando o PRIMARY KEY
índice no AdventureWorks2022
banco de dados. A FOREIGN KEY
restrição na tabela subjacente é desabilitada automaticamente e a mensagem de aviso é exibida.
ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department DISABLE;
O conjunto de resultados retorna esta mensagem de aviso.
Warning: Foreign key 'FK_EmployeeDepartmentHistory_Department_DepartmentID'
on table 'EmployeeDepartmentHistory' referencing table 'Department'
was disabled as a result of disabling the index 'PK_Department_DepartmentID'.
G. Habilitar restrições
O exemplo a seguir habilita as PRIMARY KEY
restrições e FOREIGN KEY
que foram desabilitadas no Exemplo F.
A PRIMARY KEY
restrição é habilitada pela recriação do PRIMARY KEY
índice.
ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department REBUILD;
A FOREIGN KEY
restrição é então habilitada.
ALTER TABLE HumanResources.EmployeeDepartmentHistory
CHECK CONSTRAINT FK_EmployeeDepartmentHistory_Department_DepartmentID;
GO
H. Recompilar um índice particionado
O exemplo a seguir recompila uma única partição, número de partição 5
, do índice particionado IX_TransactionHistory_TransactionDate
do banco de dados AdventureWorks2022
. A partição 5 é recompilada com ONLINE=ON
e os dez minutos do tempo de espera para o bloqueio de baixa prioridade se aplica separadamente a cada bloqueio pela operação de recompilação de índice. Se durante esse tempo o bloqueio não puder ser obtido para a recompilação de índice completa, a instrução da operação de recompilação será anulada devido a ABORT_AFTER_WAIT = SELF
.
Aplica-se a: SQL Server 2014 (12.x) e versões posteriores e Banco de Dados SQL do Azure
-- Verify the partitioned indexes.
SELECT *
FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID(N'Production.TransactionHistory'), NULL , NULL, NULL);
GO
--Rebuild only partition 5.
ALTER INDEX IX_TransactionHistory_TransactionDate
ON Production.TransactionHistory
REBUILD Partition = 5
WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 minutes, ABORT_AFTER_WAIT = SELF)));
GO
I. Alterar a configuração de compactação de um índice
O exemplo a seguir recompila um índice em uma tabela rowstore não particionada.
ALTER INDEX IX_INDEX1
ON T1
REBUILD
WITH (DATA_COMPRESSION = PAGE);
GO
J. Alterar a configuração de um índice com a compactação XML
Aplicável a: SQL Server 2022 (16.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure.
O exemplo a seguir recompila um índice em uma tabela rowstore não particionada.
ALTER INDEX IX_INDEX1
ON T1
REBUILD
WITH (XML_COMPRESSION = ON);
GO
Para obter mais exemplos de compactação de dados, consulte Compactação de dados.
K. Recompilação de índice online retomável
Aplica-se a: SQL Server 2017 (14.x) e versões posteriores e Banco de Dados SQL do Azure
Os exemplos a seguir mostram como usar a recompilação de índice online de retomável.
Execute uma recompilação de índice online como operação retomável com MAXDOP = 1
. Executar o mesmo comando novamente depois que uma operação de índice foi pausada retoma automaticamente a operação de recompilação de índice.
ALTER INDEX test_idx on test_table REBUILD WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);
Execute uma recompilação de índice online como operação retomável definida como MAX_DURATION
240 minutos.
ALTER INDEX test_idx on test_table REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);
Pause uma recompilação de índice online retomável em execução.
ALTER INDEX test_idx on test_table PAUSE;
Retome uma recompilação de índice online para uma recompilação de índice que foi executada como operação retomável especificando um novo valor para MAXDOP
definido como 4.
ALTER INDEX test_idx on test_table RESUME WITH (MAXDOP = 4);
Retome uma operação de recompilação de índice online para uma recompilação de índice online executada como retomável. Defina MAXDOP
como 2, defina o tempo de execução do índice que está sendo executado como retomável para 240 minutos e, se um índice estiver sendo bloqueado no bloqueio, aguarde 10 minutos e depois disso elimine todos os bloqueadores.
ALTER INDEX test_idx on test_table
RESUME WITH (MAXDOP = 2, MAX_DURATION = 240 MINUTES,
WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10, ABORT_AFTER_WAIT = BLOCKERS));
Anular a operação de recompilação de índice retomável que está em execução ou pausada.
ALTER INDEX test_idx on test_table ABORT;
Conteúdo relacionado
- Guia de arquitetura e design de índices do SQL Server e do SQL do Azure
- Executar operações de índice online
- CREATE INDEX (Transact-SQL)
- CREATE SPATIAL INDEX (Transact-SQL)
- CREATE XML INDEX (Transact-SQL)
- DROP INDEX (Transact-SQL)
- Desabilitar índices e restrições
- Índices XML (SQL Server)
- Otimizar a manutenção do índice para melhorar o desempenho da consulta e reduzir o consumo de recursos
- sys.dm_db_index_physical_stats (Transact-SQL)
- EVENTDATA (Transact-SQL)