Valor correto para o fator de preenchimento para índices clusterizados com chaves de identidade substitutas

Question

Eu tenho uma tabela grande que tem um índice clusterizado com uma chave primária de identidade. Estou decidindo sobre o valor correto do fator de preenchimento dessa tabela para minimizar as divisões de página. Nós mantemos índices usando um script executado diariamente que mede a fragmentação e toma as ações apropriadas. A tabela contém colunas de tamanho variável.

Meu primeiro pensamento foi defini-lo em 100 (como os registros só devem ser gravados no final da tabela), mas suponho que as alterações nas colunas de tamanho variável também possam causar divisões de página, então estou migrando para 90 .

Qualquer conselho apreciado.

    

Answer 1

Depende

É um ato de equilíbrio. Se a sua tabela é de leitura intensiva, com poucas atualizações ou exclusões, o padrão (que é 100) deve estar ok.

Se sua tabela for muito intensiva em termos de gravação, com muitas atualizações, um valor abaixo de 80 poderá ser mais apropriado.

Não há fórmula mágica para essas coisas. (AFAIK, se houver, por favor me avise) A melhor coisa a fazer é ter um ambiente de teste, ter alguma carga de trabalho para testar. Faça as alterações & Veja como seu banco de dados se comporta com a carga de trabalho.

    

Answer 2

Nick está praticamente correto.

Se você fizer atualizações que aumentem o tamanho de um registro em páginas compactadas, causará divisões de página, mas, além disso, com uma chave primária de identidade, nada causará divisões de página no índice clusterizado.

(Apesar de dizer isso, há cinco tipos de divisão de página que o Mecanismo de Armazenamento pode fazer, e nem todos causam fragmentação e movimentação de dados - o que você obtém ao inserir valores de identidade mononicamente crescentes é um fim de página Mas eu discordo ...)

Eu ajudei muitos clientes com isso e escrevi o BOL em torno de tudo isso - se você quiser apenas escolher um valor como stake-in-the-ground, 70% viu o maior sucesso. Como diz Nick, monitore e ajuste conforme apropriado.

Escolher um fator de preenchimento para qualquer índice é um ato de equilíbrio de quanto atividade ocorre que empurra a plenitude da página em direção a 100% e com que frequência você pode tomar ações corretivas para redefinir o fator de preenchimento. Você precisa pensar sobre quanto espaço será inicialmente "desperdiçado" nas páginas se você definir o fator de preenchimento como muito baixo, como 50%, mas novamente vi isso ser apropriado em alguns casos.

Você também deve considerar como o índice será usado. Se for apenas para pesquisas singleton, você poderá obter um fator de preenchimento menor e mais tempo entre reconstruir / desfragmentar, já que não desperdiçará muitos I / Os / memória de ter muito do índice de cluster esparsamente ocupado na memória. Para fazer varreduras de intervalo grande, você gostaria de ter um fator de preenchimento um pouco mais alto para aumentar a eficiência de E / S e memória.

Há também a pergunta OLTP vs DW - geralmente um DW é imutável, então os índices teriam 100% de fator de preenchimento. OLTP é a parte difícil.

Depois de ter classificado o índice clusterizado, lembre-se de que os não-agrupados também precisarão de atenção, pois provavelmente eles ficarão fragmentados.

Ao redefinir o fator de preenchimento, lembre-se de que você pode escolher entre reconstruir e desfragmentar. DBCC INDEXDEFRAG / ALTER INDEX ... O REORGANIZE pode redefinir o fator de preenchimento em alguns casos para índices que não estão muito fragmentados.

Espero que isso ajude!

(Desculpa pelo 'over-answer' - um dos meus hot-buttons, tendo escrito o código: -)