Sql-server – Why is SQL Server 2012 working set memory low

memorysql serversql-server-2012

I have recently migrated a 100gb database for a busy website from:

MS SQL 2008 Standard Edition R2
Windows Server 2008
48gb RAM

to

MS SQL 2012 Web Edition
Windows Server 2012
64gb RAM

I have noticed that the usage of memory seems different in 2012, with the working set memory relatively low compared to the 2008 server (see screenshots below).

Question

Does this seem right, and should I be concerned? I want to be sure that MSSQL has access to as much memory as it needs. The usage profiles of the two servers, in terms of requests from the website they power, is the same, so the pattern or volume of queries has not changed.

SQL Server 2008
enter image description here

SQL Server 2012
enter image description here

SQL Server 2008 memory settings
enter image description here

SQL Server 2012 memory settings
enter image description here

Thanks for any tips.

Best Answer

SQL Server 2012 has vastly changed the way it controls and manages memory. Before 2012, max server memory controlled only 8K pages, and now it controls much more than that.

Here are some good articles about this from the SQLOS team:

Article 1 | Article 2 | Article 3 | Article 4

With these changes, what you see in working set, max server memory, etc. will almost certainly be different between versions. The key is to focus on what "normal" is for these figures and see if they deviate vastly when there is a specific performance issue. I would not just look at a number when the system is working perfectly fine and panic and assume something is wrong (this happens a lot with magic numbers like 300 for page life expectancy - maybe PLE on your system is always 120).

A few queries that might help ballpark and set some baselines:

SELECT TOP (10) page_type, 
  numpages = COUNT(*), 
  size_kb = 8*COUNT(*) 
FROM sys.dm_os_buffer_descriptors
WHERE database_id < 32767
GROUP BY page_type
ORDER BY size_kb DESC;

SELECT * FROM sys.dm_os_process_memory;

SELECT TOP (10) [type], 
  size_kb = SUM(pages_kb) 
FROM sys.dm_os_memory_clerks 
GROUP BY [type]
ORDER BY size_kb DESC;

SELECT * FROM sys.dm_os_performance_counters 
WHERE object_name LIKE '%Memory Broker%'
   OR object_name LIKE '%Buffer Manager%'
   OR object_name LIKE '%Memory Manager%'
   OR object_name LIKE '%Memory Node%';
Related Question