Why won't SQL Server use Free memory

Question

Why won't SQL Server use Free memory

gregor.aj

SSC Veteran

Points: 215
More actions
October 26, 2018 at 5:15 pm

#381855

Hey all,
I have a 2 node Availability Group running. Both servers are Enterprise 2017 64 bit with 1.5 TB of memory. Min Memory is set to 650GB, Max is set to 1.15 TB.
My problem is that when I run a bunch of threads against the server most are going into Semaphore wait status.
I check resource monitor and see a whole bunch of unused memory.

I double check my memory settings, they are correct as stated above.
I query bother servers:
select *
from sys.dm_exec_query_resource_semaphores

Primary node shows my Target and Total server memory set between 90 GB and 106 GB for the default and internal pools
The secondary node results show 916GB and 1.07 TB set for target and total memory
What am I doing wrong on the primary node?
Why won't it use the memory assigned to it that it clearly needs (hence semaphore waits) and has?
Update: I disabled the resource Governor on primary node and ran a bunch of threads against it. No change in results, still semaphore waits, still same unused memory.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Saurabh.D Ten Centuries Points: 1164 More actions · Answer 1

Even I am facing the same issue where SQL Server consuming a few memory.
Found below but need concert reasons as I am dealing with Production systems.
https://www.sqlpassion.at/archive/2011/11/04/why-sql-server-is-using-so-less-memory/

ThanksSaurabh.D

Jo Pattyn SSC-Dedicated Points: 32348 More actions · Answer 2

Does is run in a virtualized environment? I had strange cases of VM ballooning denying memory for sql server

gregor.aj SSC Veteran Points: 215 More actions · Answer 3

Saurabh.D - Monday, October 29, 2018 2:45 AM
Even I am facing the same issue where SQL Server consuming a few memory.
Found below but need concert reasons as I am dealing with Production systems.
https://www.sqlpassion.at/archive/2011/11/04/why-sql-server-is-using-so-less-memory/

Interesting, I have heard of this issue with task manager. We are not using it to evaluate memory consumption.

gregor.aj SSC Veteran Points: 215 More actions · Answer 4

Jo Pattyn - Monday, October 29, 2018 7:06 AM
Does is run in a virtualized environment? I had strange cases of VM ballooning denying memory for sql server

No. These are both non-virtual servers.

gregor.aj SSC Veteran Points: 215 More actions · Answer 5

Update: I restarted the servers (resource governor still disable on primary node) and memory target and totals now come up accurately and I am not getting semaphore waits. So basically, I turned it off an on again 🙂
I still need to re-enable the resource governors. If memory remains as it should that would be great ( except for for the lack of understanding).