SQL Server Managed Instance IO Performance

SQL Server Managed Instance IO performance is dependent on a few settings.

1. Service tier
2. vCore count
3. File sizes!

Yes, file sizes. A quick explanation: if you have a small database, under 128 GB, you’re not going to get much data throughput. Only around 500 IOPS or 100 MB/s. It’s all explained in Microsoft’s resource limits documentation.

You will need to make your data file bigger; how to do that and measure the impact is covered in this Tech Community post.

You can find the Azure managed disks and their performance profiles on the managed disks pricing page.

I wrote a small proc in SQL to show you your current file sizes, IOPS, throughput and the disk tier your data files would be on in your SQL Server Managed Instance.

If you’re moving big sets of data around then increasing your file sizes will give a big performance boost. As always you have to measure it against your current workload, and of course bigger files cost more in Azure Storage.

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