Why is SQL Server 2012 working set memory low?

Question

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.

• Aaron Bertrand: Is this right after your server has been restarted? Have you waited until you have had sufficient load, have fully populated the plan cache and buffer pool, etc.?
• Andy W: Thanks Aaron. This is now 3 days after migration and restart, with the websites being pretty busy and a lot of queries having been executed. Committed memory has grown from nothing up to 60gb, but working set has stayed at about 800mb. That said, I don't know how to check the plan cache or buffer pool - is there a quick way?
• Andy W: Thanks Paul. I looked at sys.dm_os_process_memory. They values are largely equivalent, except for locked_page_allocations_kb which is 59370648kb on the new server and 0kb on the old one. Does that suggest anything to you?
• Aaron Bertrand: @Andy only that the server doesn't have "lock pages in memory" set. Whether you should have it set, or why it was set on the old server, is unknown to us. It might be that the old server was set to run as an admin account, but the new server is not. Details here and here.
• Aaron Bertrand: As an aside, most people complain that SQL Server is using too much memory, even though that's exactly what it's designed to do. I find it both humorous and refreshing that someone is complaining that it isn't using enough, so thank you. :-)

Answer 1

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%';