Introducing Parallel Data Warehouse (The project formerly known as Madison)

1. Introducing Parallel Data Warehouse(The project formerly known as Madison)

2. Agenda • The Typical problem with data warehouses • MPP vs SMP • SQL Server Parallel Data Warehouse • Hardware architecture • Query Processing • Data Loading • My email: tkejser@microsoft.com

3. Introducing Parallel Data Warehouse The Typical Problem with Data Warehouses

4. Microsoft DW Solutions Microsoft & Partner Services SSRS SSAS SSIS

5. Symmetric Multi-Processing vs. Massively Parallel Processing SMP HW advancements increasing ability to scale-out Scaling to 1 PB+ Scale out is relatively low cost Relatively high concurrency for complex workloads > 2TB up to 1 PB for DW workloads MPP OLTP, Transactional, Data Warehousing Data Warehousing (esp. VLDB, complex workloads) • HW advancements increasing ability to scale-up • But scaling limited by design • High end SMP very expensive • Extremely high concurrency for simple workloads • Less than 1-2 TB of data SMP will almost always be better. • At higher sizes - depends

6. PDW: No Assembly Required • Software • Servers • Storage arrays • Network switches • Cables • Licenses • Power distribution units • Racks • Comes fully assembled • Software is installed at the factory • Fully configured

7. Basic Building Blocks • Compute Nodes • Handles the CPU cycles required to answer queries • Storage Nodes • Stores data using Fiber Attached Disks. • Scaled to support CPU with enough throughput • Other nodes • More about those later

8. Anatomy of a Compute Node • Pre-configured For Each SQL Server Instance On Each Compute Node. • Drives Configured As RAID1 To Avoid Appliance Failover for a Single Drive Failure • IBM Compute Nodes Will Have 1 Lun (1 RAID1 Pair) • Dell Compute Nodes Will Have 2 Lun’s (2 RAID1 Pairs) • HP Compute Nodes Will Have 3 Luns’s (3 RAID1 Pairs) • TempDB: • Sort-work Area For Data Loading Into Clustered Index Tables • Work Area for PDW Temporary Work Files • Spill Area For Hash Joins Not Fitting Into Memory

9. Anatomy of a Storage Node • Pre-configured • 4 RAID10 Pairs for Primary User Data • 1 RAID10 Pair for Database Logs • 2 LUN’s Are Spread Across Each RAID Pair • User Databases are Separate Physical SQL Server Databases • Staging Database (Optional) Used for Loading & to Minimize Fragmentation

10. More Node Types • Backup node: • Stores backup files from the appliance • Can be logged into by authorized Windows users • Can be augmented with 3rd party H/W and S/W • Landing Zone: • Used as a holding place for data to be loaded • Can be logged into by authorized Windows users • Can be augmented with 3rd party H/W and S/W • Management node: • Runs the Windows domain controller (Active Directory) • Used for deploying patches to all nodes in the appliance • Holds images in case a node needs reimaging

11. Putting It All Together - PDW Control Node • Failover Protection: • Redundant Control Node • Redundant Compute Node • Cluster Failover • Redundante Array of • Inexpensive Databases  Spare Node

12. Software Architecture Internet Explorer Query Tool MS BI (AS, RS) DWSQL Other 3rd Party Tools Control Node MPP Engine OLEDB, ODBC, ADO.Net, JDBC IIS Admin Console Compute Node Compute Nodes Data Movement Service Compute Nodes Data Movement Service SQL Server User Data Landing Zone Node Data Movement Service SQL Server DW Authentication DW Configuration DW Schema TempDB

13. Create Database • CREATE DATABASE database_name WITH ( AUTOGROW = ON , REPLICATED_SIZE = 1024 , DISTRIBUTED_SIZE = 16384 , LOG_SIZE = 300 )

14. D D D D D D C C C C C C I I I I I I CD CD CD CD CD CD P P P P P P S S S S S S Distribution and Replication Data Distribution with Replication Database Distributed & Replicated Tables Date Dim D_date_sk D_date_id D_date D_month … Customer C-Customer_sk C_customer_id C_current_addr … SS Item i_item_sk i_item_id i_rec_start_date i_item_desc … SS Store Sales Ss_sold_date_sk Ss_item_sk Ss_customer_sk Ss_cdemo_sk Ss_store_sk Ss_promo_sk Ss_quantity … SS SS Promotion P_promo_sk P_promo_id P_start_date_sk P_end_date_sk … Customer Demographics Cd_demo_sk Cd_gender Cd_marital_status Cd_education … SS SS Store S_STORE_SK S_STORE_ID S_REC_START_DATE S_REC_END_DATE S_STORE_NAME …

15. Table Creation CREATE TABLE table_name    [ ( { <column_definition> } [ ,...n ] )      [ AS SELECT select_criteria ]     [ WITH ( <table_option> ) ] [;] <column_definition> ::= column_name <data_type> [ NULL | NOT NULL ] <data type> ::= type_name [ ( precision [ , scale ] ) ] <table_option> ::= { [ CLUSTER_ON ( column_name [ ,...n ] ) ] , [ DISTRIBUTE_ON ( column_name) ] | [ REPLICATE ] , [ PARTITION_ON column_name ( RANGE { LEFT | RIGHT } FOR VALUES { [ boundary_value [,...n] ] ) ) ] }

16. 8K 8K 8K 8K 8K Create Table – Behind the Scenes Create Table store_sales with distribute_on(ss_item_sk) partition_on(ss_sold_date_sk) cluster_on (ss_sold_date_sk) 8 Filegroups (one per core) - 1 Table per Filegroup 12 Partitions (ss_sold_date_sk) N-number of Pages Row

17. Physical File Layout (Per Compute Node)

18. MPP Query Processing Control Node ODBC/JDBC SQL92 with Analytical Extensions Query Rewritten Into Steps That Run Efficiently On Compute Nodes Select location, year sum(b.sales_amt) from customer a, sales b where b.sales > 500 and a.custid = b.custid group by 2,1 order by 1,2 Distribution-incompatible Joins Resolved Using High Speed Dynamic Re-distribution

19. MPP Execution Plans • The MPP engine creates parallel execution plans from client SQL • The plans can include the following types of operations: • SQL operations: used to pass SQL directly to SQL Server on 1 or more nodes. • DMS operations: used to move data among the nodes in an appliance for further processing. • Temp tables operations: used to stage data for further processing. • Return operations: push data back to the client. • Simple plans may include just one type of operation. • Complex plans may include all of these operations. • Plans are executed serially, one step at a time.

20. Example Schema Data Distribution with Replication • Sales table distributed on customer • ... And partitioned by time Date Dim D_date_sk D_date_id D_date D_month … Customer C-Customer_sk C_customer_id C_current_addr … Item i_item_sk i_item_id i_rec_start_date i_item_desc … Store Sales Ss_sold_date_sk Ss_item_sk Ss_customer_sk Ss_cdemo_sk Ss_store_sk Ss_promo_sk Ss_quantity … Promotion P_promo_sk P_promo_id P_start_date_sk P_end_date_sk … Customer Demographics Cd_demo_sk Cd_gender Cd_marital_status Cd_education … Store S_STORE_SK S_STORE_ID S_REC_START_DATE S_REC_END_DATE S_STORE_NAME …

21. Distribution Compatible Query SELECT CustomerId, SUM(Amount) AS TotalSales, SUM(Quantity) AS TotalUnitsSold FROM Sales s JOIN Item i ON s.ItemId = i.ItemId WHERE SaleDate BETWEEN '2009-08-01' AND '2009-08-31‘ AND Description LIKE '%gadgets%' GROUP BY CustomerId ORDER BY CustomerId;

22. MPP Query Plan Step 1 – On each compute node: SELECT s.[customerid], sum(s.[amount]) AS totalsales, sum(s.[quantity]) AS totalunitssold FROM [tpch_3].[dbo].[h_sales_34] s JOIN [tpch_3].[dbo].item_37 I ON (s.[itemid] = i.[itemid]) WHERE (s.[saledate] BETWEEN '2009-08-01' AND '2009-08-31' and i.[description] like '%gadgets%') GROUP BY s.[customerid] ORDER BY s.[customerid];

23. Query 1 Processing Flow Query Tool Control Node Compute Node 1 Data Movement Service Parse SQL Validate & Authorize SQL Server Data Movement Service User Data Build MPP Plan Execute Plan Return Data to Client Compute Node N MPP Engine Data Movement Service SQL Server DW Authentication DW Configuration DW Schema TempDB SQL Server User Data

24. Reshuffling the data SELECT SaleDate, SUM(Amount) AS TotalSales, SUM(Quantity) AS TotalUnitsSold FROM Sales s JOIN Item i ON s.ItemId = i.ItemId WHERE SaleDate BETWEEN '2009-08-01' AND '2009-08-31' AND Description LIKE '%gadgets%‘ GROUP BY SaleDate ORDER BY SaleDate;

25. MPP Query Plan Step 1– Create temp table on control node CREATE TABLE [tempdb].[dbo].Q_[TEMP_ID_6760] ( saledate DATE, totalsales DECIMAL(38, 2), totalunitssold INTEGER ) WITH (DATA_COMPRESSION = PAGE); Step 2 – Run on each compute node SELECT s.[saledate], sum(s.[amount]) AS totalsales, sum(s.[quantity]) AS totalunitssold FROM [tpch_3].[dbo].[h_sales_34] s JOIN [tpch_3].[dbo].item_37 i ON (s.[itemid] = i.[itemid]) WHERE (s.[saledate] BETWEEN '2009-08-01' AND '2009-08-31' and i.[description] like '%gadgets%’) GROUP BY s.[saledate]

26. MPP Query Plan continued Step 3: SELECT [saledate], sum([totalsales]) AS totalsales, sum([totalunitssold]) AS totalunitssold FROM [tempdb].[dbo].Q_[TEMP_ID_6760] GROUP BY [saledate] ORDER BY [saledate] Step 4: DROP TABLE [tempdb].[dbo].Q_[TEMP_ID_6760];

27. Reshuffling – Query Processing Flow Query Tool Compute Node Control Node Data Movement Service Parse SQL Validate & Authorize SQL Server Data Movement Service User Data Build MPP Plan Execute Plan Return Data to Client Compute Node Data Movement Service MPP Engine SQL Server User Data SQL Server DW Authentication DW Configuration DW Schema TempDB

28. Data Loading Control Node Tables Are Hash Distributed Or Replicated Landing Zone Node Text File Text File Text File Text File Spare Node

29. Data Loader Process Insert-Select Phase Insert-Select Bulk Insert Phase Sort each partition In memory or TempDB Sort each BATCH in memory or TempDB Partitioned Staging Table (Heap) Partitioned Final Table (CIDX) Bulk Insert Load File