Sergiy Lunyakin Azure SQL DWH Optimization Our Partners

Sergiy Lunyakin Azure SQL DWH: Optimization

Our Partners

About me

Agenda

Architecture

Architecture of Azure SQL DW … … Dist_DB_60 … Dist_DB_30 Dist_DB_46 Dist_DB_47 … … Dist_DB_15 Dist_DB_16 Dist_DB_17 … Dist_DB_1 Dist_DB_2

Sizing

Sizing factors DWU – Optimized for Elasticity c. DWU – Optimized for Compute (more resources, NVMe Solid State Disk cache that keeps the most frequently accessed data close to the CPUs). Gets 2. 5 x more memory.

Introducing DWU Max MB Max mem/ queries dist DWU Max GB Max mem/ queries dist

Resource Classes https: //docs. microsoft. com/en-us/azure/sql-data-warehouse/sql-datawarehouse-develop-concurrency

Load management DWU Readers Writers DW 100 8 60 DW 200 16 60 DW 300 24 60 DW 400 32 60 DW 500 40 60 DW 600 48 60 DW 1000+ 80+

Data Loading

Options Parallel Single Gated Client Poly. Base bcp / Insert Bulk Azure Data Factory (Poly. Base) SQLBulk. Copy SSIS (Azure SQL DW Comp) SSIS (data flow) Azure Data Factory

Single Gated Client DMS DMS

Single Gated Client Parallelised DMS DMS

Parallel Loading with Poly. Base DMS DMS

Recommendations for data loading • Heap, Round-Robin (Hash whether prod table is hash distributed ), No partition

Data preparation

Distributions

Distributions * Selecting the right distribution method is key for good performance

Creating distributed tables CREATE TABLE [build]. [Fact. Online. Sales] ( [Online. Sales. Key] int , [Date. Key] datetime , [Store. Key] int , [Product. Key] int , [Promotion. Key] int , [Currency. Key] int , [Customer. Key] int , [Sales. Order. Number] nvarchar(20) , [Sales. Order. Line. Number] int , [Sales. Quantity] int , [Sales. Amount] money ) WITH ( CLUSTERED COLUMNSTORE INDEX , DISTRIBUTION = ROUND_ROBIN ) ; NOT NOT NULL NULL NULL NOT NULL CREATE TABLE [build]. [Fact. Online. Sales] ( [Online. Sales. Key] int , [Date. Key] datetime , [Store. Key] int , [Product. Key] int , [Promotion. Key] int , [Currency. Key] int , [Customer. Key] int , [Sales. Order. Number] nvarchar(20) , [Sales. Order. Line. Number] int , [Sales. Quantity] int , [Sales. Amount] money ) WITH ( CLUSTERED COLUMNSTORE INDEX , DISTRIBUTION = HASH([Product. Key]) ) ; NOT NOT NULL NULL NULL NOT NULL

Round Robin Distribution 0 1 1 3 2 5 3 7 4 9 0 2 1 4 2 6 3 8 5 0 0 3 1 5 2 7 3 9 5 1 0 4 1 6 2 8 4 0 5 2 0 5 1 7 2 9 4 1 5 3 0 6 1 8 3 0 4 2 5 4 0 7 1 9 3 1 4 3 5 5 0 8 2 0 3 2 4 4 5 6 0 9 2 1 3 3 4 5 5 7 1 0 2 2 3 4 4 6 5 8 1 1 2 3 3 5 4 7 5 9 1 2 2 4 3 6 4 8 6 0

HASH Distribution HASH ( 01 0 03 02 N 1) 0 1 1 3 2 5 3 7 4 9 0 2 1 4 2 6 3 8 5 0 0 3 1 5 2 7 3 9 5 1 0 4 1 6 2 8 4 0 5 2 0 5 1 7 2 9 4 1 5 3 0 6 1 8 3 0 4 2 5 4 0 7 1 9 3 1 4 3 5 5 0 8 2 0 3 2 4 4 5 6 0 9 2 1 3 3 4 5 5 7 1 0 2 2 3 4 4 6 5 8 1 1 2 3 3 5 4 7 5 9 1 2 2 4 3 6 4 8 6 0

Hash distribution key guidance • Distribution key is not updateable!

Replicated (vs. hash distributed) Node 00 Node 01 Node 02 PRODUCT SALES Node 03 Node 04 Node 05 Node 06

Recommendations • Small dimension tables in a star schema with less than 2 GB of storage after compression (~5 x compression) Warm-Up Replicated tables after create, scale, pause/resume • • Temporary/Staging table No obvious joining key or good candidate column • • Fact tables Large dimension tables •

Statistics

Key points huge • • • DISTINCT JOIN (+composite) WHERE GROUP BY (+composite) ORDER BY

Recommendations

Indexing

Indexing • • Staging/temporary table Small tables with small lookups • • Up to 100 -m rows table Large tables (more than 100 -m rows) with only 1 -2 columns are heavily used • Large tables (more than 100 -m rows)

Recommendations

Recommendations

Summary

Links https: //docs. microsoft. com/en-us/azure/sql-datawarehouse/cheat-sheet https: //docs. microsoft. com/en-us/azure/sql-datawarehouse/guidance-for-loading-data https: //docs. microsoft. com/en-us/azure/sqldata-warehouse/sql-data-warehouse-memoryoptimizations-for-columnstore-compression https: //docs. microsoft. com/en-us/azure/sqldata-warehouse/sql-data-warehouse-tablesindex#rebuilding-indexes-to-improve-segmentquality

Questions?

Our Partners