1 / 34

introducing Performance Tuning the BI Platform bknight@pragmaticworks

introducing Performance Tuning the BI Platform bknight@pragmaticworks.com. BI Made Simple. BI Made Simple. 1. 3. 2. SSIS. Database Platform. SSAS. What You’re Going to Learn. VS . Build a better package for everyone Building a better cube Tuning some of the SQL components for BI.

ardith
Download Presentation

introducing Performance Tuning the BI Platform bknight@pragmaticworks

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. introducingPerformance Tuning the BI Platform bknight@pragmaticworks.com BI Made Simple

  2. BI Made Simple 1 3 2 SSIS Database Platform SSAS

  3. What You’re Going to Learn VS. Build a better package for everyone Building a better cube Tuning some of the SQL components for BI Developer DBA

  4. Today’s Problems with Integration • Integration today • Increasing data volumes • Increasingly diverse sources • Requirements reached the Tipping Point • Low-impact source extraction • Efficient transformation • Bulk loading techniques

  5. Component Drilldown: Data Sources • Don’t be afraid to leverage TSQL • Sorting, Joining, Filtering • Type Conversions, Null Coercing, • Use source-specific optimizations • ‘FastParse’ for text files • Connection Settings • Packet size

  6. Impact of Compression on ETL * Not official Microsoft results.

  7. Demo Source tuning

  8. The Pipeline presents the buffer to each downstream component Transform Components

  9. Case Study 83seconds 105 seconds

  10. Upsert Pattern Land large changes into a staging table Do set based insert and update Use Merge syntax in on 2008

  11. Demo Transform tuning

  12. Data Destinations • Use ‘Fast Load’ or SQL Server Destination • However ‘All or nothing’ error handling • SQL Server Destination gives about 8-10% performance gain • Table Lock on insert operations • Still works when used in parallel loads • Old principles still apply

  13. Demo Destination Tuning

  14. Location SQL Server 1 SQL Server 2 SSIS Server • Consider the following configuration… Where should SSIS run? • (Licensing issues aside)

  15. WSRM • Windows System Resource Manager (WSRM) can throttle CPU and memory • Creates a soft throttle • Can be scheduled so SSIS gets priority on weekends and nights • WSRM is free with Windows Server 2003 Enterprise Edition and included in Windows Server 2008

  16. SSAS Tuning 1 3 2 SSIS Database Platform SSAS

  17. 3 Core Tenets of Good Cube Design Attribute relationships Attribute relationships Attribute relationships  Really…

  18. Attribute Relationship • Think foreign keys between attributes • City  State, State  Country • Day  Month, Month  Quarter

  19. Where are they used? Query Performance Aggregation design Security Member Properties

  20. Demo Attribute Relationships

  21. Other tuning advice Aggregations for query performance Partition your problems away

  22. Database Engine Tuning 1 3 2 SSIS Database Platform SSAS

  23. Writing Sequential Data • Sequential scan performance starts with database creation and extent allocation • Use –E startup option is used • Allocate 64 extents at a time (4MB) • Pre-allocation of user databases is recommened • If autogrow, always use 4MB increments Microsoft Confidential

  24. Trace Flags for SQL Server -T1117 startup parameter ensures even growth of the files in a file group -T610 – Minimizes transaction logging at a connection

  25. Techniques to Maximize Scan Throughput • Minimize use of NonClustered indexes on Fact Tables • Load techniques to avoid fragmentation • Load in Clustered Index order (e.g. date) when possible • Index Creation always MAXDOP 1, SORT_IN_TEMPDB • Isolate volatile tables in separate filegroup • Isolate staging tables in separate filegroup or DB

  26. Core Fast Track Metrics • These metrics are used to measure IO performance • Maximum Consumption Rate – Ability of SQL Server to process data for a specific CPU and Server combination and a standard SQL query. • Benchmark Consumption Rate – Ability of SQL Server to process data for a specific CPU and Server combination and a user workload or query.

  27. Soft NUMA Example Simulate MAXDOP 1 Soft NUMA node 0 CPU Core 1 Dtexec 1 Port 8889 Soft NUMA node 1 CPU Core 2 Port 8890 Dtexec 2 Port 8891 Dtexec 3 Soft NUMA node 2 CPU Core 3

  28. Schedule Efficiently • Create a priority queue of work • Start multiple copies of the package • Packages process work in a loop DTEXEC (1) Get Task Do Work Loop TaskQueue DTEXEC (2) … Get Task Do Work Loop Pn P5 P4 P3 P2 P1

  29. Demo Results

  30. Demo Results

  31. Demo Results

  32. Demo Results

  33. Demo Queueing System

  34. Sales : sales@pragmaticworks.com Ideas : bknight@pragmaticworks.com Office Number: 904-638-3805 BI Made Simple www.pragmaticworks.com

More Related