Edit

Share via

sys.dm_hs_database_log_rate (Transact-SQL)

  • Article

Applies to: Azure SQL Database

Each resultset row returns a component that controls (reduces) log generation rate in a Hyperscale database. There are multiple components in Hyperscale tier architecture that could contribute to the reducing log generation rate.

Certain types of components, such as secondary compute replicas or page servers, can temporarily reduce log generation rate on the primary compute replica to ensure the overall database health and stability.

If log generation rate is not reduced by any component, a row is returned for the primary compute replica component, showing the maximum allowed log generation rate for the database.

This dynamic management function returns rows only when the session is connected to the primary replica.

Note

The sys.dm_hs_database_log_rate dynamic management function currently applies to Azure SQL Database Hyperscale tier only.

Syntax

sys.dm_hs_database_log_rate ( 
{ database_id | NULL }                                     
)

Arguments

database_id

database_id is an integer representing the database ID, with no default value. Valid inputs are either a database ID or NULL.

Specified database_id: Returns a row for that specific database.

NULL: For a single database, returns a row for the current database. For a database in an elastic pool, returns rows for all databases in the pool where the caller has sufficient permissions.

The built-in function DB_ID can be specified.

Table Returned

Column name Data type Description
database_id int NOT NULL Identifier of the database. The values are unique within a single database or an elastic pool, but not within a logical server.
replica_id uniqueidentifier NOT NULL Identifier of a compute replica which corresponds to the replica_id column in sys.dm_database_replica_states. NULL when component_id corresponds to a Hyperscale page server.

This value is returned by the DATABASEPROPERTYEX(DB_NAME(), 'ReplicaID') function call when connected to the replica.
file_id int NULL ID of database file within the database that corresponds to the page server limiting the log generation rate. Will be populated only if the role is page server, otherwise returns NULL. This value corresponds to the file_id column in sys.database_files.
current_max_log_rate bigint NOT NULL The current max log rate limit for log generation rate on the primary compute replica, in bytes/sec. If no component is reducing log generation rate, reports the log generation rate limit for a Hyperscale database.
component_id uniqueidentifier NOT NULL A unique identifier of a Hyperscale component such as a page server or a compute replica.
role smallint NOT NULL All existing component roles that can reduce log generation.

Unknown = 0
Storage = 1
Primary = 2
Replica = 4
LocalDestage = 5
Destage = 6
GeoReplica = 10
StorageCheckpoint = 12
MigrationTarget = 14
When log generation rate is limited, the following wait types corresponding to each role might be observed on the primary compute replica:
1 - RBIO_RG_STORAGE
4 - RBIO_RG_REPLICA
5 - RBIO_RG_LOCALDESTAGE
6 - RBIO_RG_DESTAGE
10 - RBIO_RG_GEOREPLICA
12 - RBIO_RG_STORAGE_CHECKPOINT
14 - RBIO_RG_MIGRATION_TARGET
For more information, see Log rate throttling waits
role_desc nvarchar(60) NOT NULL Unknown = The component role is not known
Storage = Page server(s)
Primary = Primary compute replica
Replica = Secondary compute replica such as a high availability (HA) replica or a named replica.
LocalDestage = Log service
Destage = Long term log storage
GeoReplica = Geo-secondary replica
StorageCheckpoint = A checkpoint on a page server
MigrationTarget = The target database during reverse migration from Hyperscale to a non-Hyperscale database.
catchup_rate bigint NOT NULL Catchup Rate in bytes/sec. Returns zero when log rate is not reduced.
catchup_bytes bigint NOT NULL The amount of transaction log, in bytes, that a component must apply to catch up with the primary compute replica. Returns 0 when the component is caught up.
last_reported_time datetime The last time the Hyperscale log service reported values.

Remarks

In the Hyperscale service tier of Azure SQL Database, the log service ensures that the distributed components don't get far behind in applying transaction log. This is required to maintain the overall system health and stability. When components are behind and their catch-up rate is less than current log generation rate, the log service reduces the log generation rate on the primary. The sys.dm_hs_database_log_rate() DMF can be used to understand which component is causing the reduction in log rate and to what extent, and for how long the reduction of log rate might last.

For more context about log rate reduction, see Performance diagnostics in Hyperscale.

Permissions

This dynamic management function requires VIEW DATABASE PERFORMANCE STATE permission.

Examples

A. Return the component causing log rate reduction in a specific database

The following example returns a row for the component causing log rate reduction. If log generation rate is not reduced by any component, a row will be returned for the primary compute, showing the maximum allowed log generation rate for the database.

SELECT current_max_log_rate_bps, role_desc, catchup_rate_bps, catchup_distance_bytes
FROM sys.dm_hs_database_log_rate(DB_ID(N'Contosodb'));

B. Return the components causing log rate reduction

When connected to a database in an elastic pool, the following example returns a row for the component causing log rate reduction, for every database in the pool where you have sufficient permissions. When connected to a single database, returns the row for the database.

SELECT current_max_log_rate_bps, role_desc, catchup_rate_bps, catchup_distance_bytes 
FROM sys.dm_hs_database_log_rate(NULL);

Related content