11/01/2024
O FILL FACTOR é uma configuração no SQL Server que determina o percentual de espaço que será preenchido em cada página de índice durante a criação ou a reconstrução de um índice. O espaço livre de cada página de índice serve para acomodar o crescimento futuro do índice sem a necessidade de reorganizações frequentes, que podem impactar o desempenho.
Imagine uma biblioteca com livros arranjados em ordem alfabética em uma estante. Para facilitar a adição de novos títulos, espaços são deixados entre os livros. Essa prática permite inserir novos livros em sua ordem correta sem a necessidade de mover toda a fileira. Esta estratégia simplifica enormemente a manutenção da ordem alfabética, especialmente com a frequente adição de novos livros.
O FILL FACTOR no SQL Server atua de maneira semelhante. Ele define um percentual de ocupação nas páginas de um índice, permitindo a inclusão de novos registros sem reorganizações constantes das páginas de dados. Essa configuração é vital para evitar a sobrecarga causada por reorganizações frequentes, mantendo assim a eficiência na gestão dos índices. Com um FILL FACTOR bem ajustado, o banco de dados se torna mais eficiente e rápido, tanto para consultas quanto para atualizações.
Ao definir um índice em uma tabela, você pode especificar o FILL FACTOR como parte da instrução SQL. Este valor determina o quanto do espaço disponível em cada página de índice será preenchido.
CREATE INDEX IX_Nome ON dbo.Clientes(Nome) WITH (FILLFACTOR = 90);
Este comando cria um índice chamado IX_Nome
na coluna Nome
da tabela Clientes
, com um FILL FACTOR de 90%. Isso significa que cada página do índice será preenchida até 90% da sua capacidade, deixando os restantes 10% de espaço livre para futuras inserções ou atualizações.
Escolher um FILL FACTOR de 90% é uma prática comum que busca um equilíbrio entre o espaço reservado para crescimento futuro do índice e a eficiência na utilização do espaço disponível. Contudo, dependendo das características específicas do banco de dados e das operações executadas, o valor ideal do FILL FACTOR pode variar.
A escolha correta do FILL FACTOR é crucial na gestão eficiente dos índices no SQL Server, influenciando diretamente o desempenho e a manutenção.
Para ajustar o FILL FACTOR em um índice existente:
ALTER INDEX IX_Nome ON dbo.Clientes REBUILD WITH (FILLFACTOR = 90);
Este comando reconstrói o índice IX_Nome
na tabela Clientes
com um novo FILL FACTOR de 90%.
Clientes
para definir um FILL FACTOR eficiente. Por exemplo, um aumento de 5% ao mês sugere um FILL FACTOR de 95%.Evite um FILL FACTOR inferior a 80% em índices de tabelas frequentemente acessadas, pois isso pode impactar o desempenho das consultas e exigir mais armazenamento. Em alguns casos, realizar manutenções mais frequentes pode ser uma abordagem mais eficiente do que diminuir excessivamente o FILL FACTOR.
Para assegurar um desempenho eficiente, é essencial monitorar regularmente a fragmentação dos índices e realizar manutenções apropriadas.
Utilize a consulta a seguir para identificar índices que podem necessitar de manutenção:
SELECT
object_name(ips.object_id) AS Tabela,
i.name AS Indice,
i.fill_factor AS FillFactor,
ips.avg_fragmentation_in_percent AS Fragmentacao
FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS ips
INNER JOIN
sys.indexes AS i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE
i.fill_factor BETWEEN 1 AND 99
ORDER BY
ips.avg_fragmentation_in_percent DESC;
A escolha de reorganizar (REORGANIZE
) ou reconstruir (REBUILD
) um índice depende do nível de fragmentação observado:
Reorganizar Índices (REORGANIZE):
ALTER INDEX IX_Nome ON dbo.Clientes REORGANIZE;
Ideal para fragmentações leves a moderadas (abaixo de 30%). Menos intensivo e pode ser realizado com mais frequência.
Reconstruir Índices (REBUILD):
ALTER INDEX IX_Nome ON dbo.Clientes REBUILD;
Recomendado para fragmentações mais elevadas (acima de 30%). Mais intensivo, reconstrói o índice do zero.
Realizar a manutenção dos índices, especialmente a reconstrução, pode ser uma operação intensiva em recursos. É recomendável programá-la para períodos de baixa atividade. Monitore o desempenho do banco de dados após a manutenção para garantir que as operações não afetem negativamente a usabilidade.
A manutenção eficiente de índices é vital para o desempenho do SQL Server. Com o uso de scripts, podemos automatizar esse processo, focando em índices com um FILL FACTOR específico.
Este script verifica o FILL FACTOR de cada índice e executa REORGANIZE
ou REBUILD
com base no nível de fragmentação:
DECLARE @NomeTabela NVARCHAR(255);
DECLARE @NomeIndice NVARCHAR(255);
DECLARE @Fragmentacao FLOAT;
DECLARE @FillFactor INT;
DECLARE @Comando NVARCHAR(1000);
DECLARE MeuCursor CURSOR FOR
SELECT
DB_NAME() AS DatabaseName,
ss.name + '.' + so.name AS TableName,
si.name AS IndexName,
si.fill_factor,
ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS ips
INNER JOIN sys.indexes AS si ON ips.object_id = si.object_id AND ips.index_id = si.index_id
INNER JOIN sys.objects so ON si.object_id = so.object_id
INNER JOIN sys.schemas ss ON so.schema_id = ss.schema_id
WHERE si.name IS NOT NULL AND so.type = 'U' AND si.fill_factor BETWEEN 1 AND 99;
OPEN MeuCursor;
FETCH NEXT FROM MeuCursor INTO @NomeTabela, @NomeIndice, @FillFactor, @Fragmentacao;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Fragmentacao < 30
BEGIN
SET @Comando = 'ALTER INDEX [' + @NomeIndice + '] ON [' + @NomeTabela + '] REORGANIZE;';
END
ELSE
BEGIN
SET @Comando = 'ALTER INDEX [' + @NomeIndice + '] ON [' + @NomeTabela + '] REBUILD;';
END
EXEC sp_executesql @Comando;
FETCH NEXT FROM MeuCursor INTO @NomeTabela, @NomeIndice, @FillFactor, @Fragmentacao;
END
CLOSE MeuCursor;
DEALLOCATE MeuCursor;
Este script fornece uma forma eficiente de manter os índices, focando nos que possuem um FILL FACTOR definido entre 1% e 99%.
O script deve ser testado em um ambiente de desenvolvimento antes de ser implementado em produção. Para a manutenção regular, ele pode ser agendado para execução durante períodos de baixa atividade.
Neste tutorial, abordamos aspectos cruciais relacionados ao FILL FACTOR em índices no SQL Server, desde a compreensão do conceito até a implementação prática, incluindo estratégias para o dimensionamento eficaz e a manutenção dos índices. A gestão adequada do FILL FACTOR e a manutenção regular dos índices são essenciais para manter um desempenho otimizado do banco de dados.