Desvendando o ColumnStore Index

Posted on junho 5, 2012

4


 

Introdução

 

Com a grande expansão e crescimento das informações, vemos hoje que a facilidade em se té-las cresce a cada dia.  Para que essas informações sejam disponibilizadas de uma forma concisa e conclusiva, possúimos em muita das vezes Data Warehouses para o manuseio dessas informações.

 

Porém quanto mais requisições em nosso sistema, mais temos demora na busca desses registros dentro do banco de dados. A consulta que demorava x tempo, agora passa a ocupar 10 x do tempo. Com isso temos a perda da confiabilidade e demora na entrega das informações.

 

Como muitas organização passam por esse problema, a nova versão do SQL Server, introduziu um novo recurso para que esse tempo de resposta seja 80% ~ 90% mais efetivo do que o normal.

 

Para enteder melhor esse novo recurso leia primeiramente este artigo – https://luanmorenodba.wordpress.com/2012/03/16/entenda-o-columnstore-indexes/

 

Aqui iremos detalhar como ele é utilizado.

 

Armazenamento

 

Como já sabemos, tradicionalmente os dados são armazenados em tabelas (heaps) ou em índices (B-Tree), esse armazenamento é se dados baseado em páginas e linhas.

 

Este novo tipo de armazenamento faz com que cada coluna seja separada em um conjunto de páginas fazendo com que milhares de registros sejam armazenadas em somente uma página de dados (8k).

 

image

(Figura 1 – Tabela contendo informações de empregados.)

 

No formato tradicional de armazenamento “Row Store” os dados são colocados da seguinte forma:

 

image

(Figura 2 – Armazenamento Tradicional das informações.)

 

USE tempdb

GO

 

CREATE TABLE Empregados

(

     ID INT IDENTITY NOT NULL,

     Nome VARCHAR(50),

     Sexo CHAR(1),

     Cidade VARCHAR(50),

     Estado CHAR(2)

)

 

INSERT INTO Empregados(Nome, Sexo, Cidade, Estado)

VALUES (‘Luan Moreno’,‘M’,‘Brasília’,‘DF’),

        (‘Auguimar Júnior’,‘M’,‘Goiânia’,‘GO’),

        (‘Taciana Dória’,‘F’,‘Brasília’,‘DF’),

        (‘Antônio de Pádua’,‘M’,‘Maranhão’,‘MA’)

 

Iremos analisar as informações de armazenamento dentro do banco de dados. Antes disso iremos criar uma procedure para que possamos saber em qual local se encontra as informações que desejamos analisar.

 

USE tempdb;

GO

 

IF OBJECT_ID(‘dbo.sp_AllocationMetadata’) IS NOT NULL

     DROP PROCEDURE dbo.sp_AllocationMetadata;

GO

 

CREATE PROCEDURE dbo.sp_AllocationMetadata

(

     @object VARCHAR (128) = NULL

)

AS

SELECT

     OBJECT_NAME(sp.object_id) AS [Object Name],

     sp.index_id AS [Index ID],

     sa.allocation_unit_id AS [Alloc Unit ID],

     sa.type_desc AS [Alloc Unit Type],

     ‘(‘ + CONVERT(VARCHAR (6),

          CONVERT(INT,

                SUBSTRING(sa.first_page, 6, 1) +

                SUBSTRING(sa.first_page, 5, 1))) +

     ‘:’ + CONVERT(VARCHAR (20),

          CONVERT(INT,

                SUBSTRING(sa.first_page, 4, 1) +

                SUBSTRING(sa.first_page, 3, 1) +

                SUBSTRING(sa.first_page, 2, 1) +

                SUBSTRING(sa.first_page, 1, 1))) +

     ‘)’ AS [First Page],

     ‘(‘ + CONVERT(VARCHAR (6),

          CONVERT(INT,

                SUBSTRING(sa.root_page, 6, 1) +

                SUBSTRING(sa.root_page, 5, 1))) +

     ‘:’ + CONVERT(VARCHAR (20),

          CONVERT(INT,

                SUBSTRING(sa.root_page, 4, 1) +

                SUBSTRING(sa.root_page, 3, 1) +

                SUBSTRING(sa.root_page, 2, 1) +

                SUBSTRING(sa.root_page, 1, 1))) +

     ‘)’ AS [Root Page],

     ‘(‘ + CONVERT(VARCHAR (6),

          CONVERT(INT,

                SUBSTRING(sa.first_iam_page, 6, 1) +

                SUBSTRING(sa.first_iam_page, 5, 1))) +

     ‘:’ + CONVERT(VARCHAR (20),

          CONVERT(INT,

                SUBSTRING(sa.first_iam_page, 4, 1) +

                SUBSTRING(sa.first_iam_page, 3, 1) +

                SUBSTRING(sa.first_iam_page, 2, 1) +

                SUBSTRING(sa.first_iam_page, 1, 1))) +

     ‘)’ AS [First IAM Page]   

FROM

     sys.system_internals_allocation_units AS sa,

     sys.partitions AS sp

WHERE

     sa.container_id = sp.partition_id

AND sp.object_id =

     (CASE WHEN (@object IS NULL)

          THEN sp.object_id

          ELSE OBJECT_ID(@object)

     END);

GO

 

EXEC sys.sp_MS_marksystemobjectsp_AllocationMetadata;

GO

Este script foi retirado do site – http://www.sqlskills.com/BLOGS/PAUL/post/Inside-The-Storage-Engine-sp_AllocationMetadata-putting-undocumented-system-catalog-views-to-work.aspx

Executando temos que:

EXEC tempdb.dbo.sp_AllocationMetadata‘Empregados’

image

(Figura 3 – Localização da primeira página de dados da tabela Empregados.)

 

Agora para visualizarmos iremos habilitar o trace 3604 para a visualização e utilizar o DBCC PAGE (veja como utilizá-lo) para entrarmos dentro da página de dados do SQL Server.

 

DBCC TRACEON(3604)

DBCC PAGE (2, 1, 435, 3)

 

Visualizando

PAGE: (1:435)

BUFFER:

BUF @0x000000027FC260C0

bpage = 0x0000000260414000          bhash = 0x0000000000000000          bpageno = (1:435)
bdbid = 2                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 25636                       bstat = 0x10b
blog = 0xcccccccc                   bnext = 0x0000000000000000

PAGE HEADER:

Page @0x0000000260414000

m_pageId = (1:435)                  m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 26959 m_indexId (AllocUnitId.idInd) = 11264
Metadata: AllocUnitId = 3170534139435614208
Metadata: PartitionId = 2810246169036783616                              Metadata: IndexId = 0
Metadata: ObjectId = 325576198      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 11                        m_slotCnt = 4                       m_freeCnt = 7922
m_freeData = 262                    m_reservedCnt = 0                   m_lsn = (40:200:20)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 0                      DB Frag ID = 1

Allocation Status

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

Slot 0 Offset 0x60 Length 39

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

0000000000000000:   30000b00 01000000 4d444605 00000200 1f002700  0…….MDF…….’.
0000000000000014:   4c75616e 204d6f72 656e6f42 726173ed 6c6961    Luan MorenoBrasília

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

ID = 1                             

Slot 0 Column 2 Offset 0x14 Length 11 Length (physical) 11

Nome = Luan Moreno                 

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

Sexo = M                           

Slot 0 Column 4 Offset 0x1f Length 8 Length (physical) 8

Cidade = Brasília                  

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

Estado = DF                        

Slot 1 Offset 0x87 Length 42

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 42
Memory Dump @0x0000000010B9A087

0000000000000000:   30000b00 02000000 4d474f05 00000200 23002a00  0…….MGO…..#.*.
0000000000000014:   41756775 696d6172 204afa6e 696f7247 6f69e26e  Auguimar JúniorGoiân
0000000000000028:   6961                                          ia

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

ID = 2                             

Slot 1 Column 2 Offset 0x14 Length 15 Length (physical) 15

Nome = Auguimar Júnior             

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

Sexo = M                           

Slot 1 Column 4 Offset 0x23 Length 7 Length (physical) 7

Cidade = Goiânia                   

Slot 1 Column 5 Offset 0x9 Length 2 Length (physical) 2

Estado = GO

Slot 2 Offset 0xb1 Length 41

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 41
Memory Dump @0x0000000010B9A0B1

0000000000000000:   30000b00 03000000 46444605 00000200 21002900  0…….FDF…..!.).
0000000000000014:   54616369 616e6120 44f37269 61427261 73ed6c69  Taciana DóriaBrasíli
0000000000000028:   61                                            a

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

ID = 3                             

Slot 2 Column 2 Offset 0x14 Length 13 Length (physical) 13

Nome = Taciana Dória               

Slot 2 Column 3 Offset 0x8 Length 1 Length (physical) 1

Sexo = F                           

Slot 2 Column 4 Offset 0x21 Length 8 Length (physical) 8

Cidade = Brasília                  

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

Estado = DF         

Slot 3 Offset 0xda Length 44

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 44
Memory Dump @0x0000000010B9A0DA

0000000000000000:   30000b00 04000000 4d4d4105 00000200 24002c00  0…….MMA…..$.,.
0000000000000014:   416e74f4 6e696f20 64652050 e1647561 4d617261  Antônio de PáduaMara
0000000000000028:   6e68e36f                                      nhão

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

ID = 4                             

Slot 3 Column 2 Offset 0x14 Length 16 Length (physical) 16

Nome = Antônio de Pádua            

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

Sexo = M                           

Slot 3 Column 4 Offset 0x24 Length 8 Length (physical) 8

Cidade = Maranhão                  

Slot 3 Column 5 Offset 0x9 Length 2 Length (physical) 2

Estado = MA

 

Assim temos uma maior noção de como os dados são armazenados dentro do banco de dados no modelo de armazenamento Row Store.

 

Agora no novo formato “Column  Store”  os dados são apresentados da seguinte forma:

 

image

(Figura 4 – Novo modo de armazenamento.)

 

Como podemos ver a maior diferença entre os modelos é que o ColumnStore Index armazena e agrupa os dados por colunas e assim mostra todas elas para que seja possível recuperar as informações mais rapidamente.

 

 

 

 

Separando fisicamente as informações por colunas a egine é capaz de realizar as leituras que são somente necessárias, isso faz com que a redução de I/O aconteça.

 

Como os dados são armazenados em colunas compartilhadas, o nível de compressão é muito maior e efetivo, essa compressão se chama VertiPaq e é a mesma utilizada no SQL Server 2008 R2 Analysis Services. Esse novo tipo de compressão é muito mais eficiente que a compressão de página e dados do SQL Server. Quando essa compressão é utilizada as requisições de I/O são bem menores fazendo com que a busca em disco seja mais eficiente.

 

image

(Figura 5 – Buscando informações da consulta.)

 

Analisando melhor, como possuímos os dados armazenados em colunas, então quando desejamos realizar uma chamada de informações, não é necessário realizar a chamada de diversas páginas encadeadas como no modelo Row Based.

 

A tecnologia Batch Mode Processing utiliza vetores para a localização das informações. Esse modo de operação é utilizado para operadores otimizados como Hash Join e Hash Aggregation. Todos eles são otimizados para utilizar todos os recursos da máquinas assim como melhorar o processamento e a performance das pesquisas.

 

Os dados são separados em Segmentos. O Segmento nada mais é do que dados de uma coluna que pode armazenar até 1 milhão de linhas. Em vez de armazenar os dados por página, o mesmo é armazenado por gurpos de linhas. Cada seguimento é internamente armazenado como um LOB. Porém quando o SQL Server realiza a leitura dessas informações e como a unidade de leitura de disco é constituido por um seguimento e o seguimento é uma unidade de transferência entre disco e a memória, isso faz com que a busca seja mais eficiente e lucrativa.

 

image

(Figura 6 – Visualizando os segmentos e os grupos de linhas.)

 

Utilizando o banco de dados AdventureWorks2012, vamos ver que na tabela FactFinace nã possuímos índice criado na mesmo, porém depois de criarmos o Column Store Indexes vamos ver que a quantidade de página armazenadas em LOB e bem inferior as que foram armazenadas IN-ROW….

USE AdventureWorksDW2012

go

 

CREATE NONCLUSTERED COLUMNSTORE INDEX CSIidxNCL_FactFinance

ON FactFinance

(

      FinanceKey,

      DateKey,

      OrganizationKey,

      DepartmentGroupKey,

      ScenarioKey,

      AccountKey,

      Amount,

      Date

)

 

SELECT AU.*

FROM sys.system_internals_allocation_units AS AU

INNER JOIN SYS.Partitions AS P

ON AU.Container_id = P.Partition_id

WHERE Object_ID = object_id(‘FactFinance’)

go

 

image

(Figura 7 – Quantidade de Páginas utilizando o ColumnStore Index.)

 

Sendo assim a quantidade de página depois do ColumnStore Index é muito inferior, saimos de 337 páginas para 90.