FILLFACTOR é uma boa opção para evitar divisões de páginas com alto custo em um índice. Infelizmente, o uso do FILLFACTOR é mais frequentemente comparado como no uso de um "Jarro de Água" - o mesmo FILLFACTOR será usado em cada índice. Tal uso pode levar à desvantagens quando o FILLFACTOR deixa espaços na página de dados que nunca serão usados novamente. Este artigo irá demonstrar os efeitos negativos do armazenamento de dados e do Buffer Pool com a implementação errada do FILLFACTOR.


Ambiente de Testes

Para todas às análises à seguir, usaremos às seguintes estruturas de tabelas:

CREATE TABLE dbo.OrderTypes
(
    Id    tinyint    NOT NULL    identity (1,1),
    c1    char(20)   NOT NULL,
 
    CONSTRAINT pk_OrderTypes PRIMARY KEY CLUSTERED (Id)
);
GO
 
CREATE TABLE dbo.Orders
(
    OrderId     int              NOT NULL    IDENTITY (1, 1),
    OrderTypeId tinyint   NOT NULL,
    c1          char(200) NOT NULL    DEFAULT ('some stuff'),
      -- ... e muitos outros possíveis atributos
    OrderDate   date        NOT NULL,
 
    CONSTRAINT pk_Orders PRIMARY KEY CLUSTERED (OrderId),
    CONSTRAINT fk_OrderTypeId FOREIGN KEY (OrderTypeId)
    REFERENCES dbo.OrderTypes(Id)
);
GO

O script acima cria uma tabela de referência [dbo].[OrderTypes] que detém às diferentes categorias para determinadas ordens. A tabela [dbo].[Pedidos] vai usar essa relação como uma referência para a definição da ordem colocada. Além disso - e muito importante - é o fato de que seu índice cluster é um valor crescente constante, porque o atributo [OrderId] tem valores contínuos usando IDENTITY como valor automático.        

Um equívoco comum é que o Microsoft SQL Server irá criar um índice quando uma restrição FOREIGN KEY é criada. Isso não é verdade como é demonstrado no script à seguir:

SELECT  index_id,
     name,
     type_desc
FROM    sys.indexes
WHERE   object_id = OBJECT_ID('dbo.Orders');

Como a imagem à seguir vai mostrar, é definido só o índice clustered único para à tabela disponibilizada dbo.Orders .
         



Assim, um índice adicional [ix_Orders_OrderTypeId] será criado para um melhor suporte das consultas que utilizam o operador JOIN.
       
CREATE NONCLUSTERED INDEX  ix_Orders_OrderTypeId ON dbo.Orders(OrderTypeId);
Neste cenário, haverá apenas alguns tipos de ordens diferentes, então apenas 4 registros serão inseridos na tabela de referência [dbo].[OrderTypes].
INSERT INTO  dbo.OrderTypes (c1)
VALUES  ('online order'),
    ('shop sales'),
    ('staff order'),
    ('others');

Depois que os dados de referência forem inseridos, o seguinte código vai inserir 10.000 registros de ordem com os "Tipos de Ordem" aleatória.
       
SET NOCOUNT ON;
GO
 
DECLARE @i int = 1;
WHILE @i <= 10000
BEGIN
    INSERT INTO  dbo.Orders (OrderTypeId, c1, OrderDate)
    VALUES  (
            CAST(RAND() * 4 + 1 AS  int),
            'Order: ' + CAST(@i AS  varchar(10)),
            DATEADD(dd, -CAST(RAND() * 365 + 1 AS  int), GETDATE())
        );
 
    SET @i += 1;
END
GO

Análise das Estatísticas de índices físicos

Depois que todos os dados foram inseridos, o próximo passo mostra às estatísticas físicas dos índices em [dbo].[Orders].

SELECT  i.name,
    i.type_desc,
    ps.page_count,
    ps.record_count,
    ps.avg_page_space_used_in_percent
FROM    sys.indexes i INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ps
    ON (
        i.object_id = ps.object_id AND
        i.index_id = ps.index_id
       )
WHERE   i.object_id = OBJECT_ID('dbo.Orders', 'U' AND
    ps.index_level = 0;

Enquanto o índice cluster está em uma condição perfeita por causa dos valores contínuos para à chave clusterizada, o índice sobre o atributo da foreign key[OrderTypeId] está em uma condição ruim. Ele consome apenas 65% das páginas de dados. Então, uma idéia inicial de um DBA deve ser o uso de FILLFACTOR para à prevenção de divisões de página para esse índice.


Um Split de Página em Detalhes

Um Split de uma página é uma operação custosa para execução do motor de banco de dados do Microsoft SQL Server. A divisão da página ocorre quando novos dados são inseridos em uma página de índice e não há mais espaço na página. Naquele momento, o Microsoft SQL Server precisa executar os seguintes - internos - processos para dar espaço para esses novos dados:

  • Criar uma nova página de dados
  • Atualizar o objeto de metadados sobre à inclusão da nova página (indicando à nova página adicionada)
  • Formata a nova página de dados
  • COPIA metade dos dados à partir da página de dados original para a nova página de dados
  • Exclui os dados anteriormente copiados à partir da página de dados original
  • Atualiza às informações de cabeçalho da página sobre à próxima página (página original)
  • Atualiza às informações de cabeçalho da página sobre a página anterior (antiga "próxima página")

Todas essas etapas operacionais consomem uma enorme quantidade de log de transações e fazem esta operação extremamente custosa. Com FILLFACTOR como uma opção de um REBUILD deste índice, essas divisões de página custosas podem ser reduzidas através do preenchimento de uma página de dados só até um percentual específico (por exemplo, 80%). Tal operação vai deixar 20% livre para novos dados até próximo de encher a página, assim novos dados podem ser inseridos sem quaisquer divisões de página custosas. Em relação à este comportamento, o índice fragmentado [ix_Orders_OrderTypeId] será reconstruído usando apenas 80% do armazenamento disponível em uma página.

ALTER INDEX ix_Orders_OrderTypeId REBUILD WITH (FILLFACTOR = 80);

        

Após à operação de reconstrução do índice a densidade média das páginas será de aproximadamente 75%. A situação pode não ser crítica neste contexto - mas isso não é verdade. Através desta operação, o DBA gerou uma quantidade enorme de armazenamento desperdiçado.


Um olhar para a localização física dos dados de índice

Para entender por que esta operação não diminuirá os problemas com a página dividida, vamos mergulhar mais profundamente nas páginas de dados é necessárias. Antes da investigar, à primeira informação que é necessária é o histograma dos diferentes valores (de 1 à 4) no próprio índice.

DBCC SHOW_STATISTICS ('dbo.Orders', 'ix_Orders_OrderTypeId') WITH  HISTOGRAM;

O quadro acima mostra à distribuição das chaves de índice. Às vendas da Loja são dominadas pelos negócios diários com mais de 3.500 entradas de índice. O próximo comando SQL mostra o resultado da localização física de cada registro no índice. Para uma melhorar à visão geral, apenas um trecho do resultado será exibido após o comando.
       
SELECT  sys.fn_PhysLocFormatter(%%physloc%%) AS Location, *
FROM    dbo.Orders WITH (INDEX = 2)
WHERE   OrderTypeId = 2;

A declaração acima retorna todos os registros com o OrderTypeId = 2 a localização física dos valores de chave de índice. O ponto mais importante de nosso interesse são os extremos dos dados apresentados. A próxima imagem mostram o ponto inicial e a segunda imagem mostra à última entrada no índice para OrderTypeId = 2 (estes valores podem ser diferentes se você repetir esta demo!)



As imagens acima mostram o início do valor da chave 2 para o índice [ix_Orders_OrderTypeId] na página 94 e slot 16, enquanto à última entrada do índice de valor de chave 2 é a posição física 400, no slot 338. Supondo que o aplicamos 800 registros dentro de uma página das fotos acima, então temos o seguinte cenário:
  • na página 94, o valor de chave de índice = 1 está localizado na Slots 0 - 15
  • na página 400 o próximo valor de chave de índice = 3 começa no slot de 339
  • todas as outras páginas intermediárias são completamente preenchidas com o valor da chave de índice = 2

Assim - dependendo da afirmação acima - às páginas terão à seguinte estrutura interna:



 A imagem mostra 20% de espaço livre em cada página. Enquanto à página 94 só pode adicionar novas entradas no índice dependendo do valor da chave do índice = 1, nas páginas 109, 120 e 121 não podem ter novas entradas, embora temos 20% de espaço livre disponível. A página 400 pode ter novos registros de índice para a chave de índice = 2 e a chave de índice = 3. O ponto problemático para o cenário acima é o aumento do valor para à chave de índice clusterizado! Cada índice NONCLUSTERED deve armazenar à chave de índice agrupando como referência à todos os dados da tabela. Vamos olhar para esta árvore binária (neste exemplo, na página 90) a página que gerencia às páginas exibidas mostram o seguinte:
DBCC TRACEON (3604);
DBCC PAGE ('db_demo', 1, 90, 3);



A imagem acima mostra perfeitamente à hierarquia dos dados armazenados. A chave clusterizada [OrderId] será armazenada com cada chave de índice único como referência.

Cenário para uma nova inclusão na tabela Orders

Se um novo registro é inserido há três possibilidades que podem ocorrer:

  • Um novo registo com OrderTypeId = 1 será inserido na tabela. O registro no índice [ix_Orders_OrderTypeId] será inserido na página 94, porque o valor da chave 2 é antes do 3 e do novo valor para o índice agrupado (valor contínuo) será 10.001. Devido ao fato de que à página 94 tem 20% de espaço livre a transação não vai executar uma divisão de página.
  • Um novo registo com o OrderTypeId = 2 será inserido na tabela. O registro no índice [ix_Orders_OrderTypeId] será inserido na página 400 porque a página 400 cobre os últimos registros para o OrderTypeId = 2. Como a chave de índice agrupado é um valor contínuo, o registro será inserido sem dividir à página, porque 20% da página de dados está livre.
  • Um novo registo com o OrderTypeId = 3 será inserido na tabela. O registro no índice [ix_Orders_OrderTypeId] será inserido na última página do índice que armazena o último registro do índice para OrderTypeId = 3, a página 400 não será tocada. Ela só pode armazenar novas Ordens com OrderTypeId = 2!

O cenário acima demonstra agora o problema que ocorre com todas as páginas entre os extremos dos valores dados para um índice. Em relação ao modelo de página acima, às páginas 190, 121 e 122 nunca vão armazenar quaisquer dados adicionais, porque à chave de índice clusterizado será sempre aumentar, mas nunca diminuir.


Conclusão

FILLFACTOR é uma boa ação corretiva para à fragmentação de páginas. Mas, se a chave de índice é um composto de um valor que não tem grande variância um FILLFACTOR terá um efeito secundário ruim para o armazenamento utilizado nas páginas de dados. Cada vez que um FILLFACTOR é levado em consideração tem que ser provada para os índices NONCLUSTERED e para chaves agrupadas contínuas. Se em ambas às condições são conhecidas, o FILLFACTOR não é recomendado, no lugar de uma reconstrução de índice em um intervalo menor!


Veja Também


Outros Idiomas