July 25, 2011 at 7:45 am
Environment:
Windows Server 2003 SP2
4GB RAM
SQL Server 2005 Workgroup Edition SP2
Issue:
I have SQL Server 2005 configured with Minimum server memory set to 512 MB and Maximum server memory set to 3072 MB. When I look at the Processes tab in Task Manager, sqlservr.exe has a memory usage of 1727 MB. This is as high as I ever see it get. The Performance tab shows 1460 MB available. I am skeptical that SQL Server doesn't have a need for the additional memory that is available to it. Could I have something configured incorrectly?
July 25, 2011 at 8:11 am
Hi,
From BOL:
Memory usage is increased as needed by SQL Server until reaching the value specified in max server memory. SQL Server cannot exceed this memory usage unless the value of max server memory is raised.
Vishal Gajjar
http://SqlAndMe.com
July 25, 2011 at 8:19 am
Are there some benchmarks which will confirm memory usage is below the max setting (like physical disk access or something)?
July 25, 2011 at 9:37 am
Bob,
SQL uses what it feels it needs to. I used to have a process that ran all by itself on a SQL Server and that even though there was 1.5GB of RAM available to SQL that process didn't need more than about 512MB of RAM, and this was a big process that could peg all the processors for 8 solid hours. It eventually got re-written but even then during particular segments it would only use part of the available RAM, because thats all it needed.
With all that said, are you getting bad performance? Or is the primary concern that SQL isn't using what you think it should?
CEWII
July 25, 2011 at 10:43 am
I am doing a top to bottom performance eval of our server. I am questioning anything I think might be a bottleneck. I have always heard that SQL Server will grab as much memory as you allow it to have. Just want to make sure the performance potential related to memory isn't being limited due to an incorrect configuration.
July 25, 2011 at 11:07 am
It will use as much as you allow it IF it needs to. Out of the box the max memory setting is "everything", however, even then if the workload doesn't require that much it won't take it. In a production environment I would never leave it at the default setting.
The only thing that comes to mind is whether the SQL and OS are 32 or 64-bit. But even then without some tweaks the limit is 2GB.
It sounds like you have a lightly loaded server. And that the max memory setting should read 3072 for 3GB. Eventually it will use it if you have a large enough load.
CEWII
July 26, 2011 at 6:14 am
Hello,
in my production server the maximum memory in MB reads to 2147483647.
is it set by one of the technical team or system decided, because the values doesnt contribut to any rounded of GB
Note the server memory is 8GB (2 server of same configuration cluster), i got to know it is on SAN drive.
have i understood something worngly? how it execeded more that the memory size.?
Regards
Durai Nagarajan
July 26, 2011 at 8:23 am
First, for future reference please start a new thread for your own questions instead of hijacking an existing one.
The number you reference is the default value which effectively means "use whatever you want, up to the absolute max, even if it squeezes the OS". It was set at the moment of install. In a production machine (or any other too) I never leave that as the default.
For an 8GB machine running 1 instance of SQL and no other apps, 7168-7424MB should be acceptable numbers, they equate to 7-7.25GB. The fact it is a 2 machine cluster does not change this, if you are running more than one instance of SQL or other apps then you need to make sure they have memory to run and leave some for the OS.
CEWII
July 27, 2011 at 2:16 am
This may be a bug between Windows 2003 and SQL Server 2005. In the settings of the "Local Security Policy" should be under "User Rights Assignment" the item "Lock pages in memory" of the user of the SQL Server service must be registered. A server restart is required.
July 27, 2011 at 5:50 am
There is a very precise reason why your max used memory only gets to 1700MB or so. a) it is a 32 bit system, which under default configurations can only give 2GB to any user process (sqlserver.exe in this case). So your buffer pool will never be bigger than that. However, 384MB of that 2GB is allocated as memtoleave, and if you take 384MB from 2GB you get the amount you see in task manager.
You can use the /3GB switch in boot.ini to allow SQL Server to take 3GB for it's user process memory. Best you can do, but it is a 50% increase and should make a difference on your system (unless you have a way underpowered system in which case performance may still suck).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 28, 2011 at 5:37 am
Thanks for the tip on the /3GB switch. SQL Server memory usage jumped from 1.7 GB to 2.7 GB. I used the /userva switch set to 2950 as well based on the recommendation from this posting: http://www.sql-server-performance.com/forum/threads/3gb-memory-windows-server-2003-standard-edition.14372/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply