First published on MSDN on Aug 20, 2018
Azure SQL Database Managed Instance General Purpose (GP) is SQL Server implementation on Azure cloud that keeps all database files on Azure storage. In this post you will learn how Managed Instance General Purpose allocates disks in the storage layer at the back end and why this is important.
Please note this article does not apply to Business-Critical edition of Managed Instance.
Azure SQL Database Managed Instance General Purpose tier separates compute and storage layers where the database files are placed on Azure Premium disks. Managed Instance uses pre-defined sizes of azure disks (128GB, 256GB, 512GB, etc.) for every file, so every file is placed on a single disk with the smallest size that is large enough to fit the file with the current file size.
Once you provision Managed Instance you have the following two storage limits:
- Managed Instance General Purpose user storage cannot exceed 8TB. Your max storage size cannot be greater than 8TB, and you are paying only for the amount of storage size that you choose in portal.
- Maximum of 280 data files on the General Purpose instance. This limitation is due to how Azure premium disk storage is allocated internally. 280 files placed on the smallest 128GB disk will reach beyond the 35TB of maximum internal Azure premium storage limit. Please note that this limit is internal to Azure storage under the hood, and that customers can still only use up to 8TB of storage for the Managed Instance.
When you create database files, they are allocated on Azure premium disks with sizes that are greater than the actual file size, so practically this is "internal fragmentation" of files. This is implemented because Azure premium disk storage offers fixed set of disk sizes, so Managed Instance tries to fit database files on a matching disk.
The number of data files per General Purpose Managed Instance cannot be greater than 280 files summing up to the maximum of 35TB of storage on Azure premium disks with internal fragmentation. If you reach this limit you might start getting errors even if you have not actually reached the user-defined Managed Instance storage limit of 8TB.
In this post, you will see some scripts that can help you to see are you reaching this storage limit.
First, we will create a schema and view that wraps standard sys.master_files view and returns the allocated disk size for every file:
CREATE SCHEMA mi;
GO
CREATE OR ALTER VIEW mi.master_files
AS
WITH mi_master_files AS
( SELECT *, size_gb = CAST(size * 8. / 1024 / 1024 AS decimal(12,4))
FROM sys.master_files
WHERE physical_name LIKE 'https://%' AND database_id > 4 )
SELECT *, azure_disk_size_gb = IIF(
database_id <> 2,
CASE WHEN size_gb <= 128+1 THEN 128
WHEN size_gb > 128+1 AND size_gb <= 512+1 THEN 512
WHEN size_gb > 512+1 AND size_gb <= 1024+1 THEN 1024
WHEN size_gb > 1024+1 AND size_gb <= 2048+1 THEN 2048
WHEN size_gb > 2048+1 AND size_gb <= 4096+1 THEN 4096
ELSE 8192
END, NULL)
FROM mi_master_files
;
Now we can see the size allocated for the underlying Azure Premium Disks for every database file:
SELECT db = db_name(database_id), name, size_gb, azure_disk_size_gb
from mi.master_files
WHERE physical_name like 'https://%'
AND database_id > 4
Sum of the azure disk sizes should not exceed 35TB - otherwise you will reach the azure storage limit errors. You can check total allocated azure storage space using the following query:
SELECT storage_size_tb = SUM(azure_disk_size_gb) /1024.
FROM mi.master_files
WHERE physical_name LIKE 'https://%'
AND database_id > 4
Using this information, you can find out how many additional files you can add on a managed instance (assuming that new file will be smaller than 128GB):
SELECT remaining_number_of_128gb_files =
(35 - ROUND(SUM(azure_disk_size_gb) /1024,0)) * 8
FROM mi.master_files
WHERE physical_name LIKE 'https://%'
AND database_id > 4
This is important check because if this count became zero, you will not be able to add more files of database on the instance.
