SQL Server Managed Instance IO Performance is dependant on a few settings.
- Service Tier
- vCore Count
- File Sizes !!
Yes, File Sizes. A quick explanation, if you have a small database < 128GB your not going to get much data throughput. Only around the 500 IOPS or 100 MB/s. It’s all explained here. https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/resource-limits
You will need to make your data file bigger, how to do that and measure the impact – that’s here https://techcommunity.microsoft.com/t5/azure-sql-database/increase-data-file-size-to-improve-hammerdb-workload-performance/ba-p/823337
You can find the Azure Managed Disks and their performance profiles – https://azure.microsoft.com/en-us/pricing/details/managed-disks/
I wrote a small proc in SQL to show you your current file sizes, IOPS, Throughput and Disk Tier your data files would be on in your SQL Server Managed Instance.
If your moving big sets of data around then increasing your file sizes will have a big performance boost. As always you have to measure it against your current workload, and of course bigger files cost more money in Azure Storage costs.
USE master;
GO
DROP PROCEDURE IF EXISTS dbo.sp_tdc_get_sql_managed_instance_iops_performance;
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE dbo.sp_tdc_get_sql_managed_instance_iops_performance
AS
BEGIN;
WITH DatabaseFiles AS (
SELECT DB_NAME(database_id) AS [Database Name],
type_desc AS [File Type],
name AS [File Name],
CAST(( size * 8.0 ) / 1024 AS DECIMAL(38, 1)) AS [Size MB],
CAST(( size * 8.0 ) / 1024 / 1024 AS DECIMAL(38, 1)) AS [Size GB]
FROM sys.master_files
WHERE type IN ( 0 ) AND
database_id >= 5
)
SELECT DF.[Database Name],
DF.[File Type],
DF.[File Name],
DF.[Size MB],
DF.[Size GB],
CASE WHEN DF.[Size GB] >= 0 AND
DF.[Size GB] <= 128 THEN 'P10'
WHEN DF.[Size GB] > 128 AND
DF.[Size GB] <= 256 THEN 'P15'
WHEN DF.[Size GB] > 256 AND
DF.[Size GB] <= 512 THEN 'P20'
WHEN DF.[Size GB] > 512 AND
DF.[Size GB] <= 1024 THEN 'P30'
WHEN DF.[Size GB] > 1024 AND
DF.[Size GB] <= 2048 THEN 'P40'
WHEN DF.[Size GB] > 2048 AND
DF.[Size GB] <= 4096 THEN 'P40'
WHEN DF.[Size GB] > 4096 AND
DF.[Size GB] <= 8192 THEN 'P50'
ELSE 'P60'
END AS [Azure Disk],
CASE WHEN DF.[Size GB] >= 0 AND
DF.[Size GB] <= 128 THEN 500
WHEN DF.[Size GB] > 128 AND
DF.[Size GB] <= 256 THEN 1100
WHEN DF.[Size GB] > 256 AND
DF.[Size GB] <= 512 THEN 2300
WHEN DF.[Size GB] > 512 AND
DF.[Size GB] <= 1024 THEN 5000
WHEN DF.[Size GB] > 1024 AND
DF.[Size GB] <= 2048 THEN 7500
WHEN DF.[Size GB] > 2048 AND
DF.[Size GB] <= 4096 THEN 7500
WHEN DF.[Size GB] > 4096 AND
DF.[Size GB] <= 8192 THEN 12500
ELSE 12500
END AS [Max IOPS Per FILE],
CASE WHEN DF.[Size GB] >= 0 AND
DF.[Size GB] <= 128 THEN 100
WHEN DF.[Size GB] > 128 AND
DF.[Size GB] <= 256 THEN 125
WHEN DF.[Size GB] > 256 AND
DF.[Size GB] <= 512 THEN 150
WHEN DF.[Size GB] > 512 AND
DF.[Size GB] <= 1024 THEN 200
WHEN DF.[Size GB] > 1024 AND
DF.[Size GB] <= 2048 THEN 250
WHEN DF.[Size GB] > 2048 AND
DF.[Size GB] <= 4096 THEN 250
WHEN DF.[Size GB] > 4096 AND
DF.[Size GB] <= 8192 THEN 500
ELSE 500
END AS [Max T/P Per File MiB/s]
FROM DatabaseFiles AS DF
ORDER BY DF.[Database Name],
DF.[File Type];
END;
GO
