Azure SQL Benchmark — DTU vs. vCore

I recently migrated a full-fledged SQL Server from a virtual machine to Azure SQL. The journey was full of discoveries, which inspired me to write this article.

For the sake of simplicity, let’s assume that the decision to go with Azure SQL is already made (see my article about IaaS vs. PaaS here). I will primarily focus on how I chose between DTU and vCore Azure SQL offerings regarding performance. Delving into the details is required, so be ready to meet with performance tests, hardware, tempdb, execution plans, and more.

Step 1 — Choose tier.

The official documentation doesn’t definitively answer how DTUs relate to vCores and full-fledged virtual machine cores. All the answers come down to “it depends,” which makes sense because the workload in each system is unique. Nevertheless, you can find the following formula for standard plans:

100 DTU ~ 1 vCore.

The original server had four cores, so our candidates are:

• Azure SQL Standard 400 DTU.
• Azure SQL General Purpose 4 vCore.

Step 2 — Choose a benchmark methodology.

I immediately abandoned the idea of writing synthetic tests because the system's performance with actual data and queries is the most accurate metric. Therefore, the choice shifted towards leveraging existing APIs (HTTP) with time measurements. One scenario involved sending a series of requests, each creating different load and stress levels on the database.

Within the article, I assigned names to each scenario that reflected what would happen from a database perspective. You can see the complete list below.

• Scenario 1: Medium SELECTs.
• Scenario 2: Large SELECTs.
• Scenario 3: Medium UPDATEs.
• Scenario 4: Medium INSERTs.
• Scenario 5: Large INSERTs.
• Scenario 6: Extremely Large INSERTs.
• Scenario 7: All-in.

For instance, the Medium SELECTs scenario involves queries with multiple JOINs, ORDER BYs, etc. The Large SELECTs script represents more complex queries with multi-stage JOINs and…