Azure SQL Data Warehouse DWU vs Azure SQL DTU

Question

I am considering migration from Azure SQL to Azure SQL Data Warehouse. It seems to offer some of the features that we need, however price is a concern for starting small. 100 DWU Data Warehouse is priced considerably higher ($521/month) than a seemingly comparable 100 DTU Azure SQL S2 tier ($150/month).

To make sure I am comparing apples to apples, can someone shed some light on how DWU compare to DTU (assuming basic configuration with a single database)?

Edit: to everyone who is inclined to answer that Azure SQL DW and Azure SQL are not comparable and therefore it makes no sense to compare DTU to DWU: then how does it make sense to (talk about migration) to DW?

Githens

will try and get more information, but DTU does not equal DWU. 1 DWU can handle much more data then 1 DTU. Data warehouse is the big data SQL solution on Azure. Azure SQL will not scale to terabytes of petabytes. – Lacker

The largest difference between SQL DB and SQL DW is that SQL DW is designed for massively parallel processes which will result in better performance than what you see in SQL DB. Also, you have the ability to pause a SQL DW when it is not in use (nights/weekends) to save some money as well. azure.microsoft.com/en-us/documentation/articles/… – Thrice

Thank you, I am aware of the feature differences, just trying to compare prices of the feature subset that is roughly equivalent to SQL DB. – Githens

Answer 1

For what it's worth, 1 DWU = 7.5 DTU with respect to server capacity

When you look at the server instance that you provision a DW instance on:

• 100 DWU instance consumes 750 DTUs of server capacity
• 400 DWU instance consumes 3,000 DTUs of server capacity

While this information is interesting, it may not be very useful in terms of comparing pricing because DW pricing is exclusively based on DWU, while Azure SQL pricing is the combination of DTU and database size.

Ultraconservative

Thank you, I think this is actually useful since it does, in an indirect way, show correlation between the two units. – Githens

It is interesting and useful to see the DTU quota on the server which an N sized DWU instance consumes. At this writing, for instance, a 100 DWU Gen2 Azure SQL Data Warehouse is allocated 900 DTUs on the server. – Muley

Answer 2

You can't and really shouldn't compare the two for the same workload; they're designed for different things based on completely different architectures. As such, DTU and DWU are not comparable measures. Also, how deeply have you looked into the technical differences? The high level features are not the major issue, details are what might wreck your app (e.g. can you live with a limited TSQL surface area or transaction isolation level?)

Azure SQL DB is intended to be a general purpose DB as a service. The few feature gaps aside, you should think about Azure SQL DB functionally the same way you do SQL Server, minus a lot of the administrative tasks and with a different programming model. Works great for OLTP apps and most reporting apps (or mixed) but not so great for complex analytical apps against with very large datasets (can't really store that much in SQL DB anyway).

SQL DW is intended for data warehousing, analytical type workloads. Its MPP architecture is particularly well suited for complex queries against very large data sets. It will not perform well for typical OLTP applications that have lots of small or singleton queries especially when it's a mix of insert, update and delete operations. If you get a trial instance of SQL DW, you can easily test and verify the behavior for your workload compared to what it currently looks like on SQL DB.

SQL DW also has some limitations on its TSQL surface area, types, concurrency, isolation levels (deal breaker for almost all OLTP apps), etc... so be sure to look into the documentation to get the whole picture as you evaluate feasibility. It might work great but I suspect it's not the best solution if you're running an OLTP workload. Reporting/analytical type workloads however might find a happy home in SQL DW.

Saury

OK, fine, let's forget about comparison then. What is a DWU? How is it defined? What kind of performance (e.g. TPS, throughput etc.) will I get from 100DWU? Also, is the performance going to grow linearly based on number of DWUs allocated? – Githens

SQL DW is in preview so details will be scarce. Load Rate and Scan Rate are focus areas; common measures for DW workloads. Query performance is another measure but that's hard to generalize since it's completely dependent on the query and schema. However, one can get a reasonable estimate on query perf by comparing scan and load rates of an existing platform against SQL DW. More details at github.com/Azure/azure-content/blob/master/articles/… – Saury

Answer 3

The best way to figure out what you need is to look at your current IO requirements. Data Warehouses tend to be IO hogs and consequently are optimized by maximizing IO throughput. The DWU Calculator site walks you through the process of capturing a your disk metrics and estimates how many DWUs you need to fulfill your workload.

http://dwucalculator.azurewebsites.net/

Multiplier