Performance Impact of Higher Azure Service Tier

Q

What is the Performance Impact of Higher Azure Service Tier?

✍: FYIcenter.com

A

If you want to see the performance impact of Azure Service tier, you can re-run the same test script, fyi_block_insert.sql, on another Azure SQL Database with higher service tier.

For example, run fyi_block_insert.sql, on a Azure SQL Database with "Standard: 400 eDTUs (Elastic DTU)":

 Size   Count   Duration   Throughput   Latency   Throughput   IOPS
                               Record                     KB          
  256     100        424          235         4           58      7
  512     100        404          247         4          123     15
 1024     100        403          248         4          248     31
 2048     100        404          247         4          495     61
 4096     100        436          229         4          917    114
 8192     100        417          239         4         1918    239
16384     100        537          186         5         2979    372
32768     100        710          140         7         4507    563
                                                              
 8192    1000       4213          237         4         1898    237
16384    1000       4580          218         4         3493    436
32768    1000       4977          200         4         6429    803
                                                              
 8192   10000      43760          228         4         1828    228
16384   10000      48020          208         4         3331    416
32768   10000      60614          164         6         5279    659
                                                              
 8192   20000      89057          224         4         1796    224
16384   20000      96730          206         4         3308    413
32768   20000     106990          186         5         5981    747

As you can see, if throughput performance peaks at 6,429 KB/sec.

If we put this best INSERT performance together with other best INSERT performance from previous tutorials, we can see the impact of higher Azure Service Tier:

Service:        Tier   Read   Write | Latency   Throughput   IOPS   IOPS
                         ms      ms |      ms         KB/s          /DTU
--------------------   ----   ----- | -------   ----------   ----   ----
Standard S0: 10 DTUs      5      10 |       4         1152    144     14
 Standard: 400 eDTUs      5      10 |       4         6429    803      2

This table shows us that "Standard S0: 10 DTUs" is much better deal, 7 times more work gets done per DTU comparing with "Standard: 400 eDTUs"!

 

"Execution Timeout Expired" Error on Azure Portal

Input Operation Test with Large Records

IOPS Tests on INSERT Statements

⇑⇑ SQL Server Storage Tutorials

2019-07-21, 163👍, 0💬