July 6, 2007 at 1:45 pm
Up to now we installed sql 2000\2005 on a dedicated box and let it use all the memory it wanted to. Using the Idera Diagnostic manager I occassionaly get messages such as "Operating system paging has exceeded the threshhold".
Should I trim sql's memory usage? If so, how etermine the magic number.
Barkingdog
July 6, 2007 at 7:42 pm
I don't have any metrics to give you. The short answer is if you don't suffer from performance problems on that machine, then you're alright. Specially if it's only a dev box. The server will always use all available memory given on the machine. Even if you have 3 GB of ram and only 2 gigs of DB, you can be certain that those 2 GB of databases will go straight to RAM as they are accessed for the first time.
Then there's all that software and cached plans, etc.
July 9, 2007 at 7:49 am
It is usually right to set an upper limit to the ram sql server can use. There is no magic number, however, but I use the following rough formula: for 2GB boxes I leave between 500 and 700MB, and it ramps up to a cap of 1.5-2GB for boxes with 8+GB.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 9, 2007 at 10:07 am
Are those machines running something else OTHER than SQL Server?
If you have 8 GB of RAM, then why limit it to only 2GB? What is the other 6GB of RAM going to be used for?
July 9, 2007 at 11:08 am
Sorry, I wasn't very clear in my post! I leave the noted amount of ram available for stuff OTHER than sql server. So for a 12GB box, I set sql server to 10-10.5GB limit for example.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 9, 2007 at 9:43 pm
Suppose we have a decidated x86 sql 2005 box with 4GB or RAM. What would you limit sql server to? (around 3GB?)
TIA,
Barkingdog
July 10, 2007 at 3:14 am
In that case, I would leave 1 GB for the OS. This is assuming there's nothing else running on that server. Make sure that the SQL Service account has permission to lock pages in memory (under local security policy) to prevent the OS from paging out SQL's data cache.
Generally the larger the server's memory, the more the OS needs. On my 48GB Itanium, SQL's restricted to 42 GB and that seems to work quite well
Note that you'll need ether the /3GB switch or AWE for SQL to take advantage of over 2 GB memory on a 32 bit server
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply