March 15, 2011 at 11:11 am
Hello
Our prod server have 10Gb Memory,
Win NT 5.2 (Build 3790: Service Pack 2)
Sql server 2005 x64 Ent. Edition
Sql server service started under Local System Account, so Lock pages in the memory granted by default.
But Min / Max server memory setuped by default : 0 and 2147483647
Is it can cause any problems?
And If yes which values for Min/Max memory are recommended for our server?
Thanks,
Alex.
March 15, 2011 at 12:35 pm
Well it depends on the workload on the server.
The buffer pool will always remains in the range of Min and Max memory setting value and will not be paged out by the OS in case of memory pressure if you have specified "Lock in pages".
But the buffer pool wont page out memory until it reaches the Min Memory setting in case even when "Lock in pages" is not enabled.
How many instances do you have on the box ? In case of a single instance I would set the max memory to 8GB.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 15, 2011 at 12:56 pm
When i look at a server for performance this is usually one of the first things i look at to make sure SQL server AND the system has enough memory. I have seen where a system becomes unstable because of memory usage being too high. I try to leave 2 GB to the OS and then review each application on the box and leave the recommended memory amounts. For instance, if i had a box with 10GB i would leave 2 GB for the OS and we generally have dedicated SQL boxes, so i would only leave enough for SQL Litespeed, so either 512MB or 1GB.
March 16, 2011 at 4:13 am
We have single SQL instance on dedicated server. So recommended value is 8Gb.
Is there any possible issues can heppen if we keep it unchanged min =0 and max = 2147483647 ?
Thanks, Alex.
March 16, 2011 at 5:09 am
Technically no you shouldn't have any issues because SQL will give back the memory if the OS needs it. But, i have had some performance issues and when i logged into the box there was only a little less than 200MB of free memory and the page file was being used a lot. I decreased the MAX like mentioned above and that resolved the issue.
March 17, 2011 at 7:32 am
Assuming the sql server relational engine is the only thing running on the server I would try 7GB as max memory and monitor for significant paging and memory pressure indications. Search web for dbcc memorystatus.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply