Especificar fator de preenchimento para um índice

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

Este tópico descreve o que é fator de preenchimento e como especificar um valor de fator de preenchimento em um índice no SQL Server usando o SQL Server Management Studio ou o Transact-SQL.

A opção de fator de preenchimento é fornecida para ajustar o armazenamento e o desempenho dos dados do índice. Quando um índice é criado ou reconstruído, o valor do fator de preenchimento determina a porcentagem de espaço em cada página de nível de folha a ser preenchida com dados, reservando o restante em cada página como espaço livre para crescimento futuro. Por exemplo, especificar um valor de fator de preenchimento de 80 significa que 20% de cada página de nível de folha será deixada vazia, fornecendo espaço para a expansão do índice à medida que os dados são adicionados à tabela subjacente. O espaço vazio é reservado entre as linhas de índice em vez de no final do índice.

O valor do fator de preenchimento é uma porcentagem de 1 a 100 e o padrão em todo o servidor é 0, o que significa que as páginas de nível folha são preenchidas até a capacidade.

Neste tópico

• Antes de começar:

  Considerações de Desempenho

  Security

• Para especificar um fator de preenchimento em um índice, usando:

  SQL Server Management Studio (Estúdio de Gestão do Servidor SQL)

  Transact-SQL

Antes de começar

Considerações sobre desempenho

Divisões de Páginas

Um valor de fator de preenchimento escolhido corretamente pode reduzir possíveis divisões de página, fornecendo espaço suficiente para expansão de índice à medida que os dados são adicionados à tabela subjacente. Quando uma nova linha é adicionada a uma página de índice completa, o Mecanismo de Banco de Dados move aproximadamente metade das linhas para uma nova página para abrir espaço para a nova linha. Essa reorganização é conhecida como divisão de página. Uma divisão de página abre espaço para novos registros, mas pode levar tempo para ser executada e é uma operação que consome muitos recursos. Além disso, pode causar fragmentação que causa aumento das operações de E/S. Quando ocorrem divisões de página frequentes, o índice pode ser reconstruído usando um valor de fator de preenchimento novo ou existente para redistribuir os dados. Para obter mais informações, consulte Reorganizar e reconstruir índices.

Embora um valor de fator de preenchimento baixo e diferente de zero possa reduzir a necessidade de dividir páginas à medida que o índice cresce, o índice exigirá mais espaço de armazenamento e pode diminuir o desempenho de leitura. Mesmo para um aplicativo orientado para muitas operações de inserção e atualização, o número de leituras de banco de dados normalmente supera as gravações de banco de dados por um fator de 5 a 10. Portanto, especificar um fator de preenchimento diferente do padrão pode diminuir o desempenho de leitura do banco de dados em uma quantidade inversamente proporcional à configuração do fator de preenchimento. Por exemplo, um valor de fator de preenchimento de 50 pode fazer com que o desempenho de leitura do banco de dados diminua em duas vezes. O desempenho de leitura é reduzido porque o índice contém mais páginas, aumentando assim as operações de E/S de disco necessárias para recuperar os dados.

Adicionando dados ao final da tabela

Um fator de preenchimento não nulo que não seja 0 ou 100 pode ser bom para o desempenho se os novos dados estiverem distribuídos uniformemente pela tabela. No entanto, se todos os dados forem adicionados ao final da tabela, o espaço vazio nas páginas de índice não será preenchido. Por exemplo, se a coluna de chave de índice for uma coluna IDENTITY, a chave para novas linhas estará sempre aumentando e as linhas de índice serão logicamente adicionadas ao final do índice. Se as linhas existentes forem atualizadas com dados que aumentam o tamanho das linhas, use um fator de preenchimento inferior a 100. Os bytes extras em cada página ajudarão a minimizar as divisões de página causadas pelo comprimento extra nas linhas.

Segurança

Permissions

Requer permissão ALTER na tabela ou exibição. O usuário deve ser membro da função de servidor fixa sysadmin ou das funções de banco de dados db_ddladmin e db_owner fixas.

Usando o SQL Server Management Studio

Para especificar um fator de preenchimento usando o Designer de Tabela

1. No Pesquisador de Objetos, clique no sinal de adição para expandir o banco de dados que contém a tabela na qual você deseja especificar o fator de preenchimento de um índice.

2. Clique no sinal mais para expandir a pasta Tabelas.

3. Clique com o botão direito do mouse na tabela na qual você deseja especificar o fator de preenchimento de um índice e selecione Design.

4. No menu Designer de Tabela , clique em Índices/Chaves.

5. Selecione o índice com o fator de preenchimento que você deseja especificar.

6. Expanda Especificação de Preenchimento, selecione a linha Fator de Preenchimento e insira o fator de preenchimento desejado na linha.

7. Clique em Fechar.

8. No menu Arquivo, selecione Salvartable_name.

Para especificar um fator de preenchimento em um índice usando o Pesquisador de Objetos

1. No Pesquisador de Objetos, clique no sinal de adição para expandir o banco de dados que contém a tabela na qual você deseja especificar o fator de preenchimento de um índice.

2. Clique no sinal mais para expandir a pasta Tabelas.

3. Clique no sinal de adição para expandir a tabela na qual você deseja especificar o fator de preenchimento de um índice.

4. Clique no sinal mais para expandir a pasta Índices.

5. Clique com o botão direito do rato no índice do qual pretende especificar o fator de preenchimento e selecione Propriedades.

6. Em Selecione uma página, selecione Opções .

7. Na linha Fator de preenchimento, insira o fator de preenchimento desejado.

8. Clique em OK.

Usando Transact-SQL

Para especificar um fator de preenchimento em um índice existente

1. No Pesquisador de Objetos , conecte-se a uma instância do Mecanismo de Banco de Dados.

2. Na barra Padrão, clique em Nova Consulta.

3. Copie e cole o exemplo a seguir na janela de consulta e clique em Executar. O exemplo recria um índice existente e aplica o fator de preenchimento especificado durante a operação de reconstrução.

   USE AdventureWorks2022;  
   GO  
   -- Rebuilds the IX_Employee_OrganizationLevel_OrganizationNode index   
   -- with a fill factor of 80 on the HumanResources.Employee table.  
   
   ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee  
   REBUILD WITH (FILLFACTOR = 80);   
   GO  
   

Outra maneira de especificar um fator de preenchimento em um índice

1. No Pesquisador de Objetos , conecte-se a uma instância do Mecanismo de Banco de Dados.

2. Na barra Padrão, clique em Nova Consulta.

3. Copie e cole o exemplo a seguir na janela de consulta e clique em Executar.

   USE AdventureWorks2022;  
   GO  
   -- Drops and re-creates the IX_Employee_OrganizationLevel_OrganizationNode index
   -- on the HumanResources.Employee table with a fill factor of 80.   
   
   CREATE INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee  
      (OrganizationLevel, OrganizationNode)   
   WITH (DROP_EXISTING = ON, FILLFACTOR = 80);   
   GO  
   

Para obter mais informações, consulte ALTER INDEX (Transact-SQL).