Fragmentação em HEAPS

Posted on janeiro 25, 2013

3


Introdução

Em tabelas Heaps, durante operações de DML (Insert, Update, Delete) os registros são inseridos como em uma tabela cluster contendo um campo IDENTITY, ou seja, os registros são colocados em ordem de chegada de forma ordenada, se não houver operações de DML, tabelas heaps praticamente não sofrerão de problemas de fragmentação. Porém para esses tipos de operações, as tabelas heaps, sofrem de um grande problema. Quando os registros não cabem mais ná pagina de dados os mesmos se comportam de uma forma diferente do que em tabelas contendo índice cluster. Quando a página necessita crescer o FORWARDING POINTER é usado ao ínves de realizar uma divisão (SPLIT) da página como em um índice cluster.

Fowarding Pointer

Quando há uma operação de DML update por exemplo, primeiramente a engine irá tentar colocar todos os novos registros dentro da mesma página, porém se o valor inserido for maior e não houver mais espaço na mesma página, e engine não irá realizar um PAGE SPLIT, mais sim irá criar uma nova página de dados para inserir esses novos registros. Com isso os registros são colocados em uma nova página e a mesma recebe um RID (Row Identifier) uma identificação, com isso a página anterior irá receber um forwarding pointer para essa nova página. Essa operação requer um grande uso de I/O, para cada registro que está na página o SQL Server terá que ir na nova página e dépois retornar para a anterior.

O grande motivo do forwarding pointer ocorrer é em relação a performance de  índices nonclustered em tabelas heaps, isso porque no índice não será necessário alterar a localização desses novos registros, o fowarding pointer já faz esse trabalho. Quando a consulta utiliza o índice nonclustered para satisfazer a consulta, mais necessita de mais colunas para serem retornadas no result set o mesmo irá em uma tabela chamada record locator que é armazenado no próprio índice noncluster, se a tabela for heap então essa mesma tabela é a localização física do registro na heap.

Se a tabela for um Cluster Index então o record locator será as Cluster Keys ou seja elas que garatem a unicidade durante a pesquisa. Agora se a tabela for heap então como dito anteriormente a mesma será a localização física e o nome dessa operação é mais conhecida como bookmark lookup.

 

Analisando……

 

USE tempdb

go

 

 

CREATE TABLE DadosFragmentationHeap

(

       ID UNIQUEIDENTIFIER CONSTRAINT PK_DadosFragmentationHeap_ID PRIMARY KEY NONCLUSTERED,

       Dados VARCHAR(3000) NULL

)

GO

 

INSERT INTO DadosFragmentationHeap

SELECT NEWID(), ‘Luan Moreno’

GO 100

 

SELECT  avg_fragmentation_in_percent AS avgFragPct ,

        fragment_count AS fragCount ,

        avg_fragment_size_in_pages AS avgFragSize,

             forwarded_record_count AS forwardPointers

FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ‘DETAILED’)

WHERE   index_type_desc = ‘HEAP’

        AND index_level = 0

        AND OBJECT_NAME(object_id) = ‘DadosFragmentationHeap’

image

(Figura 1 – Nível de Fragmentação da Tabela Heap.)

 

Agora se realizarmos um update, veremos que a quantidade de fragmentação irá aumentar assim como a quantidade de forward pointers.

 

UPDATE DadosFragmentationHeap

       SET Dados = REPLICATE(‘a’,100)

 

SELECT  avg_fragmentation_in_percent AS avgFragPct ,

        fragment_count AS fragCount ,

        avg_fragment_size_in_pages AS avgFragSize,

             forwarded_record_count AS forwardPointers

FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ‘DETAILED’)

WHERE   index_type_desc = ‘HEAP’

        AND index_level = 0

        AND OBJECT_NAME(object_id) = ‘DadosFragmentationHeap’

image

(Figura 2 – Aumento no Nível de Fragmentação da Tabela Heap.)

 

Forwarding Pointer Internals

Consequentemente quando realizei esse update, os registros não couberam mas nás paginas,por isso foi necessário que a engine tivesse que realizar a criação de novas páginas sendo assim gerou uma grande quantidade de Forward Pointers. Agora iremos entender realmente como a engine do banco realiza a busca das informações dentro da página de dados. Utilizando o DBCC IND – http://www.mssqltips.com/sqlservertip/1578/using-dbcc-page-to-examine-sql-server-table-and-index-data/ veremos a estrutura criada.

 

DBCC IND (‘tempdb’, ‘DadosFragmentationHeap’, 1)

image

(Figura 3 – Página de Dados da Tabela.)

 

Temos as páginas de controle da tabela heap e do índice não-cluster, além de sabermos qual a primeira página de dados que é a PAGEPID – 168, navegando na mesma iremos ver os registros inseridos anteriormente, além de possuir um forwarding pointer indicando para outra página. O interresante é que ele ainda nos fala para qual página o registro está, sendo assim se formos para a página na qual ele nos disse veremos que….

 

DBCC TRACEON (3604)

DBCC PAGE (‘tempdb’, 1, 168, 3);

Slot 0 Offset 0x60 Length 9

Record Type = FORWARDING_STUB       Record Attributes =                 Record Size = 9

Memory Dump @0x000000000FD4A060

0000000000000000:   04ae0000 00010004 00                          .®…….
Forwarding to  =  file 1 page 174 slot 4
                                

 

–Forwarding to  =  file 1 page 174 slot 4                              

 

DBCC PAGE (‘tempdb’, 1, 174, 3);

Slot 4 Offset 0x8cc Length 539

Record Type = FORWARDED_RECORD      Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 539                  
Memory Dump @0x000000000FD4A8CC

0000000000000000:   32001400 594c4726 0627d342 8b151ee4 1e065942  2…YLG&.’ÓB‹..ä..YB
0000000000000014:   02000002 0011021b 82616161 61616161 61616161  ……..‚aaaaaaaaaaa
0000000000000028:   61616161 61616161 61616161 61616161 61616161  aaaaaaaaaaaaaaaaaaaa
000000000000003C:   61616161 61616161 61616161 61616161 61616161  aaaaaaaaaaaaaaaaaaaa
0000000000000050:   61616161 61616161 61616161 61616161 61616161  aaaaaaaaaaaaaaaaaaaa
0000000000000064:   61616161 61616161 61616161 61616161 61616161  aaaaaaaaaaaaaaaaaaaa
0000000000000078:   61616161 61616161 61616161 61616161 61616161  aaaaaaaaaaaaaaaaaaaa
000000000000008C:   61616161 61616161 61616161 61616161 61616161  aaaaaaaaaaaaaaaaaaaa
00000000000000A0:   61616161 61616161 61616161 61616161 61616161  aaaaaaaaaaaaaaaaaaaa
00000000000000B4:   61616161 61616161 61616161 61616161 61616161  aaaaaaaaaaaaaaaaaaaa
00000000000000C8:   61616161 61616161 61616161 61616161 61616161  aaaaaaaaaaaaaaaaaaaa
00000000000000DC:   61616161 61616161 61616161 61616161 61616161  aaaaaaaaaaaaaaaaaaaa
00000000000000F0:   61616161 61616161 61616161 61616161 61616161  aaaaaaaaaaaaaaaaaaaa
0000000000000104:   61616161 61616161 61616161 61616161 61616161  aaaaaaaaaaaaaaaaaaaa
0000000000000118:   61616161 61616161 61616161 61616161 61616161  aaaaaaaaaaaaaaaaaaaa
000000000000012C:   61616161 61616161 61616161 61616161 61616161  aaaaaaaaaaaaaaaaaaaa
0000000000000140:   61616161 61616161 61616161 61616161 61616161  aaaaaaaaaaaaaaaaaaaa
0000000000000154:   61616161 61616161 61616161 61616161 61616161  aaaaaaaaaaaaaaaaaaaa
0000000000000168:   61616161 61616161 61616161 61616161 61616161  aaaaaaaaaaaaaaaaaaaa
000000000000017C:   61616161 61616161 61616161 61616161 61616161  aaaaaaaaaaaaaaaaaaaa
0000000000000190:   61616161 61616161 61616161 61616161 61616161  aaaaaaaaaaaaaaaaaaaa
00000000000001A4:   61616161 61616161 61616161 61616161 61616161  aaaaaaaaaaaaaaaaaaaa
00000000000001B8:   61616161 61616161 61616161 61616161 61616161  aaaaaaaaaaaaaaaaaaaa
00000000000001CC:   61616161 61616161 61616161 61616161 61616161  aaaaaaaaaaaaaaaaaaaa
00000000000001E0:   61616161 61616161 61616161 61616161 61616161  aaaaaaaaaaaaaaaaaaaa
00000000000001F4:   61616161 61616161 61616161 61616161 61616161  aaaaaaaaaaaaaaaaaaaa
0000000000000208:   61616161 61616161 610004a8 00000001 000000    aaaaaaaaa..¨…….
Forwarded from  =  file 1 page 168 slot 0
 

Realmente quando navegamos até a página na qual foi indicado, no final vemos que a mesma tem um FORWARDED FROM = file 1 page 168 slot 0 que é exatamente a página anterior na qual estávamos, e com isso ele terá que voltar até a página anterior e continuar lendo até o próximo forwarded pointer e assim por diante.

 

SELECT  avg_fragmentation_in_percent AS avgFragPct ,

        fragment_count AS fragCount ,

             page_count,

        avg_fragment_size_in_pages AS avgFragSize,

             forwarded_record_count AS forwardPointers

FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ‘DETAILED’)

WHERE   index_type_desc = ‘HEAP’

        AND index_level = 0

        AND OBJECT_NAME(object_id) = ‘DadosFragmentationHeap’

 

 

image

(Figura 4 – Quantidade de Página de Dados.)

 

Agora o que devemos realmente ter cuidado é que, por uma breve lógica como essa tabela possui a quantidade de 8 páginas o  logico é que, quando fossemos realizar um TABLE SCAN a mesma deveria realizar 8 leitura lógicas não?

 

SET STATISTICS IO ON

SELECT  *

FROM DadosFragmentationHeap

 

(100 row(s) affected)
Table ‘DadosFragmentationHeap’. Scan count 1, logical reads 95, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

Não, na verdade temos 95 leituras lógicas porque são 8 das páginas de dados e + 87 dos forwardPointes portanto temos um total de 95 leituras lógicas. Com isso temos sempre que nos atentar com a fragmentação de dados mesmo em tabelas heaps, agora podemos solucionar isso da seguinte e simples forma.

ALTER TABLE DadosFragmentationHeap REBUILD

 

SELECT  avg_fragmentation_in_percent AS avgFragPct ,

        fragment_count AS fragCount ,

             page_count,

        avg_fragment_size_in_pages AS avgFragSize,

             forwarded_record_count AS forwardPointers

FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ‘DETAILED’)

WHERE   index_type_desc = ‘HEAP’

        AND index_level = 0

        AND OBJECT_NAME(object_id) = ‘DadosFragmentationHeap’

image

(Figura 5 – Quantidade de Página após Rebuild.)

(100 row(s) affected)
Table ‘DadosFragmentationHeap’. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Agora temos somente 7 leituras lógicas no banco de dados. Portanto nunca se esqueça de implementar e cuidar muito bem da sua rotina de fragmentação de índices até porque você não vai querer realizar diversas leituras desnecessárias, isso irá causar um grande aumento de memoria dentro do seu servidor de banco de dados.