AzureDatabase Performance

SQL Server Managed Instance IO Performance

By 24 June 2020No Comments

SQL Server Managed Instance IO Performance is dependant 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 < 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

 

Follow me