Page Compression Internals

Posted on agosto 12, 2011

3


 

Temos dois tipos de compressão de dados no SQL Server que são:

 

Data Compression

 

Nesse post falarei sobre Page Level Compression.

 

O principal intuito de se usar o Page Compression (Compressão de Página) é minimizar a redundância de dados. Sendo assim realizando esse tipo de compressão o SQL Server elimina dois tipos de redundâncias que são o Column-Prefix e Dictionary Compression.

 

Page Level Compression[4]

 

Quando a operação de Column-Prefix é realizada, O SQL Server realiza a procura de ‘Bytes’ que possam ser usados como Patterns (Padrão). Essa verificação independe do tipo de dado que esteja armazenado dentro da página, assim se tivermos dois valores representados por um hexadecimal como 0x020407FA e 0x0204CDFA, teremos o Pattern (Padrão)  = ‘0x0204’.  Agora se tivermos dois valores como 0xFFAABBCCDDEE e 0x33AABBCCDDEE nesse caso não teremos Pattern, isso porque o primeiro ’byte ‘ é diferente mesmo tendo os próximos bytes iguais é preferível que não seja realizada porque o balanço entre Overhead de CPU e Compression Savings não são significativos.

 

O Dictionary-Compression é específico para uma página e essa pode ser  de dados ou leaf-index ou seja, uma página de dados de nível folha de um índice. O Armazenamento se dá pelo mesmo ‘Byte Pattern’ do Column-Prefix, porém as entradas no dicionário são armazenadas em Arrays nos quais são representados como 0,1… e assim por diante. A ordem de funcionamento sempre será respeitada, teremos Column-Prefix e depois Dictionary Compression.

 

Quando a compressão de página é realizada, informações adicionais são adicionados ao cabeçalho da página esses novos dados possuem a nomenclatura de CI (Compression Information), ela contêm dois itens que são: Anchor Tag  Column Prefix Compression e um Array Dicionary-Compression.

 

A compressão é realizada em tabelas, partições de tabelas, índices e partições de índices, ela consiste em algumas operações, como:

 

  • Row Compression (Compressão de Linha)
  • Prefix Compression (Compressão de Prefixos)
  • Dictionary Compression (Compressão de Dicionário)

 

Row Compression

No caso de Page Compression a compressão de Row (Linha) acontece nos níveis não folhas de um índice ou seja Root e Intermediate. Para maiores informações sobre compressão de linhas procure em http://msdn.microsoft.com/en-us/library/cc280576.aspx.

 

Prefix Compression

Para cada página o Prefix Compression usa os seguintes passos:

 

Quando há valores repetidos na coluna,  ele usa para a redução de armazenamento na dentro da página.

As informações são armazenadas no CI (Compression Information), embaixo do header da página.

 

Os valores repetidos na coluna são trocados por uma referência do dado. Se o valor não for exatamente o mesmo, então um match parcial é realizado.

 

Temos a seguinte ilustração:

 

Page before prefix compression

 

Temos uma página na qual a compressão não foi ainda realizada.

 

Untitled[6]

 

Vamos analisar as 3 cores, para entendermos melhor o funcionamento do Column-Prefix. Nesse momento temos embaixo do header da página, informações referentes ao Compression Information(CI).

 

Marcação Preta.

Temos o valor 4b que seria 4 = (aaab)+b formando assim o valor mostrado na imagem.

 

Marcação Verde.

Temos também 4b, porém aqui ela é representada por outra combinação que é  4 = (aaaa)+b formando assim o mesmo  valor anterior.

 

Marcação Amarela.

Como o valor que foi armazenado no CI é abcd e o dado é o mesmo, então o mesmo é representado como Empty.

 

Dictionary Compression

Depois de todos os prefixos serem realizados em cada página, o dicionário é aplicado. O mesmo consiste em buscar por valores repetidos dentro da página e assim é armazenado na CI. Diferente do Prefix-Column que verifica somente uma coluna, o Dicionário realiza a verificação de todas as colunas dentro da tabela.

 

Page after dictionary compression

 

Os valores representados no Compression Information (CI) de diferentes colunas da página.

 

Vamos criar uma tabela que possua valores repetidos, assim conseguiremos visualizar melhor a explicação mostrada acima.

 

CREATE DATABASE CompressionDatabase

 

USE CompressionDatabase

GO

 

CREATE TABLE PageLevelCompression

(

      ID INT IDENTITY(1,1),

      Nome VARCHAR(50) NOT NULL,

      DataNascimento DATETIME2 NOT NULL

)

 

INSERT INTO PageLevelCompression (Nome, DataNascimento)

VALUES (‘Luan Maciel’,‘1988-07-20’),

         (‘Antonio Padua’,‘1960-02-01’),

         (‘Antonia de Padua’,‘1960-02-01’),

         (‘Luciano Moreira’,‘1985-10-20’),

         (‘Luciana Pereira’, ‘1970-10-21’),

         (‘Luana Maciel’, ‘1970-10-21’) GO 10000

 

Agora para visualizarmos informações sobre a tabela e seus índices, vamos realizar a seguinte consulta.

SELECT D.alloc_unit_type_desc, D.index_id, D.index_type_desc, D.avg_fragment_size_in_pages, D.page_count, compressed_page_count

FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID(‘PageLevelCompression’),NULL,NULL,‘DETAILED’) AS D

 

image

 

Temos algumas informações como tipo HEAP , ou seja não temos índice criado para essa tabela, quantidade de páginas =  295 e campo compressed_page_count =  0.

 

Para conseguirmos realizar a visualização da página de dados no SQL Server é necessário que seja habilitado o Trace 3604. Depois de habilitado, vamos utilizar a procedure criada por Paul Randal que nós dá informações sobre aonde se encontra a first page, Root Page e Firt IAM – http://www.sqlskills.com/BLOGS/PAUL/post/Inside-The-Storage-Engine-sp_AllocationMetadata-putting-undocumented-system-catalog-views-to-work.aspx

 

DBCC TRACEON(3604)

EXEC dbo.sp_AllocationMetadata ‘PageLevelCompression’

 

image

 

Agora sabemos a primeira página de dados da tabela, sendo assim vamos visualizá-la utilizando

 

DBCC PAGE (8,1,153,1)

 

Sendo assim, temos a primeira página de dados….Nesse caso irei pegar somente os 3 primeiros registros.

 

 

PAGE: (1:153)

BUFFER:

BUF @0x0000000085FCF200

bpage = 0x00000000859A4000           bhash = 0x0000000000000000           bpageno = (1:153)
bdbid = 8                            breferences = 0                      bUse1 = 58569
bstat = 0xc00009                     blog = 0xbb79bb79                    bnext = 0x0000000000000000

PAGE HEADER:

Page @0x00000000859A4000

m_pageId = (1:153)                   m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8200
m_objId (AllocUnitId.idObj) = 36     m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594040287232                                
Metadata: PartitionId = 72057594039369728                                 Metadata: IndexId = 0
Metadata: ObjectId = 1156355334      m_prevPage = (0:0)                   m_nextPage = (0:0)
pminlen = 16                         m_slotCnt = 204                      m_freeCnt = 208
m_freeData = 7576                    m_reservedCnt = 0                    m_lsn = (226:50:14)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = -1805844177            

Allocation Status

GAM (1:2) = ALLOCATED                SGAM (1:3) = NOT ALLOCATED          
PFS (1:1) = 0x64 MIXED_EXT ALLOCATED 100_PCT_FULL                         DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED           

DATA:

Slot 0, Offset 0x60, Length 34, DumpStyle BYTE

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 34                    
Memory Dump @0x000000000DEFC060

0000000000000000:   30001000 01000000 00000000 00b1130b †0…………±..
0000000000000010:   03000001 0022004c 75616e20 4d616369 †…..”.Luan Maci
0000000000000020:   656c†††††††††††††††††††††††††††††††††el              

Slot 1, Offset 0x82, Length 36, DumpStyle BYTE

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 36                    
Memory Dump @0x000000000DEFC082

0000000000000000:   30001000 02000000 00000000 0014eb0a †0………….ë.
0000000000000010:   03000001 00240041 6e746f6e 696f2050 †…..$.Antonio P
0000000000000020:   61647561 ††††††††††††††††††††††††††††adua            

Slot 2, Offset 0xa6, Length 39, DumpStyle BYTE

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 39                    
Memory Dump @0x000000000DEFC0A6

0000000000000000:   30001000 03000000 00000000 0014eb0a †0………….ë.
0000000000000010:   03000001 00270041 6e746f6e 69612064 †…..’.Antonia d
0000000000000020:   65205061 647561††††††††††††††††††††††e Padua         

Slot 3, Offset 0xcd, Length 38, DumpStyle BYTE

 

Temos os 3 primeiros registros cadastrados na página de dados contendo informações de ID, Nome e Data de Nascimento, ou seja estamos vendo o comportamento natural de armazenamento do SQL Server.

 

Utilizando a procedure sp_estimate_data_compression_savings vemos a vantagem da compressão de páginas nesse caso.

 

sp_estimate_data_compression_savings

      @schema_name =‘dbo’

     ,@object_name  =‘PageLevelCompression’

     ,@index_id = NULL

     ,@partition_number = NULL

     ,@data_compression = ‘PAGE’

image

 

Percebam o ganho que teremos………

Realizando a compressão de página……

 

ALTER TABLE tempdb.dbo.PageLevelCompression

REBUILD

WITH(DATA_COMPRESSION = PAGE)

Agora visualizando as consultas lado a lado……

SELECT D.alloc_unit_type_desc, D.index_id, D.index_type_desc, D.avg_fragment_size_in_pages, D.page_count, compressed_page_count

FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID(‘PageLevelCompression’),NULL,NULL,‘DETAILED’) AS D

 

Sem Compressão

image

 

Com Compressão

image

 

Agora que a compressão foi realizada, vamos navegar novamente para a primeira página de dados. Utilizaremos agora o Root Page para ir para a primeira página de dados da tabela PageLevelCompression.

 

EXEC dbo.sp_AllocationMetadata ‘PageLevelCompression’

image

DBCC PAGE (8,1,322,1)

 

PAGE: (1:322)

BUFFER:

BUF @0x0000000088F9C900

bpage = 0x0000000088352000           bhash = 0x0000000000000000           bpageno = (1:322)
bdbid = 8                            breferences = 0                      bUse1 = 60770
bstat = 0x1c00009                    blog = 0x12121b79                    bnext = 0x0000000000000000

PAGE HEADER:

Page @0x0000000088352000

m_pageId = (1:322)                   m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x80                m_level = 0                          m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 37     m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594040352768                                
Metadata: PartitionId = 72057594039435264                                 Metadata: IndexId = 0
Metadata: ObjectId = 1156355334      m_prevPage = (1:321)                 m_nextPage = (0:0)
pminlen = 4                          m_slotCnt = 668                      m_freeCnt = 614
m_freeData = 6242                    m_reservedCnt = 0                    m_lsn = (286:306:32)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 572760848              

Allocation Status

GAM (1:2) = ALLOCATED                SGAM (1:3) = NOT ALLOCATED           PFS (1:1) = 0x43 ALLOCATED  95_PCT_FULL
DIFF (1:6) = CHANGED                 ML (1:7) = NOT MIN_LOGGED           

CompressionInfo @0x0000000012098C80

CompressionInfo Raw Bytes

0000000000000000:   06b9012a 00860021 03a41980 b6450000 †.¹.*.†.!.¤..¶E..
0000000000000010:   0000005f fa0a0101 000f004c 75636961 †…_ú……Lucia
0000000000000020:   6e612050 65726569 72610800 16001a00 †na Pereira……
0000000000000030:   1e002800 32003d00 4b005c00 0514eb0a †..(.2.=.K.\…ë.
0000000000000040:   05b1130b 05c50f0b 02616e20 4d616369 †.±…Å…an Maci
0000000000000050:   656c066f 204d6f72 65697261 02616e61 †el.o Moreira.ana
0000000000000060:   204d6163 69656c00 416e746f 6e696f20 † Maciel.Antonio 
0000000000000070:   50616475 6100416e 746f6e69 61206465 †Padua.Antonia de
0000000000000080:   20506164 7561†††††††††††††††††††††††† Padua          
CompressionInfo size (in bytes) = 134                                     PageModCount = 441
CI Header Flags =  CI_HAS_ANCHOR_RECORD CI_HAS_DICTIONARY                
AnchorRecord @0x000000001209A067

Record Type = (COMPRESSED) PRIMARY_RECORD                                 Record attributes =  LONG DATA REGION
Record size = 35                    
CD Array

CD array entry = Column 1 (cluster 0, CD array offset 0): 0x04 (THREE_BYTE_SHORT)
CD array entry = Column 2 (cluster 0, CD array offset 0): 0x0a (LONG)    
CD array entry = Column 3 (cluster 0, CD array offset 1): 0x09 (EIGHT_BYTE_SHORT)

Record Memory Dump

000000001209A067:   2103a419 80b64500 00000000 5ffa0a01 †!.¤..¶E….._ú..
000000001209A077:   01000f00 4c756369 616e6120 50657265 †….Luciana Pere
000000001209A087:   697261†††††††††††††††††††††††††††††††ira             
Anchor record entry = Column  1, offset   4 length  3 @ 0x000000001209A06B
Anchor record entry = Column  2, offset  20 length 15 @ 0x000000001209A07B
Anchor record entry = Column  3, offset   7 length  8 @ 0x000000001209A06E

Dictionary @0x000000001209A08A

Page Dictionary Memory Dump

000000001209A08A:   08001600 1a001e00 28003200 3d004b00 †……..(.2.=.K.
000000001209A09A:   5c000514 eb0a05b1 130b05c5 0f0b0261 †\…ë..±…Å…a
000000001209A0AA:   6e204d61 6369656c 066f204d 6f726569 †n Maciel.o Morei
000000001209A0BA:   72610261 6e61204d 61636965 6c00416e †ra.ana Maciel.An
000000001209A0CA:   746f6e69 6f205061 64756100 416e746f †tonio Padua.Anto
000000001209A0DA:   6e696120 64652050 61647561 ††††††††††nia de Padua    
Entry count = 8                      Dictionary size (in bytes) = 92      Data section offset = 18
Data section start = 0x000000001209A09C                                  
Offset section start = 0x000000001209A08C
                                

DATA:

Slot 0, Offset 0xe6, Length 9, DumpStyle BYTE

Record Type = (COMPRESSED) PRIMARY_RECORD                                 Record size = 9

CD Array

CD array entry = Column 1 (cluster 0, CD array offset 0): 0x01 (EMPTY)   
CD array entry = Column 2 (cluster 0, CD array offset 0): 0x01 (EMPTY)   
CD array entry = Column 3 (cluster 0, CD array offset 1): 0x01 (EMPTY)   
Record Memory Dump

000000001209A0E6:   01031111 204d6163 69†††††††††††††††††…. Maci       

Slot 1, Offset 0xef, Length 9, DumpStyle BYTE

Record Type = (COMPRESSED) PRIMARY_RECORD                                 Record size = 9

CD Array

CD array entry = Column 1 (cluster 0, CD array offset 0): 0x03 (TWO_BYTE_SHORT)
CD array entry = Column 2 (cluster 0, CD array offset 0): 0x0c (ONE_BYTE_PAGE_SYMBOL)
CD array entry = Column 3 (cluster 0, CD array offset 1): 0x01 (EMPTY)   
Record Memory Dump

000000001209A0EF:   0103c311 0246056e 69†††††††††††††††††..Ã..F.ni       

Slot 2, Offset 0xf8, Length 9, DumpStyle BYTE

Record Type = (COMPRESSED) PRIMARY_RECORD                                 Record size = 9

CD Array

CD array entry = Column 1 (cluster 0, CD array offset 0): 0x03 (TWO_BYTE_SHORT)
CD array entry = Column 2 (cluster 0, CD array offset 0): 0x0c (ONE_BYTE_PAGE_SYMBOL)
CD array entry = Column 3 (cluster 0, CD array offset 1): 0x0c (ONE_BYTE_PAGE_SYMBOL)

Record Memory Dump

000000001209A0F8:   0103c31c 02470301 75†††††††††††††††††..Ã..G..u       

Slot 3, Offset 0x101, Length 9, DumpStyle BYTE

 

 

A estruturação do Compression Info (CI) contêm

  • Cabeçalho descrevendo todas as informações contidas.
  • O Anchor Record que é o Prefix-Compression.

 

O Dicionário, quando usado.

 

image

 

CI (Compression Information) = CI_HAS_ANCHOR_RECORD ou seja nesse caso temos Prefix-Column.

 

CI_HAS_DICTIONARY sendo assim ele possui um dicionário de dados dentro dessa página.

 

image

 

Informações do Dicionário criado para essa página.

 

Tendo agora os Prefixos e o Dicionário criado, todos os registros dessa página serão otimizados utilizar a compressão de página.

 

Para maiores informações consulte –  http://sqlblogcasts.com/blogs/danny/archive/2008/06/19/page-compression-internals-and-examples.aspx

 

Gostou do Post? Então deixe seu comentário..

 

Abs e até a próxima….