In the previous blog posts we have shared the scripts on how to determine your SQL Managed Instance Service Tier type, computing (CPU vCores) and memory (RAM) parameters.
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 place holder for the series.
As indicated in the previous post, in this how-to post we shall dive into the reserved and available disk spaces for the SQL MI.
You can find and reference the whole series with the following link http://aka.ms/sqlmi-howto.
There are a couple of important aspects related to the disk reserved & used space:
- The total reserved disk space. This parameter is controlled outside of the SQL Managed Instance running environment, with the help of the Azure Portal or any of the supported command interfaces, such as CLI & PowerShell, but the reserved values and used values can be observed with the help of T-SQL.
- The total available and used space for the local SSD drives on General Purpose service tier of SQL MI, which is not included into the previous parameter.
Total Reserved & Used Disk Space
The total amount of reserved disk space means the total addressable space for data and log files that SQL MI can use. For the General Purpose (GP) service tier, the amount of space that is available for the TempDB is different, because it is not included in the total – giving you potentially an extra couple of hundreds of GBs of local SSD storage (the exact number will depend on selected configuration and the hardware generation).
The information about the total reserved space can be found in the master.sys.server_resource_stats DMV (currently exclusive for Azure SQL DB & Azure SQL MI), under the column [reserved_storage_mb] and the respective used amount of storage is located in the same DMV in the [storage_space_used_mb] column. As in previous how-to articles, we need to obtain the latest available timestamp by sorting descending on the [end_time] column in order to obtain the latest information about the space.
The query below extracts this information together with the calculation of the overall used percentage.
Warning: it does not mean that all of the underlying space was actually occupied by data, it means that your database data and log files have reserved this amount by growing their respective size.
SELECT TOP 1 reserved_storage_mb, storage_space_used_mb, CAST( (storage_space_used_mb * 100. / reserved_storage_mb) as DECIMAL(9,2)) as [ReservedStoragePercentage]
FROM master.sys.server_resource_stats
ORDER BY end_time DESC;
This is one of the key monitoring and troubleshooting aspects that needs to be payed attention to.
The owner is in the control of the reserved space and we are not automatically increasing it, since such change would represent a monetary impact on our customers - and such changes must be approved and executed by the customers.
Reserved space for TempDB on General Purpose (GP) service tier:
On Business Critical (BC) service tier, since all storage is local and this is definitely not applicable - meaning that we are giving SQL MI on GP a kind of a great bonus.
IF ( (SELECT TOP 1 SKU
FROM [sys].[server_resource_stats]
ORDER BY end_time DESC) = 'GeneralPurpose' )
BEGIN
SELECT vs.volume_mount_point as VolumeMountPoint,
CAST(MIN(total_bytes / 1024. / 1024 / 1024) AS NUMERIC(9,2)) as LocallyUsedGB,
CAST(MIN(available_bytes / 1024. / 1024 / 1024) AS NUMERIC(9,2)) as LocallyAvailableGB,
CAST(MIN((total_bytes+available_bytes) / 1024. / 1024 / 1024) AS NUMERIC(9,2)) as LocallyTotalGB
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;
END
On my test SQL MI with just 4 CPU vCores, I have received 96 GB of the local space for TempDB and currently used just around 0.19 GB, leaving 95.81 GB still available. Keep in mind that this reserved space is applicable for the whole TempDB database, including Log File, which is currently limited to 120 GB at maximum.
Joining the information about the reserved remote storage (256 GB) and the local SSD (96 GB) gives the final value of 352 GB, you can use the following query to find it out on General Purpose (GP) service tier:
SELECT SUM(TotalGB) as TotalSpaceGB
FROM (
SELECT vs.volume_mount_point as VolumeMountPoint,
CAST(MIN(total_bytes / 1024. / 1024 / 1024) AS NUMERIC(9,2)) as UsedGB,
CAST(MIN(available_bytes / 1024. / 1024 / 1024) AS NUMERIC(9,2)) as AvailableGB,
CAST(MIN((total_bytes+available_bytes) / 1024. / 1024 / 1024) AS NUMERIC(9,2)) as TotalGB
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) vs
GROUP BY vs.volume_mount_point) fsrc;
As we progress improving SQL MI, some of the details might change in the future, but the path for discovery with the help of T-SQL should remain valid.