SQL Server 2012 doesn't use enough memory

Question

Under heavy Load, my SQL Server database is executing queries really slowly. I have 96 GB of RAM and my CPU is high (100%) but my physical memory usage is at 9%. I looked at the SQL Server memory usage which is in the screenshots below. I enabled Locked In Pages but it didn't seem to help. I'm wondering if there is anything I can do in SQL Server to utilize more memory.

• Version: SQL Server 2012 Web Edition SP3 (x64)
• OS: Windows Server 2012 R2 (x64)

max server memory is set to the default. It's a web garden for one site. My database is 2 GB. I thought SQL Server would use more memory under a heavy load with more RAM, scaling up for the number of users/queries.

enter image description here

enter image description here

• Aaron Bertrand: Web edition is limited to 64 GB (ref) so you'll never get close to 96.
• Lawrage: Your database is only 2GB in size, not quite sure how much memory you are expecting SQL to use.

Answer 1

Be sure that memory is the bottleneck

Here is a query that will tell you for every session, how much memory is being used versus the ideal amount of memory the optimiser would like to assign to the query. If the latter is higher than the former, then memory is a (but not necessarily the only) issue for you.

SELECT s.session_id
 , r.granted_query_memory * 8 AS memory_used_kb
 , a.ideal_memory_kb
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
LEFT JOIN sys.dm_exec_query_memory_grants a ON a.session_id = s.session_id

• rlshaw: The granted is usually higher than the ideal values.