3

I have a customer running SQL Server 2012 Express Edition:

Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Feb 10 2012 19:39:15 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

The server is 64-bit, running Windows 7, and they have 8GB of RAM. The page file is configured to be managed by the OS.

enter image description here

The other day, they began noticing "low virtual memory condition" entries in the Windows Event Viewer such as the following:

Windows successfully diagnosed a low virtual memory condition. The following programs consumed the most virtual memory: sqlservr.exe (2792) consumed 28103749632 bytes, svchost.exe (592) consumed 617349120 bytes, and w3wp.exe (7700) consumed 609824768 bytes.

I logged in and took a look at the Windows Resource Monitor and noticed that the Commit (KB) counter for the sqlservr.exe process was "high" while the working set was relatively "low":

enter image description here

Process Explorer confirmed:

enter image description here

Based on the documentation for Commit (KB):

This is the total amount of physical and virtual memory (page file) that is committed to this specific process.

I also monitored Perfmon's Available MBytes counter and it consistently reads an average around 4.624 GB.

select * from sys.dm_os_process_memory returned this following:

enter image description here

Why would sqlservr.exe be using so much "virtual" memory when there's still plenty of "physical" memory available? Also, I thought SQL Server 2012 Express was limited to 1GB of memory - is that just a physical memory limit?

On a side note, a number of web links mentioned that McAfee Virus Scan Enterprise/McAfee Host Intrusion Prevention could cause this type of behavior. Both were installed by on this server by their IT group. I attempted to add the appropriate exclusions but never saw the Commit (KB) drop. How could I prove it's a McAfee issue?

UPDATE: I found what seems to be a similar issue with McAfee Host Intrusion Prevention and SQL Server on the same box.

Improve this question
4
  • 3
    The 1GB memory limit just applies to the buffer pool (procedure cache and data cache). SQL Server itself can use memory outside of this scope. Regarding the McAfee issue, SQL Server won't just give memory back unless the operating system actually requests it. With the exclusions now in place, restart SQL Server, and monitor it going forward.
    – Aaron Bertrand
    Commented Dec 28, 2015 at 21:12
  • Thanks @AaronBertrand. I've added the exclusions for McAfee Virus Scan Enterprise as per (kc.mcafee.com/corporate/index?page=content&id=KB67211) however, this did not reduce the virtual memory consumption. I'm still waiting on an admin password before I can change the exclusion for the Host Intrusion Prevention application.
    – John Russell
    Commented Dec 29, 2015 at 17:04
  • And have you restarted SQL Server, like I suggested? Again, SQL Server won't just give a bunch of memory back simply because you made some change in your anti-virus software. Also, apply Service Pack 3 - there is no reason heading into 2016 you should still be on the RTM build of SQL Server 2012.
    – Aaron Bertrand
    Commented Dec 29, 2015 at 17:24
  • Thanks @AaronBertrand. I did restart the SQL Server service. The virtual memory shot back up almost instantly after restart.
    – John Russell
    Commented Dec 30, 2015 at 13:48

1 Answer 1

Reset to default
3

Windows successfully diagnosed a low virtual memory condition. The following programs consumed the most virtual memory: sqlservr.exe (2792) consumed 28103749632 bytes

This is general message saying their is low virtual memory condition.

Why would sqlservr.exe be using so much virtual memory?

Their can be quite a few reasons for such messages.

  1. Windows is facing low memory condition
  2. Page file is not configured properly
  3. Some application installed is taking too much memory or leaking memory

As per the error message the SQL Server was the one which required huge amount of memory and was paged out to disk but that is not the only component. You need to find out why SQl Server required huge amount of memory. Knowing the limitation of express edition you should be aware to not run memory intensive queries.

In the error message I can see w3wp.exe which means application is also running on same machine in such case its quite possible windows would face low memory condition. What is total RAM present on the system ?. Since you have multiple appplications running make sure you have enough RAM on system

I thought SQL Server 2012 Express was limited to 1GB of memory - is that just a physical memory limit?

Aaron already pointed out and I am going to emphasize on that, starting from SQL Server 2012 the SQL Server express database engine can consume more than 1G of memory. The BOL information is not updated one it still says memory is limited to 1 G which is incorrect. As per this support article

Starting with SQL Server 2012, these memory limits are enforced only for the database cache (buffer pool). The rest of the caches in the SQL Server memory manager can use much more memory than is specified by these edition limits. For example, a SQL Server 2012 Express edition can use only a maximum size of 1.4 GB for its database cache. Other caches (such as the procedure cache, the metadata cache, and so on) can consume memory up to the size specified by the "max server memory" configuration.

EDIT: From the output of DMV sys.dm_os_process_memory.

  1. Physical_memmory_in_use_KB=445300 KB which is 445 MB

This is physical memory used by SQL Server instance. This is memory backed by physical RAM

  1. Virtual_address_space_committed_kb= 29116348 KB which is 27 G This is total SQL Server memory utilized (RAM+Pagefile) by SQL Server. So you can see now amount of SQL Server memory curently paged out to disk is 27G -0.4 G. You need to find out why and what has caused SQl Server to request so much memory. I strongly suggest you to appply SP3 ASAP.

I am not ruling out possibility of McAfee not causing the issue but with statistics posted it seems SQl Server is one which is requesting more RAM

Improve this answer
2
  • Thanks for the response. I've added more information to hopefully get to the bottom of this. I don't think there is an issue with the Page File configuration because it's managed by the system. There's definitely plenty of physical RAM available based on the PerfMon data. Why would virtual memory be used so heavily by SQL Server? Let me know if there's any additional information I can provide.
    – John Russell
    Commented Dec 29, 2015 at 16:42
  • SQL Server is committing virtual memory of 28G. (28103749632 bytes). Seems Issue is with SQL Server. Can you add output of select * from sys.dm_os_process_memory
    – Shanky
    Commented Dec 30, 2015 at 5:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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