Sql-server – SQLOS Resource Monitor and QueryMemoryResourceNotification Win API

sql serversql-server-2008-r2sql-server-2012

Here's some info about SQLOS and the Resource Monitor I found from Jonathan Kehayias:

As a general rule, SQL Server will use as much memory as you can give
it, and it will not release the memory that it has allocated under
normal operations, unless the Windows Server OS sets the memory low
resource notification flag. A component of the SQLOS called the
Resource Monitor monitors the QueryMemoryResourceNotification Windows
Server API and when Windows sets the low memory resource notification,
the SQLOS will respond by sweeping its caches internally to reduce the
process working set, and release memory back to Windows.

So, if windows sets the low memory resource notification, when the Resource Monitor discovers it, is there a way for the DBA to know it too? An event notification? An alert? Something else? Ideally, I'd like to send an email when it happens.

Best Answer

This is called external memory pressure assuming that you set the max memory setting to a reasonable value. Generally it happens when processes other than SQL server (ssis, monitoring agents, iis etc.) consumes memory.

You can identify if a memory pressure is internal ör external from ring buffers which is explained in Jonathan's post

An easier way of determining memory pressure is to monitor memory grants pending and memory grants outstanding performance counters.

In both ways either you have to use a third party monitoring software for alerting or you can create your own alerting mechanism.

Note: Cited on both question and answer :). Cheers Jonathan.