Sql-server – How to decrease RAM usage of SQL Server 2012 without restarting Service

SQL Express is limited to 1GB for the Buffer Pool, but there are many other memory pools in SQL Server. What I find surprising is the excessive use of non-buffer memory pools. To find out memory usage per memory clerk, run this:

SELECT type, SUM(pages_kb)/1024 AS MemoryMB
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY 2 DESC

Hope this helps

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