IOPS in Azure SQL DB

I learned a bit of trivia regarding Azure SQL DB today.

In the standard tier, IOPS are limited to 1-5 per DTU. That means at an S3, you get a max of 500 IOPS. DTU IOPS are counted by IO operations and not size. It doesn’t matter if you’re reading or writing a single row to a table with a single char(1) column or a single row to a table with 500 nvarchar(max) columns, 1 IOP is 1 IOP. More or less, without getting into technicalities.

That said, things like the resource governor that spreads IO requests across all the databases in a pool and the amount of activity on the storage supporting SQL will cause IOPS to fall below the 500 theoretical max of an S3.

So, while your queries might not be so complex as to tax the CPU, you could see waits and connection timeouts due to excessive Data IO. You can find out your CPU, DATA IO and LOG IO usage by clicking on the Query Performance Insight on the nav blade for a SQL Database in the Azure Portal or by using Azure SQL Analytics.

ps. just for reference a spinning disk HDD will have 50-300 IOPS. An SSD will have 3k-60k. A high performance SSD has IOPS closer to 600k.  

References:

https://docs.microsoft.com/en-us/azure/azure-sql/database/service-tiers-dtu

https://docs.microsoft.com/en-us/azure/azure-sql/database/resource-limits-logical-server