Verify SQL Database DTU Usage
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 showning 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.
2019-07-30, 363👍, 0💬
How to perform HTML entity encoding? HTML entity encoding is designed to protect HTML reserved chara...
Where to find tutorials on Apache JMeter test tool? I want to know how to use Apache JMeter. Here is...
How to use HTTP protocol to post an XML message to a server? You can follow this tutorial to post an...
Where to find test data generators? FYIcenter.com has prepared a number of test data generators for ...
Where to find online test tools? FYIcenter.com has prepared a number of online test tools: Data Look...