Índice Cluster+identity = 100% fillfactor e sem Page Splits ?


 

Pessoal, vejo dizer bastante que para não gerar page splits, basta colocar o índice cluster num campo identity e pode deixar o fillfactor com 100%.

Verdade ?….

NÃO !!!!!!!!!!.

A resposta correta é…Depende dos tipos de colunas que terão sua tabela e principalmente se estas terão atualizações no tamanho do seu conteúdo. Page Splits ocorrem 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 50 linhas continuas (indentity, com CIX no campo ID). Teoricamente não teria de haver Page Splits.

Será ?

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

 

11

PERFEITO NÃO VEJO “PAGE SPLIIIIIIIIIIIIIIITSSSSSSSSSSSSS”

Vamos alterar o conteúdo dos campos ?

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)

 

Vamos ver como ficou ?

12

Bom..Uma civilização de Page Splits !!!!

Certeza Laerte ?…Apesar do 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

 

13

Errado !!!!!

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

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

 

18

Agora sim!!!!!!!…. 

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

CONCLUSÃO ……

LENDA !!!!!..

 

A colocação do identity no índice cluster e FILLFACTOR 100%:


  • sua tabela tem campos de tamanho variável (varchar,nvarchar,varbinary) e estes são altamente atualizados (seja com valores maiores ou menores)

PAGE SPLIT NA CERTA !!!!!
 
  • sua tabela tem campos de tamanho variável (varchar,nvarchar,varbinary) e estes NÃO são atualizados
A colocação do identity no CIX PODE ser uma boa !!!

Não é uma regra que se eu colocar o CIX num campo identity posso deixar meu fillfactor a 100% pois não terei Page Split.

Vimos que em alguns casos o update também pode gerar Page Splits !!!!

A Laerte, mas no exemplo tu preencheu os campos com o valor completo deles.

Se por acaso você tiver 1 byte disponível na pagina e der um update pra 2 bytes..Já era..Page Split tio !!! (fillfactor 100%) Este registro passará inteiro para outra página e o espaço remanescente desta página será perdido!!!!

E Tô ouvindo AC/DC

“Jailbreak, let me out of here
Jailbreak, sixteen years ah
Jailbreak, had more than I can take
Jailbreak, yeah”

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.

2 Responses to Índice Cluster+identity = 100% fillfactor e sem Page Splits ?

  1. Felipe says:

    MTO bem lembrado Laerte!! gostei do artigo… abraço

  2. Laerte says:

    Grande Felipe !!! Valeu cara !!!

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