Sql-server – SQL Server 2012 Express high Commit (KB) (low virtual memory condition)

memorysql serversql-server-2012

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.

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":

Process Explorer confirmed:

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:

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.

Best Answer

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.

Windows is facing low memory condition
Page file is not configured properly
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.

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

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

Related Solutions

Sql-server – Total Memory used by SQL Server (64 bit)

I'm not sure about a single view for memory, but you can get the information from two queries. The first (taken from Glenn Berry's DMV queries) leverages sys.dm_os_buffer_descriptors and will show you buffer pool by database:

SELECT 
    DB_NAME(database_id) AS [Database Name]
    ,CAST(COUNT(*) * 8/1024.0 AS DECIMAL (10,2))  AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
WHERE database_id not in (1,3,4) -- system databases
AND database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);

The second query I put together by looking at sys.dm_os_memory_cache_entries to show me non-buffer pool information:

select 
    name
    ,sum(pages_allocated_count)/128.0 [Cache Size (MB)]
from sys.dm_os_memory_cache_entries
where pages_allocated_count > 0
group by name
order by sum(pages_allocated_count) desc

There are a host of DMVs related to memory, notated by dm_os_memory_*. You can use these to investigate how memory is used by SQL Server on a variety of levels.

Sql-server – AWE memory usage growing with SQL Server 2012

SQL Server is using the AWE memory allocation API to allocate buffer pool memory if "lock pages in memory" is enabled.

If SQL Server's max server memory setting is not set, SQL Server will eventually use as much memory as it needs. That will largely show up as AWE memory in RAMMap. A little more detail can be found here and all the SQL Server memory settings are explained here.

You should set the max server memory setting to allow an adequate amount for the operating system. If not enough memory is available for the OS, paging can occur and cause performance issues. A good starting point is 3 to 4 GB reserved for the OS, but watch the appropriate counters to see if the OS is under memory pressure.

You can get more information about how to select the right max server memory setting at the following links:

A Sysadmin’s Guide to Microsoft SQL Server Memory by Brent Ozar
Importance of setting Max Server Memory in SQL Server and How to Set it by Sakthivel Chidambaram

Full details of how SQL Server uses AWE and Locked Pages can be found in the Microsoft CSS SQL Server Engineers article:

Fun with Locked Pages, AWE, Task Manager, and the Working Set

Best Answer

Related Solutions

Sql-server – Total Memory used by SQL Server (64 bit)

Sql-server – AWE memory usage growing with SQL Server 2012

Related Question