sys.dm_user_db_resource_governance (Transact-SQL)
Applies to: 
SQL Server 
Azure SQL Database
Azure Synapse Analytics
Analytics Platform System (PDW)
Returns actual configuration and capacity settings used by resource governance mechanisms in the current database or elastic pool.
| Column name | Data type | Description |
|---|---|---|
| database_id | int | ID of the database, unique within an Azure SQL Database server. |
| logical_database_guid | uniqueidentifier | Logical GUID for user database that stays through the life of a user database. Renaming the database or changing its service level objective will not change this value. |
| physical_database_guid | uniqueidentifier | Physical GUID for a user database which stays through the life of the physical instance of the user database. Changing the database service level objective will cause this value to change. |
| server_name | nvarchar | Logical server name. |
| database_name | nvarchar | Logical database name. |
| slo_name | nvarchar | Service level objective, including hardware generation. |
| dtu_limit | int | DTU limit of database (NULL for vCore). |
| cpu_limit | int | vCore limit of database (NULL for DTU databases). |
| min_cpu | tinyint | The MIN_CPU_PERCENT value of the user workload resource pool. See Resource Pool Concepts. |
| max_cpu | tinyint | The MAX_CPU_PERCENT value of the user workload resource pool. See Resource Pool Concepts. |
| cap_cpu | tinyint | The CAP_CPU_PERCENT value of the user workload resource pool. See Resource Pool Concepts. |
| min_cores | smallint | Internal use only. |
| max_dop | smallint | The MAX_DOP value for the user workload group. See CREATE WORKLOAD GROUP. |
| min_memory | int | The MIN_MEMORY_PERCENT value of the user workload resource pool. See Resource Pool Concepts. |
| max_memory | int | The MAX_MEMORY_PERCENT value of the user workload resource pool. See Resource Pool Concepts. |
| max_sessions | int | The maximum number of sessions allowed in the user workload group. |
| max_memory_grant | int | The REQUEST_MAX_MEMORY_GRANT_PERCENT value for the user workload group. See CREATE WORKLOAD GROUP. |
| max_db_memory | int | Internal use only. |
| govern_background_io | bit | Internal use only. |
| min_db_max_size_in_mb | bigint | The minimum max_size value for a data file, in MB. See sys.database_files. |
| max_db_max_size_in_mb | bigint | The maximum max_size value for a data file, in MB. See sys.database_files. |
| default_db_max_size_in_mb | bigint | The default max_size value for a data file, in MB. See sys.database_files. |
| db_file_growth_in_mb | bigint | Default growth increment for a data file, in MB. See sys.database_files. |
| initial_db_file_size_in_mb | bigint | Default size for new data file, in MB. See sys.database_files. |
| log_size_in_mb | bigint | Default size for new log file, in MB. See sys.database_files. |
| instance_cap_cpu | int | Internal use only. |
| instance_max_log_rate | bigint | Log generation rate limit for the SQL Server instance, in bytes per second. Applies to all log generated by the instance, including tempdb and other system databases. In an elastic pool, applies to log generated by all databases in the pool. |
| instance_max_worker_threads | int | Worker thread limit for the SQL Server instance. |
| replica_type | int | Replica type, where 0 is Primary, and 1 is Secondary. |
| max_transaction_size | bigint | Max log space used by any transaction, in KB. |
| checkpoint_rate_mbps | int | Internal use only. |
| checkpoint_rate_io | int | Internal use only. |
| last_updated_date_utc | datetime | Date and time of the last setting change or reconfiguration, in UTC. |
| primary_group_id | int | Workload group ID for the user workload on primary replica and on secondary replicas. |
| primary_group_max_workers | int | Worker thread limit for the user workload group. |
| primary_min_log_rate | bigint | Minimum log rate in bytes per second at user workload group level. Resource governance will not attempt to reduce log rate below this value. |
| primary_max_log_rate | bigint | Maximum log rate in bytes per second at user workload group level. Resource governance will not allow log rate above this value. |
| primary_group_min_io | int | Minimum IOPS for the user workload group. Resource governance will not attempt to reduce IOPS below this value. |
| primary_group_max_io | int | Maximum IOPS for the user workload group. Resource governance will not allow IOPS above this value. |
| primary_group_min_cpu | float | Minimum CPU percent for the user workload group level. Resource governance will not attempt to reduce CPU utilization below this value. |
| primary_group_max_cpu | float | Maximum CPU percent for the user workload group level. Resource governance will not allow CPU utilization above this value. |
| primary_group_max_outbound_connection_workers | int | Outbound connection worker thread limit for the primary user workload group. |
| primary_pool_max_outbound_connection_workers | int | Outbound connection worker thread limit for the user workload resource pool. |
| primary_log_commit_fee | int | Log rate governance commit fee for the user workload group, in bytes. A commit fee increases the size of each log IO by a fixed value for the purposes of log rate accounting only. Actual log IO to storage is not increased. |
| primary_pool_max_workers | int | Worker thread limit for the user workload resource pool. |
| pool_max_io | int | Maximum IOPS limit for the user workload resource pool. |
| govern_db_memory_in_resource_pool | bit | Internal use only. |
| volume_local_iops | int | Internal use only. |
| volume_managed_xstore_iops | int | Internal use only. |
| volume_external_xstore_iops | int | Internal use only. |
| volume_type_local_iops | int | Internal use only. |
| volume_type_managed_xstore_iops | int | Internal use only. |
| volume_type_external_xstore_iops | int | Internal use only. |
| volume_pfs_iops | int | Internal use only. |
| volume_type_pfs_iops | int | Internal use only. |
Permissions
This view requires VIEW DATABASE STATE permission.
Remarks
For description of resource governance in Azure SQL Database, see SQL Database resource limits.
Important
Most of the data returned by this DMV is intended for internal consumption and is subject to change at any time.
Examples
The following query, executed in the context of a user database, returns maximum log rate and maximum IOPS at the user workload group and resource pool level. For a single database, one row is returned. For a database in an elastic pool, a row is returned for each database in the pool.
SELECT database_name,
primary_group_id,
primary_max_log_rate,
primary_group_max_io,
pool_max_io
FROM sys.dm_user_db_resource_governance
ORDER BY database_name;
Next steps
- Resource Governor
- sys.dm_resource_governor_resource_pools (Transact-SQL)
- sys.dm_resource_governor_workload_groups (Transact-SQL)
- sys.dm_resource_governor_resource_pools_history_ex (Transact-SQL)
- sys.dm_resource_governor_workload_groups_history_ex (Azure SQL Database)
- Transaction log rate governance
- Single database DTU resource limits
- Single database vCore resource limits
- Elastic pool vCore resource limits