O paradigma FillFactor – Parte 01

Um grupo de cientistas colocou cinco macacos numa jaula. No centro dela 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 e foi 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 por que 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

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

– Essa tabela eu conheço é muito Insert . Pode colocar um Fillfactor baixo!
– Por que?, perguntei eu.
– 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 a resposta foi:

“Ah, 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 e cada página possui um fator de preenchimento, chamado fillfactor. 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, este registro tem que ser armazenado 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 do í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 à 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 :

No último minuto, Luca decidiu ir, mas não havia espaço no carro e, como a ordem era carro dos homens primeiro e mulheres depois, ele teve que ir com o seu.

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 para 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<br />drop table Testfillfactor<br />go<br />create table Testfillfactor ( id int not null,<br />name char(2000)<br />)<br />create unique clustered index PK_TestFill on Testfillfactor(id)<br />WITH( PAD_INDEX = OFF,<br />FILLFACTOR = 100,<br />STATISTICS_NORECOMPUTE = OFF,<br />IGNORE_DUP_KEY = OFF,<br />ALLOW_ROW_LOCKS = ON,<br />ALLOW_PAGE_LOCKS = ON<br />)<br />ON [PRIMARY]<br />go<br />set nocount on<br />declare @loop int<br />set @loop = 1<br />while @loop < 11<br />begin<br />insert into TestFillfactor values (@loop,'Name ' + convert(char(10),@loop))<br />set @loop = @loop + 2<br />end<br />go<br />dbcc ind(dba, Testfillfactor, 1, 0)<br />go<br />alter index PK_TestFill on Testfillfactor Rebuild

Script 1

Este script nos gera um page split e o resultado é :

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)

01. Página 7412

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

02. Página 7414

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

Como eu criei a tabela com um valor fixo (char 2000), deverá caber 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)

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),<br />[Object] varchar(max),<br />[Field] varchar(max),<br />[Value] varchar(max)<br />)<br />insert into #dbccpageresults exec ('dbcc page( 0, 1, 7412, 3 ) with tableresults')<br />select * from #dbccpageresults where [field] = 'id'<br />go<br />drop table #dbccpageresults

Correto. Na página 7412 nós temos os IDs 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:

E representando:

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

Insert into Testfillfactor values (2,'Name 2')<br />go<br />Insert into Testfillfactor values (4,'Name 4')<br />go<br />Insert into Testfillfactor values (6,'Name 6')<br />go<br />Insert into Testfillfactor values (8,'Name 8')<br />go<br />dbcc ind(dba, Testfillfactor, 1, 0)<br />go

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:

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.

Na continuação deste artigo veremos como será se usarmos um fillfactor baixo, de 30%, por exemplo. Até lá.