Partilhar via

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

  • Artigo

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Analytics Platform System (PDW)

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 os métodos de manutenção de índice, a reorganização de um índice e a recompilação de um í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ário para a manutenção.

Observação

Este artigo não se aplica a pools de SQL dedicados no Azure Synapse Analytics. Para obter mais informações sobre a manutenção de índice para pools de SQL dedicados no Azure Synapse Analytics, veja tabelas de pool de SQL dedicado em indexação na Azure Synapse Analytics.

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

O que é a fragmentação de índice e como ela impacta o desempenho:

  • Em índices de árvore B (rowstore), a fragmentação ocorre quando os índices têm páginas nas quais a ordenação lógica no índice, com base no valor de 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 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.

  • O mecanismo de banco de dados modifica os índices automaticamente sempre que são executadas operações de inserção, atualização ou exclusão nos dados subjacentes. Por exemplo, a adição de linhas em uma tabela pode fazer com que as páginas existentes nos índices de rowstore se dividam, liberando espaço para a inserção de novas linhas. Com o decorrer do tempo, essas modificações podem fazer com que os dados do índice sejam dispersados pelo banco de dados (fragmentados).

  • Para consultas que leem muitas páginas usando verificações completas ou de índice de intervalo, índices muito fragmentados podem prejudicar o desempenho da consulta quando é necessária E/S adicional para ler os dados. Em vez de ter 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 fornece um desempenho de E/S sequencial melhor do que o desempenho de E/S aleatório, a fragmentação de índice pode prejudicar o desempenho porque uma E/S mais aleatória é necessária para ler índices fragmentados.

O que é a densidade de página (também conhecida como preenchimento da página) e como ela 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 em uma página, a densidade da página será de 100%. Se uma página estiver vazia, a densidade da página será 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 da página for baixa, mais páginas serão necessárias para armazenar a mesma quantidade de dados. Isso significa que mais E/S serão necessárias para ler e gravar esses dados, assim como mais memória será necessária para armazenar esses dados em cache. Quando a memória é limitada, menos páginas exigidas por uma consulta serão armazenadas em cache, causando ainda mais E/S de disco. Consequentemente, a densidade de página baixa afeta negativamente o desempenho.
  • Quando o Mecanismo de Banco de Dados adiciona linhas a uma página durante a criação, recompilação ou reorganização do índice, ele não preencherá a página totalmente se o fator de preenchimento do índice for definido como um valor diferente de 100 (ou 0, que é equivalente nesse contexto). Isso diminui a densidade de página e, da mesma forma, adiciona sobrecarga de E/S e afeta negativamente o desempenho.
  • A densidade de página baixa pode aumentar o número de níveis intermediários da árvore B. Isso aumenta moderadamente o custo de E/S da CPU de localizar páginas de nível folha em buscas e exames de índice.
  • Quando o Otimizador de Consulta compila um plano de consulta, ele considera o custo de E/S necessário para ler os dados exigidos pela consulta. Com a densidade de página baixa, há mais páginas a serem lidas, portanto, o custo de E/S será maior. Isso pode afetar a escolha do plano de consulta. Por exemplo, à medida que a densidade de 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 consumo de recursos e desempenho diferente.

Dica

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

Para evitar a redução desnecessária da densidade de página, a Microsoft não recomenda definir o fator de preenchimento com valores diferentes de 100 ou 0, exceto em determinados casos de índices que apresentam um grande número de divisões de página, por exemplo, índices modificados com frequência com colunas de entrelinhamento contendo valores de GUID não sequenciais.

Medir Fragmentação de índice e densidade de página

A fragmentação e a densidade da página estão entre os fatores a considerar ao decidir se a manutenção do índice deve ser executada e qual método de manutenção será usado.

A fragmentação é definida de modo diferente para os índices de rowstore e columnstore. Em índices rowstore, sys.dm_db_index_physical_stats() permite que você detecte a fragmentação e a densidade de página em um índice específico, todos os índices em uma tabela ou uma 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 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 da página.

Em grupos de linhas compactados em índices columnstore, a fragmentação é definida como a taxa de linhas excluídas para o total de linhas, expressas como uma porcentagem. O sys.dm_db_column_store_row_group_physical_stats permite que você determine 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 armazenadas fisicamente no grupo de linhas. Para rowgroups compactados, isso inclui as linhas marcadas como excluídas.
deleted_rows Número de linhas fisicamente armazenadas em um grupo de linhas compactado que são marcadas para exclusão. 0 para grupos de linhas que estão no repositório delta.

A fragmentação do grupo de linhas compactadas em um índice columnstore pode ser computada usando esta fórmula:

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

Dica

Para os índices de rowstore e columnstore, examine a fragmentação de índice ou heap e a densidade de página depois de excluir ou atualizar um grande número de linhas. Para heaps, se houver atualizações frequentes, examine periodicamente a fragmentação para evitar a proliferação de registros de encaminhamento. Para obter mais informações sobre heaps, confira Heaps (tabelas sem índices clusterizados).

Confira Exemplos para ver as consultas de exemplo para determinar a fragmentação e a densidade da página.

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

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

  • Reorganizar um índice
  • Recompilar um índice

Observação

Em índices particionados, é possível usar um dos métodos a seguir em uma partição completa ou uma única partição de um índice.

Reorganizar um índice

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

  • Para índices rowstore, o mecanismo de banco de dados 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 dos nós folha (da esquerda para a direita). A reorganização também compacta as páginas de índice para tornar a densidade de 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, confira Exemplos – reorganização de rowstore.
  • Ao usar índices columnstore, o repositório delta pode acabar com vários pequenos grupos de linha após a inserção, a atualização e a exclusão de dados ao longo do tempo. A reorganização de um índice columnstore força os grupos de linhas de armazenamento delta em grupos de linhas compactados no columnstore e combina grupos de linhas compactados 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. A reorganização de 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, assim que os dados forem compactados, o desempenho de consulta será aprimorado. Para obter exemplos de sintaxe, confira Exemplos – reorganização de columnstore.

Observação

a partir do SQL Server 2019 (15.x), o Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure, o motor de tupla recebe ajuda de uma tarefa de mesclagem em segundo plano que compacta automaticamente rowgroups delta abertos menores que existem há algum tempo, conforme determinado por um limite interno, ou mescla rowgroups compactados dos quais foi excluído um número grande de linhas. Isso melhora a qualidade do índice columnstore ao longo do tempo. Na maioria dos casos, isso ignora a necessidade de emitir comandos ALTER INDEX ... REORGANIZE.

Dica

Se você cancelar ou interromper uma operação de reorganização, o progresso feito até esse ponto será mantido no banco de dados. Para reorganizar índices grandes, é possível iniciar e interromper a operação várias vezes até a conclusão.

Recompilar um índice

A recriação de um índice descarta e recria o índice. Dependendo do tipo de índice e da versão do Mecanismo de Banco de Dados, uma operação de recompilação pode ser feita offline ou online. A recompilação de um índice offline geralmente demora menos do que a recompilação online, mas mantém os bloqueios no nível do objeto durante a operação, bloqueando o acesso das consultas à tabela ou à exibição.

Uma recompilação de índice online não requer bloqueios em nível de objeto até o final da operação, quando um bloqueio deve ser mantido por um curto período para concluir a recompilação. Dependendo da versão do Mecanismo de Banco de Dados, uma recompilação de índice online pode ser iniciada como uma operação retomável. Uma recompilação de índice retomável pode ser pausada, mantendo o progresso feito até esse ponto. Uma operação de recompilação retomável pode ser retomada após sua pausa ou interrupção, ou pode ser anulada se a conclusão da recompilação for desnecessária.

Para obter a sintaxe Transact-SQL, consulte ALTER INDEX REBUILD. Para obter mais informações sobre recompilação de índice online, confira Executar Operações de Índice Online.

Observação

Durante a recompilação online de um índice, cada modificação dos dados em colunas indexadas deve atualizar uma cópia adicional do índice. Isso pode resultar em uma pequena degradação de desempenho das instruções de modificação de dados durante a recompilação online.

Se uma operação de índice retomável online estiver em pausa, o impacto de desempenho persistirá até que a operação retomável seja concluída ou anulada. Se você não pretender concluir uma operação de índice retomável, anule-a em vez de pausá-la.

Dica

Dependendo dos recursos disponíveis e dos padrões de carga de trabalho, especificar um valor maior que o padrão MAXDOP na instrução ALTER INDEX REBUILD pode reduzir a duração da recompilação às custas de maior utilização da CPU.

  • Nos índices do rowstore, a recompilaçã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 da tabela são descartados e recompilados em uma única transação. Quando índices com 128 ou mais extensões são recompilados, o mecanismo de banco de dados adia as desalocações de página e a aquisição dos bloqueios associados até depois da conclusão da recompilação. Para obter exemplos de sintaxe, confira Exemplos – recompilação de rowstore.

  • Nosíndices columnstore, a recompilação remove a fragmentação, move todas as linhas de armazenamento delta para columnstore e exclui fisicamente as linhas que foram marcadas para exclusão. Para obter exemplos de sintaxe, confira Exemplos – recompilação de columnstore.

    Dica

    A partir do SQL Server 2016 (13.x), normalmente, não é necessário recompilar o índice columnstore porque REORGANIZE executa as etapas básicas da recompilação como uma operação online.

Usar a recompilação de índice para se recuperar de dados corrompidos

Nas versões anteriores do SQL Server 2008 (10.0.x), às vezes era possível recompilar um índice não clusterizado do rowstore para corrigir as inconsistências causadas pelos dados corrompidos no índice.

Ainda é possível reparar essas inconsistências no índice não clusterizado recompilando um índice não clusterizado offline. Entretanto, não é possível reparar inconsistências de índice não clusterizado recompilando o índice online, porque o mecanismo de recompilação online usa o índice não clusterizado existente como base para a recompilação e, portanto, transporta a inconsistência. Às vezes, a recompilação do índice offline pode forçar uma verificação do índice clusterizado (ou heap) e, portanto, substituir os dados inconsistentes no índice não clusterizado pelos dados do heap ou índice clusterizado.

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

Índice automático e gerenciamento de estatísticas

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

Considerações específicas para recompilar e reorganizar índices rowstore

Os seguintes cenários causam a recompilação automática de todos os índices rowstore não clusterizados em uma tabela:

  • Criar 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 seja armazenada como um heap

Os seguintes cenários não causam a recompilação automática de todos os índices rowstore não clusterizados na mesma tabela:

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

Importante

Um índice não poderá ser reorganizado ou recompilado se o grupo de arquivos no qual ele está localizado estiver offline ou definido como somente leitura. A instrução falhará quando a palavra-chave ALL for especificada e um ou mais índices estiverem em um grupo de arquivos offline ou somente leitura.

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 recompilação é concluída, o mecanismo de banco de dados exclui o índice original.

Quando ALL for especificado com a instrução ALTER INDEX ... REORGANIZE, os índices clusterizados, não clusterizados e XML da tabela serão reorganizados.

A reconstrução ou reorganização de índices rowstore pequenos 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, as páginas de índices pequenos são, às vezes, armazenadas em extensões mistas, o que implicitamente fragmenta os índices. Extensões mistas são compartilhadas por até oito objetos, portanto, a fragmentação em um índice pequeno pode não ser reduzida após a reorganização ou recriação.

Considerações específicas para recompilar um índice columnstore

Ao recompilar um índice columnstore, o mecanismo de banco de dados lê todos os dados do índice columnstore original, incluindo o armazenamento delta. Ele combina dados em novos grupos de linhas e compacta todos os grupos de linhas no 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 motor de tupla recebe a ajuda de uma tarefa de mesclagem em segundo plano que compacta automaticamente os menores grupos de linha de armazenamento delta aberto que existem há algum tempo, conforme determinado por um limite interno, ou mescla os grupos de linha compactados dos quais foi excluído um número grande de linhas. Isso melhora a qualidade do índice columnstore ao longo do tempo. Para obter mais informações sobre os termos e conceitos da columnstore em Índices Columnstore: visão geral.

Recompilar uma partição em vez de toda a tabela

Recompilar a tabela inteira é uma tarefa demorada se o índice for grande, e exige espaço em disco suficiente para armazenar uma cópia adicional de todo o índice durante a recompilação.

Em tabelas particionadas, você não precisa recompilar o índice columnstore inteiro se a fragmentação ocorrer apenas em algumas partições, como, por exemplo, em partições em que as instruções UPDATE, DELETE ou MERGE afetaram um grande número de linhas.

A recompilação de uma partição após o carregamento ou modificação de dados garante que todos os dados sejam armazenados em grupos de linhas compactados 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 terminar com vários grupos de linhas abertos no armazenamento delta. A recompilação move todas as linhas de armazenamento delta para grupos de linhas compactados no columnstore.

Considerações específicas para reorganizar um índice columnstore

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

  • Remove fisicamente as linhas de um grupo de linhas quando 10% ou mais linhas forem excluídas logicamente. Por exemplo, se um grupo de linhas compactado 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 volume de armazenamento.
  • Combina um ou mais grupos de linhas compactados para aumentar as linhas por grupos de linhas até o máximo de 1.048.576 linhas. Por exemplo, se você inserir em massa cinco lotes de 102.400 linhas por vez, receberá cinco grupos de linhas compactados. Se você executar REORGANIZE, esses grupos de linhas serão mesclados em um rowgroup compactado com 512.000 linhas. Isso pressupõe que não havia nenhuma limitação de tamanho ou memória de dicionário.
  • O mecanismo de banco de dados tenta combinar os grupos de linhas em que 10% ou mais 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 rowgroup 21 é compactado e tem 1.048.576 linhas. O rowgroup 21 tem 60% das linhas marcadas como excluídas, o que o deixa 409.830 linhas. O mecanismo de banco de dados favorece a combinação desses dois grupos de linha para compactar um novo grupo de linhas que tenha 909.830 linhas.

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

Observação

A reorganização de um índice columnstore usando o Management Studio combinará grupos de linhas compactados, mas não forçará a compactação de todos os grupos de linhas no columnstore. Os grupos de linhas fechados serão compactados, mas os grupos de linhas abertos não serão compactados no columnstore. Para forçar a compactação de todos os grupos de linhas, use o exemplo do Transact-SQL que inclui COMPRESS_ALL_ROW_GROUPS = ON.

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

A manutenção do índice, executada pela reorganização ou recompilação de um índice, faz uso intensivo de recursos. Isso causa um aumento significativo na utilização da CPU, da memória e na E/S de armazenamento. No entanto, dependendo da carga de trabalho do banco de dados e de outros fatores, os benefícios fornecidos variam entre ser de importância vital até não ter importância.

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

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

Importante

As decisões de manutenção de índice devem ser feitas depois de considerar vários fatores no contexto específico de cada carga de trabalho, incluindo o custo de recurso de manutenção. Elas não devem ser baseadas apenas em limites fixos de densidade de página ou de fragmentação.

Um efeito colateral positivo da recompilação de índice

Os clientes geralmente observam melhorias de desempenho após a recompilação de índices. No entanto, em muitos casos, esses aprimoramentos não estão relacionados a redução da fragmentação ou aumento da densidade de página.

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

Os clientes frequentemente atribuem de forma incorreta essa melhoria para a recompilação de índice em si, considerando-a um resultado da fragmentação reduzida e de maior densidade de página. Na verdade, é possível obter o mesmo benefício, com um custo muito menor de recursos, com a atualização das estatísticas, em vez da recompilação dos índices.

Dica

O custo de recursos para atualizar as 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 de índice

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

  • Não presuma que a manutenção do índice sempre melhorará visivelmente sua carga de trabalho.
  • Meça o impacto específico da reorganização ou recompilaçã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 testes A/B.
  • Se você observar que a recompilação de índices melhora o desempenho, tente substituí-la pela atualização de estatísticas. Isso pode resultar em uma melhoria semelhante. Nesse caso, talvez não seja necessário reconstruir os índices com tanta frequência ou, em vez disso, poderá executar atualizações periódicas de estatísticas. Em algumas estatísticas, pode ser 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 de índice e a densidade de página ao longo do tempo para ver se há uma correlação entre o aumento ou redução desses valores de tendência e o desempenho de consulta. Se a fragmentação mais alta ou a menor densidade de página diminuírem o desempenho a níveis inaceitáveis, reorganize ou recompile os índices. Geralmente, é suficiente só reorganizar ou recompilar índices específicos usados por consultas com desempenho degradado. Isso evita um custo maior de recursos na manutenção de cada índice no banco de dados.
  • Estabelecer uma correlação entre a fragmentação/densidade de página e o desempenho também permite que você determine a frequência da manutenção do índice. Não presuma que a manutenção deve ser executada em um agendamento fixo. Uma estratégia mais adequada é monitorar a fragmentação e a densidade da página e executar a manutenção do índice conforme necessário antes que o desempenho seja degradado de forma inaceitável.
  • Se você tiver determinado que a manutenção do índice é necessária e o custo dos recursos é aceitável, execute a manutenção durante períodos de baixo uso de recursos, se possível.
  • Teste periodicamente, pois os padrões de uso de recursos podem mudar com o tempo.

A manutenção do índice no Banco de Dados SQL do Azure e na Instância Gerenciada de 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 de SQL do Azure é particularmente importante considerar os custos e benefícios da manutenção do índice. Os clientes devem executá-lo somente quando realmente houver a necessidade e considerar os pontos a seguir.

  • O Banco de Dados SQL do Azure e a Instância Gerenciada de SQL do Azure e implementam a governança de recursos para definir limites de CPU, memória e consumo de E/S de acordo com o tipo de preço provisionado. Esses limites se aplicam a todas as cargas de trabalho do usuário, incluindo a manutenção do índice. Se o consumo cumulativos de recursos de todas as cargas de trabalho se aproximar dos limites de recursos, a operação de reconstrução ou reorganização pode prejudicar 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 as E/S de logs de transações estará em 100% devido a uma recompilação de índice simultânea. Na Instância Gerenciada de SQL do Azure, esse impacto pode ser reduzido ao executar a manutenção do índice em um grupo de carga de trabalho Resource Governor separado com alocação de recursos restrita à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 reserva dinâmica mínima de recursos. O tipo de preço é escolhido para ser suficiente para cargas de trabalho do aplicativo. Para acomodar um aumento significativo no uso de recursos devido à manutenção do índice sem degradar o desempenho do aplicativo, os clientes podem precisar 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, executar a manutenção do índice nesse banco de dados pode afetar as cargas de trabalho do aplicativo em execução simultânea em outros bancos de dado no mesmo pool. Para obter mais informações, veja Gerenciamento de recursos em pools elásticos densos.
  • Na maioria dos tipos de armazenamento usados no Banco de Dados SQL do Azure e na Instância Gerenciada de SQL do Azure, não há nenhuma diferença no desempenho entre a E/S sequencial e a E/S aleatória. Isso reduz o impacto da fragmentação do índice no desempenho da consulta.
  • Ao usar as réplicas de Expansão de Leitura ou Replicação Geográfica, a latência de dados em réplicas geralmente aumenta enquanto a manutenção do índice é executada na réplica primária. Se uma replicação geográfica for provisionada com recursos insuficientes para sustentar um aumento na geração de logs de transações causado pela manutenção do índice, seu retardo poderá ficar muito atrasado em relação à replica primária, fazendo com que o sistema a propague novamente. Isso indisponibiliza a réplica até que a repropagação seja concluída. Além disso, nas camadas de serviço Premium e Comercialmente Crítico, as réplicas usadas em alta disponibilidade podem, de modo semelhante, ter um retardo muito atrasado em relação à replica primária durante a manutenção do índice. Se houver a necessidade de um failover durante ou logo após a manutenção do índice, ele poderá demorar mais do que o esperado.
  • Se uma reconstrução de índice for executada na réplica primária e uma consulta de execução longa for executada em uma réplica de leitura ao mesmo tempo, a consulta poderá ser encerrada automaticamente para impedir o bloqueio do thread ao refazer a réplica.

Há cenários específicos, mas incomuns, quando 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 de SQL do Azure:

Dica

Se você tiver determinado 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 de SQL do Azure, você deverá reorganizar índices ou usar a recompilação de índice online. Isso permite que as cargas de trabalho de consulta acessem tabelas enquanto os índices são recompilados.

Além disso, tornar a operação retomável permite que você evite reiniciá-la desde o início se ela for interrompida por um failover planejado ou não planejado no banco de dados. 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. Elas devem ser usadas quando as tabelas não forem 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

Índices rowstore com mais de 128 extensões são recriados 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 desalocação, as linhas de dados são copiadas, ordenadas e, depois, movidas para novas unidades de alocação criadas para armazenar o índice recriado. Na fase física, as unidades de alocação previamente marcadas para desalocação são fisicamente canceladas em transações curtas que ocorrem em segundo plano e que não exigem muitos bloqueios. Para obter mais informações sobre as unidades de alocação, confira o Guia de Arquitetura de Páginas e Extensões.

A instrução ALTER INDEX REORGANIZE exige que o arquivo de dados que contém o índice tenha espaço disponível, pois 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. Embora 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 recriação de um índice columnstore clusterizado é uma operação offline. O mecanismo de banco de dados precisa adquirir um bloqueio exclusivo na tabela ou na partição durante a recompilação. Os dados estão offline e não estão disponíveis durante a recompilação mesmo ao usar NOLOCK, isolamento de instantâneo com leitura confirmada (RCSI) ou isolamento de instantâneo. A partir do SQL Server 2019 (15.x), um índice columnstore clusterizado pode ser recompilado usando a opção ONLINE = ON.

Aviso

É possível criar e reconstruir índices não alinhados em uma tabela com mais de 1.000 partições, mas não há suporte para isso. Isso pode provocar degradação do desempenho ou consumo excessivo de memória durante essas operações. A Microsoft recomenda usar índices alinhados apenas quando o número de partições for maior que 1.000.

Limitações de estatísticas

  • Quando um índice é criado ou recompilado, as estatísticas são criadas ou atualizadas por meio do exame de todas as linhas da tabela, o que é equivalente a usar a 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 recompilado, as estatísticas não são criadas nem atualizadas por meio da verificação de todas as linhas da tabela. Em vez disso, a taxa de amostragem padrão é usada. Para criar ou atualizar as estatísticas em índices particionados por meio do exame de 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 recompilação de índice é retomável, as estatísticas são criadas ou atualizadas com a taxa de amostragem padrão. Se as estatísticas forem criadas ou atualizadas por último com a cláusula PERSIST_SAMPLE_PERCENT definida como ON, as operações de índice retomáveis usarão a taxa de amostragem persistente para criar ou atualizar as 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 rowstore usando o Transact-SQL

O exemplo a seguir determina a densidade de página e a fragmentação média para todos os índices de rowstore 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 demorar muito.

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 saber mais, confira 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 de todos os índices columnstore com grupos de linhas compactados 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 recriar um índice

  1. No Pesquisador de Objetos, expanda o banco de dados que contém a tabela cujo índice você deseja reorganizar.
  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 mouse no índice a ser reorganizado e selecione Reorganizar.
  6. Na caixa de diálogo Reorganizar Índices, verifique se o índice correto está na grade Índices a serem reorganizados e clique em 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 de 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 Tudo.
  5. Na caixa de diálogo Reorganizar Índices , verifique se os índices corretos estão na grade Índices a serem reorganizadose clique em OK. Para remover um índice da grade Índices a serem reorganizados , 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 de como usar o Transact-SQL para recompilar ou reorganizar índices, veja Exemplos de ALTER INDEX – Índices rowstore e Exemplos ALTER INDEX – Índices columnstore.

Reorganizar um índice

O exemplo a seguir reorganiza o índice IX_Employee_OrganizationalLevel_OrganizationalNode na tabela HumanResources.Employee do 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 do banco de dados AdventureWorksDW2022. Este comando força todos os rowgroups CLOSED e OPEN para o 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 de uma tabela

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

ALTER INDEX ALL ON HumanResources.Employee
   REORGANIZE;

Recompilar um índice

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

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD
;

Recriar todos os índices de uma tabela

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

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

Para saber mais, confira ALTER INDEX.

Conteúdo relacionado