Increasing SQL Server Memory - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Increasing SQL Server Memory

Author

Topic

maximus_vj
Yak Posting Veteran

88 Posts

Posted - 2004-08-25 : 12:13:24

Hello,
I have a Dell poweredge Server running on Windows 2003 and SQL Server 2000 Enterprise Edition. I have 2 GB RAM.

Now I want to increase the RAM to 4GB. Do I have to make any changes in the SQL Server Settings after I increase the RAM?

Right now AWE is not enabled on my SQL Server?

Thanks
maximus

MuadDBA

628 Posts

Posted - 2004-08-25 : 13:18:38

Only if you want SQL Server to use it.

maximus_vj
Yak Posting Veteran

88 Posts

Posted - 2004-08-25 : 14:07:17

Hello Joe,

Of course I want SQL Server to use it. What settings do I have to change.

From this article looks like I do not need to change any settings
http://support.microsoft.com/?id=274750

The following is an excerpt from the above article:

"Windows 2000 Advanced Server or Windows 2000 Datacenter or Windows Server 2003

The maximum amount of physical memory addressable by a 32-bit addressing mode is 4 GB. All processors based on the IA-32 architecture that begin with the Intel Pentium Pro, support a new 36-bit physical addressing mode known as Physical Address Extension (PAE). PAE allows up to 8 GB of physical memory on Windows 2000 Advanced Server and up to 32 GB of physical memory on Windows 2000 Datacenter Server. This is because the tested memory limit on Windows 2000 Datacenter Server is 32 GB. The PAE mode kernel requires an Intel architecture processor, Pentium Pro, or later and either Windows 2000 Advanced Server or Windows 2000 Datacenter. "

As I am using Windows 2003 Server and if I increase my RAM to 4 GB I do not have to change any SQL Server Settings(no need of enabling AWE).
Did I interpret correctly? Please let me know if I have to make any changes to the SQL Server Settings?

Thanks
maximus

MuadDBA

628 Posts

Posted - 2004-08-26 : 13:35:15

Ok, based on what I have read, SQL Server will only be able to use 2GB of that 4GB, the other 2GB will be reserved for the OS. If that's sufficient, cool. If you want SQL Server to be able to use 3GB, you need to use the /3GB switch in the boot.ini file, which is documented in Books Online.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-08-26 : 13:52:40

you should read this note http://support.microsoft.com/default.aspx?kbid=274750

The memory stuff is a little complicated, so read that note and the associated notes that it references.

-ec

maximus_vj
Yak Posting Veteran

88 Posts

Posted - 2004-08-26 : 18:48:02

Thank You

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-27 : 01:21:05

Maximus, for 4GB of RAM, you need to put the /3GB switch in the boot.ini file only like crazyjoe said. For more than 4, add the /PAE switch to the boot.ini file as well and use sp_configure to change awe enabled to true in SQL Server. For more than 16gb of RAM, turn the /3GB switch back off.

If you have anything else memory intensive besides SQL Server running on the box, especially services like IIS, don't put the /3GB switch in. It will starve the OS if you do.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

010101110110100001100101011011100010000001101100011010010110011001100101001000000110011101101001011101100110010101110011001000000111100101101111011101010010000001100001001000000110110001100101011011010110111101101110001011000010000001100110011010010111001001100101001000000111010001101000011001010010000001000100010000100100000100101110

MuadDBA

628 Posts

Posted - 2004-08-27 : 10:33:55

Derrick, you need to change that SIG of yours....it's causing me to have to scroll about 20 pages to the right on everything you post.

maximus_vj
Yak Posting Veteran

88 Posts

Posted - 2004-08-27 : 14:17:28

Hello Derrick,
So right now the server has both IIS and SQL Server running. And in two months we will be getting a new Web Server so that we will have a dedicated SQL Server. Now this machine has 2 GB HD and AWE is not enabled.
So what scenario do you think is best for this situation:
1) Hold Off increasing the memory till I get the new Web Server and then increase the memory of SQL Server machine to 4 GB and then put the /3GB switch in the boot.ini and not enable AWE
2) Increase the memory to 4 GB with the current setting(IIS & SQL Server machine) and not add the /3GB switch in the boot.ini and not enable AWE (i.e. not change any settings)

Let me know if there is a better scenario.

Thanks
maximus

quote:
Originally posted by derrickleggett

If you have anything else memory intensive besides SQL Server running on the box, especially services like IIS, don't put the /3GB switch in. It will starve the OS if you do.

MeanOldDBA
derrickleggett@hotmail.com

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-30 : 08:20:02

crazyjoe,

I changed it. It didn't change any of the historic ones though.

Maximus, both scenarios wouldn't hurt to implement.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources