Donnell D Smith TSP Data AI Recent Updates

Donnell D. Smith TSP – Data & AI

Recent Updates Azure Advisor Performance Recommendations for ADW enhancements • Adaptive cache – Be advised when to scale to optimize cache utilization. • Table distribution – Determine when to replicate tables to reduce data movement and increase workload performance. • Tempdb – Understand when to scale and configure resource classes to reduce tempdb contention. Maintenance Scheduling - Preview Flexible Restore Points https: //azure. microsoft. com/en-us/updates/? product=sql-data-warehouse

Introducing Azure SQL Data Warehouse A relational platform-as-a-service, fully managed by Microsoft. Elastic scale cloud data warehouse with proven SQL Server capabilities. Built for businesses of all shapes, sizes, and industry. Azure Saas Azure Public Cloud Office 365

SQL DW is good for analytical workloads. ü ü ü Store large volumes of data. Consolidate disparate data into a single location. Shape, model, transform and aggregate data. Perform query analysis across large datasets. Ad-hoc reporting across large data volumes. All using simple SQL constructs.

Unsuitable workloads for SQL DW Operational workloads (OLTP) • High frequency reads and writes. • Large numbers of singleton selects. • High volumes of single row inserts.

Logical overview

OPTIMIZED FOR ELASTICITY

GEN 2 IS OPTIMIZED FOR COMPUTE

Distributed queries

ROUND ROBIN DISTRIBUTION 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60

HASH distribution HASH ( 01 03) 02 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60

Single Gated Client DMS DMS

Parallel Loading with Poly. Base DMS DMS

Sizing factors Database capacity Tempdb Concurrency & Memory Load Transaction size Memory management DWU

Concurrency: slots 128 64 Slots 32 16 8 4 2 mediumrc (m) largerc (l) xlargerc (xl) Max Concurrency Slots 00 DW 20 00 15 DW 00 12 DW DW 10 00 0 60 DW DW 50 0 0 40 DW 0 30 DW 0 20 DW DW 10 0 1

Concurrency Gen 2 Static Classes Service Level Maximum concurrent queries Concurrenc y slots available staticrc 10 staticrc 20 Staticrc 30 staticrc 40 staticrc 50 staticrc 60 staticrc 70 staticrc 80 DW 1000 c 32 40 1 2 4 8 16 32 32 32 DW 1500 c 32 60 1 2 4 8 16 32 32 32 DW 2000 c 48 80 1 2 4 8 16 32 64 64 DW 2500 c 48 100 1 2 4 8 16 32 64 64 DW 3000 c 64 120 1 2 4 8 16 32 64 128 DW 5000 c 64 200 1 2 4 8 16 32 64 128 DW 6000 c 128 240 1 2 4 8 16 32 64 128 DW 7500 c 128 300 1 2 4 8 16 32 64 128 DW 10000 c 128 400 1 2 4 8 16 32 64 128 DW 15000 c 128 600 1 2 4 8 16 32 64 128 DW 30000 c 128 1200 1 2 4 8 16 32 64 128

Recent Updates Azure Advisor Performance Recommendations for ADW enhancements • Adaptive cache – Be advised when to scale to optimize cache utilization. • Table distribution – Determine when to replicate tables to reduce data movement and increase workload performance. • Tempdb – Understand when to scale and configure resource classes to reduce tempdb contention. Maintenance Scheduling - Preview Flexible Restore Points https: //azure. microsoft. com/en-us/updates/? product=sql-data-warehouse