mibuso.com
Home› SQL Performance
Options

SQL Server consumed very huge memory (90% plus)

julkifli33julkifli33 Member Posts: 1,073
in SQL Performance
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

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

    do we need to restart the sql server first?
  • Options
    JuhlJuhl Member Posts: 724
    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
  • Options
    julkifli33julkifli33 Member Posts: 1,073
    Apps and everything become very slow..
  • Options
    krikikriki Member, Moderator Posts: 9,096
    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
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

Sign In or Register to comment.