sys.dm_user_db_resource_governance (Transact-sql) sys.dm_user_db_resource_governance (Transact-SQL)

適用於: SQL Server Azure SQL Database Azure Synapse Analytics 平行處理資料倉儲

傳回目前資料庫或彈性集區中的資源治理機制所使用的實際設定和容量設定。Returns actual configuration and capacity settings used by resource governance mechanisms in the current database or elastic pool.

資料行名稱Column name 資料類型Data type 描述Description
database_iddatabase_id intint 資料庫的識別碼,在 Azure SQL Database 伺服器內是唯一的。ID of the database, unique within an Azure SQL Database server.
logical_database_guidlogical_database_guid UNIQUEIDENTIFIERuniqueidentifier 使用者資料庫的邏輯 GUID,會持續到使用者資料庫的存留期內。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_guidphysical_database_guid UNIQUEIDENTIFIERuniqueidentifier 使用者資料庫的實體 GUID,可維持使用者資料庫實體實例的存留期。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_nameserver_name NVARCHARnvarchar 邏輯伺服器名稱。Logical server name.
database_namedatabase_name NVARCHARnvarchar 邏輯資料庫名稱。Logical database name.
slo_nameslo_name NVARCHARnvarchar 服務等級目標,包括硬體世代。Service level objective, including hardware generation.
dtu_limitdtu_limit intint VCore) 資料庫的 DTU 限制 (Null。DTU limit of database (NULL for vCore).
cpu_limitcpu_limit intint vCore DTU 資料庫) 的資料庫 (為 Null 的限制。vCore limit of database (NULL for DTU databases).
min_cpumin_cpu TINYINTtinyint 使用者工作負載資源集區的 MIN_CPU_PERCENT 值。The MIN_CPU_PERCENT value of the user workload resource pool. 請參閱 資源集區概念See Resource Pool Concepts.
max_cpumax_cpu TINYINTtinyint 使用者工作負載資源集區的 MAX_CPU_PERCENT 值。The MAX_CPU_PERCENT value of the user workload resource pool. 請參閱 資源集區概念See Resource Pool Concepts.
cap_cpucap_cpu TINYINTtinyint 使用者工作負載資源集區的 CAP_CPU_PERCENT 值。The CAP_CPU_PERCENT value of the user workload resource pool. 請參閱 資源集區概念See Resource Pool Concepts.
min_coresmin_cores SMALLINTsmallint 僅供內部使用。Internal use only.
max_dopmax_dop SMALLINTsmallint 使用者工作負載群組的 MAX_DOP 值。The MAX_DOP value for the user workload group. 請參閱 建立工作負載群組See CREATE WORKLOAD GROUP.
min_memorymin_memory intint 使用者工作負載資源集區的 MIN_MEMORY_PERCENT 值。The MIN_MEMORY_PERCENT value of the user workload resource pool. 請參閱 資源集區概念See Resource Pool Concepts.
max_memorymax_memory intint 使用者工作負載資源集區的 MAX_MEMORY_PERCENT 值。The MAX_MEMORY_PERCENT value of the user workload resource pool. 請參閱 資源集區概念See Resource Pool Concepts.
max_sessionsmax_sessions intint 使用者工作負載群組中允許的最大會話數目。The maximum number of sessions allowed in the user workload group.
max_memory_grantmax_memory_grant intint 使用者工作負載群組的 REQUEST_MAX_MEMORY_GRANT_PERCENT 值。The REQUEST_MAX_MEMORY_GRANT_PERCENT value for the user workload group. 請參閱 建立工作負載群組See CREATE WORKLOAD GROUP.
max_db_memorymax_db_memory intint 僅供內部使用。Internal use only.
govern_background_iogovern_background_io bitbit 僅供內部使用。Internal use only.
min_db_max_size_in_mbmin_db_max_size_in_mb BIGINTbigint 資料檔案的最小 max_size 值(以 MB 為單位)。The minimum max_size value for a data file, in MB. 請參閱 sys.database_filesSee sys.database_files.
max_db_max_size_in_mbmax_db_max_size_in_mb BIGINTbigint 資料檔 max_size 的最大值(以 MB 為單位)。The maximum max_size value for a data file, in MB. 請參閱 sys.database_filesSee sys.database_files.
default_db_max_size_in_mbdefault_db_max_size_in_mb BIGINTbigint 資料檔案的預設 max_size 值(以 MB 為單位)。The default max_size value for a data file, in MB. 請參閱 sys.database_filesSee sys.database_files.
db_file_growth_in_mbdb_file_growth_in_mb BIGINTbigint 資料檔案的預設成長增量(MB)。Default growth increment for a data file, in MB. 請參閱 sys.database_filesSee sys.database_files.
initial_db_file_size_in_mbinitial_db_file_size_in_mb BIGINTbigint 新資料檔案的預設大小(以 MB 為單位)。Default size for new data file, in MB. 請參閱 sys.database_filesSee sys.database_files.
log_size_in_mblog_size_in_mb BIGINTbigint 新記錄檔的預設大小(以 MB 為單位)。Default size for new log file, in MB. 請參閱 sys.database_filesSee sys.database_files.
instance_cap_cpuinstance_cap_cpu intint 僅供內部使用。Internal use only.
instance_max_log_rateinstance_max_log_rate BIGINTbigint SQL Server 實例的記錄產生速率限制(以每秒位元組數為單位)。Log generation rate limit for the SQL Server instance, in bytes per second. 適用于實例所產生的所有記錄,包括 tempdb 和其他系統資料庫。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_threadsinstance_max_worker_threads intint SQL Server 實例的背景工作執行緒限制。Worker thread limit for the SQL Server instance.
replica_typereplica_type intint 複本類型,其中0是主要,1是次要。Replica type, where 0 is Primary, and 1 is Secondary.
max_transaction_sizemax_transaction_size BIGINTbigint 任何交易使用的最大記錄檔空間,以 KB 為單位。Max log space used by any transaction, in KB.
checkpoint_rate_mbpscheckpoint_rate_mbps intint 僅供內部使用。Internal use only.
checkpoint_rate_iocheckpoint_rate_io intint 僅供內部使用。Internal use only.
last_updated_date_utclast_updated_date_utc Datetimedatetime 上次設定變更或重新設定的日期和時間(UTC 格式)。Date and time of the last setting change or reconfiguration, in UTC.
primary_group_idprimary_group_id intint 主要複本和次要複本上使用者工作負載的工作負載群組識別碼。Workload group ID for the user workload on primary replica and on secondary replicas.
primary_group_max_workersprimary_group_max_workers intint 使用者工作負載群組的背景工作執行緒限制。Worker thread limit for the user workload group.
primary_min_log_rateprimary_min_log_rate BIGINTbigint 使用者工作負載群組層級的每秒最小記錄速率(以位元組為單位)。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_rateprimary_max_log_rate BIGINTbigint 使用者工作負載群組層級的每秒最大記錄速率(以位元組為單位)。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_ioprimary_group_min_io intint 使用者工作負載群組的最小 IOPS。Minimum IOPS for the user workload group. 資源管理不會嘗試減少低於此值的 IOPS。Resource governance will not attempt to reduce IOPS below this value.
primary_group_max_ioprimary_group_max_io intint 使用者工作負載群組的最大 IOPS。Maximum IOPS for the user workload group. 資源管理不允許超過此值的 IOPS。Resource governance will not allow IOPS above this value.
primary_group_min_cpuprimary_group_min_cpu FLOATfloat 使用者工作負載群組層級的最小 CPU 百分比。Minimum CPU percent for the user workload group level. 資源管理不會嘗試降低低於此值的 CPU 使用率。Resource governance will not attempt to reduce CPU utilization below this value.
primary_group_max_cpuprimary_group_max_cpu FLOATfloat 使用者工作負載群組層級的最大 CPU 百分比。Maximum CPU percent for the user workload group level. 資源管理不允許超過此值的 CPU 使用率。Resource governance will not allow CPU utilization above this value.
primary_log_commit_feeprimary_log_commit_fee intint 使用者工作負載群組的記錄速率治理認可費用(以位元組為單位)。Log rate governance commit fee for the user workload group, in bytes. 認可費用會依據記錄速率帳戶處理的目的,將每個記錄 IO 的大小增加為固定值。A commit fee increases the size of each log IO by a fixed value for the purposes of log rate accounting only. 實際的記錄 IO 至儲存體不會增加。Actual log IO to storage is not increased.
primary_pool_max_workersprimary_pool_max_workers intint 使用者工作負載資源集區的背景工作執行緒限制。Worker thread limit for the user workload resource pool.
pool_max_iopool_max_io intint 使用者工作負載資源集區的最大 IOPS 限制。Maximum IOPS limit for the user workload resource pool.
govern_db_memory_in_resource_poolgovern_db_memory_in_resource_pool bitbit 僅供內部使用。Internal use only.
volume_local_iopsvolume_local_iops intint 僅供內部使用。Internal use only.
volume_managed_xstore_iopsvolume_managed_xstore_iops intint 僅供內部使用。Internal use only.
volume_external_xstore_iopsvolume_external_xstore_iops intint 僅供內部使用。Internal use only.
volume_type_local_iopsvolume_type_local_iops intint 僅供內部使用。Internal use only.
volume_type_managed_xstore_iopsvolume_type_managed_xstore_iops intint 僅供內部使用。Internal use only.
volume_type_external_xstore_iopsvolume_type_external_xstore_iops intint 僅供內部使用。Internal use only.
volume_pfs_iopsvolume_pfs_iops intint 僅供內部使用。Internal use only.
volume_type_pfs_iopsvolume_type_pfs_iops intint 僅供內部使用。Internal use only.

權限Permissions

此檢視需要 VIEW DATABASE STATE 權限。This view requires VIEW DATABASE STATE permission.

備註Remarks

如需 Azure SQL Database 中資源管理的說明,請參閱 SQL Database 資源限制For description of resource governance in Azure SQL Database, see SQL Database resource limits.

重要

此 DMV 所傳回的大部分資料都是供內部使用,而且隨時可能變更。Most of the data returned by this DMV is intended for internal consumption and is subject to change at any time.

範例Examples

下列查詢會在使用者資料庫的內容中執行,並傳回使用者工作負載群組和資源集區層級的最大記錄速率和最大 IOPS。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;  

另請參閱See Also