sys.server_resource_stats (Azure SQL Database)
Returns CPU usage, IO, and storage data for an Azure SQL Managed Instance. The data is collected and aggregated within five-minute intervals. There is one row for every 15 seconds reporting. The data returned includes CPU usage, storage size, IO utilization, and managed instance SKU. Historical data is retained for approximately 14 days.
The sys.server_resource_stats view has different definitions depending on the version of the Azure SQL managed instance that the database is associated with. Consider these differences and any modifications your application requires when upgrading to a new server version.
The following table describes the columns available in a v12 server:
|start_time||datetime2||UTC time indicating the start of the fifteen-second reporting interval|
|end_time||datetime||UTC time indicating the end of the fifteen-second reporting interval|
|resource_type||Nvarchar(128)||Type of the resource for which metrics are provided|
|resource_name||nvarchar(128)||Name of the resource.|
|sku||nvarchar(128)||Managed Instance Service Tier of the Instance. The following are the possible values:
|hardware_generation||nvarchar(128)||Hardware generation identifier: such as Gen 4 or Gen 5|
|virtual_core_count||int||Represents number of virtual cores per instance (8, 16 or 24 in Public Preview)|
|avg_cpu_percent||decimal(5,2)||Average compute utilization in percentage of the limit of the Managed Instance service tier utilized by the instance. It is calculated as sum of CPU time of all resource pools for all databases in the instance and divided by available CPU time for that tier in the given interval.|
|reserved_storage_mb||bigint||Reserved storage per instance (amount of storage space that customer purchased for the managed instance)|
|storage_space_used_mb||decimal(18,2)||Storage used by all managed instance databases' files (including both user and system databases)|
|io_request||bigint||Total number of i/o physical operations within the interval|
|io_bytes_read||bigint||Number of physical bytes read within the interval|
|io_bytes_written||bigint||Number of physical bytes written within the interval|
For more context about these limits and service tiers, see the topics Managed Instance service tiers.
This view is available to all user roles with permissions to connect to the master database.
The data returned by sys.server_resource_stats are expressed as the total used in either bytes or megabytes (stated in column names) other than avg_cpu, which is expressed as a percentage of the maximum allowed limits for the service tier/performance level that you are running.
The following example returns all databases that are averaging at least 80% of compute utilization over the last one week.
DECLARE @s datetime; DECLARE @e datetime; SET @s= DateAdd(d,-7,GetUTCDate()); SET @e= GETUTCDATE(); SELECT resource_name, AVG(avg_cpu_percent) AS Average_Compute_Utilization FROM sys.server_resource_stats WHERE start_time BETWEEN @s AND @e GROUP BY resource_name HAVING AVG(avg_cpu_percent) >= 80