SQL Server consumed very huge memory (90% plus)

julkifli33

Hi All,
one of my customer has issue about SQL Server.
the server is only be used for NAV 2017, Jetreport and SQL Server 2014
they only use for 1 database with 20 users

the moment we restart SQL, memory consumed will lower down until 25%
once user login and used... it will raise significantly become 93-96%

by the way, the installed memory for this server is 24 GB.

is there a way we can solve this issue?
Thanks.

robbonick

It is my understanding that SQL Server will just consume all memory it can, in order to cache as much data as possible.

julkifli33

hi Robbonick, I set as 15 GB max memory used.
but now still 18GB++

do we need to restart the sql server first?

Juhl

SQL takes what it can, but releases it when other system needs it.
Look inside sql, not outside.
Run queries to check for que and latency in sql.

julkifli33

Apps and everything become very slow..

kriki

SQL Server will grab (when needed) the memory you allowed it to grab. If you didn't change the default max mem settings, it will be all the memory of the server, leaving other programs (and Windows itself) starving for memory. SQL server will also not release memory if not forced to.
You need to use the min and max memory settings in SQL Server, so SQL server will stay between those 2 values once the cache is warmed up.
Best would also NOT to put other services on the SQL Server, but let it be dedicated to SQL Server.
But in your case I would do this:
24GB of physical memory
leave 2 GB for OS
leave 4 GB for NAV
leave 2 GB for Jet (not sure of this value because I don't know how much memory it actually uses)

That means you should set SQL Server Max memory to 24 - 2 - 4 - 2=16.
I would set min min memory to 8 or 12 to let Windows and SQL play together in case of memory use peaks.
It is better to let SQL Server give up some memory than to have memory cached to disk.

PS: give this one a look to get the basics right: https://mibuso.com/downloads/workshop-material-of-nav-techdays-2015