June 25, 2007 at 6:45 pm
Hi all,
We have an SQL 2000 Standard SP4 system with 3gb of RAM running on Windows 2003 Server R2 Standard.
The SQL memory usage of this server flatlines at around 1665mb and will not go any higher.
I understand SQL 2000 Standard on Windows 2003 Server Standard has a 2gb memory limit, however why wont the memory push up to this limit? The OS shows there is around 700mb of the 3076 unused (SQL uses 1660, windows uses around 700 making the maximum used around 2300 of the 3076)
I have set the /3gb switch on windows boot, I have changed the SQL memory configurations many different times. If I try and set 'min server memory' to anything over around 1500mb I get the error on SQL startup;
Warning: unable to allocate 'min server memory' of 1720mb
In the above example surely if the Server has a 2gb limit it should let me set say 1720/2048 ? or 1920/2048 or even 2048/2048, however anything over around 1500 generates the above error.
Our server continues to flatline at 1660mb, any help to squeeze out an extra 200-400mb of RAM would be appreciated or an explination as to why it can not be done.
Thanks for your help
Jim
June 25, 2007 at 10:30 pm
Are there any other applications running on this server? If so, those may be taking the additional memory of the server.
June 25, 2007 at 10:43 pm
Your server has only 2GB of RAM. And adding the .3GB switch is of no use as it is to be used only when the server has more than 4Gb of RAM so that OS can use 1Gb and other 3GB will be allocated to other applications running in the server. As you have only 2GB and OS uses 700MB you have only 1300MB left in your server. Remember that OS shows free memory that also includes your virtual memory. SQL Server does not use virtual memory and depends fully on physical memory so if you want your sql to use more RAM add mor RAM to your server and use the /3GB switch so that SQL can use up to 3GB.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 26, 2007 at 12:08 am
Thanks for the replies.
Lynn, no the server has no other apps running on it and even if it did I assume they should use the available app memory not the reserved SQL memory?
Sugesh, as I said in the first line of my question my server has 3GB of RAM not 2.
June 26, 2007 at 1:17 am
Jim,
Even with 3GB of RAM you will not be able to use /3GB switch as it requires a minimum of 4GB RAM to take effect. You can check the available memory free using the counter memory\available bytes.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 26, 2007 at 1:54 am
I recently upgraded my 2Gb SQL Server 2K SP4 installation from Windows 2K to Windows 2K3, and immediately noticed that the server does not use as much memory for SQL Server (nothing else major runs on the machine) as it did under Win 2K.
Currently SQL server is ticking along using 1.4Gb, and there is 128Mb free on the server. Under Windows 2K these figures were usually about 1.8Gb and 50-60Mb respectively. Presumably Win 2K3 manages memory slightly differently...
David
If it ain't broke, don't fix it...
June 28, 2007 at 11:58 am
Are you running dynamic memory allocation for the database server? If so, perhaps the server just doesn't think that it needs any more. I wonder what you'd get if you were to manually set the memory settings in the server properties to 2gig.
I have a not dissimilar thing going on. We just upgraded one of my servers to 4gig. Each instance reports 4gig available. I assumed that since Standard is capped at 2gig that it would report 2gig available. I am not concerned with it because all of the instances on that box are ticking away quite merrily and I'll be upgrading it to SS2K Enterprise in the not distant future.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
June 28, 2007 at 1:49 pm
There is a bug in Task Manager that applies to Windows 2000 and 2003, where the amount of memory used by a process is not correctly reported in all cases.
Also, SQL server can and is paged. For example, if SQL Server max server memory is set to 2048 Mb, task manager will accurately show "Mem Usage" as 1,713,295 for and the remaining 383,857 of the 2Gb is paged.
To determine the actual memory being used and how much is paged, use the Process Explorer for Windows available at:
http://www.microsoft.com/technet/sysinternals/ProcessesAndThreads/ProcessExplorer.mspx
SQL = Scarcely Qualifies as a Language
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply