1 / 66

A Evolução do Data Warehousing no Microsoft SQL Server 2008

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 pedrogalvaojunior@gmail.com. Agenda. Visão geral das melhorias de SQL Server 2008 para data warehousing.

jirair
Download Presentation

A Evolução do Data Warehousing no Microsoft SQL Server 2008

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 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 pedrogalvaojunior@gmail.com

  2. Agenda • Visão geral das melhorias de SQL Server 2008 para data warehousing. • Considerações sobre caracteristicas de performance.

  3. Microsoft SQL Server 2008

  4. 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

  5. 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

  6. Available in CTP Database Engine Merge • Nova instrução DML que combina várias operações DML. • Building block para maior eficiencia para ETL. • Implementãção compliance SQL-2006.

  7. Available in CTP Database Engine Merge • 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 ouconsulta

  8. Available in CTP Database Engine Merge • 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 XXX XXX Destino pode ser qualquer tabela ou view atualizável

  9. Available in CTP Database Engine Merge • 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 XX XXX XXX XXXXX X XXXX XXX XXX Se a fontecorresponder com o destino Update

  10. Available in CTP Database Engine Merge • 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 XXX XXX XXXX XXX XXXXXXXXXX X XXX XXXX XX XX XXXX XXXXX XXX XX XXX XXX Se nãocorresponde, Insert

  11. Available in CTP Database Engine Merge • 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 XXX XXX XXXX XXX XXXXXXXXXX X XXX XXXX XX XX XXXX XXXXX XXX XX XXX XXX Se a fontenãocorresponde, DELETE

  12. Available in CTP Database Engine Merge 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);

  13. Available in CTP Database Engine Merge 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;

  14. Available in CTP Database Engine Melhorias no Registro de Log • 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

  15. Available in CTP Database Engine Melhorias no Registro de Log • SQL Server 2008 • INSERT into table suporta o minimo de log • 3X-5X Performance Boost over fully logged INSERT Tempo de Execução

  16. Available in CTP Database Engine INSERT over DML • Abilidadeparaterumainstrução INSERT consomeresultados 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

  17. Available in CTP Database Engine INSERT over DML 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’;

  18. Available in CTP Database Engine Change Data Capture • 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 Capture process XXXXX XXX XXX XXXX XXX XXXXXXXXXX X XXX XXXX XX Transaction log XXXXX XXX XXX XXXX XXX XXXXXXXXXX X XXX XXXX XX XX XXXX XXXXX XXX XX XXX XXX XXXXXX XXX Source table CDC functions Change table

  19. Available in CTP Database Engine Change Data Capture • 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 Capture Process XXXXX XXX XXX XXXX XXX XXXXXXXXXX X XXX XXXX XX sys.sp_cdc_enable_db_change_data_capture sys.sp_cdc_enable_table_change_data_capture Transaction Log XXXXX XXX XXX XXXX XXX XXXXXXXXXX X XXX XXXX XX XX XXXX XXXXX XXX XX XXX XXX XXXXXX XXX Source Table cdc.fn_cdc_get_all_changes_<instance> cdc.fn_cdc_get_net_changes_<instance> Change Table CDC Functions

  20. Integration Services New Lookup • Lookup initialization taking longer than ETL process? • Any clever workarounds for max cache size? • New lookup transformation: • Unrestricted cache size • Persistent cache

  21. Database Engine Compressão de Dados • Shrink DW em tabelas fato • Melhora na performance de Consultas • Habilitado por tabela ou indice • Tradeoff em utilização de CPU

  22. Database Engine Data Compression

  23. Database Engine Data Compression • Microsoft® SQL Server™ 2005 Service Pack 2 (SP2) • VarDecimal • Permiteutilizarvaloresdecimaisparaarmazenamento de dados de tamanhovariável.

  24. Database Engine Data Compression • Coluna com tamanhofixo: • SQL Server 2008 estende a lógica de tamanhofixoparatodosostipos de campos: • int, bigint, etc.

  25. Database Engine Data Compression • Compressão de Prefixo: • A lista de prefixos é armazenadanapáginaparaprefixoscomuns. • Valoresindividuaissãosubstituidos: • Token paraprefixo • Sufixopara valor 2007060 4911-403C-9 6431-4D57-8 1 2 3 2 1 1 2 1 3 4 1 3 1 3 1 3 1

  26. Database Engine Data Compression 2007060 4911-403C-9 6431-4D57-8 1 2 3 • Dicionário de compressão: • O valor comum é armazenadonapágina • Valorescomunssãosubstituidospor tokens • 2X para 7X taxa de compressão real para dados fato no DW de forma antecipada, dependendo do dado 2 1 1 2 1 3 4 1 3 1 3 3 1 1 2007060 4911-403C-9 6431-4D57-8 1 2 3 1 2 10 0.00 1 2 3 4 4 2 3 1 1 1 1 2 3 4 2 1 3 4 3 4 2 1 3 4 3 4 3 1 2 3 3 2 1 3 1 3 4

  27. Available in CTP Analysis Services Melhorias no Designer • 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, melhoriasnosassistentes, simples criação e representação de chaves, Alertas • Melhorias na suportabilidade

  28. Melhorias em Data Warehousing Consulta e Análise Administração e Gerenciamento ETL Melhorias em Consultas e Analise Parallelism for Partitioning, Star Join processing, Grouping Sets, Subspace Computation

  29. Database Engine ParalelismoemTabelasParticionadas Partitioned on a weekly basis on OrderDate Table: Orders Segunda-Feira Execução de Relatórios Semanais Tempo de resposta alto Usuários Contentes Terça - Feira Execução de Relatórios Semanais Tempo de resposta baixo Usuários não contentes Porque ?

  30. Database Engine ParalelismoemTabelasParticionadas 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

  31. Database Engine ParalelismoemTabelasParticionadas Partitioned on a weekly basis on OrderDate Table: Orders Single thread per partition Terça-Feira Manhã Execução relatório semanal Tempo de resposta baixo Usuários não contentes

  32. Database Engine ParalelismoemTabelasParticionadas Partitioned on a weekly basis on OrderDate Table: Orders • 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

  33. Database Engine ParalelismoemTabelasParticionadas Two data points Worst-case scenario 64-way machine Query touches two partitions Most of the data is on one partition SQL Server 2005: 15780 (ms) SQL Server 2008: 992 (ms) • 4-core machine (x64) • 40-GB fact table • Partitioned by week • Warm start “Find total sales between 1/6/02 and 1/13/02.” • SQL Server 2005*: 1276 (ms) • SQL Server 2008: 368 (ms) selectsk_date_id,sum(sales_qty*sale_amt) fromtbl_fact_store_sales s wheres.sk_date_idbetween20020106and20020113 groupbysk_date_id orderbysk_date_idasc 15x 3.5x * Microsoft® SQL Server™ 2005

  34. Available in CTP Database Engine Star Join Query Processing Dimension 1 Fact table Table scan Dimension 2 SQL Server 2008 additional query plans considered Dimension 3 SQL Server 2005 strategies Dimension 4

  35. Available in CTP Database Engine Star Join Query Processing Hash join Dimension 2 Hash join Dimension 1 Fact table scan

  36. Available in CTP Database Engine Star Join Query Processing Hash join SQL Server 2005 can create one bitmap filter Dimension 2 Hash join Bitmap filter Dimension 1 Fact table scan

  37. Available in CTP Database Engine Star Join Query Processing Hash join SQL Server 2008 can create multiple bitmap filters Bitmap filter 1 Dimension 2 Hash join Bitmap filter 2 Dimension 1 Fact table scan

  38. Available in CTP Database Engine Star Join Query Processing Hash join SQL Server 2008 can move and reorder the filters Dimension 2 Hash join Bitmap filter 1 Bitmap filter 2 Dimension 1 Fact table scan

  39. Customer lab: Query response: >1hr Manual Tuning: 55 sec SQL Server 2008: 35 sec Available in CTP Database Engine Star Join: Early Results 11 queries slower 24 queries faster 17 queries slower 38 queries faster 27 queries slower 58 queries faster

  40. Analysis Services Subspace Computation • Cube space is generally “sparse” • Values only exist for small number of combinations of dimension keys • SQL Server 2005 evaluates expressions on complete space Goal: MDX Query Performance—Minimize the space on which calculations are performed

  41. Analysis Services Subspace Computation • 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 Goal: MDX Query Performance—Minimize the space on which calculations are performed

  42. Reporting Services Melhorias emRelatório • Scalable reporting engine • IIS agnostic deployment • Reenderização em Word • More flexible report layout • Enhanced data visualization

  43. Melhorias em Data Warehousing Consulta e Análise Administração e Gerenciamento ETL Melhorias em Administração e Gerenciabilidade Backup Compression, Backup Enhancements, Resource Governor

  44. Database Engine Compressão de Backup • Pontos: • Criar backups baseadosem disco de forma online. • Backups com longo tempo de duraçãopodem 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

  45. Analysis Services Melhorias no Backup • 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

  46. Analysis Services Performance no Backup

  47. Database Engine SQL Server 2005Resource Management SQL Server • Pool de recursos Único • Database engine não diferencia workloads • Best effort resource • sharing Executive reports Backup OLTP* activity Admin tasks Specific reports Report workload OLTP workload Admin workload Memory, CPU, threads… Resources * Online Transaction Processing (OLTP)

  48. Database Engine Resource GovernorWorkloads SQL Server • 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 Executive reports Backup OLTP activity Admin tasks Specific reports Report workload OLTP workload Admin workload Memory, CPU, threads… Resources

  49. Database Engine Resource GovernorImportance SQL Server • 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 Executive reports Backup OLTP activity Admin tasks Specific reports Report workload High OLTP workload Admin workload Memory, CPU, threads… Resources

  50. Database Engine Resource GovernorPools SQL Server • Resource pool: A virtual subset of physical database engine resources • Oferece controle para definir: • Min memory % • Max memory % • Min CPU % • Max CPU % • Max DOP • Monitoramento de recursos • Permiteaté 20 recursospor Pool Executive reports Backup OLTP activity Admin tasks Specific reports Report workload High OLTP workload Admin workload Max CPU 90% Min memory 10% Max memory 20% Max CPU 20% Application pool Admin pool

More Related