O Paradigma FILLFACTOR


 

Pessoal,

Tirei os artigos de baixo de FillFactor e coloquei em um só. Desculpe as imagens em inglês, é que o original foi pro Simple-Talk.

O Paradigma

Um grupo de cientistas colocou cinco macacos numa jaula, em cujo centro colocaram uma escada e, sobre ela, um cacho de bananas.
Quando um macaco subia a escada para apanhar as bananas, os cientistas lançavam um jato de água fria nos que estavam no chão.
Depois de certo tempo, quando um macaco ia subir a escada, os outros o enchiam de pancada.
Passado mais algum tempo, mais nenhum macaco subia a escada, apesar da tentação das bananas.
Então, os cientistas substituíram um dos cinco macacos.
A primeira coisa que ele fez foi subir a escada, dela sendo rapidamente retirado pelos outros, que lhe bateram.
Depois de algumas surras, o novo integrante do grupo não subia mais a escada.
Um segundo foi substituído, e o mesmo ocorreu, tendo o primeiro substituto participado, com entusiasmo, na surra ao novato.
Um terceiro foi trocado, e repetiu-se o fato.
Um quarto e, finalmente, o último dos veteranos foi substituído.
Os cientistas ficaram, então, com um grupo de cinco macacos que, mesmo nunca tendo tomado um banho frio, continuavam a bater naquele que tentasse chegar às bananas.

 

Moral da História

Se fosse possível perguntar a algum deles porque batiam em quem tentasse subir a escada, com certeza a resposta seria:
Não sei, as coisas sempre foram assim por aqui…”

 

O Paradigma FillFactor – Início

 

Tive a oportunidade de participar em um projeto , na qual a empresa tinha um DBA Junior.
Em determinada análise que estávamos fazendo , um análise e reorganização dos índices com base na carga de cada um, surgiu o comentário :

 

“Essa tabela eu conheço é muito Insert
Pode colocar um Fillfactor baixo !!!”

“Porque ?” (perguntei eu)…alguns segundos de silencio !!!!
“Eu não sei, aprendi assim e todo mundo fala isso !!!”

 

O Paradigma FillFactor I – “A Existência do Page Split”

 

Continuando a minha pergunta, questionei-o sobre Page Split e minha resposta foi :
“Há isso eu sei, é fácil. É quando o SQL SERVER fica parecendo pipoca pulando de um lado para outro para achar os dados “

De uma certa maneira ele não estava errado e eu particularmente adorei o “pulando feito pipoca”, mas sinceramente essa era uma resposta que eu preferia não ter ouvido. Imagine se fosse alguém do Storage Team.

 

O que é Page Split ?

 

Nós sabemos que o SQL SERVER trabalha com páginas de 8kb, cada página possui um fator de preenchimento, chamado FillFactor, e que por default este fator é 0, ou seja, página totalmente preenchida e sem espaço livre para novos registros.

Quando um registro é inserido ou atualizado, esta linha tem que ser armazenada no espaço da página.

Se a página não possui espaço livre o SQL Server precisa reorganizar todas as páginas para armazenar o novo registro.

Mas estas novas páginas não são contínuas.

O FillFactor é aplicado em um índice quando este é criado ou reorganizado e informa o quanto cada página do nível folha será preenchida.

Page Split é um processo interno que o Engine usa para acomodar os dados novos (ou quando mudamos o tamanho da informação em um campo varchar por exemplo)

Este processo consome uma carga considerável de I/O, tendo em vista que o Engine tem que ler a IAM (Index allocation Map), alocar novas páginas, mover os dados na ordem correta to índice..etc

O FillFactor é mais efetivo no índice cluster, mas pode ser aplicado nos índices não cluster.

 

O Paradigma FillFactor II – “A verdade está lá dentro”

 

A verdade com 100% de FILLFACTOR

 

Simplificando :
John, Robert, Phil, Luca, Lucy, Chloe, Hannah and Julia decidiram ir a praia. John era o organizador e resolveu colocar homens em um carro e mulheres em outro.
Um dia antes da viagem, Luca resolveu não ir e Richard foi convidado.
O layout ficou parecido com esse :

 

clip_image002

 

No último minuto, Luca decidiu ir, mas não havia espaço no carro e ele teve que ir com o seu.

 

clip_image004

 

Se John quisesse falar com Luca, ele tinha que pular o carro das garotas e o pior, se ele quisesse falar com Lucy , ele tinha que pedir pro Luca pois ele era o único a ter o número do celular dela.
Se isto é custoso pára John, imagine pro Engine do SQL Server.
Na verdade , aqui temos um Page Split “praiano.”

 

Vamos ver o que acontece :

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
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
    (script 1)

     

    Este script nos gera um page split e o resultado é :
    clip_image005
     
    Como a gente pode ver :
    • Duas Páginas de Dados (Data Pages) – Coluna PageType = 1 que são as páginas 7412 e 7414
    • Uma Página de Índice (Index Page) – Coluna Pagetype = 2 que é a página 7426
    • Uma IAM Page – Coluna PageType = 10 que é a página 7413
        Nosso foco são as páginas de dados (Data Pages)
        1. Página 7412
            · Próxima Página de dados (NextPageID) = 7414
            · Página de Dados Anterior (PrevPageID) = 0
            1. Página 7414
                • Próxima Página de dados (NextPageID) = 0
                • · Página de Dados Anterior (PrevPageID) = 7412
              O que isso está me dizendo ?
              Olha Engine, para você achar a continuação dos dados da página 7412 vá para a página 7414 (NextPageID) e se quiser saber a pagina que contém os dados anteriores a página 7414 é a 7412 (PrevPageID)
              Como eu criei a tabela com um valor fixo (char 2000) deverá caber em na página 7412 quatro linhas , que devem ser o ID 1,3,5 e 7
              Para verificar usamos :

              dbcc page( 0, 1, 7412, 3)

              clip_image007
              Como a coluna m_slotCnt me informa o número de linhas que esta página tem (4) e a coluna m_freeCnt o número de bytes livres na página, minha conta está correta.

               

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

               

              create table #dbccpageresults ( ParentObject varchar(max),
              [Object] varchar(max),
              [Field] varchar(max),
              [Value] varchar(max)
              )
              insert into #dbccpageresults exec (‘dbcc page( 0, 1, 7412, 3 ) with tableresults’)
              select * from #dbccpageresults where [field] = ‘id’
              go
              drop table #dbccpageresults
              clip_image009
              Correto. Na página 7412 nós temos os ID´s 1,3,5 e 7

               

              Na página 7414 nós temos somente uma linha que é o ID 9 , sobrando espaço não pelo FillFactor e sim porque não tivemos dados suficientes para preenchê-la.

               

              Usando o script acima, podemos confirmar :
              clip_image011

               

              E representando :

               

              clip_image013

               

              Agora vamos inserir mais 4 linhas pares. (no script inserimos linhas ímpares). Lembre-se que o índice cluster é sobre o ID
               

              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′)
              go
              dbcc ind(dba, Testfillfactor, 1, 0)
              go
              clip_image015

               
              Como podemos ver, uma nova página de dados (Data Page) foi inserida e nós temos o Page Split.
              Isso me lembra o Haley Joel Osment no filme Sexto Sentido :

              “Eu Vejo Page Spliiiiiiiiitsssss !!!!”

              Perceba que pelas colunas NextPageID e PrevPageID, o Engine tem que ir da página 7412 para a 7427 e depois para a 7414 para completar o ciclo da informações.

              Representando :
              clip_image017

               

              Este “Pulando feito pipoca” é uma operação de I/O muito pesada pro Engine.
              Com isso verificamos como é a ocorrência de Page Split e internamente representada .

               

              A verdade com 30% de FILLFACTOR

               

              Digamos que John , como um bom amigo pensou :
              “Bom, eu conheço a mala do Luca a muito tempo e sei que ele vai mudar de idéia. 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 espaço livre em cada carro, não houve problema em acomodar a galera !!!

               

              clip_image019
               
              E todo mundo curtiu o Final de semana !!!
              Uma coisa já vemos de cara, o numero 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 ?

                 

                clip_image021
                Uma página de dados foi criada , mas com o mesmo número de linhas com o FillFactor de 100%
                Como a gente pode 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)
                    1. Página 7430
                        · Próxima Página de dados (NextPageID) = 7406
                        · Página de Dados Anterior (PrevPageID) = 0
                        1. Página 7406
                            · Próxima Página de dados (NextPageID) = 7408
                            · Página de Dados Anterior (PrevPageID) = 7430
                            1. 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)
                              clip_image023
                              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 ID´s 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
                              clip_image025
                              Ou :
                              clip_image027

                               

                              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á :

                               

                              clip_image029
                              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)
                              clip_image031
                              O resultado é o mesmo !!!!

                               

                              Vamos ir mais a fundo !!!

                              dbcc page( 0, 1, 7430, 1 )
                              go
                              dbcc page( 0, 1, 7406, 1 )
                              go
                              dbcc page( 0, 1, 7408, 1 )
                              clip_image033
                               
                              E representando !!!!

                               

                              clip_image035
                               
                              “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 – “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 Lógical Reads ?

                              CORRETO !!!!!

                              Vamos ver se é verdade ? (olhem a o resultado em vermelho Logical Reads !!!!)

                               

                              Primeiramente 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 é chamado 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  (DMV´s)
                                  • Converse com o pessoal de Negócios (analista de negócios) e entenda o “produto final“ do uso destas consultas. (ex – 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 ,aprender e a solução será dada no prazo”. 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 a se fazer é assim”

                                   

                                  As pessoas esperam isso de você. Decisão e 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.
                                   
                                   
                                   
                                  Laerte Poltronieri Junior
                                  $hell Your Experience !!!
                                  www.laertejuniordba.spaces.live.com
                                  www.simple-talk.com

                                  About Laerte Junior

                                  Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
                                  This entry was posted in SQL SERVER EM GERAL. Bookmark the permalink.

                                  Leave a Reply

                                  Fill in your details below or click an icon to log in:

                                  WordPress.com Logo

                                  You are commenting using your WordPress.com account. Log Out /  Change )

                                  Google photo

                                  You are commenting using your Google account. Log Out /  Change )

                                  Twitter picture

                                  You are commenting using your Twitter account. Log Out /  Change )

                                  Facebook photo

                                  You are commenting using your Facebook account. Log Out /  Change )

                                  Connecting to %s