Dynamic Memory Setting – Should I select lower maximum? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Dynamic Memory Setting – Should I select lower maximum?

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! :)
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.
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?)
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.
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?
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?
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.
When the alert is generated you can look at the SQL Server resource usage to see whether there is any performance issue or not.
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.
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.
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |