Edit

Share via

sys.dm_os_memory_health_history

Applies to: SQL Server 2025 (17.x) Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

Provides memory health snapshots for a database engine instance. Each row represents a snapshot of recent memory usage that includes the severity of a memory health issue, if any.

For more information on troubleshooting insufficient memory in Azure SQL Database, see Troubleshoot out of memory errors with Azure SQL Database and Fabric SQL database.

In Azure SQL Managed Instance, this view is available only on instances with the SQL Server 2025 or Always-up-to-date update policy.

Column Name Data Type Description
snapshot_time datetime2 Snapshot time. Nullable.
severity_level tinyint A value describing the severity of a memory health issue. Not nullable.

1 - No memory health issue is identified.

2 - Medium confidence that a memory health issue might be present. Some attempts to allocate memory might fail. Data cache might be shrinking, and disk I/O can increase as the result.

3 - High confidence that a memory health issue is present. Available memory is likely insufficient, new memory allocation attempts can fail, and disk I/O can increase substantially because the size of the remaining data cache is too small.
severity_level_desc nvarchar(60) Severity level description, one of:

LOW
MEDIUM
HIGH
allocation_potential_memory_mb int Memory available to the database engine instance for new allocations, in megabytes. Not nullable.
reclaimable_cache_memory_mb int Memory used by reclaimable caches such as the buffer pool and the columnstore object pool, in megabytes. Under memory pressure, the database engine might shrink these caches to use the memory elsewhere. Not nullable.
top_memory_clerks nvarchar(4000) Top memory clerks by memory consumption, including the allocated page memory for each clerk. This information is provided as a JSON value. Nullable.
out_of_memory_event_count int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
memgrant_timeout_count int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
memgrant_waiter_count int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

Permissions

In SQL Server and Azure SQL Managed Instance, requires the VIEW SERVER PERFORMANCE STATE permission.

In SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account, the Microsoft Entra admin account, or membership in the ##MS_ServerPerformanceStateReader## server role is required. In all other SQL Database service objectives, either the VIEW DATABASE PERFORMANCE STATE permission on the database, or membership in the ##MS_ServerPerformanceStateReader## server role is required.

Remarks

A snapshot is added approximately every 15 seconds, for a total of up to 256 snapshots. Older snapshots are removed as newer snapshots are added. Data in this view is reset when the database engine restarts.

Examples

A. Get all available memory health snapshots

This example returns all memory health snapshot rows ordered by the snapshot time.

SELECT snapshot_time,
       severity_level,
       allocation_potential_memory_mb,
       reclaimable_cache_memory_mb,
       top_memory_clerks
FROM sys.dm_os_memory_health_history
ORDER BY snapshot_time DESC;

B. Get top memory clerks in each snapshot

This example expands the JSON data in the top_memory_clerks column into individual rows for each snapshot. Each row in the result set represents a top memory clerk in a specific memory health snapshot.

SELECT snapshot_time,
       severity_level,
       allocation_potential_memory_mb,
       reclaimable_cache_memory_mb,
       clerk_type,
       pages_allocated_kb
FROM sys.dm_os_memory_health_history
CROSS APPLY OPENJSON (top_memory_clerks)
    WITH (
        clerk_type SYSNAME '$.clerk_type',
        pages_allocated_kb BIGINT '$.pages_allocated_kb'
    )
ORDER BY snapshot_time DESC, pages_allocated_kb DESC;

Related content

  • Last updated on