sys.resource_stats (Azure SQL Database)sys.dm_db_resource_stats (Azure SQL Database)

適用範圍:Applies to: 是Azure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database 是Azure SQL 受控執行個體Azure SQL Managed InstanceYesAzure SQL 受控執行個體Azure SQL Managed Instance適用範圍:Applies to: 是Azure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database 是Azure SQL 受控執行個體Azure SQL Managed InstanceYesAzure SQL 受控執行個體Azure SQL Managed Instance

傳回 Azure SQL DatabaseAzure SQL Database 資料庫的 CPU、I/O 和記憶體耗用量。Returns CPU, I/O, and memory consumption for an Azure SQL DatabaseAzure SQL Database database. 每 15 秒有一個資料列存在,即使資料庫中沒有任何活動亦然。One row exists for every 15 seconds, even if there is no activity in the database. 歷程記錄資料的維護時間大約為一小時。Historical data is maintained for approximately one hour.

資料行Columns 資料類型Data Type 描述Description
end_timeend_time datetimedatetime UTC 時間會指出目前報告間隔的結束。UTC time indicates the end of the current reporting interval.
avg_cpu_percentavg_cpu_percent **decimal (5,2) **decimal (5,2) 平均運算使用率,以服務層限制的百分比計算。Average compute utilization in percentage of the limit of the service tier.
avg_data_io_percentavg_data_io_percent **decimal (5,2) **decimal (5,2) 平均資料 i/o 使用率(以服務層限制的百分比表示)。Average data I/O utilization in percentage of the limit of the service tier. 針對超大規模資料庫,請參閱 資源使用量統計資料中的資料 IOFor Hyperscale databases, see Data IO in resource utilization statistics.
avg_log_write_percentavg_log_write_percent **decimal (5,2) **decimal (5,2) 平均交易記錄寫入 (MBps) ,以服務層限制的百分比表示。Average transaction log writes (in MBps) as percentage of the service tier limit.
avg_memory_usage_percentavg_memory_usage_percent **decimal (5,2) **decimal (5,2) 平均記憶體使用率,以服務層限制的百分比計算。Average memory utilization in percentage of the limit of the service tier.

這包括用於緩衝集區頁面的記憶體,以及記憶體內部 OLTP 物件的儲存體。This includes memory used for buffer pool pages and storage of In-Memory OLTP objects.
xtp_storage_percentxtp_storage_percent **decimal (5,2) **decimal (5,2) 記憶體內部 OLTP 的儲存體使用量,以服務層的限制百分比為單位, (在報告間隔結束時) 。Storage utilization for In-Memory OLTP in percentage of the limit of the service tier (at the end of the reporting interval). 這包括用於儲存下列記憶體內部 OLTP 物件的記憶體:記憶體優化資料表、索引和資料表變數。This includes memory used for storage of the following In-Memory OLTP objects: memory-optimized tables, indexes, and table variables. 它也包含用來處理 ALTER TABLE 作業的記憶體。It also includes memory used for processing ALTER TABLE operations.

如果資料庫中未使用記憶體內部 OLTP,則傳回0。Returns 0 if In-Memory OLTP is not used in the database.
max_worker_percentmax_worker_percent **decimal (5,2) **decimal (5,2) 並行工作者 (要求的數目上限,) 以資料庫服務層級的限制百分比表示。Maximum concurrent workers (requests) in percentage of the limit of the database's service tier.
max_session_percentmax_session_percent **decimal (5,2) **decimal (5,2) 並行會話的數目上限,以資料庫服務層級的限制百分比表示。Maximum concurrent sessions in percentage of the limit of the database's service tier.
dtu_limitdtu_limit intint 此間隔期間,此資料庫目前的最大資料庫 DTU 設定。Current max database DTU setting for this database during this interval. 針對使用以 vCore 為基礎之模型的資料庫,這個資料行是 Null。For databases using the vCore-based model, this column is NULL.
cpu_limitcpu_limit **decimal (5,2) **decimal (5,2) 此資料庫在此間隔期間的虛擬核心數目。Number of vCores for this database during this interval. 針對使用以 DTU 為基礎之模型的資料庫,這個資料行是 Null。For databases using the DTU-based model, this column is NULL.
avg_instance_cpu_percentavg_instance_cpu_percent **decimal (5,2) **decimal (5,2) 裝載資料庫之 SQL Server 實例的平均 CPU 使用率(由作業系統測量)。Average CPU usage for the SQL Server instance hosting the database, as measured by the operating system. 包含使用者和內部工作負載的 CPU 使用率。Includes CPU utilization by both user and internal workloads.
avg_instance_memory_percentavg_instance_memory_percent **decimal (5,2) **decimal (5,2) 裝載資料庫之 SQL Server 實例的平均記憶體使用量(由作業系統測量)。Average memory usage for the SQL Server instance hosting the database, as measured by the operating system. 包含使用者和內部工作負載的記憶體使用量。Includes memory utilization by both user and internal workloads.
avg_login_rate_percentavg_login_rate_percent **decimal (5,2) **decimal (5,2) 僅供參考之用。Identified for informational purposes only. 不支援。Not supported. 我們無法保證未來的相容性。Future compatibility is not guaranteed.
replica_rolereplica_role intint 代表目前複本的角色,其具有0為主的複本、1為次要,而2作為轉寄站 (異地次要的主要) 。Represents the current replica’s role with 0 as primary, 1 as secondary, and 2 as forwarder (geo-secondary’s primary). 當您將 ReadOnly 意圖連接到所有可讀取的次要資料庫時,您會看到 "1"。You will see “1” when connected with ReadOnly intent to all readable secondaries. 如果未指定 ReadOnly 意圖而連接到異地次要資料庫,您應該會看到「2」 (連接至轉寄站) 。If connecting to a geo-secondary without specifying ReadOnly intent, you should see “2” (connecting to the forwarder).

提示

如需這些限制和服務層級的詳細內容,請參閱 服務層級在 Azure SQL Database 中手動調整查詢效能,以及 SQL Database 資源限制和資源管理For more context about these limits and service tiers, see the topics Service Tiers, Manually tune query performance in Azure SQL Database, and SQL Database resource limits and resource governance.

權限Permissions

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

備註Remarks

Sys.dm_db_resource_stats所傳回的資料會以您正在執行的服務層級/效能層級的最大允許限制百分比表示。The data returned by sys.dm_db_resource_stats is expressed as a percentage of the maximum allowed limits for the service tier/performance level that you are running.

如果資料庫在過去60分鐘內已容錯移轉到另一部伺服器,則此視圖只會傳回該容錯移轉後的時間資料。If the database was failed over to another server within the last 60 minutes, the view will only return data for the time since that failover.

若要以較長的保留期限更細微地查看這項資料,請在master資料庫中使用sys.resource_stats catalog view。For a less granular view of this data with longer retention period, use sys.resource_stats catalog view in the master database. 此檢視會每隔 5 秒擷取一次資料,並會保留 14 天內的歷程記錄資料。This view captures data every 5 minutes and maintains historical data for 14 days. 如需詳細資訊,請參閱 sys.resource_stats (Azure SQL Database)For more information, see sys.resource_stats (Azure SQL Database).

當資料庫是彈性集區的成員時,以百分比值呈現的資源統計資料會以彈性集區設定中設定之資料庫的最大限制百分比表示。When a database is a member of an elastic pool, resource statistics presented as percent values, are expressed as the percent of the max limit for the databases as set in the elastic pool configuration.

範例Example

下列範例會傳回資源使用率資料,並依據目前連接之資料庫的最新時間排序。The following example returns resource utilization data ordered by the most recent time for the currently connected database.

SELECT * FROM sys.dm_db_resource_stats ORDER BY end_time DESC;  
  

下列範例會識別平均 DTU 耗用量,其形式為過去一小時內使用者資料庫的效能層級所允許的最大 DTU 限制百分比。The following example identifies the average DTU consumption in terms of a percentage of the maximum allowed DTU limit in the performance level for the user database over the past hour. 如果這些百分比持續接近 100%,請考慮增加效能層級。Consider increasing the performance level as these percentages near 100% on a consistent basis.

SELECT end_time,   
  (SELECT Max(v)    
   FROM (VALUES (avg_cpu_percent), (avg_data_io_percent), (avg_log_write_percent)) AS    
   value(v)) AS [avg_DTU_percent]   
FROM sys.dm_db_resource_stats;  
  

下列範例會傳回 CPU 百分比、資料和記錄檔 I/O 以及記憶體耗用量在過去一個小時內的平均值和最大值。The following example returns the average and maximum values for CPU percent, data and log I/O, and memory consumption over the last hour.

SELECT    
    AVG(avg_cpu_percent) AS 'Average CPU Utilization In Percent',   
    MAX(avg_cpu_percent) AS 'Maximum CPU Utilization In Percent',   
    AVG(avg_data_io_percent) AS 'Average Data IO In Percent',   
    MAX(avg_data_io_percent) AS 'Maximum Data IO In Percent',   
    AVG(avg_log_write_percent) AS 'Average Log Write I/O Throughput Utilization In Percent',   
    MAX(avg_log_write_percent) AS 'Maximum Log Write I/O Throughput Utilization In Percent',   
    AVG(avg_memory_usage_percent) AS 'Average Memory Usage In Percent',   
    MAX(avg_memory_usage_percent) AS 'Maximum Memory Usage In Percent'   
FROM sys.dm_db_resource_stats;  
  

另請參閱See Also

sys.resource_stats (Azure SQL Database) 服務層級sys.resource_stats (Azure SQL Database) Service Tiers