Especificar fator de preenchimento para um índice

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

A opção de fator de preenchimento é fornecida para ajustar o desempenho e o armazenamento de dados do índice. Quando um índice é criado ou recriado, o valor do fator de preenchimento determina a porcentagem de espaço em cada página de nível folha a ser preenchida com dados, reservando o restante em cada página como espaço livre para o crescimento futuro. Por exemplo, especificar um valor de fator de preenchimento de 80 significa que 20% de cada página de nível folha será deixada vazia, fornecendo espaço para expansão do índice conforme 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 é expresso em porcentagem e varia entre 1 e 100. O padrão para todo o servidor é 0, o que significa que as páginas de nível folha são preenchidas até a capacidade máxima.

Nesse Tópico

• Antes de começar:

  Considerações sobre desempenho

  Segurança

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

  SQL Server Management Studio

  Transact-SQL

Antes de começar

Considerações sobre desempenho

Divisões de página

Um valor de fator de preenchimento escolhido corretamente pode reduzir possíveis divisões de página fornecendo espaço suficiente para a expansão do í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 uma 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 de uso intensivo de recursos. Além disso, pode causar fragmentação que causa o aumento das operações de E/S. Quando ocorrem divisões de página frequentes, o índice pode ser recriado usando um valor de fator de preenchimento novo ou existente para redistribuir os dados. Para obter mais informações, veja Reorganizar e recriar índices.

Embora um valor de fator de preenchimento baixo e diferente de zero possa reduzir o requisito de dividir páginas à medida que o índice aumenta, o índice exigirá mais espaço de armazenamento e poderá 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 em número 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 duas vezes. O desempenho de leitura é reduzido porque o índice contém mais páginas, aumentando, portanto, 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 zero que não seja 0 ou 100 pode ser bom para o desempenho se os novos dados forem distribuídos uniformemente ao longo da 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 aumentará continuamente e as linhas de índice serão adicionadas logicamente no final do índice. Se as linhas existentes forem atualizadas com dados que alongam 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

Permissões

Requer a 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 fixas db_ddladmin e db_owner .

Como usar 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 de 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 Fill Factor 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 de 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 de adição para expandir a pasta Índices.

5. Clique com o botão direito do mouse no índice com o fator de preenchimento que você deseja especificar 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 recompilação.

   USE AdventureWorks2012;  
   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 AdventureWorks2012;  
   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 mais informações, consulte ALTERAR ÍNDICE (Transact-SQL).