A Evolução do Data
Warehousing no Microsoft
SQL Server 2008
Pedro Antonio Galvão Junior
Latex São Roque – Engenheiro de Processos
MVP – Windows Server System – SQL Server
[email protected]
Agenda
• Visão geral das melhorias de SQL Server
2008 para data warehousing.
• Considerações sobre caracteristicas de
performance.
Microsoft SQL Server 2008
Data Warehousing
• Melhorias em toda a caixa
– Integration Services, Database Engine, Analysis
– Services, Reporting Services
• Melhoria no produto como um todo
– Foco em performance e escalabilidade
• End-to-end testing on large scale customer-driven
• configuration
– Database Engine: to 100 billion fact table rows, 32 cores
– Analysis Services: to 25 billion fact table rows, 16 cores
Melhorias em Data Warehousing
Extração,
Transformação, e
Carga (ETL)
Consulta
e
Análise
Administração
e
Gerenciamento
Melhorias em Extração, Transformação e CargaMerge, Minimal
Merge, Minimal Logging, Insert over DML, Change Data Capture, Integration
Services threading model, Lookup Enhancements, Compression
Merge
Database
Engine
Available
in CTP
• Nova instrução DML que combina várias
operações DML.
• Building block para maior eficiencia para ETL.
• Implementãção compliance SQL-2006.
Database
Engine
Merge
Available
in CTP
• Nova instrução DML que combina várias operações DML.
– Building block para maior eficiência para ETL.
– Implementação SQL-2006.
Fonte
XXXXX XXX XXX
XXXX XXX
XXXXXXXXXX
X XXX XXXX XX
XX XXXX
XXXXX XXX XX
Fonte de
dados pode
ser qualquer
table ou
consulta
Database
Engine
Merge
Available
in CTP
• Nova instrução DML que combina várias operações DML.
– Building block para maior eficiência para ETL.
– Implementação SQL 2006.
Origem
Destino
XXXXX XXX XXX
XXXX XXX
XXXXXXXXXX
X XXX XXXX XX
XX XXXX
XXXXX XXX XX
XXXXX X
XXXX
Destino pode
ser qualquer
tabela ou view
atualizável
XXX XXX
Database
Engine
Merge
Available
in CTP
• Nova instrução DML que combina várias operações DML.
– Building block para maior eficiência para ETL
– Implementação SQL 2006
Origem
Destino
XXXXX XXX XXX
XXXX XXX
XXXXX XXX XXX
XXXX XXX
XXXXXXXXXX
X XXX XXXX XX
XX XXXX
XXXXX XXX XX
Se a fonte
corresponder
com o destino
Update
XXX XXX
Database
Engine
Merge
Available
in CTP
• Nova instrução DML que combina várias operações DML.
– Building block para maior eficiência para ETL.
– Implementação SQL 2006.
Origem
Destino
XXXXX XXX XXX
XXXX XXX
XXXXX XXX XXX
XXXX XXX
XXXXXXXXXX
X XXX XXXX XX
XX XXXX
XXXXX XXX XX
Se não
corresponde,
Insert
XXXXXXXXXX
X XXX XXXX XX
XX XXXX
XXXXX XXX XX
XXX XXX
Database
Engine
Merge
Available
in CTP
• Nova instrução DML que combina várias operações DML.
– Building block para maior eficiência para ETL.
– Implementação SQL 2006.
Origem
Destino
XXXXX XXX XXX
XXXX XXX
XXXXX XXX XXX
XXXX XXX
XXXXXXXXXX
X XXX XXXX XX
XX XXXX
XXXXX XXX XX
Se a fonte não
corresponde,
DELETE
XXXXXXXXXX
X XXX XXXX XX
XX XXXX
XXXXX XXX XX
XXX XXX
Merge
Database
Engine
Available
in CTP
MERGE Stock S
USING Trades T
ON S.Stock = T.Stock
WHEN MATCHED AND (Qty + Delta = 0) THEN
DELETE -- delete stock if Qty reaches 0
WHEN MATCHED THEN
-- delete takes precedence over update
UPDATE SET Qty += Delta
WHEN NOT MATCHED THEN
INSERT VALUES (Stock, Delta);
Merge
Database
Engine
MERGE Stock S
USING Trades T
ON S.Stock = T.Stock
WHEN MATCHED AND (Qty + Delta = 0) THEN
DELETE -- delete stock if Qty reaches 0
WHEN MATCHED THEN
-- delete takes precedence on update
UPDATE SET Qty += Delta
WHEN NOT MATCHED THEN
INSERT VALUES (Stock, Delta)
OUTPUT $action, T.Stock, inserted.Delta;
Available
in CTP
Melhorias no Registro de Log
Database
Engine
• Mínima utilização de logs = “loga” somente o que é
necessário para Rollback
– Normalmente linhas individuais são registradas
– Alocações de paginas são suficientes para inserções de UNDO
• Modelo de recuperação deve ser simple ou bulklogged.
• Versões anteriores:
– CREATE INDEX
– SELECT INTO
– BULK INSERT/BCP with TABLOCK
Available
in CTP
Melhorias no Registro de Log
Database
Engine
• SQL Server 2008
– INSERT into table suporta o minimo de log
– 3X-5X Performance Boost over fully logged INSERT
Heap Insert
SQL Server 2008
SQL Server
Index Insert
Tempo de Execução
Available
in CTP
INSERT over DML
Database
Engine
Available
in CTP
• Abilidade para ter uma instrução INSERT consome
resultados de um DML.
– Melhorias na clausula OUTPUT INTO <table>
• DML OUTPUT pode ser filtrada com uma cláusula
• WHERE
• Não permitido o uso de sub-consultas, dada accessing
UDFs e full-text
• Porque ?
– History tracking of slowly changing dimensions
– Dumping DML data stream to a secondary table for postprocessing
INSERT over DML
Database
Engine
INSERT INTO Books (ISBN, Price, Shelf, EndValidDate)
SELECT ISBN, Price, Shelf, GetDate() FROM
( MERGE Books T
USING WeeklyChanges AS S
ON T.ISBN = S.ISBN AND T.EndValidDate IS NULL
WHEN MATCHED AND
(T.Price <> S.Price OR T.Shelf <> S.Shelf) THEN
UPDATE SET Price = S.Price, Shelf = S.Shelf
WHEN NOT MATCHED THEN
INSERT VALUES(S.ISBN, S.Price, S.Shelf, NULL)
OUTPUT $action, S.ISBN, Deleted.Price, Deleted.Shelf
) Changes(Action, ISBN, Price, Shelf)
WHERE Action = 'UPDATE’;
Available
in CTP
Change Data Capture
Database
Engine
Available
in CTP
• Mecanismo para facilitar o rastreamento de alterações em tabelas:
– Alterações capturadas do log de modo assíncrono
– Informações sobre o que foi alterado na fonte de dados
• Table-Valued Functions (TVF) para consultar dados alterados
– Facilmente consumido através do Integration Services
XXXXX XXX XXX
XXXX XXX
XXXXXXXXXX
X XXX XXXX XX
XX XXXX
XXXXX XXX XX
Source
table
XXXXX XXX XXX XXXX XXX XXXXXXXXXX X XXX XXXX XX
Transaction log
Capture
process
XXX
XXX
XXXXXX
XXX
CDC
functions
Change
table
Change Data Capture
Database
Engine
Available
in CTP
• Mecanismo para facilitar o rastreamento de alterações em tabelas:
– Alterações capturadas do log de modo assíncrono
– Informações sobre o que foi alterado na fonte de dados
• Table-Valued Functions (TVF) para consultar dados alterados
– Facilmente consumido através do Integration Services
XXXXX XXX XXX
XXXX XXX
Capture
XXXXXXXXXX
Process
sys.sp_cdc_enable_db_change_data_capture
Transaction Log
X XXX XXXX XX
XXXXX XXX XXX XXXX XXX XXXXXXXXXX X XXX XXXX XX
XX XXXX
XXXXX XXX XX
sys.sp_cdc_enable_table_change_data_capture
Source
Table
cdc.fn_cdc_get_all_changes_<instance>
cdc.fn_cdc_get_net_changes_<instance>
CDC
Functions
XXX
XXX
XXXXXX
XXX
Change
Table
New Lookup
• Lookup initialization
taking longer than ETL
process?
• Any clever workarounds
for max cache size?
• New lookup
transformation:
– Unrestricted cache size
– Persistent cache
Integration
Services
Compressão de Dados
•
•
•
•
Database
Engine
Shrink DW em tabelas fato
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
Database
Engine
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
Database
Engine
• 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
Database
Engine
• 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
• Compressão de
Prefixo:
– 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
Database
Engine
– 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
Database
Engine
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
Melhorias no Designer
Analysis
Services
Available
in CTP
• Um bom desenvolvimento pode oferecer melhorias de
performance e escalabilidade
• Utilizando melhores práticas para integridade baseando-se
no conhecimento dentro do designer.
– Apresentação de relacionamentos e atributos, melhorias nos
assistentes, simples criação e representação de chaves, Alertas
• Melhorias na suportabilidade
Melhorias em Data Warehousing
ETL
Consulta
e
Análise
Administração
e
Gerenciamento
Melhorias em Consultas e Analise
Parallelism for Partitioning, Star Join processing, Grouping Sets, Subspace
Computation
Paralelismo em Tabelas
Particionadas
Table: Orders
Database
Engine
Partitioned on a weekly basis on OrderDate
Segunda-Feira
Terça - Feira
Execução de Relatórios Semanais
Execução de Relatórios Semanais
Tempo de resposta alto
Tempo de resposta baixo
Usuários Contentes
Usuários não contentes
Porque ?
Paralelismo em Tabelas
Particionadas
Partitioned on a weekly basis on OrderDate
Table: Orders
Multiple threads on a single partition
Segunda-Feira Manhã
Execução relatório semanal
Tempo de resposta alto
Usuário Felizes
Database
Engine
Paralelismo em Tabelas
Particionadas
Table: Orders
Database
Engine
Partitioned on a weekly basis on OrderDate
Single thread per partition
Terça-Feira Manhã
Execução relatório semanal
Tempo de resposta baixo
Usuários não contentes
Paralelismo em Tabelas
Particionadas
Table: Orders
Database
Engine
Partitioned on a weekly basis on OrderDate
• SQL Server 2008:
Várias threads em todas partitições
e.g. 64 threads moving across partitions
Addresses major motivation for hybrid Range/Hash requests
Database
Engine
Paralelismo em Tabelas
Particionadas
Two data points
•
•
•
•
• Worst-case scenario
• 64-way machine
4-core machine (x64)
40-GB fact table
Partitioned by week
Warm start
“Find total sales between 1/6/02 and
1/13/02.”
• Query touches two partitions
• Most of the data is on one partition
select sk_date_id, sum(sales_qty*sale_amt)
from tbl_fact_store_sales s
where s.sk_date_id between 20020106 and 20020113
group by sk_date_id
order by sk_date_id asc
• SQL Server 2005*: 1276 (ms)
• SQL Server 2008: 368 (ms)
3.5x
• SQL Server 2005: 15780 (ms)
• SQL Server 2008: 992 (ms)
15x
* Microsoft® SQL Server™ 2005
Star Join Query Processing
Database
Engine
Table scan
SQL Server 2008 additional
query plans considered
SQL Server 2005 strategies
Available
in CTP
Star Join Query Processing
Database
Engine
Available
in CTP
Hash join
Dimension 2
Hash join
Dimension 1
Fact table
scan
Star Join Query Processing
Database
Engine
Available
in CTP
Hash join
SQL Server 2005
can create one
bitmap filter
Dimension 2
Hash join
Bitmap
filter
Dimension 1
Fact table
scan
Star Join Query Processing
Database
Engine
Available
in CTP
Hash join
Bitmap
filter 1
Dimension 2
SQL Server 2008
can create multiple
bitmap filters
Hash join
Bitmap
filter 2
Dimension 1
Fact table
scan
Star Join Query Processing
Database
Engine
Available
in CTP
Hash join
SQL Server 2008
can move and
reorder the filters
Dimension 2
Hash join
Bitmap
filter 2
Dimension 1
Bitmap
filter 1
Fact table
scan
Star Join: Early Results
REAL: Query response 17%
30
TPC-DS: Query response 23%
11 queries
slower
24 queries
faster
20
15
17 queries
slower
38 queries
faster
15
10
10
5
5
0
0
MSSALES: Overall 18%
30
25
20
15
10
5
0
Available
in CTP
25
25
20
Database
Engine
27 queries
slower
58 queries
faster
Customer lab:
• Query response: >1hr
• Manual Tuning: 55 sec
• SQL Server 2008: 35 sec
Subspace Computation
Analysis
Services
Goal: MDX Query Performance—Minimize the space on
which calculations are performed
• Cube space is generally
“sparse”
– Values only exist for small
number of combinations of
dimension keys
• SQL Server 2005 evaluates
expressions on complete
space
Subspace Computation
Analysis
Services
Goal: MDX Query Performance—Minimize the space on
which calculations are performed
• SQL Server 2008 divides the
space to separate calculated
members, regular members and
empty space
– SP2 includes some subspace
computation
• Analysis and evaluation of
calculations for each cell
• Null/default values substituted on
subspace basis
Melhorias em Relatório
•
•
•
•
•
Scalable reporting engine
IIS agnostic deployment
Reenderização em Word
More flexible report layout
Enhanced data visualization
Reporting
Services
Melhorias em Data Warehousing
ETL
Consulta
e
Análise
Administração
e
Gerenciamento
Melhorias em Administração e Gerenciabilidade
Backup Compression, Backup Enhancements, Resource Governor
Compressão de Backup
Database
Engine
• Pontos:
– Criar backups baseados em disco de forma online.
– Backups com longo tempo de duração podem ser
compactados.
• SQL Server 2008
– WITH COMPRESSION utilizado com BACKUP
– Menor armazenamento requirido para manter backups
online
– Backups são executados mais rápido, menor IO é feito.
– Restore automaticamento detecta a compressão
e faz o ajuste necessário
Melhorias no Backup
Analysis
Services
• Pain points:
– Exponential growth of backup times for databases over
20 GB
– Limitations on the size of individual database files and
number of metadata files
• SQL Server 2008:
– Replace the AS Backup storage subsystem
– Ensure backup times grow linearly with size of database
– Remove limitations on backup size, and metadata files
Performance no Backup
Analysis
Services
9000
8000
7000
6000
5000
4000
3000
2000
1000
0
0.000
-1000
20.000 40.000 60.000 80.000 100.000 120.000 140.000 160.000 180.000 200.000
2005 backup
Katmai
file copy
Linear (Katmai)
Database
Engine
SQL Server 2005
Resource Management
SQL Server
Backup
OLTP*
activity
Admin tasks
Admin workload
Executive
reports
Specific
reports
OLTP workload
• Pool de recursos
Único
• Database engine não
diferencia workloads
Report workload
• Best effort resource
• sharing
Memory, CPU, threads…
Resources
* Online Transaction Processing (OLTP)
Resource Governor
Database
Engine
Workloads
SQL Server
Backup
OLTP
activity
Admin tasks
Admin workload
Executive
reports
Specific
reports
OLTP workload
Report workload
Memory, CPU, threads…
Resources
• Abilidade para diferenciar
workloads
– Exemplo:
• app name, login
• Limite de recursos:
– Max memory %
– Admin Tasks Ad‐hoc
– Reports
– Max CPU time
– Grant timeout
– Max Requests
• Monitoramento de
Recursos
Resource Governor
Database
Engine
Importance
SQL Server
Backup
OLTP
activity
Admin tasks
Executive
reports
Specific
reports
High
Admin workload
OLTP workload
Report workload
Memory, CPU, threads…
Resources
• A carga de trabalho
pode ter um rótulo
de importância:
– Low
– Medium
– High
• Preferência de
Alocação de
recursos baseado
na importância
Database
Engine
Resource Governor
Pools
SQL Server
Backup
•
Resource pool: A virtual
subset of physical
database engine resources
•
Oferece controle para
definir:
Executive
reports
OLTP
activity
Admin tasks
Specific
reports
High
Admin workload
OLTP workload
Min memory 10%
Max memory 20%
Max CPU 20%
Admin pool
Report workload
Max CPU 90%
Application pool
–
Min memory %
–
–
–
–
Max memory %
Min CPU %
Max CPU %
Max DOP
•
Monitoramento de recursos
•
Permite até 20 recursos
por Pool
Resource Governor
Database
Engine
SQL Server
• Putting it all together
Backup
Executive
reports
OLTP
activity
Admin tasks
Specific
reports
High
Admin workload
OLTP workload
Report workload
– Workloads são
mapeados para
Resource Pools (n : 1)
– Alterações online of
groups/pools
– SQL Server 2005 =
default group + default
pool
• Benefício Principal
Min memory 10%
Max memory 20%
Max CPU 20%
Max CPU 90%
Admin pool
Application pool
– Prevenção durante a
exeução da query
Mais informações sobre
• Database Engine:
–
–
–
–
Grouping sets
Partition-aligned indexed views
Separate date/time data types
Locking enhancements
• Analysis Services:
–
–
–
–
Query tracing
Writeback performance
Read-only scalable database
Resource monitoring
• Integration Services:
– Visual Studio* Tools for Applications (VSTA) scripting
– Data flow thread allocation
* The Microsoft® Visual Studio® development system
Grouping Sets
Database
Engine
Available
in CTP
• Extension to the GROUP BY clause
• Lets you define multiple groupings in the
same query
• Produces a single result set that is equivalent
to a UNION ALL of differently grouped rows
• Makes aggregation querying and reporting
easier and faster
Data Flow Thread
Allocation
Integration
Services
Available
in CTP
• Previous releases
– Static thread assignment to sub expressions
• SQL Server 2008
– Dynamic thread assignment to operators
• What does it mean?
– Dramatic improvement of parallelism for long computational chains
– Thread starvation prevention
– Better performance in simple cases
• Expected improvements: From a small percentage to 2X
Subspace Computation: SP2
Number of executions for all reports
Median execution for all reports (sec)
Analysis
Services
Number of executions for interactive reports
Median execution for Int. reports (sec)
Conclusão
• O Microsoft SQL Server 2008, apresenta uma nova
forma de trabalho relacionada a descoberta de
dados,
permitindo
trabalhar
com
o
datawarehousing de uma maneira mais fácil,
principialmente
nos
processos
de
carga,
transformação e extração de dados, oferecendo
ainda mais flexibilidade, performance e segurança.
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
Connect with TechNet
Microsoft’s TechNet programs provide IT professionals with highquality, how-to information and resources to efficiently evaluate, deploy,
maintain and support their Microsoft technology. To learn more,
subscribe, or attend a free briefing, please visit:
•
•
•
•
•
Free Technical Briefings: www.microsoft.com/seminar/events
TechNet Webcasts: www.microsoft.com/webcasts
TechNet Flash Newsletter: www.microsoft.com/technet/flash
TechNet Online: www.microsoft.com/technet
Security Notification Service Sign-Up:
www.microsoft.com/technet/security/signup/default.mspx
• TechNet Subscription*:
www.microsoft.com/technet/subscriptions
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