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

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),
[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

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')
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

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