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

  • 2
    Web edition is limited to 64 GB (ref) so you'll never get close to 96. Commented Feb 25, 2016 at 2:55
  • Your database is only 2GB in size, not quite sure how much memory you are expecting SQL to use.
    – Lawrage
    Commented May 10, 2016 at 3:16

1 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
  • The granted is usually higher than the ideal values.
    – rlshaw
    Commented Feb 26, 2016 at 16:18

Your Answer

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

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