AZURE SQL MI General Purpose Performance question

Question

Hi,
we have provisioned an Azure SQL MI GP with 8 cores and 512gb reserverd space.

I have created a test DB on it with both the data file and log file 100 mb each.
The DB compatibility level is 150 for 2019.

I have created 1 table :

CREATE TABLE [Test_Data] (
[Id] INT IDENTITY,
[Date] DATETIME DEFAULT GETDATE (),
[City] CHAR (25) DEFAULT 'Testing123',
[CityName] CHAR (25) DEFAULT 'Test Test1 Test2');

Then i insert data using the below for 1 k rows.

INSERT INTO Test_DATA DEFAULT VALUES ;
GO 1000

This insert takes close to 2 minutes to insert 1000 rows!!!! 2 mins for 1k rows is excessive , in my view.

Just for comparison on my laptop local sql instnace, the same query runs in 3 seconds.

I do not know what i am missing here .

Anyone , any thoughts please ?

thanks

Answer 1

@Biju Mathew

The disks you explicitly create for Azure VMs drives will have their respective speed, independently from the data file sizes placed on them.
On Azure SQL MI General Purpose tier each file created will be placed on the nearly sized disk, meaning the bigger the file - the bigger disk (and faster speed) you will get.
IO performance of Azure Premium disks depend on the disk size. This is explained in the article I shared above.

Hope that helps.

Pease accept mark as accepted answer if the reply is helpful. Otherwise let us know how we ca better assist.

Regards,
Oury

Answer 2

Hi @Biju Mathew Thank you for posting your question on Microsoft Q&A. Consider increasing the size of the data & log files - Impact of log file size on General Purpose Managed Instance performance | by Jovan Popovic | Azure SQLDB Managed Instance | Medium.

Additionally 1000 transactions is pretty inefficient way of inserting data (wrap it into a transaction).

What is your local database recovery model ? A huge difference will be on the Transaction Log impact between Simple & Full (SQL MI) recovery models.

Also, General Purpose does offer a remote storage - comparing it to a local SSD when "hammering" the Transaction Log with sequential transactions - will never be efficient.

Regards,
Oury

Answer 3

Thanks for this input . I will try increasing the data/log files size and see if it makes a difference.

You say that GP offer remote storage - via Azure Premium disk - and so every IO becomes a network IO and hence the latency.

a related question, I am trying to compare this behaviour to how SQL Server on Azure VM (with Premium managed disks for data file) behave. Will it behave the same i,e. every IO becomes a network IO just like in GP ? or are there some optimizations when it comes to SQL on VM storage?

Thanks

Answer 4

Thanks @Oury Ba-MSFT I understand Azure VMs drives perform regardless of the filesizes.
the question was more around how the managed premium disks are attached to Azure VMs.

Are they attached locally or they follow the same remote storage pattern like in MI GP?