Post on 18-Apr-2015
Vladimir Michel Bacurau MagalhãesMCITP | MCTS | MCP | MMI | MCT
vladimir_magalhaes@ig.com.br
www.vladimir-magalhaes.spaces.live.com
Pedro Antonio Galvão JuniorEngenheiro de Processos/DBAMVP SQL Server
pedrogalvaojunior@gmail.comhttp://juniorgalvao-mvp2007.spaces.live.com
Compressão de Dados no Microsoft SQL
Server 2008
Marcondes AlexandreMCITP | MCTS | MCP | MCT | IT Hero
marcondesalexandre@yahoo.com.brwww.marcondesalexandre.spaces.live.com
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
Antes da Compressão Depois da Compressão
Problemas e Soluções
• Quais seriam os problemas?
• Soluções:– Data Compression – Backup Compression
Problemas de armazenamento
Large DatabaseStorage CostManageability Cost
Backup/RecoveryCreate/Rebuild of indexesBulk 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
SQL Server
Compression
2005: Vardecim
al Storage Format
2008: ROW and
PAGE Compres
sion
2008: Backup
Compression
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;
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.
Observações - Vardecimal Storage
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 5 3 2 54-6 5 4 2 67-9 5 5 2 710-12 9 6 2 813-15 9 8 2 1016-18 9 9 2 1119 9 10 2 1220-21 13 10 2 1222-24 13 11 2 1325-27 13 13 2 1528 13 14 2 1629-30 17 14 2 1631-33 17 15 2 1734-36 17 16 2 1837-38 17 18 2 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 CompressionDateId 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
• Microsoft® SQL Server™ 2005 Service Pack 2 (SP2)– VarDecimal
• Permite utilizar valores decimais para armazenamento de dados de tamanho variável.
DateId CarrierTracking OfferID PriceDisc
20070601 4911-403C-98 10 0.0020070601 4911-403C-99 10 0.0020070602 6431 10 0.0020070602 6431-4D57-83 10 0.0020070602 6431-4D57-84 10 0.0020070602 6431-4D57-85 10 100.0020070603 4E0A-4F89-AE 10 0.00
DateId CarrierTracking OfferID PriceDisc
20070601 4911-403C-98 10 0.0020070601 4911-403C-99 10 0.0020070602 6431 10 0.0020070602 6431-4D57-83 10 0.0020070602 6431-4D57-84 10 0.0020070602 6431-4D57-85 10 100.0020070603 4E0A-4F89-AE 10 0.00
Data Compression
• Coluna com tamanho fixo:
– SQL Server 2008 estende a lógica de tamanho fixo para todos os tipos de campos:
• int, bigint, etc.
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 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
• Compressão de Prefixo:– A lista de prefixos é
armazenada na página para prefixos comuns.
– Valores individuais são substituidos:
• Token para prefixo• Sufixo para valor
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 CarrierTracking OfferID PriceDisc
1 8 10 0.00
1 9 10 0.00
2 10 0.00
2 3 10 0.00
2 4 10 0.00
2 5 10 100.00
3 4E0A-4F89-AE 10 0.00
4911-403C-92 6431-4D57-8320070601
1
1
1
1
1
1
1
2
2
3
3
3
3
4
Data Compression
• 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
DateId CarrierTracking OfferID PriceDisc
1 8 10 0.00
1 9 10 0.00
2 10 0.00
2 3 10 0.00
2 4 10 0.00
2 5 10 100.00
3 4E0A-4F89-AE 10 0.00
4911-403C-92 6431-4D57-8320070601
1
1
1
1
1
1
1
2
2
3
3
3
3
4
DateId CarrierTracking OfferID PriceDisc
8
9
3
4
5 100.00
3 4E0A-4F89-AE
4911-403C-92 6431-4D57-8320070601
1
1
1
1
1
1
1
2
2
3
3
3
3
4
22 10311 0.004
1
1
2
2
2
2
3
3
3
3
3
3
3
4
4
4
4
4
4
Value [Datatype] Antes da CompressãoDepois 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
Compressão de linha
Page Header
Compression Pagina
Page Header
aaabb aaaab
aaabcc bbbb
aaaccc aaaacc
abcd
abcd
bbbb
Prefixo Compressão
DicionarioCompressão
Page Header
4b 4b
[0bbbb]
3ccc [0bbbb]
aaabcc aaaacc
4b
abcdaaabcc abcd
abcd
bbbb
abcdaaabb
aaabcc
aaaccc
aaaab
bbbb
aaaacc
4b
aaabcc
3ccc
aaabcc aaaacc
[0bbbb]
4b
aaaacc
[0bbbb]
0 0
1
1
[0bbbb]
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 CPU3. Ganho em performance de uma query4.Todas acima.
Revisão: Compressão de Dados
Identique a sequência correta dos passos durante a compressão de página.
Dictionary compression
Row compression
Prefix compression
Revisão: Compressão de Dados
1
2
3
Compressão de dados
Demonstração
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
Compressão de Backup
Demonstração
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 pagehttp://http://www.microsoft.com/brasil/sql/sqlexpress.mspx
• SQL Server Express Edition overviewhttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sseoverview.asp
Fóruns e Colunas Técnicas• Media Center:
https://www.technetbrasil.com.br/mediacenter/subject.aspx?center=16
• Fórum – TechNet – SQL Server: http://forums.microsoft.com/Technet-BR/default.aspx?ForumGroupID=198&SiteID=29
• Fórum – MSDN – SQL Server: http://forums.microsoft.com/MSDN-BR/default.aspx?ForumGroupID=148&SiteID=21
Blog
• http://juniorgalvao-mvp2007.spaces.live.com
Obrigado pelo Apoio
Did you know?
SQL Server Central has highly active forums, where you can get a response to a question on SQL Server and other topics in a matter of minutes.
Did you know?
Simple-Talk.com has a wealth of articles written by industry experts on SQL Server, .NET and SysAdmin, with no registration required to access each article.
Did you know?
“Red Gate Software sponsored SQL Server Day. We’d love to hear your comments and suggestions about our tools. To get in touch just email communities@red-gate.com"Annabel Bradford Red Gate Software
© 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.
Seu potencial. Nossa inspiração.MR
Obrigado !