Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ranvir_2k
Posting Yak Master
180 Posts |
 Posted - 2013-09-23 : 12:10:43
|
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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-09-23 : 13:35:46
|
| 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. |
 |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2013-09-23 : 14:31:36
|
| 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. |
|
|
|
ranvir_2k
Posting Yak Master
180 Posts |
Posted - 2013-09-23 : 15:41:45
|
| 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? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-09-23 : 15:44:13
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
ranvir_2k
Posting Yak Master
180 Posts |
Posted - 2013-09-27 : 04:39:32
|
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.aspxThis is a bit confusingquote:
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.
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-09-27 : 13:34:53
|
| 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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
|
|
SQL Server maximum memory configuration