Why would SQL Server 2008R2 (SP1) x64 Enterprise only use 21g of 98g available????

Jeff 2016-11-10 19:57:51

Plan is to "upgrade" to SP3, but I am not aware that it will "fix" this oddity."
The difference 67g shows as "available"…. Max Memory is set at 80 gig. Target and Total Memory are both sitting at the 21g (has been this way for weeks.)

The memory readings are from SQL Sentry (not PerfMon) and the OS is Win Server 2008R2 Enterprise

Thoughts????

Vlady Oselsky 2016-11-14 20:21:55
People don't usually complain of SQL Server not using enough RAM. That being said, just because you have adjusted minimum and maximum memory to 80GB it will not force SQL Server to use all 80GB. SQL Server will eventually grow to Maximum allocated RAM limit when it is under heavy enough workload to justify it.

If the same value is specified for
both min server memory and max server
memory, then once the memory allocated
to the Database Engine reaches that
value, the Database Engine stops
dynamically freeing and acquiring
memory for the buffer pool. https://technet.microsoft.com/en-us/library/ms180797(v=sql.105).aspx

Lastly, please refer to the following article showing maximum available RAM to specific SQL Server edition, but since you specified that you are running enterprise it should not be restricted.

https://msdn.microsoft.com/en-us/library/ms143685(v=sql.105).aspx

UPDATE:

Run the following script to verify currently set and running values. Value_in_use for Maximum SQL Server Memory should be equal to Target SQL Server performance counter. On my local instance it takes just under 1 minute for values to be updated.

SELECT  name
      , value
      , value_in_use
      , minimum
      , maximum
      , [description]
      , is_dynamic
      , is_advanced
FROM    sys.configurations WITH ( NOLOCK )
WHERE   name IN ( 'max server memory (MB)', 'min server memory (MB)' )
ORDER BY name
OPTION  ( RECOMPILE );
 
 
SELECT  'Target Server Memory (MB)' AS CounterName
      , cntr_value / 1024 AS CurrentValueMB
FROM    sys.dm_os_performance_counters
WHERE   counter_name = 'Target Server Memory (KB)'
 
SELECT  'Total Server Memory (MB)' AS CounterName
      , cntr_value / 1024 AS CurrentValueMB
FROM    sys.dm_os_performance_counters
WHERE   counter_name = 'Total Server Memory (KB)'
Jeff 2016-11-14 20:30:03
Min Memory is set at 25g… The server has been running for months, I find it hard to believe that it would not be advantageous for it to be using more of the available RAM. Will SQL Server grow beyond the "Target Server Memory" performance counter value?? Is there a way to get it to "Re-Calculate" that value?? Are there queries that will tell me how it is getting to 21g Target number value?
Vlady Oselsky 2016-11-14 21:02:48
I apologize for not understanding your question. Yes, you are correct Target memory should be 80GB just like maximum memory. I updated my answer to include SQL Query you could run to verify what SQL Server thinks those values are just in case there is a bug in application.
Jeff 2016-11-14 21:51:31
Yes those are the values I previously posted…
Min server memory is 25g
Max server memory is 80g
Both target and total server memory are 21g
Jeff 2016-11-22 17:15:48
So after searching and asking I have not be able to locate the actual answer BUT…. I set the max to be 40 gig and we rebooted the server. When the server restarted SQL Server performed as expected and the "Target Server Memory" was showing as the 40g. Interestingly enough I then changed the Max to be 80g, the server's target memory did not change to the new max.

My belief is that there is a bug in 2008R2 SP1 that prevents the recalculation of the target server memory when the max server setting is changed…(In reading through the release notes I didn't notice anything that said that but it my be related to something else that was fixed.)

Working to get the system upgraded to SP3 and then I will reevaluate.