0

I have an SQL Server Installation which suffers very bad performance due to lack of memory (queries runnig very slow, RESOURCE_SEMAPHORE...). The memory usage never goes above ~4.1 GB, although the machine has 16 GB of physical memory and only 44% is used.

Resource Monitor shows a working set of 4,289,000 KB for sqlservr.exe.

I'm wondering where this limitation is coming from, although we increased "maximum server memory" to 10 GB.

Both Os and DB Server are 64-bit and the editions used have much higher limitations than 4 GB.

I've used sysinternals TestLimit to see if there is a general memory limitation for processes, but a testprocess could use more memory, so it seems to be related to SQL Server itsself.

Any help is appreciated!

OS: Windows Server 2012 R2 Standard 64-bit

SQL Server: SQL Server Standard 64-bit, Version 11.0.5058.0

Server is virtualized on VMWare ESXi 5.1.0

Improve this question
5
  • 1
    Was sql restarted after the memory was set? Also, verify ESXi is not capping your memory to 4.
    – Jason B.
    Commented Aug 31, 2016 at 13:27
  • Restart of service was not done, as it is a productive system and this should not be requried for the setting change to take affect.
    – räph
    Commented Aug 31, 2016 at 13:43
  • 1
    "It should not be required" versus "sometimes what is needed" is not the same. Check your ESXi memory limits and make sure it's not set to some arbitrary number like 4gb.
    – Jason B.
    Commented Aug 31, 2016 at 13:46
  • What is the configuration for the minimum memory limit?
    – Antoine Hernandez
    Commented Aug 31, 2016 at 22:23
  • last try was min at 6 GB and max at 10 GB
    – räph
    Commented Sep 1, 2016 at 7:58

1 Answer 1

Reset to default
0

Short update: I could solve the problem myself. Problem was actually not related to the memory. We had high traffic with many very CPU expensive queries which created a constantly high CPU load, which were the reasons for RESOURCE_SEMAPHOREs.

After reducing the quantitiy of queries significantly and tuning the queries itself to be less expensive, the load decreased and there were no more RESOURCE_SEMAPHOREs.

Memory usage is still only 4 GB, but obviously system doesn't need to use more!

Improve this answer

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.