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

  • 1
    Was sql restarted after the memory was set? Also, verify ESXi is not capping your memory to 4. – Jason B. Aug 31 '16 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 Aug 31 '16 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. Aug 31 '16 at 13:46
  • What is the configuration for the minimum memory limit? – Antoine Hernandez Aug 31 '16 at 22:23
  • last try was min at 6 GB and max at 10 GB – räph Sep 1 '16 at 7:58
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!

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.