Szerkesztés

Megosztás a következőn keresztül:


sys.dm_os_out_of_memory_events

Applies to: Azure SQL Database Azure SQL Managed Instance

Returns a log of out of memory (OOM) events.

For more information on out of memory conditions in Azure SQL Database, see Troubleshoot out of memory errors in Azure SQL Database.

Column Name Data Type Description
event_time datetime2, not null OOM event time
oom_cause tinyint, not null A numeric value indicating OOM root cause. OOM cause is determined by a heuristic algorithm and is provided with a finite degree of confidence.
oom_cause_desc nvarchar(60), not null Description of oom_cause, one of:
0. UNKNOWN - OOM cause could not be determined
1. HEKATON_POOL_MEMORY_LOW - Insufficient memory in the resource pool used for In-Memory OLTP. For more information, see Monitor In-Memory OLTP.
2. MEMORY_LOW - Insufficient memory available to the database engine process
3. OS_MEMORY_PRESSURE - OOM due to external memory pressure from the operating system
4. OS_MEMORY_PRESSURE_SQL - OOM due to external memory pressure from other database engine instance(s)
5. NON_SOS_MEMORY_LEAK - OOM due to a leak in non-SOS memory, for example, loaded modules
6. SERVERLESS_MEMORY_RECLAMATION - OOM related to memory reclamation in a serverless database
7. MEMORY_LEAK - OOM due to a leak in SOS memory
8. SLOW_BUFFER_POOL_SHRINK - OOM due to the buffer pool not releasing memory fast enough under memory pressure
9. INTERNAL_POOL - Insufficient memory in the internal resource pool
10. SYSTEM_POOL - Insufficient memory in a system resource pool
11. QUERY_MEMORY_GRANTS - OOM due to large memory grants held by queries
12. REPLICAS_AND_AVAILABILITY - OOM due to workloads in SloSecSharedPool resource pool
available_physical_memory_mb int, not null Available physical memory, in megabytes
initial_job_object_memory_limit_mb int, null Job object memory limit on database engine startup, in megabytes. For more information on Job Objects, see Resource governance.
current_job_object_memory_limit_mb int, null Job object current memory limit, in megabytes
process_memory_usage_mb int, not null Total process memory usage in megabytes by the instance
non_sos_memory_usage_mb int, not null Non-SOS usage in megabytes, including SOS created threads, threads created by non-SOS components, loaded DLLs, etc.
committed_memory_target_mb int, not null SOS target memory in megabytes
committed_memory_mb int, not null SOS committed memory in megabytes
allocation_potential_memory_mb int, not null Memory available to the database engine instance for new allocations, in megabytes
oom_factor tinyint, not null A value that provides additional information related to the OOM event, for internal use only
oom_factor_desc nvarchar(60), not null Description of oom_factor. For internal use only. One of:
0 - UNDEFINED
1 - ALLOCATION_POTENTIAL
2 - BLOCK_ALLOCATOR
3 - ESCAPE_TIMEOUT
4 - FAIL_FAST
5 - MEMORY_POOL
6 - EMERGENCY_ALLOCATOR
7 - VIRTUAL_ALLOC
8 - SIMULATED
9 - BUF_ALLOCATOR
10 - QUERY_MEM_QUEUE
11 - FRAGMENT
12 - INIT_DESCRIPTOR
13 - MEMORY_POOL_PRESSURE
14 - DESCRIPTOR_ALLOCATOR
15 - DESCRIPTOR_ALLOCATOR_ESCAPE
oom_resource_pools nvarchar(max), null Resource pools that are out of memory, including memory usage statistics for each pool. This information is provided as a JSON value.
top_memory_clerks nvarchar(max), not null Top memory clerks by memory consumption, including memory usage statistics for each clerk. This information is provided as a JSON value.
top_resource_pools nvarchar(max), not null Top resource pools by memory consumption, including memory usage statistics for each resource pool. This information is provided as a JSON value.
possible_leaked_memory_clerks nvarchar(max), null Memory clerks that have leaked memory. Based on heuristics and provided with a finite degree of confidence. This information is provided as a JSON value.
possible_non_sos_leaked_memory_mb int, null Leaked non-SOS memory in megabytes, if any. Based on heuristics and provided with a finite degree of confidence.

Permissions

On Azure SQL Managed Instance, requires VIEW SERVER STATE permission.

On 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_ServerStateReader## server role is required. On all other SQL Database service objectives, either the VIEW DATABASE STATE permission on the database, or membership in the ##MS_ServerStateReader## server role is required.

Remarks

Each row in this view represents an out of memory (OOM) event that has occurred in the database engine. Not all OOM events might be captured. Older OOM events can disappear from the result set as more recent OOM events occur. Result set is not persisted across restarts of the database engine.

Currently, this DMV is visible but not supported in SQL Server 2022 (16.x).

summarized_oom_snapshot extended event

The summarized_oom_snapshot extended event is a part of the existing system_health event session to simplify detection. This event appears when out of memory (OOM) events are detected. This DMV aligns to activity recorded in the summarized_oom_snapshot extended event, both introduced to Azure SQL Database in January 2022. For more information, see Blog: A new way to troubleshoot out-of-memory errors in the database engine.

Example

The following example returns event data ordered by the most recent time for the currently connected database.

SELECT * FROM sys.dm_os_out_of_memory_events ORDER BY event_time DESC;