Hi DBAs,

We have Windows Server 2008R2 installed on VM Server.
On that we have three SQL instances running. From few past months we are observing physical memory is going high. Earlier we observed it was at 86-88%, now it is 96-97%.

We have 16RAM & 8 CPU cores on VM.

Kindly suggest what is the best and ideal cconfiguration so that we can rectify the high physical memory issue. Below are the some screenshots:

Regards,

Yashwant Vishwakarma | www.yashwant-vishwakarma.strikingly.com

Hello,

That's the normal behaviour of SQL Server to allocate as much memory as it needs and as it gets; you can limit the memory usage of SQL Server, see Server Memory Server Configuration Options

Stop looking at task manager for SQl Server memory consumption it does not always show correct value. As per task manager its using 5.5G so what is issue with that ?

You must use below DMV to see sql server memory consumption

select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory

Have you set max server memory to optimum value. Its quite normal for SQL Server to use all memory available and only return it back when SQLOS asks it to do so.

Have you set max server memory to optimum value. Its quite normal for SQL Server to use all memory available and only return it back when SQLOS asks it to do so.

And with three instances on the server, Yashwant should set max server memory for all three, or else the instances may compete with each other with degrading performance as result. With three instances, and Reporting Services and 16 GB of memory, I would set max server memory to 4000 on all instances, assuming that they all are equally important.

Another issue is that 16 GB for a server with three instances is quite modest these days...

Hi Olaf,

Thanks for your reply. We are worrying because it is continuously increasing. 

It increased from 88% to 97% in approx 45-50 days. We are worrying what if , if it will consume 100% memory in coming future. What should we do to avoid that situation. We have already set max memory for one SQL Server instance to 8048 MB.

I ran below query to check memory clerks:

select single_pages_kb + multi_pages_kb + virtual_memory_committed_kb from 
sys.dm_os_memory_clerks where type = 'MEMORYCLERK_SQLCLR'

output of above query: