SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 SQL Server maximum memory configuration
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ranvir_2k
Posting Yak Master

United Kingdom
173 Posts

Posted - 09/23/2013 :  12:10:43  Show Profile  Reply with Quote
I have SQL Server running on a dedicated server with no other major applications running.

I was wondering what maximum memory settings I should use.

If I leave it at the default and SQL Server is doing heavy processing will it release memory to the operating system.

i.e. say if I have 10GB RAM on my box and SQL Server is using all 10GB, and then the operating system requires 1GB to run a process.

Will SQL Server release memory to the operating system?

I refer to this article:

http://technet.microsoft.com/en-us/library/ms178067%28v=sql.90%29.aspx

quote:
When SQL Server is using memory dynamically, it queries the system periodically to determine the amount of free physical memory. Under Microsoft Windows 2000, SQL Server grows or shrinks the buffer cache to keep free physical memory between 4 MB and 10 MB depending on server activity. Maintaining this free memory prevents Windows 2000 from paging. If there is less memory free, SQL Server releases memory to Windows 2000. If there is more memory free, SQL Server allocates memory to the buffer pool. SQL Server adds memory to the buffer pool only when its workload requires more memory; a server at rest does not increase the size of its buffer pool.

Under Windows Server 2003, SQL Server uses the memory notification API QueryMemoryResourceNotification to determine when the buffer pool may allocate memory and release memory.

Allowing SQL Server to use memory dynamically is recommended; however, you can set the memory options manually and restrict the amount of memory that SQL Server can access. Before you set the amount of memory for SQL Server, determine the appropriate memory setting by subtracting, from the total physical memory, the memory required for Windows 2000 or Windows Server 2003 and any other instances of SQL Server (and other system uses, if the computer is not wholly dedicated to SQL Server). This difference is the maximum amount of memory you can assign to SQL Server.

James K
Flowing Fount of Yak Knowledge

3704 Posts

Posted - 09/23/2013 :  13:35:46  Show Profile  Reply with Quote
Notwithstanding what that MSDN page says, I would recommend setting a maximum memory limit on SQL Server, especially if you are on Windows 2003 Server. The maximum memory setting is the maximum memory used by SQL Server for buffer pool only. Everything else associated with SQL Server, be it CLR allocations, or memory for your backup jobs is not included in that (for SQL 2005). So, in addition to the 1GB that you plan to allocate for Windows OS, you should leave some more for all of that. If I have a dedicated server with 10GB memory, I would set max memory 8 GB.
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
798 Posts

Posted - 09/23/2013 :  14:31:36  Show Profile  Reply with Quote
In addition to what James has stated - if the server is x64 and you have lock pages in memory right then SQL Server will starve the OS and cause problems. If you are running SQL Server under local system (not recommended) - then lock pages in memory right is granted.
Go to Top of Page

ranvir_2k
Posting Yak Master

United Kingdom
173 Posts

Posted - 09/23/2013 :  15:41:45  Show Profile  Reply with Quote
Thanks for the answers.

That article from microsoft seems to suggest that even if SQL Server is using 100% of the server memory, it will release memory to the operating system if it requires it.

Is that true?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 09/23/2013 :  15:44:13  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by ranvir_2k


That article from microsoft seems to suggest that even if SQL Server is using 100% of the server memory, it will release memory to the operating system if it requires it.

Is that true?



It can, yes. Will it? Often no.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

ranvir_2k
Posting Yak Master

United Kingdom
173 Posts

Posted - 09/27/2013 :  04:39:32  Show Profile  Reply with Quote
This is another article from microsoft stating that max memory does not need to be used if there are no other significant applications running on the same server as SQL.

http://technet.microsoft.com/en-us/library/ms177455%28v=sql.105%29.aspx

This is a bit confusing

quote:
However, applications often use whatever memory is available when they start and do not request more if needed. If an application that behaves in this manner runs on the same computer at the same time as SQL Server, set the max server memory server configuration option to a value that guarantees that the memory required by the application is not allocated by SQL Server.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 09/27/2013 :  13:34:53  Show Profile  Visit tkizer's Homepage  Reply with Quote
Yeah I can ignore that advice. Do you have tape backup software running? Anti-virus software? Other routine software that is needed to keep the system healthy? All of those things need memory. I never keep the default memory config for SQL Server. I always lower the value a bit to allow the OS and other processes to get memory if needed. We had a situation yesterday on a database server where two SQL instances were still at the default (I did not set these up). SUPER DUPER SLOW server when I rdp'd to it. The SCOM monitoring software was in a hung state due to the low memory. As soon as I dropped the memory down on both SQL instances, the server finally was responding better. Both instances are SQL Server 2012.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000