Fala Pessoal,

Você que é um DBA, algum dia já deve ter feito essa pergunta para alguém ou para o google. A resposta deve ter sido que não existe uma receita de bolo para definir o valor do fillfactor de um índice, seu valor depende do ambiente ser OLTP (Online Transaction Processing) ou OLAP (Online Analytical Processing), da quantidade de INSERT, UPDATE e DELETE do índice, do tipo de dados da coluna chave do índice, dentre outros fatores.

Infelizmente também darei a mesma resposta, que não existe um valor de fillfactor ideal para todos os índices. Possuo índices que vão desde 75% de fillfactor até índices com 97%. Entretanto, vou mostrar uma query que utilizo para tentar aproximar cada um dos meus principais índices dos seus valores ideais.

No meu último post técnico mostrei como armazenar um histórico de fragmentação dos índices e é com essa informação que verifico o quanto cada índice se fragmenta individualmente para verificar se o fillfactor desse índice deve ser alterado para um valor diferente.

Para ilustrar essa situação vou popular minha tabela de histórico(criada no post anterior) com dados fictícios para dois índices.

–Segue novamente o script para a criação da tabela:

CREATE TABLE [dbo].[Hitorico_Fragmentacao_Indice](
[Id_Hitorico_Fragmentacao_Indice] [int] IDENTITY(1,1) NOT NULL,
[Dt_Referencia] [datetime] NULL,
[Nm_Servidor] [varchar](20) NULL,
[Nm_Database] [varchar](20) NULL,
[Nm_Tabela] [varchar](50) NULL,
[Nm_Indice] [varchar](70) NULL,
[Avg_Fragmentation_In_Percent] [numeric](5, 2) NULL,
[Page_Count] [int] NULL,
[Fill_Factor] [tinyint] NULL)
INSERT INTO Hitorico_Fragmentacao_Indice(Dt_Referencia,Nm_Servidor,
Nm_Database,Nm_Tabela,Nm_Indice,Avg_Fragmentation_In_Percent,
Page_Count,Fill_Factor)
select getdate()-3,’FABRICIONOTE’,’FabricioLima’,’Teste_Fragmentacao’,
‘SK01_Teste_Fragmentacao’,5.60,1500,90

INSERT INTO Hitorico_Fragmentacao_Indice(Dt_Referencia,
Nm_Servidor,Nm_Database,Nm_Tabela,Nm_Indice,Avg_Fragmentation_In_Percent, Page_Count,Fill_Factor)
select getdate()-2,’FABRICIONOTE’,’FabricioLima’,’Teste_Fragmentacao’,
‘SK01_Teste_Fragmentacao’, 8.67,1500,90

INSERT INTO Hitorico_Fragmentacao_Indice(Dt_Referencia,
Nm_Servidor,Nm_Database,Nm_Tabela,Nm_Indice,Avg_Fragmentation_In_Percent,
Page_Count,Fill_Factor)
select getdate()-1,’FABRICIONOTE’,’FabricioLima’,’Teste_Fragmentacao’,
‘SK01_Teste_Fragmentacao’,10.78,1500,90

INSERT INTO Hitorico_Fragmentacao_Indice(Dt_Referencia,
Nm_Servidor,Nm_Database,Nm_Tabela,Nm_Indice,Avg_Fragmentation_In_Percent,
Page_Count,Fill_Factor)
select getdate()-3,’FABRICIONOTE’,’FabricioLima’,’Teste_Fragmentacao’,
‘SK02_Teste_Fragmentacao’,15.60,1500,90

INSERT INTO Hitorico_Fragmentacao_Indice(Dt_Referencia,
Nm_Servidor,Nm_Database,Nm_Tabela,Nm_Indice,Avg_Fragmentation_In_Percent,
Page_Count,Fill_Factor)
select getdate()-2,’FABRICIONOTE’,’FabricioLima’,’Teste_Fragmentacao’,
‘SK02_Teste_Fragmentacao’,18.67,1500,90

INSERT INTO Hitorico_Fragmentacao_Indice(Dt_Referencia,
Nm_Servidor,Nm_Database,Nm_Tabela,Nm_Indice,Avg_Fragmentation_In_Percent,
Page_Count,Fill_Factor)
select getdate()-1,’FABRICIONOTE’,’FabricioLima’,’Teste_Fragmentacao’,
‘SK02_Teste_Fragmentacao’,23.78,1500,90

Verificando os valores que foram inseridos:

SELECT Dt_Referencia,Nm_Indice,Avg_Fragmentation_In_Percent Fragmentacao
FROM Hitorico_Fragmentacao_Indice
ORDER BY Dt_Referencia

Temos como resultado:

Como pode ser verificado no select acima, simulei uma possível fragmentação de dois índices durante 3 dias.

Com a query abaixo podemos verificar a fragmentação dos índices em um determinado período, como por exemplo, no ínicio do mês, no fim do mês, no mês inteiro, no ano todo e etc.

SELECT  A.Nm_Database, A.Nm_Tabela, A.Nm_Indice,
SUM(B.Avg_Fragmentation_In_Percent – A.Avg_Fragmentation_In_Percent) Total_Fragmentacao
FROM Hitorico_Fragmentacao_Indice A (nolock)
join Hitorico_Fragmentacao_Indice B (nolock) on A.Nm_Indice = B.Nm_Indice
and A.Nm_Tabela = B.Nm_Tabela and A.Nm_Database = B.Nm_Database
and A.Avg_Fragmentation_In_Percent < B.Avg_Fragmentation_In_Percent
and cast(floor(cast(A.Dt_Referencia+1 as float)) as datetime) = cast(floor(cast(B.Dt_Referencia as float)) as datetime)
WHERE  A.page_count > 1000 — Eliminar índices pequenos
and A.Dt_Referencia >= ‘20110223’ and A.Dt_Referencia < ‘20110227’
GROUP BY A.Nm_Database,A.Nm_Tabela,A.Nm_Indice
ORDER BY 4 DESC

Segue o resultado:

Vemos agora sumarizadamente que o índice SK02_Teste_Fragmentacao se fragmentou 8,18% e o índice SK01_Teste_Fragmentacao se fragmentou 5.18% durante 3 dias. Reparem que eu adicionei uma cláusula no join (A.Avg_Fragmentation_In_Percent < B.Avg_Fragmentation_In_Percent) para não incluir na query o dia que a fragmentação de um índice diminuir, que é o que acontece quando é realizado um REORGANIZE ou um REBUILD no índice.

Assim você identifica facilmente os índices que possuem uma maior fragmentação e pode alterar o fillfactor desses índices individualmente, ou aumentar a frequência com que esses índices são desfragmentado. Com esse procedimento também temos ganhos de performance e espaço em disco, pois identificamos os índices que pouco se fragmentam durante um certo período(1 mês por exemplo) e , com isso, esses índices podem ter seu fillfactor aumentado para economizar espaço em disco e aumentar o desempenho do ambiente.

Foi com essa rotina que alterei o valor do fillfactor de quase todos os índices das minhas principais tabelas, economizando um grande espaço em disco (mais de 20 GB) e aumentando a performance do meu banco de dados, pois quanto maior for o fillfactor de um índice, mais registros são armazenados em uma página desse índice, o que ocasiona um número menor de páginas no tamanho do mesmo, ocupando menos espaço na memória e aumentando o desempenho de todo o ambiente.

Também tive que diminiur o fillfactor de alguns índices para diminuir a fragmentação e também o número de page split das páginas desses índices.

Então, caso você seja responsável por um ambiente de banco de dados, monitorar a fragmentação dos seus índices pode te ajudar a ter um grande ganho de performance.

Gostou desse Post?

Cadastre seu e-mail para receber novos Posts e curta minha Página no Facebook para receber Dicas de Leituras e Eventos sobre SQL Server.

Abraços,

Fabrício Lima

MCITP – Database Administrator

Consultor e Instrutor SQL Server

Trabalha com SQL Server desde 2006