SQL Standard using 2GB Physical Memory

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)

SQL Standard using 2GB Physical Memory

Author

Topic

kowani1
Starting Member

42 Posts

Posted - 2006-11-20 : 01:13:37

Hi All,

I have installed and configured SQL Server 2000 Standard Edition and realised that the maximum physical memory it can use it 2G.

I however have 4G and want to know if there is a way I can fully utilise this 4G memory for better performance, whilst still using my licensed copy of SQL Server 2000 Standard.

Thanks.
Jungle DBA..

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-11-20 : 01:46:42

this thread and article may help you:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55191
http://www.sql-server-performance.com/awe_memory.asp

SqlSpec - a fast, cheap, and comprehensive data dictionary generator for
SQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org

Westley
Posting Yak Master

229 Posts

Posted - 2006-11-20 : 03:06:44

Standard edition cannot use more then 2G anyway, so you will need to upgrade :)

Kristen
Test

22859 Posts

Posted - 2006-11-20 : 03:58:56

"fully utilise this 4G memory for better performance"

How big is your database, and how many concurrent connections do you have open then?

What sort of hardware is it?

How many disk channels, and what configuration?

Its a Dedicated Server, right?

Running what O/S flavour?

A database that needs more than 2GB of memory "for performance" reasons is going to need consideration for lots of things, not just "throwing more memory at it" [no offence intended], and the fact that Standard Edition has been installed suggests that other avenues may have been overlooked - including, possibly, index and query optimisation, decent housekeeping and so on.

Kristen

monty
Posting Yak Master

130 Posts

Posted - 2006-11-22 : 00:16:34

Hi,

As you are using sql server2000(SE),though you have more memory you can't make use of it.
But in case u are going for Enterprise edition.. then you can design your own requirements by enabling AWE

The AWE (Advanced Windowing Extensions) API allows applications (that are written to use the AWE API) to run under Windows 2000 Advanced Server or Windows 2000 Datacenter Server to access more than 4GB of RAM. SQL Server 2000 Enterprise Edition (not SQL Server 2000 Standard Edition) is AWE-enabled and can take advantage of RAM in a server over 4GB. If the operating system is Windows 2000 Advanced Server, SQL Server 2000 Enterprise Edition can us up to 8GB of RAM. If the operating system is Windows 2000 Datacenter Server, SQL Server 2000 Enterprise can use up to 64GB of RAM.

you must enter one of the following switches in the boot line of the boot.ini file, and reboot the server:

4GB RAM: /3GB (AWE support is not used)

8GB RAM: /3GB /PAE

16GB RAM: /3GB /PAE

16GB + RAM: /PAE

The /3GB switch is used to tell SQL Server to take advantage of 3GB out of the base 4GB of RAM that Windows 2000 supports natively. If you don't specify this option, then SQL Server will only take advantage of 2GB of the first 4GB of RAM in the server, essentially wasting 1GB of RAM.

hope this helps you to some extent

its me monty

mr_mist
Grunnio

1870 Posts

Posted - 2006-11-22 : 06:18:40

Please don't use the /3GB switch. Aside from being pretty much useless, it can introduce a few unexpected and fairly nasty bugs in and of itself. The lack of its use does not mean that you end up wasting RAM.

-------
Moo. :)

Kristen
Test

22859 Posts

Posted - 2006-11-22 : 07:58:35

So what's the best setting for SQL Server 2000 Enterprise running on a plain Windows 2003 Server O/S with 4GB of memory?

EDIT: I am working from this:

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

Its all rather vague as to exactly what you have to do (but does include "You can use the 3 GB switch that is in the Boot.ini file with Microsoft Windows Server 2003"), in particular its not clear to me if ALL the document has been carefully reviewed WRT Win2k3. For example the linked page:

http://support.microsoft.com/kb/170756/

says "/3GB is used only in the Windows NT Server Enterprise Edition of Windows NT with Service Pack 3."

So what about /PAE then?

And do I need to do:

sp_configure 'awe enabled', 1

BoL also says "To use AWE memory, you must run the SQL Server 2000 database engine under a Windows 2000 account that has been assigned the Windows 2000 lock pages in memory privilege" is that required?

And that lots seems to heavily contradict this:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56145

and this, which was specifically about Win2k3:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=39046

Now I have no idea what to do, but whatever it is I'm planning to do it tomorrow morning on a production server

Kristen

mr_mist
Grunnio

1870 Posts

Posted - 2006-11-22 : 09:55:50

I'd use /PAE (which I think is automatically enabled anyway on W2k3 Enterprise Ed.) and enabled AWE in sql server, assuming that you have 4GB or more of memory. I wouldn't use /3GB, which has nothing to do with physical RAM at all. The 3GB switch has actually caused data issues here, albeit with our specific hardware/software setup.

-------
Moo. :)

Kristen
Test

22859 Posts

Posted - 2006-11-22 : 13:48:50

Do you reckon that SQL EE will actually use more than 2GB RAM (in a Win2k3 Standard Server with 4GB RAM [no other apps]) without the /3GB flag?

Is there an easy way I can tell that SQL Server is actually using that RAM? (Ent.Man. Properties : [Memory] shows that Max set is 3,839MB now, I presume it has not ACTUALLY managed to get more than 2GB though!?

PerfMon shows:

Memory : Available MBytes 1739
SQLServer : Memory manager : Total Server Memory 1,681,752KB

Control Panel System:
Windows Server 2003 Standard Edition SP1
Xeon MP CPU 3.00GHz
2.99GHz, 3.75GB of RAM

DELL OpenManage:

Memory : Installed Capacity = 4096MB
Installed Capacity Available to the OS = 3840 MB

Processors: 4 x Xeon MP Model 2 Stepping 6 3GHz (External 400MHz)
Installed size Cache1 = 8KB, Cache2=512KB, Cache3=4096KB

Thanks

Kristen

mr_mist
Grunnio

1870 Posts

Posted - 2006-11-23 : 04:42:41

If you turn on \PAE and AWE in sql server and set the memory size to somewhere over 2GB, then, if if \3GB switch was needed and it wasn't on, you'd get an error that it was unable to allocate the memory. You could then repeat the same with the 3GB switch on. That should tell you either way.

-------
Moo. :)

Kristen
Test

22859 Posts

Posted - 2006-11-23 : 06:40:17

Cheers. I did the 3GB switch on its own this morning. Will try the PAE switch tomorrow morning ...

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources