0

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. – Aaron Bertrand Feb 25 '16 at 2:55
  • Your database is only 2GB in size, not quite sure how much memory you are expecting SQL to use. – Lawrage May 10 '16 at 3:16
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 Feb 26 '16 at 16:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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