Table of Contents IntroduçãoArmazenamento
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 –http://luanmorenodba.wordpress.com/2012/03/16/entenda-o-columnstore-indexes/
Aqui iremos detalhar como ele é utilizado.
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).
(Figura 1 – Tabela contendo informações de empregados.)
No formato tradicional de armazenamento “Row Store” os dados são colocados da seguinte forma:
(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;
IF OBJECT_ID(‘dbo.sp_AllocationMetadata’) IS NOT NULL
DROP PROCEDURE dbo.sp_AllocationMetadata;
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),
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],
SUBSTRING(sa.root_page, 6, 1) +
SUBSTRING(sa.root_page, 5, 1))) +
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],
SUBSTRING(sa.first_iam_page, 6, 1) +
SUBSTRING(sa.first_iam_page, 5, 1))) +
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);
EXEC sys.sp_MS_marksystemobjectsp_AllocationMetadata;
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’
(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 = 0×0000000260414000 bhash = 0×0000000000000000 bpageno = (1:435) bdbid = 2 breferences = 0 bcputicks = 0 bsampleCount = 0 bUse1 = 25636 bstat = 0x10b blog = 0xcccccccc bnext = 0×0000000000000000
PAGE HEADER:
Page @0×0000000260414000
m_pageId = (1:435) m_headerVersion = 1 m_type = 1 m_typeFlagBits = 0×0 m_level = 0 m_flagBits = 0×8000 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) = 0×61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = NOT CHANGED ML (1:7) = NOT MIN_LOGGED
Slot 0 Offset 0×60 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 0×4 Length 4 Length (physical) 4
ID = 1
Slot 0 Column 2 Offset 0×14 Length 11 Length (physical) 11
Nome = Luan Moreno
Slot 0 Column 3 Offset 0×8 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 0×9 Length 2 Length (physical) 2
Estado = DF
Slot 1 Offset 0×87 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 0×4 Length 4 Length (physical) 4
ID = 2
Slot 1 Column 2 Offset 0×14 Length 15 Length (physical) 15
Nome = Auguimar Júnior
Slot 1 Column 3 Offset 0×8 Length 1 Length (physical) 1
Slot 1 Column 4 Offset 0×23 Length 7 Length (physical) 7
Cidade = Goiânia
Slot 1 Column 5 Offset 0×9 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 0×4 Length 4 Length (physical) 4
ID = 3
Slot 2 Column 2 Offset 0×14 Length 13 Length (physical) 13
Nome = Taciana Dória
Slot 2 Column 3 Offset 0×8 Length 1 Length (physical) 1
Sexo = F
Slot 2 Column 4 Offset 0×21 Length 8 Length (physical) 8
Slot 2 Column 5 Offset 0×9 Length 2 Length (physical) 2
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 0×4 Length 4 Length (physical) 4
ID = 4
Slot 3 Column 2 Offset 0×14 Length 16 Length (physical) 16
Nome = Antônio de Pádua
Slot 3 Column 3 Offset 0×8 Length 1 Length (physical) 1
Slot 3 Column 4 Offset 0×24 Length 8 Length (physical) 8
Cidade = Maranhão
Slot 3 Column 5 Offset 0×9 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:
(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.
(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.
(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’)
(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.