Curious Memory Pressure Alert - SQL Server Process Physical Memory Low

We are, on occasion, out of Sentry, receiving a SQL Server Process Physical Memory Low alert on one of our warehouse instances. The kicker is, I can find absolutely no indication of memory pressure at the time the alert fires.

We are running SQL Server 2019 Enterprise Edition. 256GB of RAM with around 228GB allocated to SQL Server.

Sentry shows 21GB of available RAM for the OS during this whole time. Nothing else is lighting up which supports this alert being real.

Last time this alert fired was 6:02:56 AM this morning. At 6:03AM, system is reporting 21GB available memory.

Thoughts on how I can figure out what is going on here. I could just call it transient and ignore it, but that's just going to bug the snot out of me. :)

Parents
  • We struggled with this alert not being actionable at times also.  I believe it is triggered by the bit value in sys.dm_os_sys_memory, which is triggered by a signal from Windows.  You could try digging into other logs from Windows in attempt to find the reason, but I found it better to use other alert conditions to identify actionable memory pressure - things like prolonged low PLE, RESOURCE_SEMAPHORE waits, etc.

  • I believe it is triggered by the bit value in sys.dm_os_sys_memory, which is triggered by a signal from Windows.

    Almost!  The value is derived from sys.dm_os_process_memory.process_physical_memory_low.  From SQL Docs, the description is "Indicates that the process is responding to low physical memory notification."  (Unfortunately not overly helpful)  I agree with your assessment/explanation that Windows (or the OS in general) is sending a signal to SQL Server.

    All of that said, I would be agree with using better data points to determine SQL Server memory pressure.  There are plenty of articles out there on it, and the new(er) memory clerks would be a great place to start.

Reply
  • I believe it is triggered by the bit value in sys.dm_os_sys_memory, which is triggered by a signal from Windows.

    Almost!  The value is derived from sys.dm_os_process_memory.process_physical_memory_low.  From SQL Docs, the description is "Indicates that the process is responding to low physical memory notification."  (Unfortunately not overly helpful)  I agree with your assessment/explanation that Windows (or the OS in general) is sending a signal to SQL Server.

    All of that said, I would be agree with using better data points to determine SQL Server memory pressure.  There are plenty of articles out there on it, and the new(er) memory clerks would be a great place to start.

Children
No Data