How to verify Azure SQL Database DTU Usage?

You can follow this tutorial to verify Azure SQL Database DTU usage.

1. Login to your Azure Web portal and open the Azure SQL Database, "fyi_db".

2. Click "Query editor" in the left menu and run the fyi_int_insert.sql script with @Count = 50000.

3. Wait for the script to finish. You see the following output:

Count   Duration   Throughput   Latency
50000     215640          231         4

The actual input data throughput is 231 * 4 bytes/sec = 924 bytes/sec = 0.9 KB/sec.

4. Click on "Metrics" under the "Monitoring" section of the left menu. You see the metrics report tool screen.

5. Change the report time range to "Last minutes (Automatic - 1 minute)".

6. Select "DTU used" from the Metric dropdown list, and select "Max" from Aggregation dropdown list. You see a graph displayed showing the history of the maximum DTU usage.

7. The graph shows 10 DTUs were used during the execution of the script.

8. Check the service tier level of "Standard S0" of this Azure SQL Database, which we paid $15.00 per month:

IO throughput (approximate): 2.5 IOPS per DTU
IO latency (approximate):    5 ms (read), 10 ms (write)

9. Calculate the IO throughput at 10 DTUs, which should be 25 IOPS. If we assume the IO block size is 8 KB, 25 IOPS should give us a throughput of 200 KB/sec.

Translate the 200 KB/sec to our FYI_INT table records, that will be 25,000 records/sec. In other words, at the 10 DTU level, we should be able to insert 25,000 records in 1 second, or 50,000 records in 2 seconds.

But out script output shows that it took 215640 milliseconds, or 216 seconds, to insert 50,000 records. This is about 100 times higher than what is reported on the Azure metrics graph.

Maybe our record size is too small and makes IO operations very inefficient.

⇒ Input Operation Test with BIGINT Data Type

⇐ Verify Azure SQL Database Storage Usage

⇑ IOPS Tests on Azure SQL Database with INSERT

⇑⇑ SQL Server Storage Tutorials