In the previous blog posts in the SQL MI How-Tos we have already touched on the aspect of SQL MI reserved and available Disk Space, but as in everything - there is so many things to add and expand. In this post we shall focus on the General Purpose service tier and the remote disk storage that is used in this service tier. Besides the explicit limits of the addressable space that is connected to the number of CPU vCores, there are important aspects of the remote storage that will limit the number of database files that can be located there.
If you are interested in other posts on how-to discover different aspects of SQL MI - please visit the http://aka.ms/sqlmi-howto, which serves as a placeholder for the series.
General Purpose service tier uses 2 types of storages - the fast local storage that is used for the [tempdb] database with 24 GB per each of the CPU vCores, and the remote storage that is used for the user databases and which is reserved and controlled by the customer. The minimum size for each of the user data & log files internally is 128 GB, because under the hood a single managed disk is allocated for each of the user database files. The possible sizes for each of the files are 128GB, 256 GB, 512 GB, 1024 GB, 2048 GB, 4096 GB & 8192 GB (current maximum), and each managed disk besides the size will also provide a different amount of IOPS and different throughput in MB/s.
The image below shows the current values associated with each of the managed disk:
This means that if your data or log file is 5 TB in size, then a single managed disk (P60) with storage capacity up to 8 TB will be allocated, and if you have a 1.5 TB file - then the next biggest managed disk (P40) with storage capacity up to 2 TB will be allocated. The upgrades and changes are done automatically for the customers.
The total maximum addressable space with managed disks for a General Purpose service tier remote storage is capped to 35 TB, where it will be split between the data files. This means that the maximum number of managed disks per SQL Managed Instance allocated with 128 GB (P10) would be 280, and for the user database file between 1 and 2 TB will be a 2TB managed disk (P40) that will have the total maximum of 15 user database files, mainly because of the total size is capped at 16 TB and 15 database files of over 1 TB will occupy this space if they are still have not reached 16 TB in sum.
This gets even more complicated because the total reservable space has a limit associated with the allocated number of CPU vCores, as presented on the picture below:
To make it more simple for the customers, we decided to share the following script that will provide key details on the current SQL MI reservable & addressable storage offering.
This script is split into 4 parts with each one delivering a different output, providing an important perspective:
- The first output provides the overall knowledge of the remote storage
- The second output brings the potential maximum number of files on each of the managed disks for the current configuration (CPU vCores, Reserved & Used space)
- The third output shows the potential maximum number of files on each of the managed disk for the current configuration of the CPU vCores and while applying the maximum reservable space for that configuration.
- The last output shows the overall maximum potential of the General Purpose, by showing the potential maximum number of files on each of the managed disk, given the current space occupation.
Important Note: this script will return NULLs if executed against the Business Critical service tier.
General Remote Storage Configuration
For the test I am running a SQL Managed Instance on General Purpose service tier on the standard-series hardware with 4 CPU vCores where I have reserved 1 TB of space and used 503 GB for the user databases.
It take a rather complex query that will take the data from [master] database, map and identify each data & log file into the respective managed disk and then add the necessary calculations for the maximum reservable space for the currently used hardware configuration, plus calculating some additional information about the amount of the local storage, total used storage for the user databases and the total number of data files in them:
;WITH mfiles AS
(
SELECT database_id, name as fileName, CAST(size * 8. / 1024 / 1024 AS decimal(9,2)) as sizeGB
FROM master.sys.master_files
WHERE physical_name LIKE 'https:%'
),
AzureDisk AS
( SELECT DB_NAME(database_id) as DBName, fileName, sizeGB,
IIF( database_id <> 2,
CASE WHEN sizeGB <= 128 THEN 128
WHEN sizeGB > 128 AND sizeGB <= 256 THEN 256
WHEN sizeGB > 256 AND sizeGB <= 512 THEN 512
WHEN sizeGB > 512 AND sizeGB <= 1024 THEN 1024
WHEN sizeGB > 1024 AND sizeGB <= 2048 THEN 2048
WHEN sizeGB > 2048 AND sizeGB <= 4096 THEN 4096
WHEN sizeGB > 4096 AND sizeGB <= 8192 THEN 8192
ELSE 8192
END, NULL) as AzureDiskInGB
FROM mfiles )
SELECT MAX(localSpace.LocallyAvailableTB) as LocalStorageTB,
MAX(actualSpace.ReservedStorageTB) as ReservedStorageTB,
MAX(actualSpace.UsedStorageTB) as UsedStorageTB,
CASE WHEN MAX(st.ServiceTier) = 'GeneralPurpose' THEN CAST(16 - (CASE MAX(cpu.CPU_vCores) WHEN 4 THEN 14 WHEN 8 THEN 8 ELSE 0 END) AS DECIMAL(9,3))
ELSE NULL END as CurrentInstanceMaxReservableTB,
CAST(16 AS DECIMAL(9,3))as MaxSQLMIReservableTB,
CASE WHEN MAX(st.ServiceTier) = 'GeneralPurpose' THEN COUNT(*) ELSE NULL END as UserDBFilesCount
FROM AzureDisk
CROSS APPLY(
SELECT TOP 1 CAST( reserved_storage_mb / 1024. / 1024 as DECIMAL(9,3) ) as ReservedStorageTB,
CAST( storage_space_used_mb / 1024. / 1024 as DECIMAL(9,3) ) as UsedStorageTB,
CAST( (storage_space_used_mb * 100. / reserved_storage_mb) as DECIMAL(9,3)) as [ReservedStoragePercentage]
FROM master.sys.server_resource_stats
ORDER BY end_time DESC ) as actualSpace
CROSS APPLY( SELECT cpu_rate / 100 as CPU_vCores FROM sys.dm_os_job_object ) as cpu
CROSS APPLY( SELECT TOP 1 sku as ServiceTier FROM [sys].[server_resource_stats] ORDER BY end_time DESC) as st
CROSS APPLY( SELECT vs.volume_mount_point as VolumeMountPoint,
CAST(MIN(total_bytes / 1024. / 1024 / 1024 / 1024 ) AS NUMERIC(9,3)) as LocallyUsedTB,
CAST(MIN(available_bytes / 1024. / 1024 / 1024 / 1024 ) AS NUMERIC(9,3)) as LocallyAvailableTB,
CAST(MIN((total_bytes+available_bytes) / 1024. / 1024 / 1024 / 1024 ) AS NUMERIC(9,3)) as LocallyTotalTB
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) vs
WHERE UPPER(vs.volume_mount_point) like 'C:\%'
GROUP BY vs.volume_mount_point ) as localSpace;
On the picture above you can see that I do have 94 GB of the local SSD storage (4 * 24 GB, 4 is a number of CPU vCores and 24 GB of local SSD space are given per each CPU vCore), 1 TB of the reserved remote Storage, 503 GB of the used storage and since we have here a system with 4 CPU vCores, we can address and reserve the maximum of 2 TB (column [CurrentInstanceMaxReservableTB]), with the total current maximum being set to 16 TB and the total number of user data & log files is 12, as is being shown in the last column.
Potential Managed Disks for the current configuration (CPU vCores, Reserved & Used space)
From the second query we shall find out how many different data files for each of the possible managed disks we can add to our current instance. The query is very similar on the base to the first one, but we shall do different calculations to present each of the potential disks that can be used, given the overall limit of the 35, minus the disks that were already used for each of the data & log files inside the user databases:
-- The current CPU and vCore configuration
;WITH mfiles AS
(
SELECT database_id, name as fileName, CAST(size * 8. / 1024 / 1024 AS decimal(9,2)) as sizeGB
FROM master.sys.master_files
WHERE physical_name LIKE 'https:%'
),
AzureDisk AS
( SELECT DB_NAME(database_id) as DBName, fileName, sizeGB,
IIF( database_id <> 2,
CASE WHEN sizeGB <= 128 THEN 128
WHEN sizeGB > 128 AND sizeGB <= 256 THEN 256
WHEN sizeGB > 256 AND sizeGB <= 512 THEN 512
WHEN sizeGB > 512 AND sizeGB <= 1024 THEN 1024
WHEN sizeGB > 1024 AND sizeGB <= 2048 THEN 2048
WHEN sizeGB > 2048 AND sizeGB <= 4096 THEN 4096
WHEN sizeGB > 4096 AND sizeGB <= 8192 THEN 8192
ELSE 8192
END, NULL) as AzureDiskInGB
FROM mfiles )
,cpu AS ( SELECT TOP 1 cpu_rate / 100 as CPU_vCores FROM sys.dm_os_job_object )
SELECT MAX(ReservedStorageTB) as ReservedStorageTB,
MAX(UsedStorageTB) as UsedStorageTB,
CASE WHEN MAX(st.ServiceTier) = 'GeneralPurpose' THEN CAST(35 - SUM(AzureDiskInGB)/1024. as DECIMAL(9,3)) ELSE NULL END as PotentialDiskReservableTB,
CASE WHEN MAX(st.ServiceTier) = 'GeneralPurpose' THEN CAST( LEAST( (35 - ROUND(SUM(AzureDiskInGB) /1024,0)) * 8,
( MAX(actualSpace.ReservedStorageTB) - MAX(UsedStorageTB)) * 1024 )
AS INT )
ELSE NULL END as CurrentConfigPotential128GBDisks,
CASE WHEN MAX(st.ServiceTier) = 'GeneralPurpose' THEN CAST( LEAST(
(35 - ROUND(SUM(AzureDiskInGB) /1024,0)) * 4,
(MAX(actualSpace.ReservedStorageTB) - MAX(actualSpace.UsedStorageTB)) / 0.128
)AS INT )
ELSE NULL END as CurrentConfigPotential256GBDisks,
CASE WHEN MAX(st.ServiceTier) = 'GeneralPurpose' THEN CAST(LEAST(
(35 - ROUND(SUM(AzureDiskInGB) /1024,0)) * 2,
( (MAX(actualSpace.ReservedStorageTB) - MAX(actualSpace.UsedStorageTB)) / 0.256 )
)AS INT )
ELSE NULL END as CurrentConfigPotential512GBDisks,
CASE WHEN MAX(st.ServiceTier) = 'GeneralPurpose' THEN CAST(LEAST(
(35 - ROUND(SUM(AzureDiskInGB) /1024,0)) * 1,
(MAX(actualSpace.ReservedStorageTB) - MAX(actualSpace.UsedStorageTB)) / 0.512
) AS INT )
ELSE NULL END as CurrentConfigPotential1TBDisks,
CASE WHEN MAX(st.ServiceTier) = 'GeneralPurpose' THEN CAST( LEAST(
(35 - ROUND(SUM(AzureDiskInGB) /1024,0)) * 0.5,
(MAX(actualSpace.ReservedStorageTB) - MAX(actualSpace.UsedStorageTB)) / 1
) AS INT )
ELSE NULL END as CurrentConfigPotential2TBDisks,
CASE WHEN MAX(st.ServiceTier) = 'GeneralPurpose' THEN CAST( LEAST(
(35 - ROUND(SUM(AzureDiskInGB) /1024,0)) * 0.25,
(MAX(actualSpace.ReservedStorageTB) - MAX(actualSpace.UsedStorageTB)) / 2
) AS INT )
ELSE NULL END as CurrentConfigPotential4TBDisks,
CASE WHEN MAX(st.ServiceTier) = 'GeneralPurpose' THEN CAST( LEAST(
(35 - ROUND(SUM(AzureDiskInGB) /1024,0)) * 0.125,
(MAX(actualSpace.ReservedStorageTB) - MAX(actualSpace.UsedStorageTB)) / 4
) AS INT )
ELSE NULL END as CurrentConfigPotential8TBDisks
FROM AzureDisk
CROSS APPLY(
SELECT TOP 1 CAST( reserved_storage_mb / 1024. / 1024 as DECIMAL(9,3) ) as ReservedStorageTB,
CAST( storage_space_used_mb / 1024. / 1024 as DECIMAL(9,3) ) as UsedStorageTB,
CAST( (storage_space_used_mb * 100. / reserved_storage_mb) as DECIMAL(9,3)) as [ReservedStoragePercentage]
FROM master.sys.server_resource_stats
ORDER BY end_time DESC ) as actualSpace
CROSS APPLY( SELECT CAST(16 - (CASE MAX(cpu.CPU_vCores) WHEN 4 THEN 14 WHEN 8 THEN 8 ELSE 0 END) AS DECIMAL(9,3)) as CurrentInstanceMaxReservableTB FROM cpu) as storage
CROSS APPLY( SELECT TOP 1 sku as ServiceTier FROM sys.server_resource_stats ORDER BY end_time DESC) as st;
This query returns the 32.750 TB of the addressable space for the managed disks (for occupying 503 GB we have spent 2.25 TB of the addressable space), and we can potentially occupy up to 264 128GB managed disks (which will have to be very very small in the real space, since we have just 501 GB left (1024 GB reserved - 503 GB spent)), or just 3 256 GB managed disks (the smallest real occupied space for those disks is 128 GB, as per the picture for the managed disks in the beginning of the article) or we can occupy incompletely a single 512 GB managed disk (we have just 501 GB left out of our reserved space)
Potential Managed Disks for the current configuration (CPU vCores, Reserved & Used space)
The third output shows the potential maximum number of files on each of the managed disk for the current configuration of the CPU vCores and while applying the maximum reservable space for that configuration.
This will allow the user to discover how much further space they can achieve with the current hardware configuration without resizing the CPU vCores but by simply reserving the maximum addressable space.
In the case of the current example we have reserved 1 TB, but the maximum addressable space for the 4 CPU vCores on General Purpose service tier currently is 2 TB, meaning that the customer can potentially can reserve another 1 TB without the need of scaling.
-- Currently Addressable without CPU vCores Scaling
;WITH mfiles AS
(
SELECT database_id, name as fileName, CAST(size * 8. / 1024 / 1024 AS decimal(9,2)) as sizeGB
FROM master.sys.master_files
WHERE physical_name LIKE 'https:%'
),
AzureDisk AS
( SELECT DB_NAME(database_id) as DBName, fileName, sizeGB,
IIF( database_id <> 2,
CASE WHEN sizeGB <= 128 THEN 128
WHEN sizeGB > 128 AND sizeGB <= 256 THEN 256
WHEN sizeGB > 256 AND sizeGB <= 512 THEN 512
WHEN sizeGB > 512 AND sizeGB <= 1024 THEN 1024
WHEN sizeGB > 1024 AND sizeGB <= 2048 THEN 2048
WHEN sizeGB > 2048 AND sizeGB <= 4096 THEN 4096
WHEN sizeGB > 4096 AND sizeGB <= 8192 THEN 8192
ELSE 8192
END, NULL) as AzureDiskInGB
FROM mfiles )
,cpu AS ( SELECT TOP 1 cpu_rate / 100 as CPU_vCores FROM sys.dm_os_job_object )
SELECT MAX(ReservedStorageTB) as ReservedStorageTB,
MAX(UsedStorageTB) as UsedStorageTB,
MAX(storage.CurrentInstanceMaxReservableTB) as CurrentInstanceMaxReservableTB,
CASE WHEN MAX(st.ServiceTier) = 'GeneralPurpose' THEN CAST(35 - SUM(AzureDiskInGB)/1024. as DECIMAL(9,3)) ELSE NULL END as PotentialDiskReservableTB,
CASE WHEN MAX(st.ServiceTier) = 'GeneralPurpose' THEN CAST( LEAST( (35 - ROUND(SUM(AzureDiskInGB) /1024,0)) * 8,
( MAX(storage.CurrentInstanceMaxReservableTB) - MAX(UsedStorageTB)) * 1024 )
AS INT )
ELSE NULL END as CurrentInstancePotential128GBDisks,
CASE WHEN MAX(st.ServiceTier) = 'GeneralPurpose' THEN CAST( LEAST(
(35 - ROUND(SUM(AzureDiskInGB) /1024,0)) * 4,
(MAX(storage.CurrentInstanceMaxReservableTB) - MAX(actualSpace.UsedStorageTB)) / 0.128
)AS INT )
ELSE NULL END as CurrentInstancePotential256GBDisks,
CASE WHEN MAX(st.ServiceTier) = 'GeneralPurpose' THEN CAST(LEAST(
(35 - ROUND(SUM(AzureDiskInGB) /1024,0)) * 2,
( (MAX(storage.CurrentInstanceMaxReservableTB) - MAX(actualSpace.UsedStorageTB)) / 0.256 )
)AS INT )
ELSE NULL END as CurrentInstancePotential512GBDisks,
CASE WHEN MAX(st.ServiceTier) = 'GeneralPurpose' THEN CAST(LEAST(
(35 - ROUND(SUM(AzureDiskInGB) /1024,0)) * 1,
(MAX(storage.CurrentInstanceMaxReservableTB) - MAX(actualSpace.UsedStorageTB)) / 0.512
) AS INT )
ELSE NULL END as CurrentInstancePotential1TBDisks,
CASE WHEN MAX(st.ServiceTier) = 'GeneralPurpose' THEN CAST( LEAST(
(35 - ROUND(SUM(AzureDiskInGB) /1024,0)) * 0.5,
(MAX(storage.CurrentInstanceMaxReservableTB) - MAX(actualSpace.UsedStorageTB)) / 1
) AS INT )
ELSE NULL END as CurrentInstancePotential2TBDisks,
CASE WHEN MAX(st.ServiceTier) = 'GeneralPurpose' THEN CAST( LEAST(
(35 - ROUND(SUM(AzureDiskInGB) /1024,0)) * 0.25,
(MAX(storage.CurrentInstanceMaxReservableTB) - MAX(actualSpace.UsedStorageTB)) / 2
) AS INT )
ELSE NULL END as CurrentInstancePotential4TBDisks,
CASE WHEN MAX(st.ServiceTier) = 'GeneralPurpose' THEN CAST( LEAST(
(35 - ROUND(SUM(AzureDiskInGB) /1024,0)) * 0.125,
(MAX(storage.CurrentInstanceMaxReservableTB) - MAX(actualSpace.UsedStorageTB)) / 4
) AS INT )
ELSE NULL END as CurrentInstancePotential8TBDisks
FROM AzureDisk
CROSS APPLY(
SELECT TOP 1 CAST( reserved_storage_mb / 1024. / 1024 as DECIMAL(9,3) ) as ReservedStorageTB,
CAST( storage_space_used_mb / 1024. / 1024 as DECIMAL(9,3) ) as UsedStorageTB,
CAST( (storage_space_used_mb * 100. / reserved_storage_mb) as DECIMAL(9,3)) as [ReservedStoragePercentage]
FROM master.sys.server_resource_stats
ORDER BY end_time DESC ) as actualSpace
CROSS APPLY( SELECT CAST(16 - (CASE MAX(cpu.CPU_vCores) WHEN 4 THEN 14 WHEN 8 THEN 8 ELSE 0 END) AS DECIMAL(9,3)) as CurrentInstanceMaxReservableTB FROM cpu) as storage
CROSS APPLY( SELECT TOP 1 sku as ServiceTier FROM sys.server_resource_stats ORDER BY end_time DESC) as st;
The result on the picture above will show that we can potentially occupy up to 264 128GB managed disks, or 11 256 GB managed disks (instead of the currently addressable 3 without reserving additional space), or 5 512 GB managed disks, or 2 1 TB managed disks or even 1 2 TB managed disk (starts with just a bit above 1 TB of the reserved space by the user database file).
Potential Managed Disks for the biggest SQL MI disk space
The last script output shows the overall maximum potential of the General Purpose, by showing the potential maximum number of files on each of the managed disk, given the current space occupation. As of the moment of this article writing the maximum of reservable disk space is limited to 16 TB and it is reachable with just 16 CPU vCores.
;WITH mfiles AS
(
SELECT database_id, name as fileName, CAST(size * 8. / 1024 / 1024 AS decimal(9,2)) as sizeGB
FROM master.sys.master_files
WHERE physical_name LIKE 'https:%'
),
AzureDisk AS
( SELECT DB_NAME(database_id) as DBName, fileName, sizeGB,
IIF( database_id <> 2,
CASE WHEN sizeGB <= 128 THEN 128
WHEN sizeGB > 128 AND sizeGB <= 256 THEN 256
WHEN sizeGB > 256 AND sizeGB <= 512 THEN 512
WHEN sizeGB > 512 AND sizeGB <= 1024 THEN 1024
WHEN sizeGB > 1024 AND sizeGB <= 2048 THEN 2048
WHEN sizeGB > 2048 AND sizeGB <= 4096 THEN 4096
WHEN sizeGB > 4096 AND sizeGB <= 8192 THEN 8192
ELSE 8192
END, NULL) as AzureDiskInGB
FROM mfiles )
SELECT MAX(ReservedStorageTB) as ReservedStorageTB,
MAX(UsedStorageTB) as UsedStorageTB,
CAST(16 AS DECIMAL(9,3)) as MaxSQLMIReservableTB,
CASE WHEN MAX(st.ServiceTier) = 'GeneralPurpose' THEN CAST(35 - SUM(AzureDiskInGB)/1024. as DECIMAL(9,3)) ELSE NULL END as PotentialDiskReservableTB,
CASE WHEN MAX(st.ServiceTier) = 'GeneralPurpose' THEN (35 - ROUND(SUM(AzureDiskInGB) /1024,0)) * 8
ELSE NULL END as Potential128GBDisks,
CASE WHEN MAX(st.ServiceTier) = 'GeneralPurpose' THEN CAST( LEAST(
(35 - ROUND(SUM(AzureDiskInGB) /1024,0)) * 4,
(16 - MAX(actualSpace.UsedStorageTB)) / 0.128
)AS INT )
ELSE NULL END as Potential256GBDisks,
CASE WHEN MAX(st.ServiceTier) = 'GeneralPurpose' THEN CAST(LEAST(
(35 - ROUND(SUM(AzureDiskInGB) /1024,0)) * 2,
( (16 - MAX(actualSpace.UsedStorageTB)) / 0.256 )
)AS INT )
ELSE NULL END as Potential512GBDisks,
CASE WHEN MAX(st.ServiceTier) = 'GeneralPurpose' THEN CAST(LEAST(
(35 - ROUND(SUM(AzureDiskInGB) /1024,0)) * 1,
(16 - MAX(actualSpace.UsedStorageTB)) / 0.512
) AS INT )
ELSE NULL END as Potential1TBDisks,
CASE WHEN MAX(st.ServiceTier) = 'GeneralPurpose' THEN CAST( LEAST(
(35 - ROUND(SUM(AzureDiskInGB) /1024,0)) * 0.5,
(16 - MAX(actualSpace.UsedStorageTB)) / 1
) AS INT )
ELSE NULL END as Potential2TBDisks,
CASE WHEN MAX(st.ServiceTier) = 'GeneralPurpose' THEN CAST( LEAST(
(35 - ROUND(SUM(AzureDiskInGB) /1024,0)) * 0.25,
(16 - MAX(actualSpace.UsedStorageTB)) / 2
) AS INT )
ELSE NULL END as Potential4TBDisks,
CASE WHEN MAX(st.ServiceTier) = 'GeneralPurpose' THEN CAST( LEAST(
(35 - ROUND(SUM(AzureDiskInGB) /1024,0)) * 0.125,
(16 - MAX(actualSpace.UsedStorageTB)) / 4
) AS INT )
ELSE NULL END as Potential8TBDisks
FROM AzureDisk
CROSS APPLY(
SELECT TOP 1 CAST( reserved_storage_mb / 1024. / 1024 as DECIMAL(9,3) ) as ReservedStorageTB,
CAST( storage_space_used_mb / 1024. / 1024 as DECIMAL(9,3) ) as UsedStorageTB,
CAST( (storage_space_used_mb * 100. / reserved_storage_mb) as DECIMAL(9,3)) as [ReservedStoragePercentage]
FROM master.sys.server_resource_stats
ORDER BY end_time DESC ) as actualSpace
CROSS APPLY( SELECT TOP 1 sku as ServiceTier FROM [sys].[server_resource_stats] ORDER BY end_time DESC) as st;
As you will see on the image of the results below, we can reach pretty impressive managed disk sizes (and hence performance), if we size the files to maximize the throughput:
Besides the smaller disk sizes which will grow to 121 256GB managed disks, or 60 512GB managed disks, or 30 1TB managed disks, we can even reach 15 2TB managed disks, or 7 4TB managed disks and even 3 8 TB managed disks (starting with a bit over 4TB)!
These scripts will help you planning your Managed Instance General Purpose service tier user database files performance. You can join those scripts into a single output, getting a whole overview, or feel free to mold them and reuse them in your scripts.
Posted at https://sl.advdat.com/3mZktWY