Business critical tier - IOs - min and max inserts into the table are 340 and 18061 - wide variation. Unable to understand why such a big gap in IO

Sudhakar Thota 0 Reputation points
2023-08-30T00:13:00.7033333+00:00

I am running 32 parallel threads from 2 machines, each having 16 processors to call 16 procedures in the database just inserting one record by each one. I am doing this for testing the IO limits that are promised for Business Critical tier Standard-series(Gen5). I am not reaching that limit and also I am seeing wide difference in min and max inserts per second varies widely. 340 min and 18061 max. And I run the test for 32 million records (runs for a while).

Can some one help me understand the promised vs usable IO for azure sql database. Or is there a room to tune the database.

Azure SQL Database
{count} votes

1 answer

Sort by: Most helpful
  1. ShaktiSingh-MSFT 13,271 Reputation points Microsoft Employee
    2023-08-30T09:44:32.8+00:00

    Hi
    Sudhakar Thota
    •,

    I have got the below reply from the internal team:

    Generally speaking, there are no “promised” or guaranteed IOPS but only limits/caps.

    Plus, translating client operations like a procedure call to underlying IOPS or log generation rate usage is not a 1:1 mapping, and there may be multiple concurrency issues that explain that wide variance in results.

    Tuning data table and index design can, as an example, result in reduced data/log usage and better insert performance.

    It is recommended to go through this link to understand more about how those limits are applied:

    https://learn.microsoft.com/en-us/azure/azure-sql/database/resource-limits-logical-server?view=azuresql#data-io-governance

    Let us know if this helps or you have further questions.

    Thanks