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, 229👍, 0💬
Where to find test data generators? FYIcenter.com has prepared a number of test data generators for ...
How to valid IP addresses? In order to help your programming or testing tasks, FYIcenter.com has des...
How to generate currency test values? Currency test values are frequently needed in testing date and...
How to generate email addresses? To help you to obtain some email addresses for testing purpose, FYI...
How to use HTTP protocol to post an XML message to a server? You can follow this tutorial to post an...