Partilhar via

Otimize a manutenção do índice para melhorar o desempenho da consulta e reduzir o consumo de recursos

  • Artigo

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada SQL do AzureSistema de Plataforma de Análise (PDW)Banco de Dados SQL no Microsoft Fabric

Este artigo ajuda você a decidir quando e como executar a manutenção do índice. Ele aborda conceitos como fragmentação de índice e densidade de página, e seu impacto no desempenho da consulta e no consumo de recursos. Ele descreve métodos de manutenção de índice, reorganizar uma de índice e reconstruir umade índice e sugere uma estratégia de manutenção de índice que equilibra possíveis melhorias de desempenho em relação ao consumo de recursos necessários para manutenção.

Observação

Este artigo não se aplica a um pool SQL dedicado no Azure Synapse Analytics. Para obter informações sobre a manutenção de índices para tabelas de pool SQL dedicados no Azure Synapse Analytics, consulte Indexação de tabelas de pool SQL dedicado no Azure Synapse Analytics.

Conceitos: fragmentação do índice e densidade de páginas

Qual é o índice de fragmentação e e como afecta o desempenho:

  • Nos índices de árvore B (rowstore), a fragmentação existe quando os índices têm páginas nas quais a ordem lógica dentro do índice, com base nos valores-chave do índice, não corresponde à ordenação física das páginas de índice.

    Observação

    A documentação usa o termo árvore B geralmente em referência a índices. Em índices rowstore, o Mecanismo de Banco de Dados implementa uma árvore B+. Isso não se aplica a índices columnstore ou índices em tabelas com otimização de memória. Para obter mais informações, consulte o SQL Server e o guia de design de arquitetura e design de índice SQL do Azure.

  • O Mecanismo de Banco de Dados modifica automaticamente os índices sempre que operações de inserção, atualização ou exclusão são feitas nos dados subjacentes. Por exemplo, a adição de linhas numa tabela pode fazer com que páginas existentes nos índices de rowstore se dividam, criando espaço para a inserção de novas linhas. Com o tempo, essas modificações podem fazer com que os dados no índice fiquem espalhados no banco de dados (fragmentados).

  • Para consultas que leem muitas páginas usando varrimentos de índice completos ou de intervalo, índices muito fragmentados podem degradar o desempenho das consultas quando são necessárias E/S adicionais para ler os dados. Em vez de um pequeno número de solicitações de E/S grandes, a consulta exigiria um número maior de solicitações de E/S pequenas para ler a mesma quantidade de dados.

  • Quando o subsistema de armazenamento oferece melhor desempenho de E/S sequencial do que desempenho de E/S aleatórias, a fragmentação do índice pode degradar o desempenho porque mais E/S aleatórias são necessárias para ler índices fragmentados.

O que é densidade de página (também conhecida como plenitude de página) e como isso afeta o desempenho:

  • Cada página no banco de dados pode conter um número variável de linhas. Se as linhas ocuparem todo o espaço de uma página, a densidade da página será de 100%. Se uma página estiver vazia, a densidade da página será de 0%. Se uma página com densidade de 100% for dividida em duas páginas para acomodar uma nova linha, a densidade das duas novas páginas será de aproximadamente 50%.
  • Quando a densidade de páginas é baixa, mais páginas são necessárias para armazenar a mesma quantidade de dados. Isso significa que mais E/S é necessária para ler e gravar esses dados, e mais memória é necessária para armazenar esses dados em cache. Quando a memória é limitada, menos páginas exigidas por uma consulta são armazenadas em cache, causando ainda mais E/S de disco. Consequentemente, a baixa densidade de páginas afeta negativamente o desempenho.
  • Quando o Mecanismo de Banco de Dados adiciona linhas a uma página durante a criação, reconstrução ou reorganização do índice, ele não preencherá a página totalmente se o fator de preenchimento para o índice for definido como um valor diferente de 100 (ou 0, que é equivalente neste contexto). Isso causa menor densidade de página e, da mesma forma, adiciona sobrecarga de E/S e afeta negativamente o desempenho.
  • A baixa densidade de páginas pode aumentar o número de níveis intermediários numa árvore B. Isso aumenta moderadamente o custo de CPU e de operações de entrada/saída para encontrar páginas de nível folha em verificações e buscas no índice.
  • Quando o Otimizador de Consultas compila um plano de consulta, ele considera o custo de E/S necessário para ler os dados exigidos pela consulta. Com baixa densidade de páginas, há mais páginas para ler, portanto, o custo de E/S é maior. Isso pode afetar a escolha do plano de consulta. Por exemplo, à medida que a densidade da página diminui ao longo do tempo devido a divisões de página, o otimizador pode compilar um plano diferente para a mesma consulta, com um perfil de desempenho e consumo de recursos diferente.

Dica

Em muitas cargas de trabalho, aumentar a densidade da página resulta em um impacto positivo maior no desempenho do que reduzir a fragmentação.

Para evitar reduzir a densidade de página desnecessariamente, a Microsoft não recomenda definir o fator de preenchimento para valores diferentes de 100 ou 0, exceto em certos casos para índices que experimentam um alto número de divisões de página, por exemplo, índices modificados com freqüência com colunas à esquerda contendo valores GUID não sequenciais.

Meça a fragmentação do índice e a densidade da página

Tanto a fragmentação quanto a densidade de página estão entre os fatores a serem considerados ao decidir se a manutenção do índice deve ser executada e qual método de manutenção usar.

A fragmentação é definida de forma diferente para índices de rowstore e de columnstore . Para índices de armazenamento de linhas, sys.dm_db_index_physical_stats() permite determinar a fragmentação e a densidade de páginas em um índice específico, todos os índices em uma tabela ou exibição indexada, todos os índices em um banco de dados ou todos os índices em todos os bancos de dados. Para índices particionados, sys.dm_db_index_physical_stats() fornece essas informações para cada partição.

O conjunto de resultados retornado por sys.dm_db_index_physical_stats inclui as seguintes colunas:

Coluna Descrição
avg_fragmentation_in_percent Fragmentação lógica (páginas fora de ordem no índice).
avg_page_space_used_in_percent Densidade média de páginas.

Para grupos de linhas compactadas em índices columnstore, a fragmentação é definida como a proporção de linhas excluídas em relação ao total de linhas, expressa como uma porcentagem. sys.dm_db_column_store_row_group_physical_stats permite determinar o número de linhas totais e excluídas por grupo de linhas em um índice específico, todos os índices em uma tabela ou todos os índices em um banco de dados.

O conjunto de resultados retornado por sys.dm_db_column_store_row_group_physical_stats inclui as seguintes colunas:

Coluna Descrição
total_rows Número de linhas fisicamente armazenadas no grupo de linhas. Para grupos de linhas compactadas, isso inclui as linhas marcadas como excluídas.
deleted_rows Número de linhas fisicamente armazenadas em um grupo de linhas compactadas que estão marcadas para exclusão. 0 para grupos de linhas que estão no repositório delta.

A fragmentação do grupo de linhas compactado num índice de armazenamento por colunas pode ser calculada usando esta fórmula:

100.0*(ISNULL(deleted_rows,0))/NULLIF(total_rows,0)

Dica

Para os índices rowstore e columnstore, reveja a fragmentação do índice ou do heap e a densidade da página depois de um grande número de linhas ter sido excluído ou atualizado. No caso de heaps, se houver atualizações frequentes, revise a fragmentação periodicamente para evitar a proliferação de registos de reencaminhamento. Para obter mais informações sobre heaps, consulte Heaps (Tabelas sem Índices Agrupados).

Consulte os Exemplos para obter consultas que ajudem a determinar a fragmentação e a densidade das páginas.

Métodos de manutenção do índice: reorganizar e reconstruir

Você pode reduzir a fragmentação do índice e aumentar a densidade da página usando um dos seguintes métodos:

  • Reorganizar um índice
  • Reconstruir um índice

Observação

Para índices particionados de, é possível utilizar um dos seguintes métodos em todas as partições ou numa única partição de um índice.

Reorganizar um índice

Reorganizar um índice consome menos recursos do que reconstruir um índice. Por esse motivo, deve ser o seu método de manutenção de índice preferido, a menos que haja um motivo específico para usar a reconstrução de índice. Reorganizar é sempre uma operação online. Isso significa que os bloqueios de longo prazo no nível do objeto não são mantidos e as consultas ou atualizações da tabela subjacente podem continuar durante a operação ALTER INDEX ... REORGANIZE.

  • Para índices de armazenamento de linha, o Mecanismo de Banco de Dados desfragmenta apenas o nível de folha de índices agrupados e não agrupados em tabelas e exibições reordenando fisicamente as páginas de nível de folha para corresponder à ordem lógica dos nós folha (da esquerda para a direita). A reorganização também compacta as páginas de índice para tornar a densidade da página igual ao fator de preenchimento do índice. Para exibir a configuração do fator de preenchimento, use sys.indexes. Para obter exemplos de sintaxe, consulte Exemplos - Reorganização do Rowstore.
  • Ao usar índices columnstore, o repositório delta pode acabar com vários pequenos grupos de linhas depois de inserir, atualizar e excluir dados ao longo do tempo. A reorganização de um índice columnstore força grupos de linhas de armazenamento delta em grupos de linhas compactadas no columnstore e combina os grupos de linhas compactadas menores em grupos de linhas maiores. A operação de reorganização também remove fisicamente as linhas que foram marcadas como excluídas no columnstore. Reorganizar um índice columnstore pode exigir recursos adicionais da CPU para compactar dados. Enquanto a operação está em execução, o desempenho pode diminuir. No entanto, depois que os dados são compactados, o desempenho da consulta melhora. Para obter exemplos de sintaxe, consulte Exemplos - Columnstore reorganize.

Observação

A partir do SQL Server 2019 (15.x), da Base de Dados SQL do Azure e da Instância Gerida SQL do Azure, o tuple-mover é auxiliado por uma tarefa de mesclagem em segundo plano que compacta automaticamente grupos de linhas delta abertos menores que existem há algum tempo, conforme determinado por um limite interno, ou mescla grupos de linhas compactados de onde foi excluído um grande número de linhas. Isso melhora a qualidade do índice columnstore ao longo do tempo. Na maioria dos casos, isso dispensa a necessidade de emitir comandos ALTER INDEX ... REORGANIZE.

Dica

Se você cancelar uma operação de reorganização, ou se ela for interrompida de outra forma, o progresso feito até esse ponto será mantido no banco de dados. Para reorganizar grandes índices, a operação pode ser iniciada e interrompida várias vezes até ser concluída.

Reconstruir um índice

A reconstrução de um índice elimina e recria o índice. Dependendo do tipo de índice e da versão do Mecanismo de Banco de Dados, uma operação de reconstrução pode ser feita offline ou online. Uma reconstrução de índice offline geralmente leva menos tempo do que uma reconstrução online, mas mantém bloqueios no nível do objeto durante a operação de reconstrução, impedindo que as consultas acessem a tabela ou exibição.

Uma reconstrução de índice online não requer bloqueios no nível do objeto até o final da operação, quando um bloqueio deve ser mantido por um curto período para concluir a reconstrução. Dependendo da versão do Mecanismo de Banco de Dados, uma reconstrução de índice online pode ser iniciada como uma operação retomável. Uma reconstrução de índice retomável pode ser pausada, mantendo o progresso feito até esse ponto. Uma operação de reconstrução retomada pode ser retomada depois de ter sido pausada ou interrompida, ou abortada se a conclusão da reconstrução se tornar desnecessária.

Para a sintaxe Transact-SQL, consulte ALTER INDEX REBUILD. Para obter mais informações sobre reconstruções de índice online, consulte Executar operações de índice online.

Observação

Enquanto um índice está sendo reconstruído online, cada modificação de dados em colunas indexadas deve atualizar uma cópia adicional do índice. Isso pode resultar em uma pequena degradação do desempenho das instruções de modificação de dados durante a reconstrução on-line.

Se uma operação de índice retomável online for pausada, esse impacto no desempenho persistirá até que a operação retomável seja concluída ou abortada. Se você não pretende concluir uma operação de índice retomável, cancele-a em vez de pausá-la.

Dica

Dependendo dos recursos disponíveis e dos padrões de carga de trabalho, especificar um valor superior ao padrão de MAXDOP na instrução ALTER INDEX REBUILD pode reduzir a duração da reconstrução, resultando em uma maior utilização da CPU.

  • Para os índices rowstore , a reconstrução remove a fragmentação em todos os níveis do índice e compacta as páginas com base no fator de preenchimento especificado ou atual. Quando ALL é especificado, todos os índices na tabela são descartados e reconstruídos em uma única operação. Quando índices com 128 ou mais extensões são reconstruídos, o Mecanismo de Banco de Dados adia as desalocações de páginas e a aquisição dos bloqueios associados até a conclusão da reconstrução. Para obter exemplos de sintaxe, consulte Exemplos - Rowstore rebuild.

  • Para índices columnstore, a reconstrução remove a fragmentação, move todas as linhas do repositório delta para columnstore e exclui fisicamente as linhas que foram marcadas para exclusão. Para obter exemplos de sintaxe, consulte Exemplos - Columnstore rebuild.

    Dica

    A partir do SQL Server 2016 (13.x), a reconstrução do índice columnstore geralmente não é necessária, pois o REORGANIZE realiza as tarefas essenciais de uma reconstrução como uma operação online.

Utilize a reconstrução do índice para se recuperar de corrupção nos dados

Antes do SQL Server 2008 (10.0.x), às vezes era possível reconstruir um índice não clusterizado de armazenamento de linha para corrigir inconsistências devido a corrupção de dados no índice.

Você ainda pode reparar essas inconsistências no índice não clusterizado reconstruindo um índice não clusterizado offline. No entanto, não é possível reparar inconsistências de índice não clusterizadas reconstruindo o índice online, porque o mecanismo de reconstrução online usa o índice não clusterizado existente como base para a reconstrução e, portanto, carrega a inconsistência. Reconstruir o índice offline às vezes pode forçar uma verificação do índice clusterizado (ou heap) e, assim, substituir os dados inconsistentes no índice não clusterizado pelos dados do índice clusterizado ou heap.

Para garantir que o índice clusterizado ou heap seja usado como fonte de dados, elimine e recrie o índice não clusterizado em vez de reconstruí-lo. Como nas versões anteriores, você pode se recuperar de inconsistências restaurando os dados afetados de um backup. No entanto, você poderá reparar inconsistências de índice não clusterizadas reconstruindo-o offline ou recriando-o. Para obter mais informações, consulte DBCC CHECKDB (Transact-SQL).

Gestão automática de índices e estatísticas

Use soluções como Adaptive Index Defrag para gerenciar automaticamente a fragmentação do índice e as atualizações de estatísticas para um ou mais bancos de dados. Este procedimento escolhe automaticamente se deseja reconstruir ou reorganizar um índice de acordo com seu nível de fragmentação, entre outros parâmetros, e atualizar as estatísticas com um limite linear.

Considerações específicas para reconstruir e reorganizar índices de armazenamento em linha

Os seguintes cenários levam a que todos os índices não clusterizados baseados em rowstore numa tabela sejam recriados automaticamente:

  • Criando um índice clusterizado em uma tabela, incluindo a recriação do índice clusterizado com uma chave diferente usando CREATE CLUSTERED INDEX ... WITH (DROP_EXISTING = ON)
  • Remover um índice clusterizado, o que faz com que a tabela fique armazenada como uma pilha

Os cenários a seguir não recriam automaticamente todos os índices não clusterizados de armazenamento de linha na mesma tabela:

  • Reconstruindo um índice clusterizado
  • Alterar o armazenamento de índice clusterizado, como aplicar um esquema de particionamento ou mover o índice clusterizado para um grupo de arquivos diferente

Importante

Um índice não pode ser reorganizado ou reconstruído se o grupo de arquivos no qual ele está localizado estiver offline ou somente leitura. Quando a palavra-chave ALL é especificada e um ou mais índices estão em um grupo de arquivos offline ou somente leitura, a instrução falha.

Enquanto ocorre uma reconstrução de índice, a mídia física deve ter espaço suficiente para armazenar duas cópias do índice. Quando a reconstrução estiver concluída, o Mecanismo de Banco de Dados excluirá o índice original.

Quando ALL é especificado com a instrução ALTER INDEX ... REORGANIZE, os índices agrupados, não agrupados e XML da tabela são reorganizados.

Reconstruir ou reorganizar pequenos índices de linhas geralmente não reduz a fragmentação. Até e incluindo o SQL Server 2014 (12.x), o Mecanismo de Banco de Dados do SQL Server aloca espaço usando extensões mistas. Portanto, páginas de pequenos índices às vezes são armazenadas em extensões mistas, o que implicitamente torna tais índices fragmentados. Extensões mistas são compartilhadas por até oito objetos, de modo que a fragmentação em um pequeno índice pode não ser reduzida depois de reorganizá-lo ou reconstruí-lo.

Considerações específicas sobre a reconstrução de um índice columnstore

Ao reconstruir um índice columnstore, o Mecanismo de Banco de Dados lê todos os dados do índice columnstore original, incluindo o repositório delta. Ele combina dados em novos grupos de linhas e compacta todos os grupos de linhas em columnstore. O Mecanismo de Banco de Dados desfragmenta o columnstore excluindo fisicamente as linhas que foram marcadas como excluídas.

Observação

A partir do SQL Server 2019 (15.x), o movimentador de tuplas é ajudado por uma tarefa de mesclagem em segundo plano que compacta automaticamente grupos de linhas de armazenamento delta abertos menores que existem há algum tempo, de acordo com um limite interno, ou mescla grupos de linhas compactadas quando um grande número de linhas é excluído. Isso melhora a qualidade do índice columnstore ao longo do tempo. Para obter mais informações sobre termos e conceitos columnstore, consulte Columnstore indexes: Overview.

Reconstruir uma partição em vez da tabela inteira

A reconstrução de toda a tabela leva muito tempo se o índice for grande e requer espaço em disco suficiente para armazenar uma cópia adicional de todo o índice durante a reconstrução.

Para tabelas particionadas, não é necessário reconstruir todo o índice de armazenamento em colunas se a fragmentação estiver presente apenas em algumas partições, por exemplo, em partições onde as instruções UPDATE, DELETEou MERGE afetaram um grande número de linhas.

Reconstruir uma partição depois de carregar ou modificar dados garante que todos os dados sejam armazenados em grupos de linhas compactadas no columnstore. Quando o processo de carregamento de dados insere dados em uma partição usando lotes menores que 102.400 linhas, a partição pode acabar com vários grupos de linhas abertas no armazenamento delta. A reconstrução move todas as linhas de armazenamento delta para grupos de linhas compactadas em columnstore.

Considerações específicas acerca da reorganização de um índice columnstore

Ao reorganizar um índice columnstore, o Mecanismo de Banco de Dados compacta cada grupo de linhas fechado no repositório delta em columnstore como um grupo de linhas compactadas. A partir do SQL Server 2016 (13.x) e no Banco de Dados SQL do Azure, o comando REORGANIZE executa as seguintes otimizações de desfragmentação adicionais online:

  • Remove fisicamente linhas de um grupo de linhas quando 10% ou mais das linhas foram excluídas logicamente. 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 grupo de linhas com 900.000 linhas, reduzindo o espaço de armazenamento.
  • Combina um ou mais grupos de linhas compactadas para aumentar as linhas por grupo de linhas, até o máximo de 1.048.576 linhas. Por exemplo, se você inserir em massa cinco lotes de 102.400 linhas cada, obterá cinco grupos de linhas compactadas. Se você executar REORGANIZE, esses grupos de linhas serão mesclados em um grupo de linhas compactado com 512.000 linhas. Isso pressupõe que não havia limitações de tamanho de dicionário ou memória.
  • O Mecanismo de Banco de Dados tenta combinar grupos de linhas nos quais 10% ou mais das linhas foram marcadas como excluídas com outros grupos de linhas. Por exemplo, o grupo de linhas 1 é compactado e tem 500.000 linhas, enquanto o grupo de linhas 21 é compactado e tem 1.048.576 linhas. O grupo de linhas 21 tem 60% de suas linhas marcadas como excluídas, o que deixa 409.830 linhas. O Mecanismo de Banco de Dados favorece a combinação desses dois grupos de linhas para compactar um novo grupo de linhas com 909.830 linhas.

Depois de executar carregamentos de dados, você pode ter vários pequenos grupos de linhas no armazenamento delta. Você pode usar ALTER INDEX REORGANIZE para forçar esses grupos de linhas em columnstore e, em seguida, combinar grupos de linhas compactadas menores em grupos de linhas compactadas maiores. A operação de reorganização também removerá linhas que foram marcadas como excluídas do columnstore.

Observação

A reorganização de um índice columnstore usando o Management Studio combina grupos de linhas compactadas, mas não força todos os grupos de linhas a serem compactados no columnstore. Os grupos de linhas fechadas serão compactados, mas os grupos de linhas abertas não serão compactados em columnstore. Para comprimir à força todos os grupos de linhas, utilize o exemplo Transact-SQL que inclui COMPRESS_ALL_ROW_GROUPS = ON.

O que considerar antes de realizar a manutenção do índice

A manutenção do índice, realizada reorganizando ou reconstruindo um índice, consome muitos recursos. Isso causa um aumento significativo na utilização da CPU, memória usada e E/S de armazenamento. No entanto, dependendo da carga de trabalho do banco de dados e de outros fatores, os benefícios que ele oferece variam de vital importância a minúsculos.

Para evitar a utilização desnecessária de recursos, evite realizar a manutenção do índice indiscriminadamente. Em vez disso, os benefícios de desempenho da manutenção do índice devem ser determinados empiricamente para cada carga de trabalho usando a estratégia de recomendadae ponderados em relação aos custos de recursos e ao impacto da carga de trabalho necessários para alcançar esses benefícios.

A probabilidade de ver os benefícios de desempenho da reorganização ou reconstrução de um índice é maior quando o índice está fortemente fragmentado ou quando sua densidade de página é baixa. No entanto, estas não são as únicas coisas a considerar. Fatores como padrões de consulta (processamento de transações versus análise e emissão de relatórios), comportamento do subsistema de armazenamento, memória disponível e melhorias no mecanismo de banco de dados ao longo do tempo desempenham um papel.

Importante

As decisões de manutenção do índice devem ser tomadas depois de considerar vários fatores no contexto específico de cada carga de trabalho, incluindo o custo de recursos de manutenção. Não devem basear-se apenas em limiares fixos de fragmentação ou densidade de páginas.

Um efeito colateral positivo da reconstrução do índice

Os clientes geralmente observam melhorias de desempenho após a reconstrução de índices. No entanto, em muitos casos, essas melhorias não estão relacionadas à redução da fragmentação ou ao aumento da densidade de páginas.

Uma reconstrução de índice tem um benefício importante: atualiza estatísticas em colunas-chave do índice, percorrendo todas as linhas do índice. Isso é o equivalente a executar UPDATE STATISTICS ... WITH FULLSCAN, que torna as estatísticas atuais e, às vezes, melhora sua qualidade em comparação com a atualização de estatísticas de amostra padrão. Quando as estatísticas são atualizadas, os planos de consulta que fazem referência a elas são recompilados. Se o plano anterior para uma consulta não era ideal devido a estatísticas obsoletas, taxa de amostragem estatística insuficiente ou por outros motivos, o plano recompilado geralmente terá um desempenho melhor.

Os clientes muitas vezes atribuem incorretamente essa melhoria à própria reconstrução do índice, considerando-a como resultado da redução da fragmentação e do aumento da densidade de páginas. Na realidade, o mesmo benefício pode muitas vezes ser alcançado a um custo de recursos muito mais barato através da atualização estatísticas em vez de reconstruir índices.

Dica

O custo do recurso de atualização de estatísticas é menor em comparação com a reconstrução do índice, e a operação geralmente é concluída em minutos. As reconstruções de índice podem levar horas.

Estratégia de manutenção do índice

A Microsoft recomenda que os clientes considerem e adotem a seguinte estratégia de manutenção de índice:

  • Não assuma que a manutenção do índice sempre melhorará visivelmente sua carga de trabalho.
  • Meça o impacto específico da reorganização ou reconstrução de índices no desempenho da consulta em sua carga de trabalho. O Repositório de Consultas é uma boa maneira de medir o desempenho "antes da manutenção" e "após a manutenção" usando a técnica de de teste A/B.
  • Se observar que reconstruir índices melhora o desempenho, tente substituí-los pela atualização de estatísticas. Isto pode resultar numa melhoria semelhante. Nesse caso, talvez não seja necessário reconstruir índices com tanta frequência, ou de todo, e, em vez disso, pode executar atualizações periódicas de estatísticas. Para algumas estatísticas, talvez seja necessário aumentar a taxa de amostragem usando as cláusulas WITH SAMPLE ... PERCENT ou WITH FULLSCAN (isso não é comum).
  • Monitore a fragmentação do índice e a densidade da página ao longo do tempo para ver se há uma correlação entre esses valores com tendência para cima ou para baixo e o desempenho da consulta. Se uma maior fragmentação ou menor densidade de página degradar o desempenho de forma inaceitável, reorganize ou reconstrua índices. Muitas vezes, é suficiente apenas reorganizar ou reconstruir índices específicos usados por consultas com desempenho degradado. Isso evita um custo de recurso mais alto para manter cada índice no banco de dados.
  • Estabelecer uma correlação entre fragmentação/densidade de página e desempenho também permite determinar a frequência de manutenção do índice. Não assuma que a manutenção deve ser realizada em um cronograma fixo. Uma estratégia melhor é monitorar a fragmentação e a densidade da página e executar a manutenção do índice conforme necessário antes que o desempenho se degrade de forma inaceitável.
  • Se você determinou que a manutenção do índice é necessária e seu custo de recursos é aceitável, execute a manutenção durante tempos de baixo uso de recursos, se possível.
  • Teste periodicamente, pois os padrões de uso de recursos podem mudar ao longo do tempo.

Manutenção de índice no Banco de Dados SQL do Azure e na Instância Gerenciada SQL do Azure

Além das considerações e da estratégia acima, no Banco de Dados SQL do Azure e na Instância Gerenciada SQL do Azure é particularmente importante considerar os custos e benefícios da manutenção do índice. Os clientes devem realizá-lo apenas quando houver uma necessidade demonstrada, e tendo em conta os seguintes pontos.

  • O Banco de Dados SQL do Azure e a Instância Gerenciada SQL do Azure implementam de governança de recursos para definir limites no consumo de CPU, memória e E/S de acordo com a camada de preço provisionada. Esses limites se aplicam a todas as cargas de trabalho do usuário, incluindo a manutenção do índice. Se o consumo cumulativo de recursos por todas as cargas de trabalho se aproximar dos limites de recursos, a operação de reconstrução ou reorganização poderá degradar o desempenho de outras cargas de trabalho devido à contenção de recursos. Por exemplo, as cargas de dados em massa podem se tornar mais lentas porque a E/S do log de transações está a 100% devido a uma reconstrução simultânea do índice. Na Instância Gerenciada SQL do Azure, esse impacto pode ser reduzido executando a manutenção do índice em um grupo de carga de trabalho separado do Administrador de Recursos com alocação restrita de recursos, às custas da extensão da duração da manutenção do índice.
  • Para economizar custos, os clientes geralmente provisionam bancos de dados, pools elásticos e instâncias gerenciadas com espaço mínimo de recursos. O nível de preço é escolhido para ser suficiente para as cargas de trabalho das aplicações. Para acomodar um aumento significativo no uso de recursos devido à manutenção do índice sem degradar o desempenho do aplicativo, os clientes podem ter que provisionar mais recursos e aumentar os custos, sem necessariamente melhorar o desempenho do aplicativo.
  • Em pools elásticos, os recursos são compartilhados entre todos os bancos de dados em um pool. Mesmo que um determinado banco de dados esteja ocioso, a execução da manutenção de índice nesse banco de dados pode afetar cargas de trabalho de aplicativos em execução simultânea em outros bancos de dados no mesmo pool. Para obter mais informações, consulte Gerenciamento de recursos em pools elásticos densos.
  • Para a maioria dos tipos de armazenamento usados no Banco de Dados SQL do Azure e na Instância Gerenciada SQL do Azure, não há diferença no desempenho entre E/S sequencial e E/S aleatória. Isso reduz o impacto da fragmentação do índice no desempenho da consulta.
  • Ao usar de expansão de leitura ou réplicas de de replicação geográfica, a latência de dados em réplicas geralmente aumenta enquanto a manutenção do índice está sendo executada na réplica principal. Se uma réplica geográfica for provisionada com recursos insuficientes para sustentar um aumento na geração de logs de transações causado pela manutenção de índices, ela poderá ficar muito atrás da principal, fazendo com que o sistema a resemente. Isso torna a réplica indisponível até que o reposicionamento seja concluído. Além disso, nas camadas de serviço Premium e Business Critical, as réplicas usadas para alta disponibilidade também podem ficar muito atrás da base de dados principal durante a manutenção de índices. Se for necessário um failover durante ou logo após a manutenção do índice, ele pode levar mais tempo do que o esperado.
  • Se uma reconstrução de índice for executada na réplica primária e uma consulta de longa duração for executada numa réplica legível ao mesmo tempo, a consulta poderá ser encerrada automaticamente para evitar bloquear a tarefa de restabelecimento na réplica.

Há cenários específicos, mas incomuns, em que a manutenção de índice única ou periódica pode ser necessária no Banco de Dados SQL do Azure e na Instância Gerenciada SQL do Azure:

Dica

Se você determinou que a manutenção do índice é necessária para suas cargas de trabalho do Banco de Dados SQL do Azure e da Instância Gerenciada do SQL do Azure, você deve reorganizar os índices ou usar a reconstrução de índice online. Isso permite que cargas de trabalho de consulta acessem tabelas enquanto os índices estão sendo reconstruídos.

Além disso, tornar a operação retomável permite evitar reiniciá-la desde o início se ela for interrompida por um failover de banco de dados planejado ou não planejado. O uso de operações de índice retomáveis é particularmente importante quando os índices são grandes.

Dica

As operações de índice offline normalmente são concluídas mais rapidamente do que as operações online. Eles devem ser usados quando as tabelas não serão acessadas por consultas durante a operação, por exemplo, depois de carregar dados em tabelas de preparo como parte de um processo de ETL sequencial.

Limitações e restrições

Os índices de armazenamento de linha com mais de 128 extensões são reconstruídos em duas fases separadas: lógica e física. Na fase lógica, as unidades de alocação existentes usadas pelo índice são marcadas para deallocation, as linhas de dados são copiadas e classificadas e, em seguida, movidas para novas unidades de alocação criadas para armazenar o índice reconstruído. Na fase física, as unidades de alocação previamente marcadas para desalocação são fisicamente descartadas em transações curtas que acontecem em segundo plano e não exigem muitos bloqueios. Para obter mais informações sobre unidades de alocação, consulte Pages and Extents Architecture Guide.

A instrução ALTER INDEX REORGANIZE requer que o arquivo de dados que contém o índice tenha espaço disponível, porque a operação só pode alocar páginas de trabalho temporárias no mesmo arquivo, não em outro arquivo dentro do mesmo grupo de arquivos. Mesmo que o grupo de arquivos tenha espaço livre disponível, o usuário ainda pode encontrar o erro 1105: Could not allocate space for object '###' in database '###' because the '###' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup durante a operação de reorganização se um arquivo de dados estiver sem espaço.

Um índice não pode ser reorganizado quando ALLOW_PAGE_LOCKS está definido como OFF.

Até o SQL Server 2017 (14.x), a reconstrução de um índice columnstore clusterizado é uma operação offline. O Mecanismo de Banco de Dados tem que adquirir um bloqueio exclusivo na tabela ou partição enquanto a reconstrução ocorre. Os dados ficam offline e indisponíveis durante a reconstrução, mesmo ao usar NOLOCK, RCSI (isolamento de instantâneo confirmado por leitura) ou isolamento de instantâneo. A partir do SQL Server 2019 (15.x), um índice columnstore clusterizado pode ser reconstruído usando a opção ONLINE = ON.

Advertência

Criar e reconstruir índices não alinhados em uma tabela com mais de 1.000 partições é possível, mas não é suportado. 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.

Limitações das estatísticas

  • Quando um índice é criado ou reconstruído, as estatísticas são geradas ou atualizadas percorrendo todas as linhas da tabela, sendo isso equivalente ao uso da cláusula FULLSCAN em CREATE STATISTICS ou UPDATE STATISTICS. No entanto, a partir do SQL Server 2012 (11.x), quando um índice particionado é criado ou reconstruído, as estatísticas não são criadas ou atualizadas examinando todas as linhas da tabela. Em vez disso, é utilizada a taxa de amostragem predefinida. Para criar ou atualizar estatísticas em índices particionados examinando todas as linhas da tabela, use CREATE STATISTICS ou UPDATE STATISTICS com a cláusula FULLSCAN.
  • Da mesma forma, quando a operação de criação ou reconstrução do índice é retomável, as estatísticas são criadas ou atualizadas com a razão de amostragem padrão. Se as estatísticas foram criadas ou atualizadas pela última vez com a cláusula PERSIST_SAMPLE_PERCENT definida como ON, as operações de índice retomáveis usam a razão de amostragem persistente para criar ou atualizar estatísticas.
  • Quando um índice é reorganizado, as estatísticas não são atualizadas.

Exemplos

Verifique a fragmentação e a densidade de página de um índice de armazenamento de linhas usando Transact-SQL

O exemplo a seguir determina a fragmentação média e a densidade de página para todos os índices de armazenamento de linhas no banco de dados atual. Ele usa o modo SAMPLED para retornar resultados acionáveis rapidamente. Para obter resultados mais precisos, use o modo DETAILED. Isso requer a verificação de todas as páginas de índice e pode levar muito tempo.

SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       ips.avg_fragmentation_in_percent,
       ips.avg_page_space_used_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc
FROM sys.dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i
ON ips.object_id = i.object_id
   AND
   ips.index_id = i.index_id
ORDER BY page_count DESC;

A instrução anterior retorna um conjunto de resultados semelhante ao seguinte:

schema_name  object_name           index_name                               index_type    avg_fragmentation_in_percent avg_page_space_used_in_percent page_count  alloc_unit_type_desc
------------ --------------------- ---------------------------------------- ------------- ---------------------------- ------------------------------ ----------- --------------------
dbo          FactProductInventory  PK_FactProductInventory                  CLUSTERED     0.390015600624025            99.7244625648629               3846        IN_ROW_DATA
dbo          DimProduct            PK_DimProduct_ProductKey                 CLUSTERED     0                            89.6839757845318               497         LOB_DATA
dbo          DimProduct            PK_DimProduct_ProductKey                 CLUSTERED     0                            80.7132814430442               251         IN_ROW_DATA
dbo          FactFinance           NULL                                     HEAP          0                            99.7982456140351               239         IN_ROW_DATA
dbo          ProspectiveBuyer      PK_ProspectiveBuyer_ProspectiveBuyerKey  CLUSTERED     0                            98.1086236718557               79          IN_ROW_DATA
dbo          DimCustomer           IX_DimCustomer_CustomerAlternateKey      NONCLUSTERED  0                            99.5197553743514               78          IN_ROW_DATA

Para obter mais informações, consulte sys.dm_db_index_physical_stats.

Verifique a fragmentação de um índice columnstore usando Transact-SQL

O exemplo a seguir determina a fragmentação média para todos os índices columnstore com grupos de linhas compactadas no banco de dados atual.

SELECT OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
       OBJECT_NAME(i.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       100.0 * (ISNULL(SUM(rgs.deleted_rows), 0)) / NULLIF(SUM(rgs.total_rows), 0) AS avg_fragmentation_in_percent
FROM sys.indexes AS i
INNER JOIN sys.dm_db_column_store_row_group_physical_stats AS rgs
ON i.object_id = rgs.object_id
   AND
   i.index_id = rgs.index_id
WHERE rgs.state_desc = 'COMPRESSED'
GROUP BY i.object_id, i.index_id, i.name, i.type_desc
ORDER BY schema_name, object_name, index_name, index_type;

A instrução anterior retorna um conjunto de resultados semelhante ao seguinte:

schema_name  object_name            index_name                           index_type                avg_fragmentation_in_percent
------------ ---------------------- ------------------------------------ ------------------------- ----------------------------
Sales        InvoiceLines           NCCX_Sales_InvoiceLines              NONCLUSTERED COLUMNSTORE  0.000000000000000
Sales        OrderLines             NCCX_Sales_OrderLines                NONCLUSTERED COLUMNSTORE  0.000000000000000
Warehouse    StockItemTransactions  CCX_Warehouse_StockItemTransactions  CLUSTERED COLUMNSTORE     4.225346161484279

Manter índices usando o SQL Server Management Studio

Reorganizar ou reconstruir um índice

  1. No Pesquisador de Objetos, expanda o banco de dados que contém a tabela na qual você deseja reorganizar um índice.
  2. Expanda a pasta Tabelas.
  3. Expanda a tabela na qual você deseja reorganizar um índice.
  4. Expanda a pasta Índices.
  5. Clique com o botão direito do rato no índice que pretende reorganizar e selecione Reorganizar.
  6. Na caixa de diálogo Reorganizar Índices, verifique se o índice correto está na grade Índices a serem reorganizados e selecione OK.
  7. Marque a caixa de seleção Compactar dados de coluna de objeto grande para especificar que todas as páginas que contêm dados de objeto grande (LOB) também sejam compactadas.
  8. Selecione OK.

Reorganizar todos os índices em uma tabela

  1. No Pesquisador de Objetos, expanda o banco de dados que contém a tabela na qual você deseja reorganizar os índices.
  2. Expanda a pasta Tabelas.
  3. Expanda a tabela na qual você deseja reorganizar os índices.
  4. Clique com o botão direito do mouse na pasta Índices e selecione Reorganizar todos os.
  5. Na caixa de diálogo Reorganizar Índices, verifique se os índices corretos estão entre os 'Índices a serem reorganizados'. Para remover um índice da Índices a serem reorganizados grade, selecione o índice e pressione a tecla Delete.
  6. Marque a caixa de seleção Compactar dados de coluna de objeto grande para especificar que todas as páginas que contêm dados de objeto grande (LOB) também sejam compactadas.
  7. Selecione OK.

Manter índices usando Transact-SQL

Observação

Para obter mais exemplos sobre como usar Transact-SQL para reconstruir ou reorganizar índices, consulte ALTER INDEX Examples - Rowstore Indexes and ALTER INDEX Examples - Columnstore Indexes.

Reorganizar um índice

O exemplo a seguir reorganiza o índice de IX_Employee_OrganizationalLevel_OrganizationalNode na tabela HumanResources.Employee no banco de dados AdventureWorks2022.

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode
    ON HumanResources.Employee
    REORGANIZE;

O exemplo a seguir reorganiza o índice columnstore IndFactResellerSalesXL_CCI na tabela dbo.FactResellerSalesXL_CCI no banco de dados AdventureWorksDW2022. Este comando força todos os grupos de linhas fechados e abertos para columnstore.

-- This command forces all closed and open row groups into columnstore.
ALTER INDEX IndFactResellerSalesXL_CCI
    ON FactResellerSalesXL_CCI
    REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

Reorganizar todos os índices em uma tabela

O exemplo a seguir reorganiza todos os índices na tabela HumanResources.Employee no banco de dados AdventureWorks2022.

ALTER INDEX ALL ON HumanResources.Employee
   REORGANIZE;

Reconstruir um índice

O exemplo a seguir recria um único índice na tabela Employee no banco de dados AdventureWorks2022.

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD
;

Reconstruir todos os índices em uma tabela

O exemplo a seguir recria todos os índices associados à tabela no banco de dados AdventureWorks2022 usando a palavra-chave ALL. São especificadas três opções.

ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON)
;

Para obter mais informações, consulte ALTER INDEX.

Conteúdo relacionado