O paradigma FillFactor – Parte 02

Na primeira parte do artigo vimos como funciona internamente quando trabalhamos com um fillfactor de 100%. Hoje mostrarei o que acontece se usarmos um fillfactor baixo, de 30%, por exemplo. E garanto que neste caso não veremos “Page Spliiiiiiiiits”.

A verdade com 30% de FillFactor

Digamos que John , como um bom amigo pensou: “Bom, eu conheço a mala do Luca há muito tempo e sei que ele vai mudar de ideia. Vou deixar um espaço em cada carro, caso ele queira levar mais alguém também”.

E isto aconteceu, no último minuto Luca chegou. E trouxe sua amiga, Jana. Como John deixou um espaço livre em cada carro, não houve problema em acomodar todo mundo, e todos curtiram o final de semana!

Uma coisa já vemos de cara: o número de carros aumentou com o mesmo número de pessoas que iam (antes do Luca e Jana).

Vamos usar o (script 1) para vermos o que acontece , mas alterando o FillFactor para 30%.

if object_id('Testfillfactor') is not null

    drop table Testfillfactor

    go


create table Testfillfactor    (    id int not null,    

                                    name char(2000)

                                )

create unique clustered index  PK_TestFill on Testfillfactor(id)


WITH(    PAD_INDEX = OFF,

        FILLFACTOR = 30,

        STATISTICS_NORECOMPUTE = OFF,

        IGNORE_DUP_KEY = OFF,

        ALLOW_ROW_LOCKS = ON,

        ALLOW_PAGE_LOCKS = ON

    )

ON [PRIMARY]

go


set nocount on

declare @loop int

set @loop = 1

while @loop < 11

begin

    insert into TestFillfactor values (@loop,'Name ' + convert(char(10),@loop))

    set @loop = @loop + 2

end 

go


dbcc ind(dba, Testfillfactor, 1, 0)

go

alter index PK_TestFill on Testfillfactor Rebuild

E o que a figura abaixo nos diz, sem pensar muito?

Uma página de dados foi criada, mas com o mesmo número de linhas com o FillFactor de 100%

Como podemos ver:

Três Páginas de Dados (Data Pages) Coluna PageType = 1, que são as páginas 7430,7406 e 7408;
Uma Página de Índice (Index Page) Coluna Pagetype = 2, que é a página 7407;
Uma IAM Page – Coluna PageType = 10, que é a página 7431.

Nosso foco são as páginas de dados (Data Pages)

01. Página 7430

Próxima Página de dados (NextPageID) = 7406
Página de Dados Anterior (PrevPageID) = 0

02. Página 7406

Próxima Página de dados (NextPageID) = 7408
Página de Dados Anterior (PrevPageID) = 7430

03. Página 7408

Próxima Página de dados (NextPageID) = 0
Página de Dados Anterior (PrevPageID) = 7406

Com eu criei a coluna fixa (char 2000) devemos ter na página 7430 duas linhas , que seriam o ID 3 e 5. Lembre-se que o índice cluster
sobre o ID.

Verifcando:

dbcc page( 0, 1, 7430, 3)

Como a coluna m_slotCnt me informa o número de linhas que esta página tem (2) e a coluna m_freeCnt o número de bytes livres na página, minha conta novamente está correta. E outra, a página 7406, terá os IDs 5 e 7, e a página 7408 o ID 9.

Podemos executar este script para visualizar esta afirmação:

dbcc traceon( 3604 )       
go
create table #dbccpageresults (    ParentObject varchar(max),
                                [Object] varchar(max),
                                [Field] varchar(max),
                                [Value] varchar(max)
                              )   
insert into #dbccpageresults exec ('dbcc page( 0, 1, 7430, 3 ) with tableresults')                                
go
select 'PAGE 7430',* from #dbccpageresults where [field] = 'id'
go
truncate table #dbccpageresults
go
insert into #dbccpageresults exec ('dbcc page( 0, 1, 7406, 3 ) with tableresults')   
go
select 'PAGE 7406',* from #dbccpageresults where [field] = 'id'
go
truncate table #dbccpageresults
go
insert into #dbccpageresults exec ('dbcc page( 0, 1, 7408, 3 ) with tableresults')   
go
select 'PAGE 7408', * from #dbccpageresults where [field] = 'id'
go
drop table #dbccpageresults

Novamente vamos inserir mais 4 linhas pares :

Insert into Testfillfactor  values (2,'Name 2')
go
Insert into Testfillfactor  values (4,'Name 4')
go
Insert into Testfillfactor  values (6,'Name 6')
go
Insert into Testfillfactor  values (8,'Name 8')

E vamos rodar o script novamente :

dbcc traceon( 3604 )       
go
create table #dbccpageresults (    ParentObject varchar(max),
                        [Object] varchar(max),
                        [Field] varchar(max),
                        [Value] varchar(max)
                              )   
insert into #dbccpageresults exec ('dbcc page( 0, 1, 7430, 3 ) with tableresults')                                
go
select 'PAGE 7430',* from #dbccpageresults where [field] = 'id'
go
truncate table #dbccpageresults
go
insert into #dbccpageresults exec ('dbcc page( 0, 1, 7406, 3 ) with tableresults')   
go
select 'PAGE 7406',* from #dbccpageresults where [field] = 'id'
go
truncate table #dbccpageresults
go
insert into #dbccpageresults exec ('dbcc page( 0, 1, 7408, 3 ) with tableresults')   
go
select 'PAGE 7408', * from #dbccpageresults where [field] = 'id'
go
drop table #dbccpageresults

O resultado será :

As páginas estão contínuas, e o engine não precisa pular de uma página a outra para achar os dados.

Não temos Page Split!!!!

Será?

dbcc ind(dba, Testfillfactor, 1, 0)

O resultado é o mesmo! Vamos mais a fundo:

dbcc page( 0, 1, 7430, 1 )
go
dbcc page( 0, 1, 7406, 1 )
go
dbcc page( 0, 1, 7408, 1 )

E representando:

“Eu não vejo Page Spliiiiiiitttsssss”

Qual é a mágica?

Não tem mágica. É matemática pura. Temos espaço em cada página para acomodar os dados novos!

O Paradigma FillFactor III – Índice Cluster+Identity = 100% FillFactor sem Page Splits?

É comum encontrarmos em fóruns a seguinte pergunta (ou parecida com ela): “tenho um CIX (clustered index) no campo identity. Posso deixar com FillFactor de 100%?”. E a resposta tradicional: “Sim. Já que os dados são inseridos no final da página, não haverá page splits”.

A resposta correta na verdade é DEPENDE.

Depende dos tipos de colunas que terão sua tabela e, principalmente, se elas terão atualizações no tamanho do seu conteúdo (varchar, nvarchar, varbinary).

Page Split ocorre no update também.

Vamos lá?

if object_id('Testfillfactor') is not null
    drop table Testfillfactor
    go

create table Testfillfactor    (    id int identity(1,1) not null,    
                                    name1 varchar(1000),
                                    name2 varchar(1000),
                                    name3 varchar(1000),
                                    name4 varchar(1000),
                                    name5 varchar(1000),
                                    name6 varchar(1000),
                                    name7 varchar(1000),
                                    name8 varchar(1000),
                                )
create unique clustered index  PK_TestFill on Testfillfactor(id)

WITH(    PAD_INDEX = OFF,
        FILLFACTOR = 100,
        STATISTICS_NORECOMPUTE = OFF,
        IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS = ON,
        ALLOW_PAGE_LOCKS = ON
    )
ON [PRIMARY]
go

set nocount on
declare @loop int
set @loop = 1
while @loop < 50
begin
    insert into TestFillfactor(name1,name2,name3,name4,name5,name6,name7,name8)
    values ('Name 1 ' + convert(char(10),@loop),'Name 2 ' + convert(char(10),@loop),'Name 3 ' + convert(char(10),@loop),
    'Name 4 ' + convert(char(10),@loop),'Name 5 ' + convert(char(10),@loop),'Name 6 ' + convert(char(10),@loop),'Name 7 ' + convert(char(10),@loop),
    'Name 8 ' + convert(char(10),@loop))
    set @loop = @loop + 1
end 
go

Criamos uma tabela com tipos de dados variante. Reparem que os campos nome são varchar. Populamos com 50 linhas contínuas (identity , com CIX no campo ID). Teoricamente não haveria Pages Splits.

Será?

alter index PK_TestFill on Testfillfactor Rebuild
go
dbcc ind(dba, Testfillfactor, 1, 0)
go

Correto. Não vemos “Page SPLIIIIIIIIITTSSS”. MAS, vamos alterar o conteúdo dos campos nome?

update Testfillfactor set    name1 = REPLICATE('X',1000),
                            name2 = REPLICATE('X',1000),
                            name3 = REPLICATE('X',1000),
                            name4 = REPLICATE('X',1000),
                            name5 = REPLICATE('X',1000),
                            name6 = REPLICATE('X',1000),
                            name7 = REPLICATE('X',1000),
                            name8 = REPLICATE('X',1000)
go
dbcc ind(dba, Testfillfactor, 1, 0)

Vendo como ficou:

Uma CIVILIZAÇÂO DE PAGE SPLITS!

Certeza, Laerte? Apesar de o campo NextPageId já me mostrar isso, mas vamos ver:

Peguei a Página 7548. O próximo deveria ser a Página 7549.

create table #dbccpageresults (    ParentObject varchar(max),
                        [Object] varchar(max),
                        [Field] varchar(max),
                        [Value] varchar(max)
                      )   
insert into #dbccpageresults exec ('dbcc page( 0, 1, 7548, 3 ) with tableresults')   

select * from #dbccpageresults where [field] = 'id'
go
truncate table #dbccpageresults
go
insert into #dbccpageresults exec ('dbcc page( 0, 1, 7549, 3 ) with tableresults')   
go
select * from #dbccpageresults where [field] = 'id'
go
drop table #dbccpageresults

Errado!

Segundo o NextPageID, a Próxima Página é a 8174, que está lá embaixo e nem aparece na imagem por não caber!

Pegando as páginas 7548 e 8174:

create table #dbccpageresults (    ParentObject varchar(max),
                        [Object] varchar(max),
                        [Field] varchar(max),
                        [Value] varchar(max)
                      )   
insert into #dbccpageresults exec ('dbcc page( 0, 1, 7548, 3 ) with tableresults')   

select * from #dbccpageresults where [field] = 'id'
go
truncate table #dbccpageresults
go
insert into #dbccpageresults exec ('dbcc page( 0, 1, 8174, 3 ) with tableresults')   
go
select * from #dbccpageresults where [field] = 'id'
go
drop table #dbccpageresults

Agora sim! Ou seja da página 7548 pulou para a página 8174 para continuar a ordem do CIX (clustered index)!

Ou seja, a afirmação que só basta colocar o CIX no identity e pode deixar o FillFactor com 100% que não terá Page Splits é LENDA. Além do que, fazendo isso, se sua tabela tiver uma carga alta de inserts, você poderá criar um hotspot (muita inserção ao mesmo tempo).

Se sua tabela tem campos de tamanho variável (varchar, nvarchar, varbinary) e estes são altamente atualizados (seja com valores maiores ou menores) provavelmente você terá Page Split.

Se sua tabela tem campos de tamanho variável (varchar, nvarchar, varbinary) e estes NÃO são atualizados ou não tem, a colocação do CIX no identity e FillFactor de 100% parece ser mais viável.

O Paradigma FillFactor IV – Muito cuidado!

Espera, deixa eu ver se entendi. NESTE CASO:

Com FillFactor de 100% o Engine acomoda 4 linhas na página. Eu tenho um menor número de páginas e Page Split.
Com FillFactor de 30% o Engine acomoda 2 linhas na página. Eu tenho um número maior de páginas, mas não tenho Page Split.

Mas um número maior de páginas não quer dizer mais Logical Reads?

CORRETO!

Vamos ver se é verdade?

Primeiro, com 30% de FillFactor:

if object_id('Testfillfactor') is not null
    drop table Testfillfactor
    go

create table Testfillfactor    (    id int not null,     
                                    name char(2000) 
                                )
create unique clustered index  PK_TestFill on Testfillfactor(id) 

WITH(    PAD_INDEX = OFF, 
        FILLFACTOR = 30, 
        STATISTICS_NORECOMPUTE = OFF, 
        IGNORE_DUP_KEY = OFF, 
        ALLOW_ROW_LOCKS = ON, 
        ALLOW_PAGE_LOCKS = ON
    ) 
ON [PRIMARY]
Go
go 
alter index PK_TestFill on Testfillfactor Rebuild 
go
set nocount on
declare @loop int
set @loop = 1
while @loop < 10000
begin
    insert into TestFillfactor values (@loop,'Name ' + convert(char(10),@loop))
    set @loop = @loop + 1
end  
go 
set statistics io on
go
select ID from TestFillfactor where id = 9999
go
set statistics io off

Table
'Testfillfactor'. Scan count 1, logical reads 55, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.

Agora, com 100% de FillFactor:

if object_id('Testfillfactor') is not null
    drop table Testfillfactor
    go

create table Testfillfactor    (    id int not null,    
                                    name char(2000)
                                )
create unique clustered index  PK_TestFill on Testfillfactor(id)

WITH(    PAD_INDEX = OFF,
        FILLFACTOR = 100,
        STATISTICS_NORECOMPUTE = OFF,
        IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS = ON,
        ALLOW_PAGE_LOCKS = ON
    )
ON [PRIMARY]
go
alter index PK_TestFill on Testfillfactor Rebuild
go

set nocount on
declare @loop int
set @loop = 1
while @loop < 10000
begin
    insert into TestFillfactor values (@loop,'Name ' + convert(char(10),@loop))
    set @loop = @loop + 1
end 
go
set statistics io on
go
select ID from TestFillfactor where id = 9999
go
set statistics io off

Table 'Testfillfactor'. Scan count 1, logical reads 30, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

É verdade!

Baixo FillFactor –> Alto Page Reads
Alto FillFactor –> Baixo Page Reads

Se formos pela lógica:

Muito Insert, update = Baixo FillFactor
Muito Select = Alto FillFactor

Mas sabemos que ná prática não existe regra. Cada ambiente é diferente do outro e possui características próprias, seja pela regra de negócio envolvida, seja por limitação de infra (falta de janela de reindexação) ou alta disponibilidade.

Algumas vezes é interessante deixar um alto Fillfactor para uma tabela de muito insert e update, mas também esta tabela tem muito select, pois alimenta um relatório muito importante pra sua organização e é chamada muitas vezes ao dia… Neste caso, reindexar ONLINE (ou reorganizar) mais vezes este índice pode ser mais vantajoso.

Eu tenho certeza disso? Claro que não.

Mas eu faço algumas coisas que me ajudam na análise e definição da solução:

Analise a carga de seus índices separadamente (cada um deles tem uma caraterística diferente)
Identifique as consultas que usam as tabelas destes índices (DMVs são uma boa ajuda)
Converse com o pessoal de Negócios (analista de negócios) e entenda o “produto final” do uso destas consultas. Por exemplo, um relatório de vendas online por filial
Crie suas próprias métricas de controle e organização. Um baseline não serve somente para dados macros do servidor
Se o seu problema é um caso particular, crie um baseline para este processo. Como você poderá saber se melhorou ou ficou pior com seus ajustes se você não sabe com era o comportamento dele antes, durante e depois?
Entenda o Produto (SQL Server)

Nada melhor que você entender um pouco como funciona internamente o que está acontecendo. Isto lhe dará uma visão clara e objetiva para definir sua solução.

Exemplo de ótimas leituras:

Inside Microsoft SQL Server 2005: T-SQL Querying
Inside Microsoft SQL Server 2005: The Storage Engine
Inside Microsoft SQL Server 2005: Query Tuning and Optimization
E os mesmos livros no SQL SERVER 2008

Não tenha medo de dizer “eu não sei, mas vou ver e aprender”. Isso é muito melhor do que dizer “eu não sei… sempre foi assim… aprendi assim”. Na verdade você esta dizendo “eu não sei, não quero saber e não tenho a mínima vontade de aprender” e isso não soa bem.

Quando tiver todo controle da situação: teste. Teste muito. Canse de testar antes de dizer “o jeito de fazer é assim”. As pessoas esperam isso de você.

A decisão correta é “faça assim. Eu sei o que estou falando!”.

Conclusão

Vimos que o FillFactor pode ser o herói ou vilão da história.

Teste, implemente com cuidado.

Trate cada objeto e entidade do seu banco de dados como única e sendo assim o comportamento é diferente uma das outras.

E não olhe para Paradigmas. Especialmente em TI.

Até a próxima!

De 0 a 10, o quanto você recomendaria este artigo para um amigo?

Powered by: