Compressão de Dados no
Microsoft SQL Server 2008
Pedro Antonio Galvão Junior.
MVP – Windows Server System – SQL Server.
Membro/Palestrante e Colunista Técnico Oficial Microsoft Brasil.
Coordenador de Projetos/DBA – Látex São Roque.
Professor – Núcleo de Tecnologia da Informação – AES São Roque.
[email protected]
Agenda
•
•
•
•
•
Microsoft SQL Server 2008.
Gerenciando o tamanho dos Dados no Storage.
Vardecimal Storage.
Compressão de Dados.
Compressão de backup de banco de dados.
Microsoft SQL Server 2008
GERENCIANDO O TAMANHO
DOS DADOS NO STORAGE
Gerenciando o Tamanho dos
Dados no Storage
Tamanho de utilização reduzido no Storage
Otimização de I/O-bound na performance da query
Tradeoff para a CPU custo aumentado no read/write
Problemas e Soluções
• Quais seriam os problemas?
• Soluções:
– Data Compression
– Backup Compression
Problemas de armazenamento
Large Database
Storage Cost
Manageability Cost
Backup/Recovery
Create/Rebuild of indexes
Bulk Import
Consistency Checking (e.g. CheckDB)
Soluções
Reduzir o espaço de armazenamento
Eficiência no armazenamento das página de dados e linhas
(+) Maior alocação de dados em memória.
(+) Maior performance para carga de dados.
(-) Menor custo ou degração da utilização de CPU
Solução baseada no Microsoft SQL Server
2005:
Vardecimal
Storage
Format
SQL Server
Compression
2008: Backup
Compression
2008: ROW
and PAGE
Compression
VARDECIMAL STORAGE
Vardecimal Storage
Os tipos de dados decimal e numeric normalmente são
armazenados no disco como dados de comprimento fixo. O tipo de
dados numeric é funcionalmente equivalente ao tipo de dados
decimal. No SQL Server 2005 Service Pack 2 (SP2) e nas versões
posteriores, os tipos de dados decimal e numeric podem ser
armazenados como uma coluna de comprimento variável usando o
formato de armazenamento vardecimal. Esse formato de
armazenamento está disponível somente nas edições Enterprise,
Developer e Evaluation do SQL Server.
Efeitos:
Redução do espaço de armazenamento de dados;
Elevação de quantidade transferidos por segundo;
Pequeno aumento de utilização de CPU;
Mudanças não permitidas na estrutura da aplicação;
Observações - Vardecimal Storage
 Requer SQL Server 2005 SP2 ou versões posteriores.
O formato de armazenamento vardecimal não pode ser habilitado nos bancos de
dados do sistema: mestre, modelo, msdb, tempdb ou distribuição. Quando uma
consulta classifica dados armazenados em formato de armazenamento vardecimal,
eles são classificados no tempdb em um estado decimal fixo. Geralmente, os dados
irão exigir um espaço significativamente maior no tempdb do que o espaço ocupado
pela tabela de origem do formato de armazenamento vardecimal no banco de dados
de origem.
O formato de armazenamento vardecimal não pode ser aplicado a exibições,
exibições indexadas, índices XML e índices de texto completo. No entanto, as tabelas
subjacentes a esses objetos podem usar o formato de armazenamento vardecimal.
 Tabelas internas, como as tabelas de metadados e notificação, não podem usar o
formato de armazenamento vardecimal.
Observações - Vardecimal Storage
Funções com valor de tabela não podem usar o formato de armazenamento
vardecimal.
A coluna numeric armazenada em uma tabela no formato de armazenamento
vardecimal também não pode ser criptografada.
Não há suporte para partições heterogêneas (ou seja, partições com formato
decimal fixo e de armazenamento vardecimal).
Novas tabelas criadas a partir da tabela com formato de armazenamento vardecimal
usando a sintaxe Transact-SQL SELECT … INTO… não herdam o formato de
armazenamento vardecimal.
Não é possível alterar os estados de formato de armazenamento vardecimal de
bancos de dados habilitados para espelhamento de banco de dados. É necessário
remover o espelhamento de banco de dados para habilitar o formato de
armazenamento vardecimal no banco de dados. No entanto, não é necessário remover
o espelhamento de banco de dados quando tabelas individuais são habilitadas ou
desabilitadas para o formato de armazenamento vardecimal.
Vardecimal Storage
Precisão da
coluna
Tamanho de
decimal fixo
original (bytes)
Área máxima de
dados
vardecimais
(bytes)
Sobrecarga para
armazenar
deslocamento
(bytes)
Armazenamento
vardecimal
máximo usado
(bytes)
1-3
4-6
7-9
10-12
13-15
16-18
19
20-21
22-24
25-27
28
29-30
31-33
34-36
37-38
5
5
5
9
9
9
9
13
13
13
13
17
17
17
17
3
4
5
6
8
9
10
10
11
13
14
14
15
16
18
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
5
6
7
8
10
11
12
12
13
15
16
16
17
18
20
COMPRESSÃO DE DADOS
Compressão de Dados
• Melhora na performance de Consultas
• Habilitado por tabela ou indice
• Tradeoff em utilização de CPU
Data Compression
DateId
CarrierTracking
OfferID
PriceDisc
20070601 4911-403C-98
10
0.00
20070601 4911-403C-99
10
0.00
20070602 6431
10
0.00
20070602 6431-4D57-83
10
0.00
20070602 6431-4D57-84
10
0.00
20070602 6431-4D57-85
10
100.00
20070603 4E0A-4F89-AE
10
0.00
Data Compression
DateId
CarrierTracking
OfferID
PriceDisc
20070601 4911-403C-98
10
0.00
20070601 4911-403C-99
10
0.00
20070602 6431
10
0.00
20070602 6431-4D57-83
10
0.00
20070602 6431-4D57-84
10
0.00
20070602 6431-4D57-85
10
100.00
20070603 4E0A-4F89-AE
10
0.00
DateId
OfferID
PriceDisc
20070601 4911-403C-98
10
0.00
20070601 4911-403C-99
10
0.00
20070602 6431
10
0.00
20070602 6431-4D57-83
10
0.00
20070602 6431-4D57-84
10
0.00
20070602 6431-4D57-85
10
100.00
20070603 4E0A-4F89-AE
10
0.00
CarrierTracking
• Microsoft® SQL
Server™ 2005
Service Pack 2 (SP2)
– VarDecimal
• Permite utilizar
valores decimais
para armazenamento
de dados de
tamanho variável.
Data Compression
DateId
CarrierTracking
OfferID
PriceDisc
20070601 4911-403C-98
10
0.00
20070601 4911-403C-99
10
0.00
20070602 6431
10
0.00
20070602 6431-4D57-83
10
0.00
20070602 6431-4D57-84
10
0.00
20070602 6431-4D57-85
10
100.00
20070603 4E0A-4F89-AE
10
0.00
DateId
OfferID
PriceDisc
20070601 4911-403C-98
10
0.00
20070601 4911-403C-99
10
0.00
20070602 6431
10
0.00
20070602 6431-4D57-83
10
0.00
20070602 6431-4D57-84
10
0.00
20070602 6431-4D57-85
10
100.00
20070603 4E0A-4F89-AE
10
0.00
CarrierTracking
• Coluna com
tamanho fixo:
– SQL Server 2008
estende a lógica de
tamanho fixo para
todos os tipos de
campos:
• int, bigint, etc.
Data Compression
DateId
OfferID
PriceDisc
20070601 4911-403C-98
10
0.00
20070601 4911-403C-99
10
0.00
20070602 6431
10
0.00
20070602 6431-4D57-83
10
0.00
20070602 6431-4D57-84
10
0.00
20070602 6431-4D57-85
10
100.00
20070603 4E0A-4F89-AE
10
0.00
DateId
OfferID
PriceDisc
1
CarrierTracking
CarrierTracking
2
2007060
4911-403C-9
3
– A lista de prefixos é
armazenada
na
página para prefixos
comuns.
6431-4D57-8
1 1
2 8
10
0.00
1 1
2 9
10
0.00
1 2
3
10
0.00
1 2
3 3
10
0.00
1 2
3 4
10
0.00
1 2
3 5
10
100.00
1 3
4E0A-4F89-AE
10
0.00
4
• Compressão de
Prefixo:
– Valores individuais
são substituidos:
• Token para prefixo
• Sufixo para valor
Data Compression
DateId
1
CarrierTracking
2
2007060
OfferID
3
4911-403C-9
PriceDisc
6431-4D57-8
1 1
2 8
10
0.00
1 1
2 9
10
0.00
1 2
3
10
0.00
1 2
3 3
10
0.00
1 2
3 4
10
0.00
1 2
3 5
10
100.00
1 3
4E0A-4F89-AE
10
0.00
DateId
CarrierTracking
OfferID
PriceDisc
4
1
2007060
1
1
1
1
2 8
3
4
1
1
2 9
3
4
1
2
3
3
4
1
2
3 3
3
4
1
2
3 4
3
4
1
2
3 5
3
4E0A-4F89-AE
3
1 3
2
2
2
3
10
3
4911-403C-9
4
4
6431-4D57-8
0.00
100.00
4
• Dicionário de compressão:
– O valor comum é
armazenado na página
– Valores comuns são
substituidos por tokens
• 2X para 7X taxa de
compressão real para
dados fato no DW de
forma antecipada,
dependendo do dado
Compressão de linha
Antes da Compressão
Depois da
Compressão
34 [int]
4 bytes
1 byte
32,767 [smallint]
2 bytes
2 bytes
Redmond [char(50)]
50 bytes
7 bytes
WA [char(2)]
2 bytes
2 bytes
Value [Datatype]
Compression Pagina
Page Header
aaabb
aaaab
aaabcc bbbb
Page Header
Page Header
aaabcc aaaacc abcd
aaabcc aaaacc abcd
abcd
aaabb
4
b
abcd
aaabcc [bbbb
0bbbb] abcd
aaaccc aaaacc bbbb
aaaab
4
b
abcd
aaaccc
aaaacc [0bbbb
bbbb]
3
ccc
4b
[0bbbb]
4b
0
4b
0
1
[0bbbb]
3ccc
[0bbbb]
1
Revisão: Compressão de Dados
Quais efeitos NÃO resultam em compressão de
dados?
1. Decréssimo na utilzação do storage (espaço)
2. Diminuição do custo de CPU
3. Ganho em performance de uma query
4.Todas acima.
Revisão: Compressão de Dados
Identique a sequência correta dos passos
durante a compressão de página.
3 Dictionary compression
1 Row compression
2 Prefix compression
COMPRESSÃO DE BACKUP
Compressão de Backup
• Economia de espaço
em disco
• Backups e
Restaurações mais
rápidos
• Detecção automática
da compressão no
processo de RESTORE
Considerações sobre a
Compressão de Backup
• Disponível apenas no Microsoft SQL Server
2008 Enterprise Edition
• Desativado por Padrão
• Métodos de utilizar a compressão:
– EXEC sp_configure ‘backup compression default’, ‘1’
– RECONFIGURE WITH OVERRIDE
– BACKUP…WITH COMPRESSION
Considerações sobre a
Compressão de Backup (cont.)
• Somente um tipo de
backup no conjunto de
mídia (Media Set)
• Dados compactados
podem não sofrer
compressão durante o
Backup
Demonstração
Compressão de Backup
Conclusão
• Embora a criação de backups compactados seja suportada apenas no SQL
Server 2008 Enterprise e posterior, toda edição do SQL Server 2008 ou
posterior pode restaurar um backup compactado.
• O SQL Server 2008 oferece suporte a compactação de linha e de página para
tabelas e índices. A compactação de dados pode ser configurada para os
seguintes objetos de banco de dados:
–
–
–
–
–
Uma tabela inteira que é armazenada como um heap.
Uma tabela inteira que é armazenada como um índice clusterizado.
Um índice não clusterizado inteiro.
Uma exibição indexada inteira.
Para tabelas e índices particionados, a opção de compactação pode ser configurada para cada
partição e as várias partições de um objeto não precisam ter a mesma configuração de
compactação.
Maiores informações
• Visite TechNet www.microsoft.com/technet/brasil
• Visite MSDN www.microsoft.com/msdn/brasil
• SQL Server Express home page
http://http://www.microsoft.com/brasil/sql/sqlex
press.mspx
• SQL Server Express Edition overview
http://msdn.microsoft.com/library/default.asp?url=/li
brary/en-us/dnsse/html/sseoverview.asp
Fóruns e Colunas Técnicas
• Media Center:
https://www.technetbrasil.com.br/mediacenter/subje
ct.aspx?center=16
• Fórum – TechNet – SQL Server:
http://forums.microsoft.com/TechnetBR/default.aspx?ForumGroupID=198&SiteID=29
• Fórum – MSDN – SQL Server:
http://forums.microsoft.com/MSDNBR/default.aspx?ForumGroupID=148&SiteID=21
Questões ?
• [email protected]
Blog
• http://juniorgalvao-mvp2007.spaces.live.com
Seu potencial. Nossa inspiração.
© 2006 Microsoft Corporation. Todos os direitos reservados.
O propósito desta apresentação é apenas informativa. Microsoft não faz nenhuma garantia expressa ou implícita nesta apresentação.
MR