Data Compression Labs #1 – Tipagem inteligente e páginas zumbis

Olá,

Compressão no SQL Server é de longe uma das features Enterprise mais importantes do produto presentes desde o SQL Server 2008.

Quem já implantou, viu (inúmeras) vantagens e quem não implantou tem vontade ou interesse. Já quem não gosta…caso patológico. Brincadeira, não conheço quem não tenha gostado.

Quando digo compressão me refiro ao termo em geral, e o SQL Server entrega várias tecnologias de compressão. Os principais são Backup Compression (abordado em um post passado) e Data Compression.

Então vamos falar um pouco sobre Data Compression…

Presumo que você já saiba o que é Data Compression e sabe a diferença entre Row Compression e Page Compression.

Caso contrário, recomendo a seção “Recomendações de Leitura” no final do post.

A ideia aqui é explorar um pouco mais os detalhes internos de dados comprimidos e eliminar alguns mitos que giram em torno da compressão de dados, além de comprovar algumas informações óbvias sobre a compressão. Estou fazendo alguns testes com compressão e gostaria de aproveitar o momento e compartilhar alguns dos resultados aqui.

0. Script do post

Segue script inicial para o post:

USE MASTER

GO

IF EXISTS (SELECT NAME FROM SYS.DATABASES WHERE NAME LIKE '%DComp%')

BEGIN

ALTER DATABASE DComp SET RESTRICTED_USER;

DROP DATABASE  DComp;

END

GO

CREATE DATABASE DComp

GO

USE DComp

GO

CREATE TABLE dbo.Pessoa

(

ID INT IDENTITY (1,1) PRIMARY KEY NOT NULL,

NOME VARCHAR(50) NULL DEFAULT 'Nome de alguém',

DESCRICAO CHAR(8000) NULL DEFAULT REPLICATE('A',8000),

IDADE INT NULL

);

INSERT INTO DBO.PESSOA (NOME, DESCRICAO, IDADE) VALUES ('Quico','Realizando o teste da compressão',9)

INSERT INTO DBO.PESSOA (NOME, DESCRICAO, IDADE) VALUES ('Seu Madruga','Realizando o teste da compressão',50)

INSERT INTO DBO.PESSOA (NOME, DESCRICAO, IDADE) VALUES ('Chaves','Realizando o teste da compressão',8)

INSERT INTO DBO.PESSOA (NOME, DESCRICAO, IDADE) VALUES ('Chiquinha','Realizando o teste da compressão',7)

INSERT INTO DBO.PESSOA (NOME, DESCRICAO, IDADE) VALUES ('Bruxa do 71','Realizando o teste da compressão',50)

INSERT INTO DBO.PESSOA (NOME, DESCRICAO, IDADE) VALUES ('Bruxa do 71','TATATATATATATATATATATATATATATATATATATATATATAKKKKKKTATATATATATATATATATATATATATATAKKKKK',50)

Até então, o script não é difícil de entender. A ideia é que essa tabela seja utilizada como exemplo para essa e demais postagens sobre o tema. Da forma como foi modelada, teremos um registro por página (8K). É a situação ideal para nosso primeiro tópico.

1. Tipagem eficiente do Row Compression na prática e páginas “zumbis “

Vamos usar o comado não-documentado (e provavelmente um dos mais “documentados” dentre os DBCC, hehe) DBCC PAGE, e para enxergar a saída deste comando, vamos precisar direcionar a saída para um output acessível:

DBCC TRACEON (3604,-1)

DBCC TRACEON é um velho conhecido dentre os DBCC’s documentados. É uma das formas de se ativar traceflags no SQL Server. O TF 3604 faz com que determinados comandos entreguem seu resultado em forma textual em uma janela de resultados, o que é exatamente o que iremos fazer de agora em diante. Em resumo: Sem o 3604, sem brincadeiras (a não ser que você use o DBCC PAGE xxx WITH TABLERESULTS, mas isso é outra história). O -1 significa que estou ativando o TF em escopo global, e não em escopo de sessão. Estou fazendo isso em uma instância de treinamento por motivos de praticidade, não é obrigatório se você utilizar a mesma sessão caso queira realizar os testes.

Agora, para capturar o número das páginas para explorá-las, vamos utilizar a função fn_PhysLocCracker, que retorna de modo amigável a localização física de um registro. É uma das várias formas de se obter a localização física de um registro. Notamos algo interessante na imagem a seguir, e que foi intencional: cada registro está em uma página diferente (ou seja, não coube mais de um registro por página).

SELECT * FROM dbo.Pessoa AS P CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) AS FPLC
ORDER BY FPLC.file_id, FPLC.page_id, FPLC.slot_id;

Usamos a query acima por dois motivos: o primeiro era realizar o carregamento das páginas para o Data Cache (continue acompanhando, já explicarei o motivo) e o segundo é obter as informações que vamos precisar passar para o DBCC PAGE. A estrutura do DBCC PAGE é:

DBCC PAGE('database_name ou database_id',file_id,page_id,@param_visualizacao)

O primeiro parâmetro é para identificar a base, o segundo é o file_id (sem usar o PhysLocCracker é intuitivo que estamos falando do file_id = 1 em nosso exemplo por se tratar de um data file que está no filegroup primary certamente possui a identificação = 1 (já que não criamos outros arquivos no código de exemplo). O terceiro é o page_id, e esse sim é o motivo de usarmos a função: para obter a localização física (entenda-se página de dados, de 8K) do registro. O último parâmetro dita o modo de visualização que o DBCC PAGE oferece. O parâmetro 3 provê um maior detalhamento do conteúdo das linhas (pra ser mais específico, linha por linha), então vamos usá-lo.

Como exemplo, vamos dar uma olhada na página onde o registro “Chaves” está localizado (página 93).

O conteúdo a seguir é a saída integral (sem cortes) do comando que executei.

DBCC PAGE('DComp',1,93,3)

Segue resultado da página. Coloquei no blog para fins de documentação, caso você queira ignorar e pular para o essencial, postei uma imagem logo em seguida da saída. A ideia é mostrar e estrutura de uma página antes e depois da compressão.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

PAGE: (1:93)

BUFFER:

BUF @0x000000008BFA87C0

bpage = 0x000000008B16A000 bhash = 0x0000000000000000 bpageno = (1:93)

bdbid = 25 breferences = 0 bcputicks = 0

bsampleCount = 0 bUse1 = 5047 bstat = 0xc0010b

blog = 0x9a2159bb bnext = 0x0000000000000000

PAGE HEADER:

Page @0x000000008B16A000

m_pageId = (1:93) m_headerVersion = 1 m_type = 1

m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0

m_objId (AllocUnitId.idObj) = 29 m_indexId (AllocUnitId.idInd) = 256

Metadata: AllocUnitId = 72057594039828480

Metadata: PartitionId = 72057594038779904 Metadata: IndexId = 1

Metadata: ObjectId = 2105058535 m_prevPage = (1:90) m_nextPage = (1:94)

pminlen = 8012 m_slotCnt = 1 m_freeCnt = 69

m_freeData = 8121 m_reservedCnt = 0 m_lsn = (24:200:8)

m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0

m_tornBits = 0

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED

PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED

ML (1:7) = NOT MIN_LOGGED

Slot 0 Offset 0x60 Length 8025

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Record Size = 8025

Memory Dump @0x000000000E23A060

0000000000000000: 30004c1f 03000000 5265616c 697a616e †0.L…..Realizan

0000000000000010: 646f206f 20746573 74652064 6120636f †do o teste da co

0000000000000020: 6d707265 7373e36f 20202020 20202020 †mpressão

0000000000000030: 20202020 20202020 20202020 20202020 †

0000000000000040: 20202020 20202020 20202020 20202020 †

0000000000000050: 20202020 20202020 20202020 20202020 †

0000000000000060: 20202020 20202020 20202020 20202020 †

0000000000000070: 20202020 20202020 20202020 20202020 †

0000000000000080: 20202020 20202020 20202020 20202020 †

0000000000000090: 20202020 20202020 20202020 20202020 †

00000000000000A0: 20202020 20202020 20202020 20202020 †

00000000000000B0: 20202020 20202020 20202020 20202020 †

00000000000000C0: 20202020 20202020 20202020 20202020 †

00000000000000D0: 20202020 20202020 20202020 20202020 †

00000000000000E0: 20202020 20202020 20202020 20202020 †

00000000000000F0: 20202020 20202020 20202020 20202020 †

0000000000000100: 20202020 20202020 20202020 20202020 †

0000000000000110: 20202020 20202020 20202020 20202020 †

0000000000000120: 20202020 20202020 20202020 20202020 †

0000000000000130: 20202020 20202020 20202020 20202020 †

0000000000000140: 20202020 20202020 20202020 20202020 †

0000000000000150: 20202020 20202020 20202020 20202020 †

0000000000000160: 20202020 20202020 20202020 20202020 †

0000000000000170: 20202020 20202020 20202020 20202020 †

0000000000000180: 20202020 20202020 20202020 20202020 †

0000000000000190: 20202020 20202020 20202020 20202020 †

00000000000001A0: 20202020 20202020 20202020 20202020 †

00000000000001B0: 20202020 20202020 20202020 20202020 †

00000000000001C0: 20202020 20202020 20202020 20202020 †

00000000000001D0: 20202020 20202020 20202020 20202020 †

00000000000001E0: 20202020 20202020 20202020 20202020 †

00000000000001F0: 20202020 20202020 20202020 20202020 †

0000000000000200: 20202020 20202020 20202020 20202020 †

0000000000000210: 20202020 20202020 20202020 20202020 †

0000000000000220: 20202020 20202020 20202020 20202020 †

0000000000000230: 20202020 20202020 20202020 20202020 †

0000000000000240: 20202020 20202020 20202020 20202020 †

0000000000000250: 20202020 20202020 20202020 20202020 †

0000000000000260: 20202020 20202020 20202020 20202020 †

0000000000000270: 20202020 20202020 20202020 20202020 †

0000000000000280: 20202020 20202020 20202020 20202020 †

0000000000000290: 20202020 20202020 20202020 20202020 †

00000000000002A0: 20202020 20202020 20202020 20202020 †

00000000000002B0: 20202020 20202020 20202020 20202020 †

00000000000002C0: 20202020 20202020 20202020 20202020 †

00000000000002D0: 20202020 20202020 20202020 20202020 †

00000000000002E0: 20202020 20202020 20202020 20202020 †

00000000000002F0: 20202020 20202020 20202020 20202020 †

0000000000000300: 20202020 20202020 20202020 20202020 †

0000000000000310: 20202020 20202020 20202020 20202020 †

0000000000000320: 20202020 20202020 20202020 20202020 †

0000000000000330: 20202020 20202020 20202020 20202020 †

0000000000000340: 20202020 20202020 20202020 20202020 †

0000000000000350: 20202020 20202020 20202020 20202020 †

0000000000000360: 20202020 20202020 20202020 20202020 †

0000000000000370: 20202020 20202020 20202020 20202020 †

0000000000000380: 20202020 20202020 20202020 20202020 †

0000000000000390: 20202020 20202020 20202020 20202020 †

00000000000003A0: 20202020 20202020 20202020 20202020 †

00000000000003B0: 20202020 20202020 20202020 20202020 †

00000000000003C0: 20202020 20202020 20202020 20202020 †

00000000000003D0: 20202020 20202020 20202020 20202020 †

00000000000003E0: 20202020 20202020 20202020 20202020 †

00000000000003F0: 20202020 20202020 20202020 20202020 †

0000000000000400: 20202020 20202020 20202020 20202020 †

0000000000000410: 20202020 20202020 20202020 20202020 †

0000000000000420: 20202020 20202020 20202020 20202020 †

0000000000000430: 20202020 20202020 20202020 20202020 †

0000000000000440: 20202020 20202020 20202020 20202020 †

0000000000000450: 20202020 20202020 20202020 20202020 †

0000000000000460: 20202020 20202020 20202020 20202020 †

0000000000000470: 20202020 20202020 20202020 20202020 †

0000000000000480: 20202020 20202020 20202020 20202020 †

0000000000000490: 20202020 20202020 20202020 20202020 †

00000000000004A0: 20202020 20202020 20202020 20202020 †

00000000000004B0: 20202020 20202020 20202020 20202020 †

00000000000004C0: 20202020 20202020 20202020 20202020 †

00000000000004D0: 20202020 20202020 20202020 20202020 †

00000000000004E0: 20202020 20202020 20202020 20202020 †

00000000000004F0: 20202020 20202020 20202020 20202020 †

0000000000000500: 20202020 20202020 20202020 20202020 †

0000000000000510: 20202020 20202020 20202020 20202020 †

0000000000000520: 20202020 20202020 20202020 20202020 †

0000000000000530: 20202020 20202020 20202020 20202020 †

0000000000000540: 20202020 20202020 20202020 20202020 †

0000000000000550: 20202020 20202020 20202020 20202020 †

0000000000000560: 20202020 20202020 20202020 20202020 †

0000000000000570: 20202020 20202020 20202020 20202020 †

0000000000000580: 20202020 20202020 20202020 20202020 †

0000000000000590: 20202020 20202020 20202020 20202020 †

00000000000005A0: 20202020 20202020 20202020 20202020 †

00000000000005B0: 20202020 20202020 20202020 20202020 †

00000000000005C0: 20202020 20202020 20202020 20202020 †

00000000000005D0: 20202020 20202020 20202020 20202020 †

00000000000005E0: 20202020 20202020 20202020 20202020 †

00000000000005F0: 20202020 20202020 20202020 20202020 †

0000000000000600: 20202020 20202020 20202020 20202020 †

0000000000000610: 20202020 20202020 20202020 20202020 †

0000000000000620: 20202020 20202020 20202020 20202020 †

0000000000000630: 20202020 20202020 20202020 20202020 †

0000000000000640: 20202020 20202020 20202020 20202020 †

0000000000000650: 20202020 20202020 20202020 20202020 †

0000000000000660: 20202020 20202020 20202020 20202020 †

0000000000000670: 20202020 20202020 20202020 20202020 †

0000000000000680: 20202020 20202020 20202020 20202020 †

0000000000000690: 20202020 20202020 20202020 20202020 †

00000000000006A0: 20202020 20202020 20202020 20202020 †

00000000000006B0: 20202020 20202020 20202020 20202020 †

00000000000006C0: 20202020 20202020 20202020 20202020 †

00000000000006D0: 20202020 20202020 20202020 20202020 †

00000000000006E0: 20202020 20202020 20202020 20202020 †

00000000000006F0: 20202020 20202020 20202020 20202020 †

0000000000000700: 20202020 20202020 20202020 20202020 †

0000000000000710: 20202020 20202020 20202020 20202020 †

0000000000000720: 20202020 20202020 20202020 20202020 †

0000000000000730: 20202020 20202020 20202020 20202020 †

0000000000000740: 20202020 20202020 20202020 20202020 †

0000000000000750: 20202020 20202020 20202020 20202020 †

0000000000000760: 20202020 20202020 20202020 20202020 †

0000000000000770: 20202020 20202020 20202020 20202020 †

0000000000000780: 20202020 20202020 20202020 20202020 †

0000000000000790: 20202020 20202020 20202020 20202020 †

00000000000007A0: 20202020 20202020 20202020 20202020 †

00000000000007B0: 20202020 20202020 20202020 20202020 †

00000000000007C0: 20202020 20202020 20202020 20202020 †

00000000000007D0: 20202020 20202020 20202020 20202020 †

00000000000007E0: 20202020 20202020 20202020 20202020 †

00000000000007F0: 20202020 20202020 20202020 20202020 †

0000000000000800: 20202020 20202020 20202020 20202020 †

0000000000000810: 20202020 20202020 20202020 20202020 †

0000000000000820: 20202020 20202020 20202020 20202020 †

0000000000000830: 20202020 20202020 20202020 20202020 †

0000000000000840: 20202020 20202020 20202020 20202020 †

0000000000000850: 20202020 20202020 20202020 20202020 †

0000000000000860: 20202020 20202020 20202020 20202020 †

0000000000000870: 20202020 20202020 20202020 20202020 †

0000000000000880: 20202020 20202020 20202020 20202020 †

0000000000000890: 20202020 20202020 20202020 20202020 †

00000000000008A0: 20202020 20202020 20202020 20202020 †

00000000000008B0: 20202020 20202020 20202020 20202020 †

00000000000008C0: 20202020 20202020 20202020 20202020 †

00000000000008D0: 20202020 20202020 20202020 20202020 †

00000000000008E0: 20202020 20202020 20202020 20202020 †

00000000000008F0: 20202020 20202020 20202020 20202020 †

0000000000000900: 20202020 20202020 20202020 20202020 †

0000000000000910: 20202020 20202020 20202020 20202020 †

0000000000000920: 20202020 20202020 20202020 20202020 †

0000000000000930: 20202020 20202020 20202020 20202020 †

0000000000000940: 20202020 20202020 20202020 20202020 †

0000000000000950: 20202020 20202020 20202020 20202020 †

0000000000000960: 20202020 20202020 20202020 20202020 †

0000000000000970: 20202020 20202020 20202020 20202020 †

0000000000000980: 20202020 20202020 20202020 20202020 †

0000000000000990: 20202020 20202020 20202020 20202020 †

00000000000009A0: 20202020 20202020 20202020 20202020 †

00000000000009B0: 20202020 20202020 20202020 20202020 †

00000000000009C0: 20202020 20202020 20202020 20202020 †

00000000000009D0: 20202020 20202020 20202020 20202020 †

00000000000009E0: 20202020 20202020 20202020 20202020 †

00000000000009F0: 20202020 20202020 20202020 20202020 †

0000000000000A00: 20202020 20202020 20202020 20202020 †

0000000000000A10: 20202020 20202020 20202020 20202020 †

0000000000000A20: 20202020 20202020 20202020 20202020 †

0000000000000A30: 20202020 20202020 20202020 20202020 †

0000000000000A40: 20202020 20202020 20202020 20202020 †

0000000000000A50: 20202020 20202020 20202020 20202020 †

0000000000000A60: 20202020 20202020 20202020 20202020 †

0000000000000A70: 20202020 20202020 20202020 20202020 †

0000000000000A80: 20202020 20202020 20202020 20202020 †

0000000000000A90: 20202020 20202020 20202020 20202020 †

0000000000000AA0: 20202020 20202020 20202020 20202020 †

0000000000000AB0: 20202020 20202020 20202020 20202020 †

0000000000000AC0: 20202020 20202020 20202020 20202020 †

0000000000000AD0: 20202020 20202020 20202020 20202020 †

0000000000000AE0: 20202020 20202020 20202020 20202020 †

0000000000000AF0: 20202020 20202020 20202020 20202020 †

0000000000000B00: 20202020 20202020 20202020 20202020 †

0000000000000B10: 20202020 20202020 20202020 20202020 †

0000000000000B20: 20202020 20202020 20202020 20202020 †

0000000000000B30: 20202020 20202020 20202020 20202020 †

0000000000000B40: 20202020 20202020 20202020 20202020 †

0000000000000B50: 20202020 20202020 20202020 20202020 †

0000000000000B60: 20202020 20202020 20202020 20202020 †

0000000000000B70: 20202020 20202020 20202020 20202020 †

0000000000000B80: 20202020 20202020 20202020 20202020 †

0000000000000B90: 20202020 20202020 20202020 20202020 †

0000000000000BA0: 20202020 20202020 20202020 20202020 †

0000000000000BB0: 20202020 20202020 20202020 20202020 †

0000000000000BC0: 20202020 20202020 20202020 20202020 †

0000000000000BD0: 20202020 20202020 20202020 20202020 †

0000000000000BE0: 20202020 20202020 20202020 20202020 †

0000000000000BF0: 20202020 20202020 20202020 20202020 †

0000000000000C00: 20202020 20202020 20202020 20202020 †

0000000000000C10: 20202020 20202020 20202020 20202020 †

0000000000000C20: 20202020 20202020 20202020 20202020 †

0000000000000C30: 20202020 20202020 20202020 20202020 †

0000000000000C40: 20202020 20202020 20202020 20202020 †

0000000000000C50: 20202020 20202020 20202020 20202020 †

0000000000000C60: 20202020 20202020 20202020 20202020 †

0000000000000C70: 20202020 20202020 20202020 20202020 †

0000000000000C80: 20202020 20202020 20202020 20202020 †

0000000000000C90: 20202020 20202020 20202020 20202020 †

0000000000000CA0: 20202020 20202020 20202020 20202020 †

0000000000000CB0: 20202020 20202020 20202020 20202020 †

0000000000000CC0: 20202020 20202020 20202020 20202020 †

0000000000000CD0: 20202020 20202020 20202020 20202020 †

0000000000000CE0: 20202020 20202020 20202020 20202020 †

0000000000000CF0: 20202020 20202020 20202020 20202020 †

0000000000000D00: 20202020 20202020 20202020 20202020 †

0000000000000D10: 20202020 20202020 20202020 20202020 †

0000000000000D20: 20202020 20202020 20202020 20202020 †

0000000000000D30: 20202020 20202020 20202020 20202020 †

0000000000000D40: 20202020 20202020 20202020 20202020 †

0000000000000D50: 20202020 20202020 20202020 20202020 †

0000000000000D60: 20202020 20202020 20202020 20202020 †

0000000000000D70: 20202020 20202020 20202020 20202020 †

0000000000000D80: 20202020 20202020 20202020 20202020 †

0000000000000D90: 20202020 20202020 20202020 20202020 †

0000000000000DA0: 20202020 20202020 20202020 20202020 †

0000000000000DB0: 20202020 20202020 20202020 20202020 †

0000000000000DC0: 20202020 20202020 20202020 20202020 †

0000000000000DD0: 20202020 20202020 20202020 20202020 †

0000000000000DE0: 20202020 20202020 20202020 20202020 †

0000000000000DF0: 20202020 20202020 20202020 20202020 †

0000000000000E00: 20202020 20202020 20202020 20202020 †

0000000000000E10: 20202020 20202020 20202020 20202020 †

0000000000000E20: 20202020 20202020 20202020 20202020 †

0000000000000E30: 20202020 20202020 20202020 20202020 †

0000000000000E40: 20202020 20202020 20202020 20202020 †

0000000000000E50: 20202020 20202020 20202020 20202020 †

0000000000000E60: 20202020 20202020 20202020 20202020 †

0000000000000E70: 20202020 20202020 20202020 20202020 †

0000000000000E80: 20202020 20202020 20202020 20202020 †

0000000000000E90: 20202020 20202020 20202020 20202020 †

0000000000000EA0: 20202020 20202020 20202020 20202020 †

0000000000000EB0: 20202020 20202020 20202020 20202020 †

0000000000000EC0: 20202020 20202020 20202020 20202020 †

0000000000000ED0: 20202020 20202020 20202020 20202020 †

0000000000000EE0: 20202020 20202020 20202020 20202020 †

0000000000000EF0: 20202020 20202020 20202020 20202020 †

0000000000000F00: 20202020 20202020 20202020 20202020 †

0000000000000F10: 20202020 20202020 20202020 20202020 †

0000000000000F20: 20202020 20202020 20202020 20202020 †

0000000000000F30: 20202020 20202020 20202020 20202020 †

0000000000000F40: 20202020 20202020 20202020 20202020 †

0000000000000F50: 20202020 20202020 20202020 20202020 †

0000000000000F60: 20202020 20202020 20202020 20202020 †

0000000000000F70: 20202020 20202020 20202020 20202020 †

0000000000000F80: 20202020 20202020 20202020 20202020 †

0000000000000F90: 20202020 20202020 20202020 20202020 †

0000000000000FA0: 20202020 20202020 20202020 20202020 †

0000000000000FB0: 20202020 20202020 20202020 20202020 †

0000000000000FC0: 20202020 20202020 20202020 20202020 †

0000000000000FD0: 20202020 20202020 20202020 20202020 †

0000000000000FE0: 20202020 20202020 20202020 20202020 †

0000000000000FF0: 20202020 20202020 20202020 20202020 †

0000000000001000: 20202020 20202020 20202020 20202020 †

0000000000001010: 20202020 20202020 20202020 20202020 †

0000000000001020: 20202020 20202020 20202020 20202020 †

0000000000001030: 20202020 20202020 20202020 20202020 †

0000000000001040: 20202020 20202020 20202020 20202020 †

0000000000001050: 20202020 20202020 20202020 20202020 †

0000000000001060: 20202020 20202020 20202020 20202020 †

0000000000001070: 20202020 20202020 20202020 20202020 †

0000000000001080: 20202020 20202020 20202020 20202020 †

0000000000001090: 20202020 20202020 20202020 20202020 †

00000000000010A0: 20202020 20202020 20202020 20202020 †

00000000000010B0: 20202020 20202020 20202020 20202020 †

00000000000010C0: 20202020 20202020 20202020 20202020 †

00000000000010D0: 20202020 20202020 20202020 20202020 †

00000000000010E0: 20202020 20202020 20202020 20202020 †

00000000000010F0: 20202020 20202020 20202020 20202020 †

0000000000001100: 20202020 20202020 20202020 20202020 †

0000000000001110: 20202020 20202020 20202020 20202020 †

0000000000001120: 20202020 20202020 20202020 20202020 †

0000000000001130: 20202020 20202020 20202020 20202020 †

0000000000001140: 20202020 20202020 20202020 20202020 †

0000000000001150: 20202020 20202020 20202020 20202020 †

0000000000001160: 20202020 20202020 20202020 20202020 †

0000000000001170: 20202020 20202020 20202020 20202020 †

0000000000001180: 20202020 20202020 20202020 20202020 †

0000000000001190: 20202020 20202020 20202020 20202020 †

00000000000011A0: 20202020 20202020 20202020 20202020 †

00000000000011B0: 20202020 20202020 20202020 20202020 †

00000000000011C0: 20202020 20202020 20202020 20202020 †

00000000000011D0: 20202020 20202020 20202020 20202020 †

00000000000011E0: 20202020 20202020 20202020 20202020 †

00000000000011F0: 20202020 20202020 20202020 20202020 †

0000000000001200: 20202020 20202020 20202020 20202020 †

0000000000001210: 20202020 20202020 20202020 20202020 †

0000000000001220: 20202020 20202020 20202020 20202020 †

0000000000001230: 20202020 20202020 20202020 20202020 †

0000000000001240: 20202020 20202020 20202020 20202020 †

0000000000001250: 20202020 20202020 20202020 20202020 †

0000000000001260: 20202020 20202020 20202020 20202020 †

0000000000001270: 20202020 20202020 20202020 20202020 †

0000000000001280: 20202020 20202020 20202020 20202020 †

0000000000001290: 20202020 20202020 20202020 20202020 †

00000000000012A0: 20202020 20202020 20202020 20202020 †

00000000000012B0: 20202020 20202020 20202020 20202020 †

00000000000012C0: 20202020 20202020 20202020 20202020 †

00000000000012D0: 20202020 20202020 20202020 20202020 †

00000000000012E0: 20202020 20202020 20202020 20202020 †

00000000000012F0: 20202020 20202020 20202020 20202020 †

0000000000001300: 20202020 20202020 20202020 20202020 †

0000000000001310: 20202020 20202020 20202020 20202020 †

0000000000001320: 20202020 20202020 20202020 20202020 †

0000000000001330: 20202020 20202020 20202020 20202020 †

0000000000001340: 20202020 20202020 20202020 20202020 †

0000000000001350: 20202020 20202020 20202020 20202020 †

0000000000001360: 20202020 20202020 20202020 20202020 †

0000000000001370: 20202020 20202020 20202020 20202020 †

0000000000001380: 20202020 20202020 20202020 20202020 †

0000000000001390: 20202020 20202020 20202020 20202020 †

00000000000013A0: 20202020 20202020 20202020 20202020 †

00000000000013B0: 20202020 20202020 20202020 20202020 †

00000000000013C0: 20202020 20202020 20202020 20202020 †

00000000000013D0: 20202020 20202020 20202020 20202020 †

00000000000013E0: 20202020 20202020 20202020 20202020 †

00000000000013F0: 20202020 20202020 20202020 20202020 †

0000000000001400: 20202020 20202020 20202020 20202020 †

0000000000001410: 20202020 20202020 20202020 20202020 †

0000000000001420: 20202020 20202020 20202020 20202020 †

0000000000001430: 20202020 20202020 20202020 20202020 †

0000000000001440: 20202020 20202020 20202020 20202020 †

0000000000001450: 20202020 20202020 20202020 20202020 †

0000000000001460: 20202020 20202020 20202020 20202020 †

0000000000001470: 20202020 20202020 20202020 20202020 †

0000000000001480: 20202020 20202020 20202020 20202020 †

0000000000001490: 20202020 20202020 20202020 20202020 †

00000000000014A0: 20202020 20202020 20202020 20202020 †

00000000000014B0: 20202020 20202020 20202020 20202020 †

00000000000014C0: 20202020 20202020 20202020 20202020 †

00000000000014D0: 20202020 20202020 20202020 20202020 †

00000000000014E0: 20202020 20202020 20202020 20202020 †

00000000000014F0: 20202020 20202020 20202020 20202020 †

0000000000001500: 20202020 20202020 20202020 20202020 †

0000000000001510: 20202020 20202020 20202020 20202020 †

0000000000001520: 20202020 20202020 20202020 20202020 †

0000000000001530: 20202020 20202020 20202020 20202020 †

0000000000001540: 20202020 20202020 20202020 20202020 †

0000000000001550: 20202020 20202020 20202020 20202020 †

0000000000001560: 20202020 20202020 20202020 20202020 †

0000000000001570: 20202020 20202020 20202020 20202020 †

0000000000001580: 20202020 20202020 20202020 20202020 †

0000000000001590: 20202020 20202020 20202020 20202020 †

00000000000015A0: 20202020 20202020 20202020 20202020 †

00000000000015B0: 20202020 20202020 20202020 20202020 †

00000000000015C0: 20202020 20202020 20202020 20202020 †

00000000000015D0: 20202020 20202020 20202020 20202020 †

00000000000015E0: 20202020 20202020 20202020 20202020 †

00000000000015F0: 20202020 20202020 20202020 20202020 †

0000000000001600: 20202020 20202020 20202020 20202020 †

0000000000001610: 20202020 20202020 20202020 20202020 †

0000000000001620: 20202020 20202020 20202020 20202020 †

0000000000001630: 20202020 20202020 20202020 20202020 †

0000000000001640: 20202020 20202020 20202020 20202020 †

0000000000001650: 20202020 20202020 20202020 20202020 †

0000000000001660: 20202020 20202020 20202020 20202020 †

0000000000001670: 20202020 20202020 20202020 20202020 †

0000000000001680: 20202020 20202020 20202020 20202020 †

0000000000001690: 20202020 20202020 20202020 20202020 †

00000000000016A0: 20202020 20202020 20202020 20202020 †

00000000000016B0: 20202020 20202020 20202020 20202020 †

00000000000016C0: 20202020 20202020 20202020 20202020 †

00000000000016D0: 20202020 20202020 20202020 20202020 †

00000000000016E0: 20202020 20202020 20202020 20202020 †

00000000000016F0: 20202020 20202020 20202020 20202020 †

0000000000001700: 20202020 20202020 20202020 20202020 †

0000000000001710: 20202020 20202020 20202020 20202020 †

0000000000001720: 20202020 20202020 20202020 20202020 †

0000000000001730: 20202020 20202020 20202020 20202020 †

0000000000001740: 20202020 20202020 20202020 20202020 †

0000000000001750: 20202020 20202020 20202020 20202020 †

0000000000001760: 20202020 20202020 20202020 20202020 †

0000000000001770: 20202020 20202020 20202020 20202020 †

0000000000001780: 20202020 20202020 20202020 20202020 †

0000000000001790: 20202020 20202020 20202020 20202020 †

00000000000017A0: 20202020 20202020 20202020 20202020 †

00000000000017B0: 20202020 20202020 20202020 20202020 †

00000000000017C0: 20202020 20202020 20202020 20202020 †

00000000000017D0: 20202020 20202020 20202020 20202020 †

00000000000017E0: 20202020 20202020 20202020 20202020 †

00000000000017F0: 20202020 20202020 20202020 20202020 †

0000000000001800: 20202020 20202020 20202020 20202020 †

0000000000001810: 20202020 20202020 20202020 20202020 †

0000000000001820: 20202020 20202020 20202020 20202020 †

0000000000001830: 20202020 20202020 20202020 20202020 †

0000000000001840: 20202020 20202020 20202020 20202020 †

0000000000001850: 20202020 20202020 20202020 20202020 †

0000000000001860: 20202020 20202020 20202020 20202020 †

0000000000001870: 20202020 20202020 20202020 20202020 †

0000000000001880: 20202020 20202020 20202020 20202020 †

0000000000001890: 20202020 20202020 20202020 20202020 †

00000000000018A0: 20202020 20202020 20202020 20202020 †

00000000000018B0: 20202020 20202020 20202020 20202020 †

00000000000018C0: 20202020 20202020 20202020 20202020 †

00000000000018D0: 20202020 20202020 20202020 20202020 †

00000000000018E0: 20202020 20202020 20202020 20202020 †

00000000000018F0: 20202020 20202020 20202020 20202020 †

0000000000001900: 20202020 20202020 20202020 20202020 †

0000000000001910: 20202020 20202020 20202020 20202020 †

0000000000001920: 20202020 20202020 20202020 20202020 †

0000000000001930: 20202020 20202020 20202020 20202020 †

0000000000001940: 20202020 20202020 20202020 20202020 †

0000000000001950: 20202020 20202020 20202020 20202020 †

0000000000001960: 20202020 20202020 20202020 20202020 †

0000000000001970: 20202020 20202020 20202020 20202020 †

0000000000001980: 20202020 20202020 20202020 20202020 †

0000000000001990: 20202020 20202020 20202020 20202020 †

00000000000019A0: 20202020 20202020 20202020 20202020 †

00000000000019B0: 20202020 20202020 20202020 20202020 †

00000000000019C0: 20202020 20202020 20202020 20202020 †

00000000000019D0: 20202020 20202020 20202020 20202020 †

00000000000019E0: 20202020 20202020 20202020 20202020 †

00000000000019F0: 20202020 20202020 20202020 20202020 †

0000000000001A00: 20202020 20202020 20202020 20202020 †

0000000000001A10: 20202020 20202020 20202020 20202020 †

0000000000001A20: 20202020 20202020 20202020 20202020 †

0000000000001A30: 20202020 20202020 20202020 20202020 †

0000000000001A40: 20202020 20202020 20202020 20202020 †

0000000000001A50: 20202020 20202020 20202020 20202020 †

0000000000001A60: 20202020 20202020 20202020 20202020 †

0000000000001A70: 20202020 20202020 20202020 20202020 †

0000000000001A80: 20202020 20202020 20202020 20202020 †

0000000000001A90: 20202020 20202020 20202020 20202020 †

0000000000001AA0: 20202020 20202020 20202020 20202020 †

0000000000001AB0: 20202020 20202020 20202020 20202020 †

0000000000001AC0: 20202020 20202020 20202020 20202020 †

0000000000001AD0: 20202020 20202020 20202020 20202020 †

0000000000001AE0: 20202020 20202020 20202020 20202020 †

0000000000001AF0: 20202020 20202020 20202020 20202020 †

0000000000001B00: 20202020 20202020 20202020 20202020 †

0000000000001B10: 20202020 20202020 20202020 20202020 †

0000000000001B20: 20202020 20202020 20202020 20202020 †

0000000000001B30: 20202020 20202020 20202020 20202020 †

0000000000001B40: 20202020 20202020 20202020 20202020 †

0000000000001B50: 20202020 20202020 20202020 20202020 †

0000000000001B60: 20202020 20202020 20202020 20202020 †

0000000000001B70: 20202020 20202020 20202020 20202020 †

0000000000001B80: 20202020 20202020 20202020 20202020 †

0000000000001B90: 20202020 20202020 20202020 20202020 †

0000000000001BA0: 20202020 20202020 20202020 20202020 †

0000000000001BB0: 20202020 20202020 20202020 20202020 †

0000000000001BC0: 20202020 20202020 20202020 20202020 †

0000000000001BD0: 20202020 20202020 20202020 20202020 †

0000000000001BE0: 20202020 20202020 20202020 20202020 †

0000000000001BF0: 20202020 20202020 20202020 20202020 †

0000000000001C00: 20202020 20202020 20202020 20202020 †

0000000000001C10: 20202020 20202020 20202020 20202020 †

0000000000001C20: 20202020 20202020 20202020 20202020 †

0000000000001C30: 20202020 20202020 20202020 20202020 †

0000000000001C40: 20202020 20202020 20202020 20202020 †

0000000000001C50: 20202020 20202020 20202020 20202020 †

0000000000001C60: 20202020 20202020 20202020 20202020 †

0000000000001C70: 20202020 20202020 20202020 20202020 †

0000000000001C80: 20202020 20202020 20202020 20202020 †

0000000000001C90: 20202020 20202020 20202020 20202020 †

0000000000001CA0: 20202020 20202020 20202020 20202020 †

0000000000001CB0: 20202020 20202020 20202020 20202020 †

0000000000001CC0: 20202020 20202020 20202020 20202020 †

0000000000001CD0: 20202020 20202020 20202020 20202020 †

0000000000001CE0: 20202020 20202020 20202020 20202020 †

0000000000001CF0: 20202020 20202020 20202020 20202020 †

0000000000001D00: 20202020 20202020 20202020 20202020 †

0000000000001D10: 20202020 20202020 20202020 20202020 †

0000000000001D20: 20202020 20202020 20202020 20202020 †

0000000000001D30: 20202020 20202020 20202020 20202020 †

0000000000001D40: 20202020 20202020 20202020 20202020 †

0000000000001D50: 20202020 20202020 20202020 20202020 †

0000000000001D60: 20202020 20202020 20202020 20202020 †

0000000000001D70: 20202020 20202020 20202020 20202020 †

0000000000001D80: 20202020 20202020 20202020 20202020 †

0000000000001D90: 20202020 20202020 20202020 20202020 †

0000000000001DA0: 20202020 20202020 20202020 20202020 †

0000000000001DB0: 20202020 20202020 20202020 20202020 †

0000000000001DC0: 20202020 20202020 20202020 20202020 †

0000000000001DD0: 20202020 20202020 20202020 20202020 †

0000000000001DE0: 20202020 20202020 20202020 20202020 †

0000000000001DF0: 20202020 20202020 20202020 20202020 †

0000000000001E00: 20202020 20202020 20202020 20202020 †

0000000000001E10: 20202020 20202020 20202020 20202020 †

0000000000001E20: 20202020 20202020 20202020 20202020 †

0000000000001E30: 20202020 20202020 20202020 20202020 †

0000000000001E40: 20202020 20202020 20202020 20202020 †

0000000000001E50: 20202020 20202020 20202020 20202020 †

0000000000001E60: 20202020 20202020 20202020 20202020 †

0000000000001E70: 20202020 20202020 20202020 20202020 †

0000000000001E80: 20202020 20202020 20202020 20202020 †

0000000000001E90: 20202020 20202020 20202020 20202020 †

0000000000001EA0: 20202020 20202020 20202020 20202020 †

0000000000001EB0: 20202020 20202020 20202020 20202020 †

0000000000001EC0: 20202020 20202020 20202020 20202020 †

0000000000001ED0: 20202020 20202020 20202020 20202020 †

0000000000001EE0: 20202020 20202020 20202020 20202020 †

0000000000001EF0: 20202020 20202020 20202020 20202020 †

0000000000001F00: 20202020 20202020 20202020 20202020 †

0000000000001F10: 20202020 20202020 20202020 20202020 †

0000000000001F20: 20202020 20202020 20202020 20202020 †

0000000000001F30: 20202020 20202020 20202020 20202020 †

0000000000001F40: 20202020 20202020 08000000 04000001 † ……..

0000000000001F50: 00591f43 68617665 73†††††††††††††††††.Y.Chaves

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

ID = 3

Slot 0 Column 2 Offset 0x1f53 Length 6 Length (physical) 6

NOME = Chaves

Slot 0 Column 3 Offset 0x8 Length 8000 Length (physical) 8000

DESCRICAO = [Error converting to string (length 8000 bytes)]

Slot 0 Column 4 Offset 0x1f48 Length 4 Length (physical) 4

IDADE = 8

Slot 0 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (98ec012aa510)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Muita informação acima, mas o que realmente precisamos para o post é só isso: mostrar um desperdício de espaço que uma modelagem ineficiente pode causar:

O motivo é que o espaço desperdiçado se refere à coluna Descricao que é CHAR(8000), e como já sabemos é um tipo de dado com tamanho fixo. Se em determinado registro, a coluna tem apenas 100 caracteres, na página, ele vai ocupar 8000 bytes. Se tiver 300 caracteres, os mesmos 8000. Bem, e a compressão ajudaria neste caso? Vamos comprimir e observar então….

ALTER TABLE dbo.Pessoa REBUILD WITH (DATA_COMPRESSION=PAGE)

Usei, através do REBUILD, PAGE COMPRESSION, que tem uma importante característica: realiza uma otimização de tipagem e uso real dos dados. Apenas para contextualizar: PAGE COMPRESSION implementa várias compressões (Dictionary, Row e Prefix), e sem dúvida a principal delas é a ROW Compression. Para o fim desta postagem, row ou page compression atingiriam o mesmo objetivo. Por motivos de praticidade (e por causa de outros exemplos que futuramente postarei), usei PAGE.

Row Compression, se fosse gente, ia bater com a mão na table e dizer: “Pô gente olha o desperdício aí. Bora fazer o seguinte: Campos fixo agora são variáveis e digo mais, a coluna vai pagar apenas o que de fato está utilizando. Se for nulo ou zero então…aí aí que a gente comprime mesmo”. Essa otimização de metadados (tipagem) e consumo é tão importante que está presente na PAGE e não é por acaso.

Vamos usar aquele método do fn_PhysLocCracker novamente pra comprovar uma coisa interessante:

OH! Todos os registros couberam numa página (e com folga, percebemos que cabe muito mais que isso). Vamos ver como ficou o conteúdo internamente na página com compressão?

PAGE: (1:114)

BUFFER:

BUF @0x0000000086FA72C0

bpage = 0x0000000086132000 bhash = 0x0000000000000000 bpageno = (1:114)

bdbid = 25 breferences = 0 bcputicks = 0

bsampleCount = 0 bUse1 = 6221 bstat = 0xc0010b

blog = 0x1212121b bnext = 0x0000000000000000

PAGE HEADER:

Page @0x0000000086132000

m_pageId = (1:114) m_headerVersion = 1 m_type = 1

m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0

m_objId (AllocUnitId.idObj) = 31 m_indexId (AllocUnitId.idInd) = 256

Metadata: AllocUnitId = 72057594039959552

Metadata: PartitionId = 72057594038845440 Metadata: IndexId = 1

Metadata: ObjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (0:0)

pminlen = 4 m_slotCnt = 6 m_freeCnt = 7742

m_freeData = 438 m_reservedCnt = 0 m_lsn = (24:280:23)

m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0

m_tornBits = 0

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED

PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED

ML (1:7) = NOT MIN_LOGGED

Slot 0 Offset 0x60 Length 48

Record Type = (COMPRESSED) PRIMARY_RECORD Record attributes = LONG DATA REGION

Record size = 48

CD Array

CD array entry = Column 1 (cluster 0, CD array offset 0): 0x02 (ONE_BYTE_SHORT)

CD array entry = Column 2 (cluster 0, CD array offset 0): 0x06 (FIVE_BYTE_SHORT)

CD array entry = Column 3 (cluster 0, CD array offset 1): 0x0a (LONG)

CD array entry = Column 4 (cluster 0, CD array offset 1): 0x02 (ONE_BYTE_SHORT)

Record Memory Dump

000000000B47A060: 2104622a 81517569 636f8901 01002000 †!.b*.Quico… .

000000000B47A070: 5265616c 697a616e 646f206f 20746573 †Realizando o tes

000000000B47A080: 74652064 6120636f 6d707265 7373e36f †te da compressão

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 1

ID = 1

Slot 0 Column 2 Offset 0x5 Length 5 Length (physical) 5

NOME = Quico

Slot 0 Column 3 Offset 0x10 Length 8000 Length (physical) 32

DESCRICAO = [Error converting to string (length 8000 bytes)]

Slot 0 Column 4 Offset 0xa Length 4 Length (physical) 1

IDADE = 9

Slot 0 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (0de2a6f75d1a)

Slot 1 Offset 0x90 Length 56

Record Type = (COMPRESSED) PRIMARY_RECORD Record attributes = LONG DATA REGION

Record size = 56

CD Array

CD array entry = Column 1 (cluster 0, CD array offset 0): 0x02 (ONE_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): 0x0a (LONG)

CD array entry = Column 4 (cluster 0, CD array offset 1): 0x02 (ONE_BYTE_SHORT)

Record Memory Dump

000000000B47A090: 2104a22a 82b20102 000b002b 00536575 †!.¢*²…..+.Seu

000000000B47A0A0: 204d6164 72756761 5265616c 697a616e † MadrugaRealizan

000000000B47A0B0: 646f206f 20746573 74652064 6120636f †do o teste da co

000000000B47A0C0: 6d707265 7373e36f †††††††††††††††††††mpressão

Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 1

ID = 2

Slot 1 Column 2 Offset 0xd Length 11 Length (physical) 11

NOME = Seu Madruga

Slot 1 Column 3 Offset 0x18 Length 8000 Length (physical) 32

DESCRICAO = [Error converting to string (length 8000 bytes)]

Slot 1 Column 4 Offset 0x5 Length 4 Length (physical) 1

IDADE = 50

Slot 1 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (31c013ac63e0)

Slot 2 Offset 0xc8 Length 49

Record Type = (COMPRESSED) PRIMARY_RECORD Record attributes = LONG DATA REGION

Record size = 49

CD Array

CD array entry = Column 1 (cluster 0, CD array offset 0): 0x02 (ONE_BYTE_SHORT)

CD array entry = Column 2 (cluster 0, CD array offset 0): 0x07 (SIX_BYTE_SHORT)

CD array entry = Column 3 (cluster 0, CD array offset 1): 0x0a (LONG)

CD array entry = Column 4 (cluster 0, CD array offset 1): 0x02 (ONE_BYTE_SHORT)

Record Memory Dump

000000000B47A0C8: 2104722a 83436861 76657388 01010020 †!.r*Chaves….

000000000B47A0D8: 00526561 6c697a61 6e646f20 6f207465 †.Realizando o te

000000000B47A0E8: 73746520 64612063 6f6d7072 657373e3 †ste da compressã

000000000B47A0F8: 6f†††††††††††††††††††††††††††††††††††o

Slot 2 Column 1 Offset 0x4 Length 4 Length (physical) 1

ID = 3

Slot 2 Column 2 Offset 0x5 Length 6 Length (physical) 6

NOME = Chaves

Slot 2 Column 3 Offset 0x11 Length 8000 Length (physical) 32

DESCRICAO = [Error converting to string (length 8000 bytes)]

Slot 2 Column 4 Offset 0xb Length 4 Length (physical) 1

IDADE = 8

Slot 2 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (da21809a8949)

Slot 3 Offset 0xf9 Length 54

Record Type = (COMPRESSED) PRIMARY_RECORD Record attributes = LONG DATA REGION

Record size = 54

CD Array

CD array entry = Column 1 (cluster 0, CD array offset 0): 0x02 (ONE_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): 0x0a (LONG)

CD array entry = Column 4 (cluster 0, CD array offset 1): 0x02 (ONE_BYTE_SHORT)

Record Memory Dump

000000000B47A0F9: 2104a22a 84870102 00090029 00436869 †!.¢*… .).Chi

000000000B47A109: 7175696e 68615265 616c697a 616e646f †quinhaRealizando

000000000B47A119: 206f2074 65737465 20646120 636f6d70 † o teste da comp

000000000B47A129: 72657373 e36f††††††††††††††††††††††††ressão

Slot 3 Column 1 Offset 0x4 Length 4 Length (physical) 1

ID = 4

Slot 3 Column 2 Offset 0xd Length 9 Length (physical) 9

NOME = Chiquinha

Slot 3 Column 3 Offset 0x16 Length 8000 Length (physical) 32

DESCRICAO = [Error converting to string (length 8000 bytes)]

Slot 3 Column 4 Offset 0x5 Length 4 Length (physical) 1

IDADE = 7

Slot 3 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (a365ea2df5bd)

Slot 4 Offset 0x12f Length 56

Record Type = (COMPRESSED) PRIMARY_RECORD Record attributes = LONG DATA REGION

Record size = 56

CD Array

CD array entry = Column 1 (cluster 0, CD array offset 0): 0x02 (ONE_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): 0x0a (LONG)

CD array entry = Column 4 (cluster 0, CD array offset 1): 0x02 (ONE_BYTE_SHORT)

Record Memory Dump

000000000B47A12F: 2104a22a 85b20102 000b002b 00427275 †!.¢*²…..+.Bru

000000000B47A13F: 78612064 6f203731 5265616c 697a616e †xa do 71Realizan

000000000B47A14F: 646f206f 20746573 74652064 6120636f †do o teste da co

000000000B47A15F: 6d707265 7373e36f †††††††††††††††††††mpressão

Slot 4 Column 1 Offset 0x4 Length 4 Length (physical) 1

ID = 5

Slot 4 Column 2 Offset 0xd Length 11 Length (physical) 11

NOME = Bruxa do 71

Slot 4 Column 3 Offset 0x18 Length 8000 Length (physical) 32

DESCRICAO = [Error converting to string (length 8000 bytes)]

Slot 4 Column 4 Offset 0x5 Length 4 Length (physical) 1

IDADE = 50

Slot 4 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (4884791b1f14)

Slot 5 Offset 0x167 Length 79

Record Type = (COMPRESSED) PRIMARY_RECORD Record attributes = LONG DATA REGION

Record size = 79

CD Array

CD array entry = Column 1 (cluster 0, CD array offset 0): 0x02 (ONE_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): 0x0a (LONG)

CD array entry = Column 4 (cluster 0, CD array offset 1): 0x02 (ONE_BYTE_SHORT)

Record Memory Dump

000000000B47A167: 2104a22a 86b20102 000b0042 00427275 †!.¢*²…..B.Bru

000000000B47A177: 78612064 6f203731 54415441 54415441 †xa do 71TATATATA

000000000B47A187: 54415441 54415441 54415441 54415441 †TATATATATATATATA

000000000B47A197: 54415441 54415441 54415441 54415441 †TATATATATATATATA

000000000B47A1A7: 54415441 4b4b4b4b 4b4b5441 544154††††TATAKKKKKKTATAT

Slot 5 Column 1 Offset 0x4 Length 4 Length (physical) 1

ID = 6

Slot 5 Column 2 Offset 0xd Length 11 Length (physical) 11

NOME = Bruxa do 71

Slot 5 Column 3 Offset 0x18 Length 8000 Length (physical) 55

DESCRICAO = [Error converting to string (length 8000 bytes)]

Slot 5 Column 4 Offset 0x5 Length 4 Length (physical) 1

IDADE = 50

Slot 5 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (74a6cc4021ee)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Algumas das informações mais importantes abaixo:

Coisas que chamam a atenção: a coluna de Descricao CHAR(8000) que antes era fixa agora é armazenada em página com tamanho variável e isso pode ser visto na prática. Essa informação é facilmente comprovada no destaque em verde (Length -( Physical) 32 (significa que os dados efetivamente ocupam 32 bytes). Trata-se de uma melhoria na tipagem, e o mesmo ocorre com outras colunas, como por exemplo, a coluna ID (que é um INT (4 bytes), mas como o valor 1 pode ser convertido em um tipo que use apenas 1 byte (comprove a informação acima na linha CD array entry = Column 1 ….. (ONE_BYTE_SHORT), a tipagem na página é alterada. Esse comportamento é observando tanto utilizando ROW como PAGE Compression.

E aqui vai um teste interessante…O que está em memória? As páginas comprimidas ou as páginas sem compressão? Bem, as páginas sem compressão foram carregadas primeiro. Será que foram substituídas pró-ativamente no Data Cache? Agora que as páginas sem compressão teoricamente são inúteis, Vamos comprovar na prática. Vamos utilizar uma das mil DMV’s disponíveis para verificar as páginas que estão em memória.

select * from sys.dm_os_buffer_descriptors where database_id = db_id('DComp') and file_id = 1 and page_id in (93,114)

Lembrando que a página 93 é a pré-compressão e a 114 = 114 pós-compressão.

Comentando algumas informações destacadas.

Row_count:

Detalhe em vermelho – Antes cabia um registro por página. Agora couberam 6, ou seja, toda a tabela, em uma só página (e caberia muito, mas muito mais páginas). Sabe o que isso significa? Menos páginas em memória, menos páginas sendo recuperadas do disco (que é um recurso lento), menos páginas pra uma query usar, e logo, mais velocidade….Em resumo, ótimo ganho. Muita gente pensa em economia de espaço mas nem todas pensam em economia de memória e relacionados. Claro que isso vai exigir uma descompressão na leitura das páginas e pra isso operações adicionais de CPU serão utilizadas. No geral, o tradeoff vale a pena. A regra de ouro é: teste em seu ambiente. Se você identificar que seu ambiente possui problemas com CPU e usar compressão pode ser arriscado, não use.

Free space in bytes:

Detalhe em azul – Mostra que coube pra página 93 apenas um registro na página e só sobraram 69 bytes na mesma. Muito pouco…. No caso da página 114, temos 7742 bytes livres. Matemática básica, mas levando em conta que o tamanho de uma página é de 8192 bytes…Qual seu ponto de vista: página meio cheia ou página meio vazia? =p

Is_modified:

Detalhe em verde – Mostra que trata-se de uma página suja (dirty page), ou seja, a versão da página que está em memória é diferente da que está em disco. As duas páginas estão sujas. Nenhuma novidade até então…

Você acha estranho a página sem compressão estar em memória? Se ela não vai ser mais utilizada….

Pode parecer estranho, pois:

Não existe pressão de memória no ambiente em questão. Logo, o Lazy Writer não foi executado (um dos processos que sincroniza memória com disco e libera páginas do data cache).
Nenhum CHECKPOINT foi executado (e mesmo se fosse, a página que não é mais necessária lá permaneceria, embora fossem marcadas como CLEAN PAGES (is_modified=0)….)
Não existe (até onde eu sei) nenhuma thread do SQL Server destinada a remover proativamente páginas do cache. Ou seja, não temos nenhum proactive writer da vida pra fazer isso (entendedores entenderão…).

Então, vamos ver se o CHECKPOINT tira a página da memória?

CHECKPOINT
select * from sys.dm_os_buffer_descriptors where database_id = db_id('DComp') and file_id = 1 and page_id in (93,114)

Aqui não tirou…

Então primeiro ponto: Usou compression, não necessariamente a antiga página sairá do cache (não de imediato), nem com checkpoint. Faz sentido que ela suma com o Lazy Writer…Vamos ver isso também em outras postagens…

Próximo Lab…Lazy Writer e Compressão Seletiva

O segundo ponto é: temos uma tabela com compressão. O que acontece se eu adicionar registros nela? Eles serão comprimidos em tempo real ou serão adicionados em uma página sem compressão, de modo que sejam comprimidos apenas no próximo rebuild com data compression?

Como criamos apenas uma tabela (que é um índice cluster), observamos que a compressão ocorre em tempo real. Caso você tivesse além de uma tabela clusterizada, um índice não-cluster SEM compressão, os dados seriam inseridos comprimidos no índice cluster e sem compressão no índice não-cluster. Essa granularidade da compressão é bem interessante e permite soluções diversificadas.

Caso tenha alguma observação, comentário, correção (principalmente), fique à vontade para comentar 🙂

[]’s

Recomendações de leitura

Data Compression: Strategy, Capacity Planning and Best Practices
http://technet.microsoft.com/en-us/library/dd894051(v=sql.100).aspx

Gustavo

October 3, 2014 at 8:30 pm

Cara, eu estava lendo exatamente esse artigo hoje. Hahaha.
Será que estamos assinando as mesmas publicações? Ou é só coincidência? =]

Excelente post, como é de costume seu. Parabéns!

Renato Siqueira
October 3, 2014 at 8:49 pm

Fala Gustavo,

Pura coincidência. Estou totalmente atrasado com meus feeds desta semana, kkkk. Esse artigo estava no One Note parado desde antes de irmos pro Sat, hehe.
Qual foi a publicação? Tenho interesse em ler, qualquer coisa manda link aqui 🙂

No mais, agradeço!