SQL Server Performance

Dynamic Memory Setting - Should I select lower maximum?

Discussion in 'General DBA Questions' started by Trev256b, Aug 10, 2011.

  1. Trev256b Member

    On a SQL2000 machine I have noticed that the memory settings are dynamically configured: min: 0MB, max: 2048MB. There is only 2GB on the machine and task manager and perfmon show only 80MB free.
    1) How can I determine how much meory SQL Server is actually using - I tried to look in perfmon, but couldn't find the answer.
    2) Also, Should I set a lower maximum memory setting so SQL Server doesn't deprive the OS of memory? As I understand SQL Server could take all the memory.
    3) Overall, I want to determine if SQL Server needs 2GB or RAM or more... It looks like it is using 2GB but there aren't that many connections and I wouldn't expect perfmon to show 2GB being used...
    Thanks! :)
  2. satya Moderator

    If you have left the memory settings to DYNAMIC you cannot control and better to leave to the system.
    1) Do not go by Task Manager that available memory is so and so, better to perform a baseline and benchmark of the resource usage using PERFMON *SYSMON. You should continue the collection of memory counters for a period of time to analyze.
    2)If you set min and max memory then it cannot be classified as DYNAMIC memory, as you are controlling the memory setting. However I would like to ask whether you are getting any performance loss due this memory flakiness.
    3)Its the default memory architecture of SQL Server as it will grab all the available memory 97% then it will only release to OS when it is required.
  3. Trev256b Member

    Hi Satya - so it looks like if there is 2GB on the server and the memory settings are dynamically configured: min: 0MB, max: 2048MB; then SQL server will take all the memory (97%) and only release to OS when required.

    I am getting alert that there is low physical memory available. So remove this alert I was wondering if it is wise to set a lower MAX memory setting and therefore provide the OS a dedicated amount of memory.

    Would you recommend setting a lower MAX memory setting lower that 2GB? If so what would you set this to? And would this setting be dependant on the type of OS system? (If so do you have a handy web link for OS memory setting requirements?)
  4. satya Moderator

    WEll if there aren't any other applications running on SQL Server machine then you can leave the memory settings to DYNAMIC, otherwise you can test and see by setting up the MAX memory around 1.8GB range. Make sure you test the implications of these settings.
  5. Trev256b Member

    Hi Satya
    1) Do you think it is pointless to set an alert on a server when physical memory reaches >95% if SQL Server grabs 100% straight away?
    2) What other useful alert could I setup to monitor high memory usage?
  6. satya Moderator

    If you setup the MAX MEMORY to 1.8gb then it will not grab 100% of physical memory, in this case you must setup an alert for 85% when it reaches.

    Do you have any third party tool usage in yoru company?
  7. Trev256b Member

    hi satya - if i setup max memory at 1.8 GB (with 2GB on server) and an alert on 85%, won't sql grab 90% and therefore the alert will always fire continuously?

    i want to setup a 'useful' alert.
  8. satya Moderator

    When the alert is generated you can look at the SQL Server resource usage to see whether there is any performance issue or not.
  9. Trev256b Member

    thanks satya - it doesn't look like there is a useful alert for this - but i will see what software is around that can analyse sql processes and memory/cpu usage more specifically.
  10. satya Moderator

    Then you must go with third party tools to get such alert mechanism, in general the PERFMON and SQLAgent alert system may not be good enough for you to depend.

Share This Page