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

Question

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

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.

SSingh-MSFT 16,371 Reputation points Moderator

2023-08-30T06:23:16.54+00:00

Hi
Sudhakar Thota •,

Welcome to Microsoft Q&A forum and thanks for using Azure Services.

As I understand, you want to know the difference reason between min and max IOs of Business-Critical tier Azure SQL Database.

I am checking on this question internally and will update you.

Thanks for your patience.
SSingh-MSFT 16,371 Reputation points Moderator

2023-08-31T08:48:38.17+00:00

Hi
Sudhakar Thota •,

Just checking if the provided reply helped. If not, please let us know so that we can further assist you.

Thanks

1 answer

Your answer

SSingh-MSFT 16,371 Reputation points Moderator

2023-08-30T06:23:16.54+00:00

Hi
Sudhakar Thota •,

Welcome to Microsoft Q&A forum and thanks for using Azure Services.

As I understand, you want to know the difference reason between min and max IOs of Business-Critical tier Azure SQL Database.

I am checking on this question internally and will update you.

Thanks for your patience.
SSingh-MSFT 16,371 Reputation points Moderator

2023-08-31T08:48:38.17+00:00

Hi
Sudhakar Thota •,

Just checking if the provided reply helped. If not, please let us know so that we can further assist you.

Thanks

Answer 1

SSingh-MSFT 16,371 Moderator

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

Sudhakar Thota 0 Reputation points

2023-08-31T19:57:45.5933333+00:00

Thanks for prompt response on the issue. I went thru the link that you provided. I totally I agree that it is not possible to get the promised IO with database resource contention.

In my case I was ingesting into one table by 32 processes calling 16 procedure from 2 unix machines which have 16 cores, and there are no other user processes running on the database.

To remove contention further I have increased the number of tables to 32 and procs to 32 and ran the test. It is understandable that IO numbers cannot be exactly translated while using the database.

But my concern is about the periods where the ingestion goes to low point.

I am thinking that the database is engaged in house keeping tasks during those periods. Please take a look at these attachments and let me know if there is some thing that could be done to have a constant rate of ingestion.
Sudhakar Thota 0 Reputation points

2023-08-31T21:05:01.47+00:00

Thanks for prompt response on the issue. I went thru the link that you provided. I totally I agree that it is not possible to get the promised IO with database resource contention.

In my case I was ingesting into one table by 32 processes calling 16 procedure from 2 unix machines which have 16 cores, and there are no other user processes running on the database.

To remove contention further I have increased the number of tables to 32 and procs to 32 and ran the test. It is understandable that IO numbers cannot be exactly translated while using the database.

But my concern is about the periods where the ingestion goes to low point.

I am thinking that the database is engaged in house keeping tasks during those periods. Please take a look at these attachments and let me know if there is some thing that could be done to have a constant rate of ingestion.
Sudhakar Thota 0 Reputation points

2023-08-31T21:05:30.2266667+00:00

ingest_perf_6vCore_532GB_32tables-32procs.txt
Sudhakar Thota 0 Reputation points

2023-08-31T21:07:19.52+00:00

Please look at the second attachment
Sudhakar Thota 0 Reputation points

2023-08-31T21:08:15.5566667+00:00

Please look at the second attachment

io-issue-1.png
Sudhakar Thota 0 Reputation points

2023-09-01T17:53:39.74+00:00
Sorry, the site is not allowing me to attach the excel file to show numbers and picture in one file. I had to upload .txt file and .png file separately.

This is what I did to get that png.

Rename that txt file to csv.

Open it with excel

Insert a "2D Line with Markers" using columns A and B

Sorry for the inconvenience and thanks for your prompt response.

Sudhakar Thota
SSingh-MSFT 16,371 Reputation points Moderator

2023-09-04T05:54:32.9633333+00:00

Hi
Sudhakar Thota •,

I would recommend you to please file a support ticket for deeper investigation and in case if you don't have a support plan, do let us know here so that we can check on other options to unblock you.

Thanks

Share via

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

1 answer

Your answer