1

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.

1

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.

| improve this answer | |
  • Thanks for the link to Jonathon's article. It gives me the data I'm looking for. I could run a query manually, or "poll" it periodically with a SQL Agent job. It would be nice if I could have SQL "push" an alert to me for RESOURCE_MEMPHYSICAL_LOW, though... – Dave Mason Mar 27 '15 at 14:21
0

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.

This is normal and happens quite a few times on a busy SQL Server OLTP system. I am not sure how to put alert from your side on internal flags set by windows. But you have ResourceNotificationHighandLow.exe which can tell you about the notifications. You have to download it. Complete details about it are on This Link

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.