Spot Low-Memory Conditions with sys.dm_os_process_memory

calendar Oct 9, 2026 / · 10 min read · sql server sys.dm_os_process_memory sys.dm_os_sys_memory sys.dm_os_sys_info sys.dm_os_memory_clerks sys.dm_db_xtp_table_memory_stats memory pressure low memory buffer pool monitoring scripts  ·
Share on: linkedin copy

sys.dm_os_process_memory is SQL Server's process-level counterpart to the operating system's own memory counters — a single row reporting exactly how much of the machine's memory space the sqlservr.exe process is holding, down to large-page allocations, committed virtual address space, and two flags that fire the moment the operating system itself starts signaling memory pressure. Reading those two flags correctly, before reaching for a single aggregate number, is the difference between catching a real low-memory condition and missing one entirely.

Purpose and Overview

sys.dm_os_process_memory returns a single row describing the complete SQL Server process address space, available on SQL Server, Azure SQL Managed Instance, and Azure Synapse Analytics — where it's called under the name sys.dm_pdw_nodes_os_process_memory instead, and where it is not supported against serverless SQL pool. Every value in the view reports in kilobytes and comes straight from a call to the underlying operating system — values aren't manipulated by SQL Server's own memory-management routines, except where it accounts for locked-page or large-page allocations. The core columns split into two groups: physical reality (physical_memory_in_use_kb for the process working set, large_page_allocations_kb, locked_page_allocations_kb, page_fault_count) and virtual address space (total_virtual_address_space_kb, virtual_address_space_committed_kb, virtual_address_space_available_kb, available_commit_limit_kb). memory_utilization_percentage folds the two together — the share of committed memory that's actually sitting in the working set. Reading the view requires VIEW SERVER STATE on SQL Server and SQL Managed Instance through SQL Server 2019; SQL Server 2022 and later narrow that to the more specific VIEW SERVER PERFORMANCE STATE. On Azure SQL Database, the Basic, S0, and S1 tiers and databases in elastic pools require the server admin account, the Microsoft Entra admin account, or membership in the ##MS_ServerStateReader## server role; every other service tier accepts either VIEW DATABASE STATE on the database or that same server role.

The two columns worth watching before anything else are process_physical_memory_low and process_virtual_memory_low — bit flags, not percentages, that read 1 only when the operating system has actually told the SQL Server process it's under memory pressure. process_physical_memory_low reads 1 specifically when the process is responding to a low physical memory notification from the operating system — the same signal fired when physical memory as a whole is scarce. process_virtual_memory_low is a separate condition entirely: it tracks the user-mode virtual address space this same view reports via total_virtual_address_space_kb and virtual_address_space_available_kb, and it can read 1 even while physical RAM is plentiful, because it's measuring a different ceiling. Treating the two as separate alarms rather than folding them into one combined "memory is low" score is why this script decodes them individually instead of summarizing them. That distinction matters because SQL Server's default behavior works against intuition: left alone, an instance will consume most of the server's available memory over time and will not voluntarily give any of it back unless the operating system signals a shortage — which Microsoft's own guidance on monitoring memory usage describes plainly as normal design, not a memory leak.

The script below reads sys.dm_os_process_memory on its own first, then widens the picture twice — once against sys.dm_os_sys_memory for the operating-system side of the same question, and once against sys.dm_os_sys_info to compare SQL Server's own Total and Target Server Memory figures to the process's reported working set. A fourth pass breaks the non-buffer-pool portion down further with sys.dm_os_memory_clerks, for the cases where the process-level numbers alone don't say which internal consumer is actually responsible.

Code Breakdown

The first query decodes the process memory row directly, translating the two low-memory bit flags into plain language instead of leaving them as a bare 0 or 1 a dashboard query would otherwise skip past.

 1SELECT
 2    physical_memory_in_use_kb / 1024               AS physical_memory_in_use_mb,
 3    large_page_allocations_kb / 1024               AS large_page_allocations_mb,
 4    locked_page_allocations_kb / 1024              AS locked_page_allocations_mb,
 5    total_virtual_address_space_kb / 1024          AS total_vas_mb,
 6    virtual_address_space_committed_kb / 1024      AS vas_committed_mb,
 7    virtual_address_space_available_kb / 1024      AS vas_available_mb,
 8    available_commit_limit_kb / 1024               AS available_commit_limit_mb,
 9    page_fault_count,
10    memory_utilization_percentage,
11    CASE process_physical_memory_low
12        WHEN 1 THEN 'YES - responding to low physical memory notification'
13        ELSE 'No'
14    END                                             AS physical_memory_low,
15    CASE process_virtual_memory_low
16        WHEN 1 THEN 'YES - low virtual memory condition detected'
17        ELSE 'No'
18    END                                             AS virtual_memory_low
19FROM sys.dm_os_process_memory;

Decoding the two low-memory flags

Both CASE expressions exist so the two conditions never collapse into a single pass/fail read. A server can trip process_physical_memory_low while process_virtual_memory_low stays clean, or the reverse — they're independent operating-system signals about two different resources, and a query that only checks one is blind to the other.

The second query sets the process figure against the operating system's own free-memory counters through sys.dm_os_sys_memory.

1SELECT
2    pm.physical_memory_in_use_kb / 1024            AS sql_process_memory_mb,
3    sm.total_physical_memory_kb / 1024             AS total_os_memory_mb,
4    sm.available_physical_memory_kb / 1024         AS available_os_memory_mb,
5    CAST(pm.physical_memory_in_use_kb * 100.0
6        / sm.total_physical_memory_kb AS DECIMAL(5,2)) AS pct_of_total_os_memory
7FROM sys.dm_os_process_memory AS pm
8CROSS JOIN sys.dm_os_sys_memory AS sm;

Separating OS-wide shortage from SQL-Server-specific pressure

sys.dm_os_sys_memory's available_physical_memory_kb column is the direct T-SQL equivalent of the Windows Memory: Available Bytes performance counter — Microsoft's monitoring guidance names it as exactly that substitute. A low reading there points at an overall operating-system shortage, not something specific to the SQL Server process. Cross joining it against sys.dm_os_process_memory works cleanly because both views return exactly one row, so the join never multiplies anything: a high pct_of_total_os_memory with both low-memory flags still reading 0 describes an instance correctly using most of the box's memory by design, while the same percentage with either flag tripped describes an instance already past the point the operating system considers comfortable.

A third query lines the process figure up against SQL Server's own Total and Target Server Memory values from sys.dm_os_sys_info.

1SELECT
2    si.sqlserver_start_time,
3    si.committed_kb / 1024          AS total_server_memory_mb,
4    si.committed_target_kb / 1024   AS target_server_memory_mb,
5    pm.physical_memory_in_use_kb / 1024 AS process_working_set_mb,
6    pm.process_physical_memory_low,
7    pm.process_virtual_memory_low
8FROM sys.dm_os_sys_info AS si
9CROSS JOIN sys.dm_os_process_memory AS pm;

Why Total Server Memory and physical_memory_in_use_kb rarely match exactly

committed_kb on sys.dm_os_sys_info is the figure behind the SQL Server: Memory Manager: Total Server Memory (KB) performance counter — the amount of operating-system memory the SQL Server memory manager itself has committed. physical_memory_in_use_kb on sys.dm_os_process_memory is a different measurement: the whole sqlservr.exe process working set as the operating system reports it, which is why the two numbers are routinely close but not identical. Microsoft's memory-monitoring guidance maps them to two different counters for that reason: Total Server Memory to committed_kb, and the Process: Working Set counter for sqlservr.exe to physical_memory_in_use_kb. committed_target_kb is the companion figure worth watching alongside both: it's the memory manager's estimate of the ideal amount SQL Server could use given recent workload, and Total Server Memory sitting well below Target Server Memory after a period of stable, typical operation — rather than shortly after a restart, when the gap is simply the cache still filling — is worth investigating in its own right, well before either low-memory flag ever trips.

A fourth pass answers the question the first three can't: when the process-level numbers look wrong, which specific internal consumer is responsible?

1SELECT TOP (15)
2    type                                     AS memory_clerk_type,
3    SUM(pages_kb) / 1024                     AS pages_mb,
4    SUM(virtual_memory_committed_kb) / 1024  AS virtual_memory_committed_mb
5FROM sys.dm_os_memory_clerks
6GROUP BY type
7ORDER BY pages_mb DESC;

Finding the non-buffer-pool consumer with sys.dm_os_memory_clerks

sys.dm_os_process_memory reports the process as a whole and says nothing about which internal component is actually holding the memory — that detail lives in sys.dm_os_memory_clerks, one row per memory clerk. pages_kb is the clerk's page allocation; it replaced the separate single_pages_kb and multi_pages_kb columns, which exist only in SQL Server 2008 and 2008 R2, so older scripts that still select multi_pages_kb fail on any current version. Grouping by type and summing across every clerk of that type turns dozens of rows into a short, ranked list of the components holding the most memory. For a per-clerk walk-through, see top memory consumers with sys.dm_os_memory_clerks.

Key Benefits and Use Cases

  • A single query answers "is this SQL Server, or the whole box" — cross-joining sys.dm_os_process_memory against sys.dm_os_sys_memory separates a SQL-Server-specific issue from an operating-system-wide one in one result set.
  • The two low-memory flags are an earlier signal than most dashboards show — process_physical_memory_low and process_virtual_memory_low read 1 the moment the operating system notifies the process, ahead of most percentage-based alert thresholds.
  • Total vs Target Server Memory catches drift before it becomes a flag — a persistent gap between committed_kb and committed_target_kb on sys.dm_os_sys_info is visible well before either low-memory bit ever trips.
  • Memory clerks name the actual consumer — when the process-level total looks wrong, sys.dm_os_memory_clerks grouped by type is the next query, not a guess.
  • No configuration required — all four views return data against a stock instance, with no trace flag, Extended Events session, or restart needed first.
  • Works the same regardless of how max server memory is set — the same columns and the same two flags exist whether the buffer pool is tightly capped or left at the default.

Performance Considerations

  • Memory-only, resets on restart: like every sys.dm_os_* view, none of this is retained once the Database Engine restarts — a history table populated on a schedule is the only way to see a trend rather than a single point.
  • Permission floor changed in SQL Server 2022: VIEW SERVER STATE covers SQL Server 2019 and earlier; SQL Server 2022 and later require the narrower VIEW SERVER PERFORMANCE STATE instead.
  • max server memory doesn't bound the whole process: per Microsoft's server memory configuration options, it does not limit the memory SQL Server leaves for extended stored procedure DLLs, COM objects (sp_OA calls), linked server providers and other non-shared DLLs, nor thread stacks, so physical_memory_in_use_kb can legitimately read above the setting.
  • vas_available_mb can overstate truly usable space: per Microsoft's own note on the view, free regions smaller than the allocation granularity still count toward virtual_address_space_available_kb even though they can't actually be allocated against.
  • Serverless SQL pool in Azure Synapse Analytics doesn't support this view at all — plan around that gap specifically if a monitoring script needs to run across a mixed Synapse estate.

Practical Tips

  • Schedule all four queries as a SQL Server Agent job writing to a history table — a single point-in-time read of any sys.dm_os_* view can't show whether a low-memory flag is new or has been tripped for hours.
  • Alert directly on process_physical_memory_low = 1 or process_virtual_memory_low = 1 rather than on a percentage threshold alone — the flags fire on the operating system's own notification, a cleaner signal than a derived number. Glenn Berry's SQL Server diagnostic queries check the same two columns and expect 0 on both, noting that he very rarely sees either one come back as 1 — so a 1 is worth acting on.
  • Check sqlserver_start_time from sys.dm_os_sys_info before reading too much into a Total-Server-Memory-below-Target gap — that gap is expected right after a restart, and only worth investigating once the instance has been up under typical load for a while.
  • If the workload includes memory-optimized tables, don't stop at sys.dm_os_process_memory for that specific footprint — sys.dm_db_xtp_table_memory_stats reports memory-optimized table and index memory separately, and the process-level view doesn't break that portion out on its own.

Conclusion

sys.dm_os_process_memory, read alongside sys.dm_os_sys_memory, sys.dm_os_sys_info, and sys.dm_os_memory_clerks, turns "SQL Server memory looks high" into a specific answer: whether the pressure is OS-wide or SQL-specific, whether it's drifting ahead of a restart-driven ramp-up, and which internal consumer is responsible if it isn't the buffer pool. The two low-memory bit flags are the fastest-to-check piece of that picture and the one worth wiring into an alert first — they report the operating system's own verdict, not a derived threshold guessed at from a dashboard.

References

Posts in this series