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, 198👍, 0💬
Here is a list of top popular Software Test Management Commercial and Free Tools: RankNameVendorPric...
How to generate IP Addresses? To help you to obtain some IP addresses for testing purpose, FYIcenter...
How to convert binary data to hexadecimal format (or Binary to Hex Encoding)? Binary to Hex encoding...
How to convert hexadecimal encoded data back to binary data (or Hex to Binary Decoding)? Hex to Bina...
How to generate IPv6 Addresses? To help you to obtain some IPv6 addresses for testing purpose, FYIce...