November 26, 2010 at 1:42 pm
I have a machine that has a fair amount of ram. SQL Server max memory setting is not set, so it takes up all the memory it can. We dont really have any problems with the box, so im wondering, is there still merit in setting the max server memory? There are no other apps running on the box at the mo.
November 26, 2010 at 1:55 pm
Is this a 64 bit or a 32 bit Server? You do not see any memory starvation, do you? Usually you set the Max memory as 90% of the total memory. You leave the rest to the OS and other process.
Theoretically, the SQL Server is supposed to free up memory as it is needed by the OS. But I have seen cases where the OS and the server becomes a bit sluggish because of memory starvation.
-Roy
November 26, 2010 at 2:13 pm
I always ensure I am using Lock Pages in Memory. And if I was running a 32 bit version of SQL then I definiately would set max memory to ensure some memory is left for OS type functions (defrags, file copies, virus checks etc) see http://qa.sqlservercentral.com/blogs/glennberry/archive/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008.aspx
for more info
Francis
November 27, 2010 at 6:03 am
I would cap it too. Exact numbers are hard to come by because it's so dependent on the OS, what you're doing within SQL Server, etc., but I'd say, for most systems, Roy's 90% is not a bad starting point. But don't become completely dependent on that number.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply