sys.dm_resource_governor_resource_pools_history_ex (Transact-SQL)
Applies to: 
Azure SQL Database
Azure SQL Managed Instance
Each row represents a periodic snapshot of resource pool statistics in Azure SQL Database and Azure SQL Managed Instance. A snapshot is taken when the database engine starts, and every few seconds thereafter. The interval between the current and the previous snapshot can vary, and is provided in the duration_ms column. The latest available snapshots are returned, up to 128 snapshots for each resource pool.
Important
Most of the data surfaced by this DMV is intended for internal consumption and is subject to change.
| Column name | Data type | Description |
|---|---|---|
pool_id |
int | The ID of the resource pool. Not nullable. |
name |
sysname | The name of the resource pool. Not nullable. |
snapshot_time |
datetime2 | Datetime of the resource pool stats snapshot taken. |
duration_ms |
int | Duration between current and previous snapshot. |
statistics_start_time |
datetime2 | The time when statistics was reset for this pool. Not nullable. |
active_session_count |
int | Total active sessions in current snapshot. |
active_worker_count |
int | Total workers in current snapshot. |
delta_cpu_usage_ms |
int | CPU usage in milliseconds since last snapshot. Not nullable. |
delta_cpu_usage_preemptive_ms |
int | Preemptive Win32 calls not governed by SQL CPU RG, since last snapshot. |
used_data_space_kb |
bigint | Total space used in user databases associated with user pool. |
allocated_disk_space_kb |
bigint | Total data file size of user databases in the associated with user pool. |
target_memory_kb |
bigint | The target amount of memory, in kilobytes, the resource pool is trying to attain. This is based on the current settings and server state. Not nullable. |
used_memory_kb |
bigint | The amount of memory used, in kilobytes, for the resource pool. Not nullable. |
cache_memory_kb |
bigint | The current total cache memory usage in kilobytes. Not nullable. |
compile_memory_kb |
bigint | The current total stolen memory usage in kilobytes (KB). Most of this usage would be for compile and optimization, but it can also include other memory users. Not nullable. |
active_memgrant_count |
bigint | The current count of memory grants. Not nullable. |
active_memgrant_kb |
bigint | The sum, in kilobytes (KB), of current memory grants. Not nullable. |
used_memgrant_kb |
bigint | The current total used (stolen) memory from memory grants. Not nullable. |
delta_memgrant_timeout_count |
int | The count of memory grant time-outs in this resource pool in this period. Not nullable. |
delta_memgrant_waiter_count |
int | The count of queries currently pending on memory grants. Not nullable. |
delta_out_of_memory_count |
int | The number of failed memory allocations in the pool since last snapshot. Not nullable. |
delta_read_io_queued |
int | The total read IOs enqueued since last snapshot. Nullable. Null if the resource pool isn't governed for IO. |
delta_read_io_issued |
int | The total read IOs issued since last snapshot. Nullable. Null if the resource pool isn't governed for IO. |
delta_read_io_completed |
int | The total read IOs completed since last snapshot. Not nullable. |
delta_read_io_throttled |
int | The total read IOs throttled since snapshot. Nullable. Null if the resource pool isn't governed for IO. |
delta_read_bytes |
bigint | The total number of bytes read since last snapshot. Not nullable. |
delta_read_io_stall_ms |
int | Total time (in milliseconds) between read IO arrival and completion since last snapshot. Not nullable. |
delta_read_io_stall_queued_ms |
int | Total time (in milliseconds) between read IO arrival and issue since last snapshot. Nullable. Null if the resource pool isn't governed for IO. Non-zero delta_read_io_stall_queued_ms means IOs are being delayed by resource governance. |
delta_write_io_queued |
int | The total write IOs enqueued since last snapshot. Nullable. Null if the resource pool isn't governed for IO. |
delta_write_io_issued |
int | The total write IOs issued since last snapshot. Nullable. Null if the resource pool isn't governed for IO. |
delta_write_io_completed |
int | The total write IOs completed since last snapshot. Not nullable. |
delta_write_io_throttled |
int | The total write IOs throttled since last snapshot. Not nullable. |
delta_write_bytes |
bigint | The total number of bytes written since last snapshot. Not nullable. |
delta_write_io_stall_ms |
int | Total time (in milliseconds) between write IO arrival and completion since last snapshot. Not nullable. |
delta_write_io_stall_queued_ms |
int | Total time (in milliseconds) between write IO arrival and issue since last snapshot. Nullable. Null if the resource pool isn't governed for IO. |
delta_io_issue_delay_ms |
int | Total time (in milliseconds) between the scheduled issue and actual issue of IO since last snapshot. Nullable. Null if the resource pool isn't governed for IO. |
max_iops_per_volume |
int | The Maximum IO per second (IOPS) per disk volume setting for this Pool. Nullable. Null if the resource pool isn't governed for IO. |
max_memory_kb |
bigint | The maximum amount of memory, in kilobytes, that the resource pool can have. This is based on the current settings and server state. Not nullable. |
max_log_rate_kb |
bigint | Maximum log rate (kilo-bytes per sec) at resource pool level. |
max_data_space_kb |
bigint | Max elastic pool storage limit setting for this elastic pool in kilobytes. |
max_session |
int | Session limit for the pool. |
max_worker |
int | Worker limit for the pool. |
min_cpu_percent |
int | The current configuration for the guaranteed average CPU bandwidth for all requests in the resource pool when there's CPU contention. Not nullable. |
max_cpu_percent |
int | The current configuration for the maximum average CPU bandwidth allowed for all requests in the resource pool when there's CPU contention. Not nullable. |
cap_cpu_percent |
int | Hard cap on the CPU bandwidth that all requests in the resource pool receive. Limits the maximum CPU bandwidth level to the specified level. The allowed range for value is from 1 through 100. Not nullable. |
min_vcores |
decimal(5,2) | The current configuration for the guaranteed average CPU bandwidth for all requests in the resource pool when there's CPU contention. In units of vCores. |
max_vcores |
decimal(5,2) | The current configuration for the maximum average CPU bandwidth allowed for all requests in the resource pool when there's CPU contention. Expressed in the unit of vCores and might not reflect the total number of vCores or logical CPUs available to a database, elastic pool, or SQL managed instance. |
cap_vcores |
decimal(5,2) | Hard cap on the CPU bandwidth that all requests in the resource pool receive. In unit of vCores. |
instance_cpu_count |
int | Number of CPU configured for the instance. |
instance_cpu_percent |
decimal(5,2) | CPU percent configured for the instance. |
instance_vcores |
decimal(5,2) | Number of vCores configured for the instance. |
delta_log_bytes_used |
decimal(5,2) | Total log generation (in bytes) at pool level since last snapshot. |
avg_login_rate_percent |
decimal(5,2) | Number of logins since last snapshot, compared against login limit. |
delta_vcores_used |
decimal(5,2) | Compute utilization in count of vCores since last snapshot. |
cap_vcores_used_percent |
decimal(5,2) | Average compute utilization in percentage of the limit of the pool. |
instance_vcores_used_percent |
decimal(5,2) | Average compute utilization in percentage of the limit of the SQL instance. |
avg_data_io_percent |
decimal(5,2) | Average I/O utilization in percentage based on the limit of the pool. |
avg_log_write_percent |
decimal(5,2) | Average write resource utilization in percentage of the limit of the pool. |
avg_storage_percent |
decimal(5,2) | Average storage utilization in percentage of the storage limit of the pool. |
avg_allocated_storage_percent |
decimal(5,2) | The percentage of data space allocated by all databases in the elastic pool. This is the ratio of data space allocated to data max size for the elastic pool. For more information, visit File space management in SQL Database. |
max_worker_percent |
decimal(5,2) | Maximum concurrent workers (requests) in percentage based on the limit of the pool. |
max_session_percent |
decimal(5,2) | Maximum concurrent sessions in percentage based on the limit of the pool. |
active_outbound_connection_worker_count |
int | Total outbound connection workers in current snapshot. |
max_outbound_connection_worker |
int | Outbound connection worker limit for the pool. |
max_outbound_connection_worker_percent |
decimal(5,2) | Maximum concurrent outbound connection workers (requests) in percentage based on the limit of the pool. |
Permissions
This view requires VIEW SERVER STATE permission.
Remarks
Users can access this dynamic management view to monitor near real time resource consumption for user workload pool and system internal pools of Azure SQL Database instance.
Examples
The following example returns maximum log rate data and consumption at each snapshot by user pool:
SELECT snapshot_time,
name,
max_log_rate_kb,
delta_log_bytes_used
FROM sys.dm_resource_governor_resource_pools_history_ex
WHERE name LIKE 'SloSharedPool1'
ORDER BY snapshot_time DESC;