SQL Server consumed very huge memory (90% plus)

julkifli33julkifli33 Posts: 910Member
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.

Answers

  • robbonickrobbonick Posts: 39Member
    It is my understanding that SQL Server will just consume all memory it can, in order to cache as much data as possible.
  • julkifli33julkifli33 Posts: 910Member
    hi Robbonick, I set as 15 GB max memory used.
    but now still 18GB++

    do we need to restart the sql server first?
  • JuhlJuhl Posts: 675Member
    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.
    Follow me on my blog juhl.blog
  • julkifli33julkifli33 Posts: 910Member
    Apps and everything become very slow..
  • krikikriki Posts: 8,853Member, Moderator
    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
    Regards,Alain Krikilion
    Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
    NAV TechDays 2019: 21 & 22 November 2019, Antwerp (Belgium)
Sign In or Register to comment.