Max Memory recommendation?
-
Has anyone heard of a recommendation to reduce Maximum memory for SQL Server 2000 ("official" Microsoft recommendation or otherwise)?
While taking a training course for Microsoft Great Plains, the course content in Performance Tuning said that "official" Microsoft recommendation was to reduce the maximum memory by 10-15% of installed memory. The referred setting was on the SQL Server Properties, Memory tab, under Dynamically Configure Memory. If you have 1gb (1024K) of installed memory, you would set Maximum memory somewhere from 921Kb to 972Kb. And this was recommended for servers running exclusively as database servers (no other applications or major services running on the server). The theory is to allow the Operating System to have enough room to perform its tasks, especially for virtual paging.
Since the course (from Microsoft) said it was an "official" Microsoft recommendation, I tried finding it somewhere (i.e. Books Online, MSDN, Technet, etc) but do not see anything like that. There was a similar suggestion in the course setting Minimum memory to 5-10% of install RAM, but it explicitly said that was not an official recommendation, but based on anecdotal evidence from other installations.
These sound good, but was wondering if this community had any heard of use any similar recommendations.
Mark
Mark -
quote:
Has anyone heard of a recommendation to reduce Maximum memory for SQL Server 2000 ("official" Microsoft recommendation or otherwise)?Not heard any official advice, but have noticed that our one exclusive SQL Server maxes out the SQL Memory to about 1.6GB out of 3.25GB. (Probably due to our NT specialists not having applied the /3GB option, like I asked...)
Why does memory have to be partitioned like this? It's annoying.
Thomas Rushton
blog: https://thelonedba.wordpress.com -
I have always heard and read several places (sorry don't remember) to leave 60 MB minimum for the OS, but the more you leave the better things will be because if the OS is squashed up and paged out you will see higher page faults and see the system slow down or even freeze. 10-15 sounds high and 5-10 could be low. I suggest 60 for the OS unless more than a GB of memory, then push up to 256 mb but up to you.
-
To see how much memory is needed for processes outside SQL Server, I disable the SQL Server services, and restart the server.
The memory used by the server without the SQL services is the minimum memory that have to be available for the OS; so the maximum memory available to SQL Server must at least be reduced by this amount.
-
I like to leave 256 MB for the OS on a machine dedicated to SQL Server. Especially if you OS is W2K or W2K3.
Otherwise you could come to swapping.
Of course if you server is not a dedicated one you have to add the memory need of your application as well
Bye
Gabor
Bye
Gabor -
Hi there
I have a 4Gb, i leave around 500Mb free. As a general rule, i try and leave 20% free, but again it depends on what else is running on the server. The killer i find is sqlserver itself. The old insufficient memory error is crap, locking up the server, preventing sql connections etc (ie. cant kill jobs!), very annoying and sort of defeats the whole 24x7 approach to the DBMS.
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA" -
If you set SQL Server memory to be dynamically allocated, shouldn't it automatically leave memory for the O/S, etc.? Also, I've noticed that the memory tab in SQL Server Properties (Configure)gives a yellow/green/red visual guide for the "use a fixed memory size" option. I haven't calculated what percentages it corresponds to, but maybe this is evidence of the "official" recommendation.
-
Some mention of this is at http://www.microsoft.com/technet/treeview/default.asp?url=/technet/itcommunity/chats/trans/SQL/sql0513.asp. There they recommend a fixed size. Also interesting is the MemToLeave question. BOL describes a –g startup option to control the MemToLeave size.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
-
SQL will leave memory for the OS, but the deallocation and reallocation can be (relatively) slow sometimes. If you have concerns or issues, you should set it.
Hard to determine exactly what the OS needs. Keep in mind for AWE that there is a "Window" for swapping memory around so you don't want to squash this. This becomes especially important after you go above 4GB. After 8GB you need about 1GB just for the PAE memory swapping.
Steve Jones
-
The official recommendation sounds more generic and may be well suited for the servers running with high memory (>3 GB). As the use /3 GB and /PAE switches with fixed config needs minimum amounts of memory to manage it. I generally leave about 500 MB for O/S with systems running 4 GB.
-
quote:
If you set SQL Server memory to be dynamically allocated, shouldn't it automatically leave memory for the O/S, etc.?It doesn't exactly "leave room," but instead "makes room" when the OS needs nonpaged memory. On a server dedicated to SQL Server, you should usually not cap the maximum memory, as that can hurt SQL Server's performance by limiting its memory. Microsoft was clever in this design, and allows other processes to take memory from SQL Server to prevent excessive paging, but SQL Server can then regain that memory when the other processes no longer need it.
Exceptions are when using full-text indexing (although, IMHO, that really means the server is no longer "dedicated" to SQL Server) and when using AWE.
In other words, the Microsoft Great Plains recommendation is incorrect for the system as described. This is the same group that recently insisted that SQL data must reside on a RAID 5 array (on an OLTP system
) and currently recommends that users purchase per-processor SQL Server licenses for LAN use with an accounting package.--Jonathan
--Jonathan
) and currently recommends that users purchase per-processor SQL Server licenses for LAN use with an accounting package.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply