ALTER INDEX (Transact-SQL)

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsPDW (Analytics Platform System)Banco de Dados SQL no Microsoft Fabric

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

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.

TODOS

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 no SQL Server 2017 (14.x) e somente versões mais antigas. Versões posteriores dão suporte à recompilação online de índices columnstore.
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
ONLINE = ON	Índice XML Índice espacial Índice columnstore
RESUMABLE = ON	Índices retomáveis sem suporte com a ALL palavra-chave

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 mais informações sobre operações de índice 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 os detalhes do índice de uma tabela ou exibição, use a exibição do catálogo sys.indexes .

O Banco de Dados SQL do Azure dá suporte ao formato <database_name>.<schema_name>.<object_name> de nome de três partes quando <database_name> é o nome do banco de dados atual ou <database_name> é tempdb e <object_name> começa com # ou ##. Se o nome do esquema for dbo, <schema_name> poderá ser omitido.

RECONSTRUIR [ COM ( <rebuild_index_option> [ ,... n ] ) ]

Aplica-se a: SQL Server 2012 (11.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure

Especifica que o índice é recriado usando as mesmas colunas, tipo de índice, atributo de exclusividade e ordem de classificação. 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 em sys.indexes serão aplicados . Para qualquer opção de índice cujo valor não aparece sys.indexes, o padrão indicado na definição de argumento da opção se aplica.

Se ALL for especificado e a tabela subjacente for um heap, a operação de recompilação não terá efeito no heap. Quaisquer índices não clusterizados associados à tabela serão recriados.

A REBUILD operação poderá ser minimamente registrada se o modelo de recuperação de banco de dados for registrado em massa ou simples.

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 de recompilação:

• Compacta novamente todos os dados no columnstore. Existem duas cópias do índice columnstore enquanto a operação de recompilação está em andamento. Quando a recompilação for concluída, o Mecanismo de Banco de Dados excluirá o índice columnstore original.
• Não preserva a ordem de classificação, se houver. Para recompilar um índice columnstore e preservar ou introduzir uma ordem de classificação, use a CREATE [CLUSTERED] COLUMNSTORE INDEX ... ORDER (...) ... WITH (DROP_EXISTING = ON) instruçã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.

PARTIÇÃO

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 apenas índices alinhados quando o número de partições exceder 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, MAXDOPe DATA_COMPRESSIONXML_COMPRESSION são as opções que podem ser especificadas quando você recompila uma única partição usando a (PARTITION = partition_number) sintaxe. Índices XML não podem ser especificados em uma única operação de recompilação de partição.

DESABILITAR

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 bloqueio de longo prazo não são mantidos e consultas ou atualizações para os dados na tabela subjacente podem continuar durante a ALTER INDEX REORGANIZE transação.
• Não é permitida para um índice desabilitado.
• Não permitido quando ALLOW_PAGE_LOCKS está definido como OFF.
• 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.

REORGANIZAR WITH ( LOB_COMPACTION = { ON | OFF } )

Aplica-se a índices rowstore.

• ATIVADO

  • 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 a compactação LOB 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.

• DESLIGADO

  • Páginas que contêm dados de objeto grande não são compactadas.
  • OFF não tem nenhum efeito em um heap.

REORGANIZE um índice columnstore

Para índices columnstore, REORGANIZE compacta cada rowgroup delta fechado 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 mover os rowgroups delta fechados para rowgroups compactados. O processo de TM (tupla-mover em segundo plano) é ativado periodicamente para compactar os rowgroups delta fechados. Recomendamos usar REORGANIZE quando o movimentador de tupla estiver ficando para trás. REORGANIZE pode compactar rowgroups de forma mais agressiva.
• Para compactar todos os rowgroups abertos e fechados, consulte REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS).

Para índices columnstore no SQL Server 2016 (13.x) e versões posteriores, o Banco de Dados SQL do Azure e a Instância REORGANIZE Gerenciada de SQL do Azure executam as seguintes otimizações extras de desfragmentação online:

• Remove fisicamente linhas excluídas de um rowgroup quando 10% ou mais das linhas foram excluídas logicamente. Os bytes excluídos são recuperados na mídia física. Por exemplo, se um grupo de linhas compactadas de 1 milhão de linhas tiver 100.000 linhas excluídas, o Mecanismo de Banco de Dados removerá as linhas excluídas e recompactará o rowgroup com 900.000 linhas.

• 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, esses rowgroups são mesclados em um rowgroup compactado com 512.000 linhas. Isso pressupõe que não haja limitações de tamanho ou memória do dicionário.

• Para rowgroups nos quais 10% ou mais linhas foram excluídas logicamente, o Mecanismo de Banco de Dados 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 Mecanismo de Banco de Dados favorece a combinação desses dois rowgroups para compactar um novo rowgroup com 909.830 linhas.

REORGANIZAR WITH ( COMPRESS_ALL_ROW_GROUPS = { ON | OFF } )

Aplica-se em índices columnstore.

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure

COMPRESS_ALL_ROW_GROUPS fornece uma maneira de forçar rowgroups delta abertos ou fechados no columnstore. Com essa opção, não é necessário recompilar o índice columnstore para esvaziar os rowgroups delta. Combinado com os outros recursos de remoção e desfragmentação de mesclagem, isso não torna mais necessário recriar um índice columnstore na maioria das situações.

• ATIVADO

  Força todos os rowgroups para o columnstore, independentemente do tamanho e do estado (fechado ou aberto).

• DESLIGADO

  Força todos os rowgroups fechados para o 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 ] )

Modifica as 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 especificado pelo fator de preenchimento é aplicado às páginas de nível intermediário do índice. Se FILLFACTOR não for especificado ao mesmo tempo PAD_INDEXON, o valor do fator de preenchimento em sys.indexes será usado.

• DESLIGADO

  As páginas de nível intermediário são preenchidas até próximo de sua capacidade, deixando espaço suficiente para pelo menos uma linha do tamanho máximo que o índice pode ter, considerando o conjunto de chaves em páginas intermediárias. Isso também ocorre se PAD_INDEX estiver definido como ON , mas o fator de preenchimento não for especificado.

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 de um índice com FILLFACTOR menos de 100 aumenta a quantidade de espaço de armazenamento que os dados ocupam porque o Mecanismo de Banco de Dados redistribui os dados de acordo com o fator de preenchimento quando ele cria ou recria um índice.

SORT_IN_TEMPDB = { ON | OFF }

Especifica se a classificação temporária deve ser armazenada.tempdb O padrão é OFF exceto para a Hiperescala do Banco de Dados SQL do Azure. Para todas as operações de build de índice na Hiperescala, é sempreSORT_IN_TEMPDB, ON a menos que um build de índice retomável seja usado. Para builds de índice retomáveis, SORT_IN_TEMPDB é sempre OFF.

• ATIVADO

  Os resultados de classificação intermediários usados para criar o índice são armazenados em tempdb. Isso pode reduzir o tempo necessário para criar um índice. Entretanto, isso aumenta o espaço em disco usado durante a criação do índice.

• DESLIGADO

  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 SORT_IN_TEMPDB opção 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 não são inseridas.

• DESLIGADO

  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 a IGNORE_DUP_KEY configuração de um índice, use a ignore_dup_key coluna na exibição de catálogo 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 }

Desabilite ou habilite a opção de atualização automática de estatísticas para AUTO_STATISTICS_UPDATEas estatísticas no índice. O padrão é OFF.

• ATIVADO

  As atualizações automáticas de estatísticas são desabilitadas depois que o índice é recriado.

• DESLIGADO

  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_NORECOMPUTEOFFou execute UPDATE STATISTICS sem a NORECOMPUTE cláusula.

Aviso

Se você desabilitar a recomputação automática de estatísticas definindo STATISTICS_NORECOMPUTE = ON, poderá impedir que o otimizador de consulta escolha planos de execução ideais para consultas que envolvem a tabela.

STATISTICS_NORECOMPUTE A configuração ON não impede a atualização das estatísticas de índice que ocorrem durante a operação de recompilação de índice.

STATISTICS_INCREMENTAL = { ON | OFF }

Aplica-se a: SQL Server 2014 (12.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure

Quando ON, as estatísticas criadas no índice são por estatísticas de partição. Quando OFFas estatísticas existentes são descartadas e o Mecanismo de Banco de Dados recompila 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. Não há suporte para estatísticas incrementais nos seguintes casos:

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

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, somente um bloqueio compartilhado deIS intenção é mantido na tabela de origem. Ele permite o prosseguimento de consultas ou atualizações feitas na tabela e nos índices subjacentes. No início da operação, um bloqueio compartilhado (S) é mantido no objeto de origem por um curto período de tempo. No final da operação, por um curto período de tempo, um bloqueio compartilhado (S) será adquirido no objeto 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 como ON quando um índice está sendo criado em uma tabela temporária local.

  Observação

  Você pode usar a opção WAIT_AT_LOW_PRIORITY para reduzir ou evitar bloqueios durante operações de índice online. Para obter mais informações, consulte WAIT_AT_LOW_PRIORITY com operações de índice online.

• DESLIGADO

  Os bloqueios de tabela são aplicados enquanto durar a operação de índice. Uma operação de índice offline que cria, recompila ou descarta um índice clusterizado, espacial ou XML ou recria ou descarta um índice não clusterizado, adquire um bloqueio de modificação de esquema (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 inicialmente adquire um bloqueio compartilhado (S) na tabela. Isso impede modificações da definição da tabela subjacente, mas permite ler e modificar os dados na tabela enquanto o build do índice está em andamento.

Para obter mais informações, consulte Executar operações de índice online e diretrizes para 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 clusterizados desabilitados
• Índices columnstore clusterizados no SQL Server 2017 (14.x)) e versões anteriores
• Índices columnstore não clusterizados no SQL Server 2016 (13.x)) e versões anteriores
• Í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 uma chave de índice. No SQL Server (começando com o SQL Server 2012 (11.x)), no Banco de Dados SQL do Azure e na Instância Gerenciada de SQL do Azure, quando uma tabela contém colunas varchar(max) ou varbinary(max ), um índice clusterizado que contém outras colunas pode ser compilado ou recriado usando a opção ONLINE .

Para obter mais informações, consulte Como funcionam as operações de índice online.

RESUMABLE = { ON | OFF}

Aplica-se a: SQL Server 2017 (14.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure

Especifica se uma operação de índice online é retomável.

• ATIVADO

  A operação do índice é retomável.

• DESLIGADO

  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, Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure

Especifica por quanto tempo, em minutos inteiros, uma operação de índice retomável é executada antes de ser pausada.

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.

• DESLIGADO

  Bloqueios de linha não são usados.

PERMITIR_TRAVAS_DE_PÁGINA = { LIGADO | DESLIGADO }

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.

• DESLIGADO

  Bloqueios de página não serão usados.

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

aplica-se a: SQL Server 2019 (15.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure

Especifica se a opção deve ou não ser otimizada para evitar a contenção de inserção da última página. O padrão é OFF. Para obter mais informações, consulte Chaves sequenciais.

MAXDOP = max_degree_of_parallelism

Substitui a opção de configuração de grau máximo de paralelismo para a operação de índice. Para obter mais informações, veja Configurar a opção max degree of parallelism de configuração de servidor. Use MAXDOP para limitar o grau de paralelismo e o consumo de recursos resultante para uma operação de build de índice.

Embora a opção MAXDOP tenha suporte sintático para todos os índices XML e índices espaciais, ALTER INDEX atualmente usa apenas um único processador.

max_degree_of_parallelism pode ser:

• 1

  Suprime a geração de plano paralelo.

• >1

  Restringe o grau máximo de paralelismo usado em uma operação de índice paralelo ao número especificado ou menos com base na carga de trabalho atual do sistema.

• 0 (padrão)

  Usa o grau de paralelismo especificado no nível do servidor, do banco de dados ou do grupo de carga de trabalho, a menos que seja reduzido 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 com SQL Server 2016 (13.x)), Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure

Para uma tabela baseada em disco com um índice columnstore, especifica o número mínimo de minutos que um rowgroup delta no estado fechado deve permanecer no repositório delta antes que o Mecanismo de Banco de Dados possa compactá-lo em um rowgroup compactado. Como as tabelas baseadas em disco não acompanham os tempos de inserção e atualização em linhas individuais, o Mecanismo de Banco de Dados aplica esse atraso somente a rowgroups do repositório delta no estado fechado.

O padrão é 0 minuto.

Para obter recomendações sobre quando usar COMPRESSION_DELAY, consulte Introdução ao columnstore para análise operacional em tempo real.

COMPRESSÃO_DE_DADOS

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.

• LINHA

  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.

• PÁGINA

  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.

• armazenamento em colunas

  Aplica-se a: SQL Server 2014 (12.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure

  Aplica-se somente a índices columnstore, incluindo índices columnstore não clusterizados e clusterizados. Especificar COLUMNSTORE remove todas as outras compactações de dados, incluindo COLUMNSTORE_ARCHIVE.

• COLUMNSTORE_ARCHIVE

  Aplica-se a: SQL Server 2014 (12.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada de 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

aplica-se 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.

• DESLIGADO

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

RETOMAR

Aplica-se a: SQL Server 2017 (14.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure

Retoma uma operação de índice que é pausada manualmente, porque a duração máxima é atingida ou devido a uma falha.

• DURAÇÃO_MÁXIMA

  Especifica por quanto tempo, em minutos inteiros, uma operação de índice retomável é executada após ser retomada antes de ser pausada novamente.

• WAIT_AT_LOW_PRIORITY

  Retomar uma operação de build de índice após uma pausa precisa adquirir os bloqueios necessários. WAIT_AT_LOW_PRIORITY indica que a operação de build de índice adquire bloqueios de baixa prioridade, que permitem que outras operações prossigam enquanto a operação de build de índice está 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.

PAUSA

Aplica-se a: SQL Server 2017 (14.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure

Pausa uma operação de build de índice retomável.

ABORTAR

Aplica-se a: SQL Server 2017 (14.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure

Anula uma operação de build de índice em execução ou pausada que foi iniciada como retomável. Você deve executar explicitamente um ABORT comando para encerrar uma operação de build de índice retomável. Uma falha ou uma pausa em uma operação de índice retomável não encerra sua execução; em vez disso, 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. Por outro lado, ALTER INDEX REORGANIZE é uma única operação encadeada. Para obter mais informações, consulte Configurar operações de índice paralelo.

No banco de dados SQL no Microsoft Fabric, ALTER INDEX ALL não há suporte, mas ALTER INDEX <index name> é.

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 Desalocação adiada.

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 ao especificar ALL.

Para obter mais informações, consulte Otimizar a manutenção do índice para melhorar o desempenho da consulta e reduzir o consumo de recursos.

Observação

Para uma tabela com um índice columnstore ordenado, ALTER INDEX REORGANIZE não classifica novamente os dados. Para reclassificar os dados, use CREATE [CLUSTERED] COLUMNSTORE INDEX ... ORDER (...) ... WITH (DROP_EXISTING = ON).

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 ver se um índice está desabilitado, use a is_disabled coluna na exibição de sys.indexes catálogo.

Observação

A documentação usa o termo árvore B geralmente em referência a índices. Em índices de rowstore, o Database Engine implementa uma árvore B+. Isso não se aplica a índices columnstore ou índice em tabelas com otimização de 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, você não poderá desabilitar um índice associado a uma restrição de chave primária. Esses índices são necessários para a replicação. Para desabilitar esse índice, primeiro você deve remover 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_KEYe 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, 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	Aplica-se a
ALLOW_ROW_LOCKS = ON ou OFF	O heap e todos os índices não clusterizados associados.
ALLOW_PAGE_LOCKS = ON	O heap e todos os índices não clusterizados associados.
ALLOW_PAGE_LOCKS = OFF	Os índices não clusterizados, em que todos os bloqueios de página não são permitidos. Para o heap, somente os bloqueios de página compartilhados (S), atualizados (U) e exclusivos (X) não são permitidos. O Mecanismo de Banco de Dados ainda pode adquirir bloqueios de página de intenção (ISou IUIX) para fins internos.

Aviso

Não é recomendável desabilitar bloqueios de linha ou página em um índice. Podem ocorrer problemas relacionados à simultaneidade e determinadas funcionalidades podem estar indisponíveis. Por exemplo, um índice não pode ser reorganizado quando ALLOW_PAGE_LOCKS é definido como OFF.

Operações de índice online

Ao recriar um índice e a opção ONLINE é definida como ON, os dados no índice, sua tabela associada e outros índices na mesma tabela estão disponíveis para consultas e modificação. Você também pode recriar online uma parte de um índice que reside em uma única partição. Os bloqueios de tabela exclusivos são mantidos apenas por um curto período de tempo no final da recompilação do índice.

A reorganização de um índice sempre é executada online. O processo mantém bloqueios apenas por curtos períodos de tempo e é improvável que bloqueie consultas ou atualizações.

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, Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure

Você pode tornar uma recompilação de índice online retomável. Isso significa que a recompilação do índice pode ser interrompida e reiniciada posteriormente a partir do ponto em que parou. Para executar uma recompilação de índice como retomável, especifique a opção RESUMABLE = ON .

As diretrizes a seguir se aplicam a operações de índice retomáveis:

• Para usar a opção RESUMABLE , você também deve usar a opção ONLINE .
• A RESUMABLE opção não é mantida nos metadados de um determinado índice e se aplica apenas à duração da instrução DDL atual. Portanto, a cláusula RESUMABLE = ON deve ser especificada explicitamente para habilitar a capacidade de retomada.
• A MAX_DURATION opção pode ser especificada em dois contextos:
  • MAX_DURATION para a opção RESUMABLE especifica o intervalo de tempo para um índice que está sendo criado. Após esse tempo decorrido, e se o build do índice ainda estiver em execução, ele será pausado. Você decide quando o build de um índice pausado pode ser retomado. O tempo em minutos deve MAX_DURATION ser maior que 0 minutos e menor ou igual a uma semana (7 * 24 * 60 = 10080 minutos). Uma longa pausa em uma operação de índice pode afetar visivelmente o desempenho DML em uma tabela específica, bem como a capacidade do disco de banco de dados, pois tanto o índice original quanto o índice recém-criado exigem espaço em disco e precisam ser atualizados pelas operações DML. Se MAX_DURATION a opção for omitida, a operação de índice continuará até a conclusão ou até que ocorra uma falha.
  • MAX_DURATION para a opção WAIT_AT_LOW_PRIORITY especifica o tempo de espera usando bloqueios de baixa prioridade se a operação de índice estiver bloqueada, antes de tomar medidas. Para obter mais informações, consulte WAIT_AT_LOW_PRIORITY com operações de índice online.
• Para pausar a operação de índice imediatamente, você pode executar o ALTER INDEX PAUSE comando ou executar o KILL <session_id> comando.
• 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ção ALTER INDEX RESUME.
• O ABORT comando elimina a sessão que está executando um build de índice e cancela a operação de índice. Você não pode retomar uma operação de índice que foi anulada.
• Ao retomar uma operação de recompilação de índice pausada, você pode alterar o MAXDOP valor para um novo valor. Se MAXDOP não for especificado ao retomar uma operação de índice pausada, o MAXDOP valor usado para o último currículo será usado. Se a opção MAXDOP não for especificada para uma operação de recompilação de índice, o valor padrão será usado.

Uma operação de índice retomável é executada até concluir, pausar ou falhar. Caso a operação seja pausada, um erro será emitido indicando que a operação foi pausada e que a recompilação do índice não foi concluída. Caso a operação falhe, um erro também será emitido.

Para ver se uma operação de índice é executada como uma operação retomável e verificar seu estado de execução atual, use a exibição sys.index_resumable_operations catálogo.

Recursos

Os seguintes recursos são necessários para operações de índice retomáveis:

• Espaço adicional necessário para manter o índice sendo criado, incluindo a hora em que o build é pausado.
• Taxa de transferência de log adicional durante a fase de classificação. O uso geral do espaço de log para índice retomável é menor em comparação com a recompilação de índice online regular e permite truncamento de log durante essa operação.
• Instruções DDL que tentam modificar um índice que está sendo recriado ou sua tabela associada enquanto a operação de índice está pausada não são permitidas.
• A limpeza de fantasma está bloqueada no índice no build pela duração da operação, em pausa e enquanto a operação está em execução.
• Se a tabela contiver colunas LOB, um build de índice clusterizado retomável exigirá um bloqueio de modificação de esquema (Sch-M) no início da operação.

Limitações funcionais atuais

As operações de recompilação de índice retomável têm as seguintes limitações:

• A SORT_IN_TEMPDB = ON opção não tem suporte para operações de índice retomáveis.
• O comando DDL com RESUMABLE = ON não pode ser executado dentro de uma transação explícita.
• Você não pode criar um índice retomável que contenha:
  • Colunas derowversion computadas ou de / de data/hora como colunas de chave.
  • Coluna LOB como uma coluna incluída.
• Não há suporte para operações de índice retomáveis para:
  • O comando ALTER INDEX REBUILD ALL
  • O comando ALTER TABLE REBUILD
  • Índices columnstore
  • Índices filtrados
  • Índices desabilitados

WAIT_AT_LOW_PRIORITY com operações de índice online

Aplica-se a: SQL Server 2014 (12.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure

Quando você não usa a opção WAIT_AT_LOW_PRIORITY , todas as transações de bloqueio ativas que contêm bloqueios na tabela ou índice devem ser concluídas para que a operação de recompilação de índice seja iniciada e concluída. Quando a operação de índice online é iniciada e antes de ser concluída, ela precisa adquirir um bloqueio compartilhado (S) ou uma modificação de esquema (Sch-M) na tabela e mantê-la por um curto período de tempo. Embora o bloqueio seja mantido apenas por um curto período de tempo, ele pode afetar significativamente a taxa de transferência da carga de trabalho, aumentar a latência da consulta ou causar tempo limite de execução.

Para evitar esses problemas, a opção WAIT_AT_LOW_PRIORITY permite que você gerencie o comportamento ou SSch-M bloqueios necessários para que uma operação de índice online seja iniciada e concluída, selecionando entre três opções. Em todos os casos, se durante o tempo de espera especificado por MAX_DURATION = n [minutes] não houver bloqueio que envolva a operação de índice, a operação de índice continuará imediatamente.

WAIT_AT_LOW_PRIORITY faz com que a operação de índice online aguarde usando bloqueios de baixa prioridade, permitindo que outras operações usando bloqueios de prioridade normais prossigam enquanto isso. Omitir a opção WAIT_AT_LOW_PRIORITY é equivalente a WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = time [MINUTES]

O tempo de espera (um valor inteiro especificado em minutos) que a operação de índice online aguarda usando bloqueios de baixa prioridade. 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= [NONE | SELF | BLOCKERS ]

• NONE: continue aguardando o bloqueio com prioridade normal.
• SELF: saia da operação de índice online que está sendo executada no momento, sem executar nenhuma ação. A opção SELF não pode ser usada quando MAX_DURATION é 0.
• BLOCKERS: encerre todas as transações de usuário que bloqueiam a operação de índice online para que a operação possa continuar. A BLOCKERS opção exige que a entidade de segurança que executa a CREATE INDEX instrução ou ALTER INDEX a instrução tenha a ALTER ANY CONNECTION permissão.

Você pode usar os seguintes eventos estendidos para monitorar as operações de índice que esperam por bloqueios de baixa prioridade:

• lock_request_priority_state
• process_killed_by_abort_blockers
• ddl_with_wait_at_low_priority

Restrições em índices espaciais

Quando você recompila um índice espacial, a tabela de usuários subjacente fica indisponível durante a operação de índice.

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. No entanto, você pode especificar índices espaciais em uma recompilação de tabela.

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.

Veja a seguir os principais pontos a serem considerados no contexto das operações de build de índice quando a compactação de dados é usada:

• A compactação pode permitir que mais linhas sejam armazenadas em uma página, mas não altera o tamanho máximo da linha.
• Páginas não folha de um índice não são compactadas por página, mas podem ser compactadas por linha.
• Cada índice não clusterizado tem uma configuração de compactação individual e não herda a configuração de compactação da tabela subjacente.
• Quando um índice clusterizado é criado em um heap, ele herda o estado de compactação do heap, a menos que um estado de compactação alternativo seja especificado.

As seguintes considerações aplicam a recriação de índices particionados:

• Não será possível alterar a configuração de compactação de uma única partição se a tabela tiver índices não alinhados.
• A ALTER INDEX <index> ... REBUILD PARTITION ... WITH DATA_COMPRESSION = ... sintaxe recria a partição especificada do índice com a opção de compactação especificada. Se a WITH DATA_COMPRESSION cláusula for omitida, a opção de compactação existente será usada.
• A ALTER INDEX <index> ... REBUILD PARTITION = ALL sintaxe recria todas as partições do índice usando as opções de compactação existentes.
• A sintaxe ALTER INDEX <index> ... REBUILD PARTITION = ALL (WITH ...) recompila todas as partições do índice. Você pode escolher compactação diferente para partições diferentes usando a DATA_COMPRESSION = ... ON PARTITIONS ( ...) cláusula.

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 .

Estatísticas

Quando você recompila um índice, as estatísticas no índice são atualizadas com verificação completa de índices não particionados e com a taxa de amostragem padrão para índices particionados. Nenhuma outra estatística na tabela é atualizada como parte da recompilação de índice.

Permissões

A ALTER permissão na tabela ou exibição é necessária.

Notas de versão

• O Banco de Dados SQL do Azure não dá suporte a grupos de arquivos que PRIMARYnão sejam .
• O Banco de Dados SQL do Azure e a Instância Gerenciada de SQL do Azure não dão suporte FILESTREAM a opções.
• Os índices columnstore não estão disponíveis antes do SQL Server 2012 (11.x).
• As operações de índice retomáveis estão disponíveis no SQL Server 2017 (14.x) e versões posteriores, no Banco de Dados SQL do Azure e na Instância Gerenciada de 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 CLOSEDOPEN 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 2016 (13.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada de 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 OPENCLOSED 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, Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure

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, Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure

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, Banco de Dados SQL do Azure e Instância Gerenciada de 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, Banco de Dados SQL do Azure e Instância Gerenciada de 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

Eu. 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, Banco de Dados SQL do Azure e Instância Gerenciada de 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)