Why would SQL Server 2008R2 (SP1) x64 Enterprise only use 21g of 98g available????
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????
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)'
Min server memory is 25g
Max server memory is 80g
Both target and total server memory are 21g
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.