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.
There are a couple of important aspects related to the disk reserved & used 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;
On my small test SQL Managed Instance I have 262144 MB (256 GB) of the reserved space and I have currently used (created data & log files) that occupy 261062 MB (254.96 GB), representing 99.59% of the available reserved space.
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.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.