Post reply

memory setting advise

  • August 7, 2019 at 5:56 am

    Go to Answer

    #3669361

    Hi All,

    Need some advise from experts.

    In our environment, we have monitoring tool called SolarWinds and performs health checks on SQL Server Instances.

    2 days back we have a got an Alert from solarwinds that "The Physical Memory on SRV1 is currently running at 95 %." and an incidents gets created to our team.

    Observations :

    I do see 95% memory usage on the server.

    Total physical memory is 32 GB

    max server memory was set to 28GB

    Also, we are seeing approximate of 2GB of Available Physical memory at all times.

    Users never complained about any performance issue.

    And more importantly, it's a vendor specific database(worksoft) running on the instance and it is dedicated for that.

    Coming back, to resolve the incident we wanted to reduce the max server memory to 24GB instead of 28GB as Solarwinds threshold is set for anything >=80% memory usage,

    create an INCIDENT.

    Is that we are doing is correct? or any other alternate thoughts?

    Thanks,

    Sam

  • August 7, 2019 at 7:55 am

    #3669384

    in your case I would do the following:

    1. Ensure that SQL server has enough memory now (check PLE's trend,  ring buffer for memory warnings). SQL Server as a process acquires more memory than specified by max server memory option. Both internal and external components can allocate memory outside of the buffer pool, which consumes additional memory and can take up to 10% (no proof links though).
    2. Using perfmon monitor amount of available RAM (OS level) for a week or a month.
    3. Do analysis of results and adjust either max memory for SQL   or   threshold in Solarwinds

     

    However , 28GBs of 32GBs  is already 87% of total RAM. And actually, you should get tickets evary day if SQL server consumes all RAM configured.

    So, I would check 2 counters and see what's the real amount of RAM SQL consumes during a typical work day.

    select * from sys.dm_os_performance_counters where counter_name like 'T%Se%Me%'

    Total server memory - current consumption

    Target server memory - estimates to consume

  • August 7, 2019 at 11:36 am

    Answer

    #3669426

    SQL Server will always use as much memory as it can. You may want to adjust or even disable that alert. You could be getting a lot of false positives from it otherwise.

    Make sure you cap SQL Server's memory so that the OS has enough and you're not generating contention between the OS & SQL Server.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply