I have a bit of a conundrum. I recently upgraded a ~20gb database from Window Server 2008 R2 || SQL Server 2008 R2 to Window Server 2012 || SQL Server 2012. The previous server had 32gb of memory, upon booting the Max Server memory of 28gb would be consumed within one day. The new server has 48gb of memory however upon booting the database

  1. does not take the minimum memory of 4gb
  2. after a week of running has only consumed 7gb of data

The database is mainly ad-hoc workload that does not utilize stored procs. So the hope in adding memory that was more of query plans would stay cached.

Any idea what could be causing this ?



enter image description here


2 Answers 2


My first question where are you looking for SQL Server memory consumption. Please don't use task manager or process monitor, these do not show correct value when SQL Server service account has Locked pages in memory privilege. just use below query to see memory consumed by SQL Server

(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
from sys. dm_os_process_memory

There was notable change in SQL Server memory code in SQL Server 2012 but no where its related to what you are seeing. SQL Server memory allocation is dynamic in nature when SQL Server boots up it would grow its memory notification till the highMemoryResourceNotification resource notification is revoked.SQL Server will keep allocating memory based on its need as long as there is memory available I.e. as long as MEMPHYSICAL_HIGH (HighMemoryResourceNotification )notification is signaled in widows and will scale down its usage when there is MEMPHYSICAL_LOW (LowMemoryResourceNotification) signaled in windows. When available memory is between the low memory and high memory SQL Server will try to maintain the memory usage stable( RESOURCE_MEM_STEADY) with some exceptions.

With 48G memory you have set max server memory to 44G and only given 4 G to OS. IMO this is not correct you must give at least 6 G OS. You should give enough memory to OS so that it would have enough to allocate otherwise it would start signaling low memory notifications and SQL Server would have to trim its memory consumption.

Does SQL Server service account has Locked pages in memory privilege. If not you should give it, but before doing that please leave 6-7 G for OS( I am assuming here that this is dedicated system for SQL Server)

If you want SQL server to consume more memory create a dummy table and then start inserting large number of records in it at same time start running multiple Select * statements you would see SQL Server memory consumption rising. If you want to check SQL Server memory consumed by SQL Server use query posted above

  • Mind if I ask why you're advocating for 6G for OS over 4G? I've also been adhering to 4G for the OS on dedicated machines, and haven't really seen anything stating 4G is not enough.
    – Reaces
    Nov 12, 2014 at 10:33
  • Reason is on a production system 3-4 G should be given just for OS. Some 1-1.5 G for Antivirus and rest for remote desktop and other third party software running. If you have monitoring tool installed you must consider leaving sufficient memory for that as well. IMO 4 G is definitely less which might not always cause issue but not recommended.Memory for backup, extended stored procs and third party dll, linked servers are still allocated outside buffer pool.
    – Shanky
    Nov 12, 2014 at 18:00

Start by looking at the min server memory and max server memory configuration options detailed at http://msdn.microsoft.com/en-us/library/ms178067.aspx.

You can pull them up by running:

exec sp_configure 'min server memory'
exec sp_configure 'max server memory'

Ultimately, you have to decide if performance is being affected by these settings or not.


Your Answer

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

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