Memory Usage

[BlakeK]

We had 2GB of RAM in our Win2K3 Standard server, which is running SQL 2000.
All 2GB of RAM was being used. The sqlserver.exe process in Task Manager was using 1,736,128K.
We upgraded the machine to 4GB of RAM and rebooted.
The sqlserver.exe process in Task Manager is still only using 1,736,128K.
In Enterprise Manager, on the Memory tab of the Properties window for the server it is set to "Dynamically configure SQL Server memory" and the Minimum MB is set to 32 and the Maximum MB is set to 3712.
The Configured Values match the Running Values.
I found it odd that SQL didn't take more memory after the upgrade. Could it be that it has all it needs, or could there be something wrong with the configuration?

Thanks.

 

[dbomrrsm]

it will grab more as it needs it

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[BlakeK]

It seems a bit odd that when there was 2GB in the machine it was maxed out, but when we went to 4GB it didn't use a single byte more than before.
I just found it hard to believe that 2GB was totally being used, but nothing more is needed.

 

[Corran007]

Its not using it. See this article

http://support.microsoft.com/default.aspx?scid=kb;en-us;274750

 

[Catadmin]

Yikes! Good catch, Corran007. I was not even aware of that problem. A star for you!



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[mrdenny]

Blake,
Are you using SQL 2000 Standard of Enterprise edition?

Have you set the /3GB switch in the OS?
How about the /AWE?
Have you setup AWE in SQL Server?

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[BlakeK]

----Posted by Denny----
Blake,
Are you using SQL 2000 Standard of Enterprise edition?

Have you set the /3GB switch in the OS?
How about the /AWE?
Have you setup AWE in SQL Server?

Denny
-----------------------

Denny,
We are using Standard edition.
We have not done the 3GB switch or the AWE, but I plan to read through the article Corran posted, and try that stuff out on Monday. Thanks.

 

[mrdenny]

First, SQL Server Standard edition won't use more than 2 GB of RAM. To use more than 2GB of RAM, you'll need to install SQL Enterprise Edition.

AWE for SQL Server is setup via sp_configure.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[BlakeK]

Denny,
So even if I do the /3GB swich in the boot.ini, SQL 2K Standard will only use 2GB?

I just went to MS and checked pricing on Enterprise:

http://www.microsoft.com/sql/howtobuy/default.asp

So, our 2 processor machine would cost $40K to upgrade?!
LOL!! Might as well forget upgrading, I know there is NFWmy company would ever go for that!
I'm sure pricing for SQL 2K5 will be even worse!
Do you happen to know if SQL 2K5 Standard will have the same 2GB limit?

 

[Corran007]

http://www.microsoft.com/sql/spotlight/expandsqlserver.asp

no it does not have a limit for standard

 

[mrdenny]

Yeah, according to this doc 2005 standard won't have a memory limit like 2000 does. So that problem with be solved.

With all the Enterprise Features that they are adding into Standard I'd assume that the Standard price will be going up. I didn't see prices when skimming this article.

From what I see on this page 2005 Workgroup is more like 2000 Standard. 2005 Standard is 2000 Standard+ and 2005 Enterprise is 2000 Enterprise+.

Although to get more than 4 Gigs of RAM on SQL 2005 Standard edition, you'll have to be running Windows 2003 Enterprise Edition (Windows 2003 Standard only supports 4 GB of RAM).

With your SQL 2000, no you won't be able to get more than 2 GB of RAM with the /3GB switch.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[mrdenny]

I didn't see prices when skimming this article.

I'm just blind. The are prices listed in the image in the middle.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[BlakeK]

Thanks for all the help guys.

It looks like we will be waiting to utilize our new memory until SQL 2K5 is released.

We are running Win2K3 Server Standard, so the next blockade would be 4GB of RAM. However, I checked the pricing on Win2K3 Server, and the pricing jump from Standard to Enteprise is not too bad. About $1200 going up to $4000. At least it is not as ridiculous as the price jump between SQL2K5 Standard and Enterprise.

And Win2K3 Server Enterprise will allow us up to 32GB of RAM, and SQL2K5 will be able to use all of that.

Thanks all.