SQL Server not using all of its memory.
-
Our SQL Server has 9.75 gigs of ram but it is only accessing about 5.15 gigs. I have included all the settings that I believe may effect this below. Also if you have any suggestions on my configuration those will be helpfull as well. Thanks.
my startup parameters:
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /NoExecute=OptOut /3GB /PAE
my awe settings:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'min server memory', 8985
RECONFIGURE
GO
sp_configure 'max server memory', 8985
RECONFIGURE
GO
Other Sql Settings
"reserve physical memory for sql" OFF
"Minimum query" 3024
"cpu" 0-4 enabled
"maximum threads" 255
"boost priority" OFF
"use fibers" OFF
Virtual Memory Settings
Drive c
scsi noraid):1024-1224drive e
scsi RAID 5):3200-4000 -
What edition of SQL Server are you using? How much data is being held on the server? Why do you need to fix the minimum and maximum sizes?
You could also try the "set working set size" configuration option.
--------------------
Colt 45 - the original point and click interface
-
The data file is about 70 gig for the main database, Split into 2 datafiles. The sql server is under a heavy load, at times we use pretty heavy queries. Some of these could be optimized but with our development schedule we really don't have time to clean up hundreds of sql queries and sprocs. Basically the server runs really well most of the time. But I want it to kick ass even when its faced with a ridiculous sql statement containing subqueries.
Right now the server is only using about 50% of its memory. I want it to have all memory available for queries. The database is sitting on a sick raid(DAS), Its got 4 xeon processors and 10 gigs of RAM. I know this thing can perform better.
I will check out "set working set size"; but I would like to get some more input or a definative answer first. I dont want to keep having to start and stop sql. And restarting the sql server is like something I only want to do in the middle of the night. And I need to get some sleep. It seems like something is definately limiting me from using more than that 5 gigs of memory, Im just not sure what.
-jason
-
i set the set working set size Option to 1. It still will not go over 5.18g.
-
What edition of SQL Server are you running?
--------------------
Colt 45 - the original point and click interface -
Also, take a look at this article,
http://www.sql-server-performance.com/awe_memory.asp
--------------------
Colt 45 - the original point and click interface -
Hi phil. we are running enterprise edition. it should support up 64 gigs of memory.
I have taken a look at tha tarticle but dont really see anything different from my current settings.
-
I presuming you, restarted the services after changing the option
You might even need a full server reboot so SQL can grab that slab of memory.
Have you tried altering the memory using the GUI?
Select "Used fixed memory size (MB)", move the slider all to the size you want. You can also choose "Reserve physical memory for SQL Server", this is the same as "set working set size".
--------------------
Colt 45 - the original point and click interface -
well i stopped and started the service. i have no restarted. I do not want to do this unless i am absolutely sure.
-
How do you know it is only accessing 5gb?
With AWE, you can only see the actual amount used via the permon counter 'Total Server Memory', not task manager.
What does your SQL Server error log say on start up? It may give an indication as to why it isn't able to use the full amount requested
-
in my sql log i see the message.
2005-09-22 23:44:36.13 spid1 Warning: unable to allocate 'min server memory' of 8985MB.
when i look at the performance monitor the memory just sticks at 5076280.
-
I'm not sure about Windows Server 2003, but in Windows 2000, the 3gb switch actually tells the OS to reserve 1gb for itself, and leave 3gb for the apps.
If it's the same in 2003, then asking for 8985 may be just too much, as the OS won't be left with 1gb to itself.
Have you got anything else running on the server that is using up memory?
It might be worth reducing the allocation bit by bit until you no longer get the warning message
-
the server is in production, I cant keep stopping and starting it. Though I have a feeling that it will stop erroring about 5 gigs.
There is nothing but sql server and agent running on the machine.
I decided to put the /3gb option on because of the sql server performance article. It stated to put that on for 8 gigs of memory. im not sure if it means anything but 8 - 3 = 5.
Since I have 10 gigs of memory im not positive this is the right setting.
Originally I only had the /pae option set on my boot.ini and sql server only used like 1.8gigs of ram. But the thing is I had changed my awe setting to 1 and enabled the /3gb fix at the same time, then restarted. So i am not sure what took me from 1.8 to 5.1.
I really cant restart the server 3 times to try every combination. And unfoutunately I do not have another server with 10 gigs of ram for testing purposes.
-
Are you monitoring SQL memory usage using the performance counters SQL Server provides or via the task-manager?
-
It was the awe setting that took you from 1.8 to 5.1 GB ram. That is for sure. Now, here is a guess: the /3GB switch might be a problem when allocating that much memory. Without it you might very well be able to allocate over 8 GB ram with PAE. But unfortunately that requires another reboot, so I guess you will want to wait for a more definitive answer.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply