Large memory on SQL Server Enterprise Edition

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)

Large memory on SQL Server Enterprise Edition

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-09-23 : 08:01:34

Peter writes "How to verify the change in SQL Server memory handle when you set the /3GB switch in boot.ini?

SQL Server configuration:
- Enterprise Edition
- SP2
- Windows 2000 with SP2
- 4 GB RAM

After the configuration above (/3GB)you don't see any change:

- under SQL Server properties memory
- when running sp_configure
- or watching memory on NT level in Task Manager

Is their any simple way to confirm that SQL Server use 3 GB RAM instead of 2 GB.

Regards"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-23 : 12:51:17

How do you even know that SQL Server needs more than 2GB? Do you see heavy paging in the sqlservr.exe process? You should check out the memory counters in Performance Monitor to check if the memory allocation is growing. It is totally possible that SQL Server just doesn't need the memory yet.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-23 : 16:31:27

Brian Knight's books is saying it automatically references up to 4 gb on windows 2000 or NT for Enterprise Edition.

8 GB of Windows 2k Advanced center

and 64 gb on Windows 2k Datacenter...

I though SQL Server managed memory pretty well....

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-23 : 17:20:04

What does automatically reference mean though? I thought that if the server had over 2GB, no matter what edition, that you had to make the switch change in the boot.ini file. I could be wrong though.

Tara

bm1000
Starting Member

37 Posts

Posted - 2003-09-23 : 20:07:11

quote:
Originally posted by AskSQLTeam

Peter writes "How to verify the change in SQL Server memory handle when you set the /3GB switch in boot.ini?

SQL Server configuration:
- Enterprise Edition
- SP2
- Windows 2000 with SP2
- 4 GB RAM

After the configuration above (/3GB)you don't see any change:

- under SQL Server properties memory
- when running sp_configure
- or watching memory on NT level in Task Manager

Is their any simple way to confirm that SQL Server use 3 GB RAM instead of 2 GB.

Regards"

You can verify in Perfmon:
Select the Process performance object.
Select the Working Set Counter.
Select the SQLSERVR process.
SQL Server should be using more than 2 gigabytes of memory if the switch is working.

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-24 : 09:46:34

quote:
Originally posted by tduggan
could be wrong though.

Tara,

My money will always be on you...just don't see any reference to it...

Knight mentions that all configurations are done on the Memory TAB of the Servers Properties...

But I could be (and the odds are 50/50) wrong.....

I've only got 2 gb on all of my boxes...do you have any over that? And did you NOT modify boot.ini and see what you get?

s/he asked if there was a simple way...wouldn't the memory tab be that simple way?

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2003-09-24 : 10:50:30

quote:
Originally posted by tduggan

What does automatically reference mean though? I thought that if the server had over 2GB, no matter what edition, that you had to make the switch change in the boot.ini file. I could be wrong though.

Tara

memory switches are indeed necessary.

SQL may be able to reference the memory, but win2k cannot without a little help. It is surprising that Brian's book did not mention this. See these technet articles:

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

basically you need to use the /3GB and/or /PAE switches in the boot.ini to address large amounts of memory.

-ec

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-24 : 12:16:22

Eyechart, Peter mentioned that he already made the change in the boot.ini but SQL Server is still not using over 2GB of memory. I commented that maybe SQL Server doesn't need additional memory yet. Just because it can use it, doesn't mean that it has to or will.

To see if the boot.ini change was done correctly, I would set the minimum amount of memory that SQL Server can use. This can be done using sp_configure or in the server properties in EM. Just set the value over 2GB then look at the sqlservr.exe process to see how much it is using. If it isn't using over 2GB, then if the boot.ini is configured correctly, I would contact MS.

Tara

chadmat
The Chadinator

1974 Posts

Posted - 2003-09-24 : 12:21:40

quote:
You can verify in Perfmon:
Select the Process performance object.
Select the Working Set Counter.
Select the SQLSERVR process.
SQL Server should be using more than 2 gigabytes of memory if the switch is working.

I don't think SQL will take the memory unless it needs it, or if you set it up to use a fixed amount of memory over 2GB. So perfmon may not show that memory in excess of 2GB is being used if SQL hasn't needed it.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2003-09-25 : 01:14:52

quote:
Originally posted by AskSQLTeam

Peter writes "How to verify the change in SQL Server memory handle when you set the /3GB switch in boot.ini?

SQL Server configuration:
- Enterprise Edition
- SP2
- Windows 2000 with SP2
- 4 GB RAM

After the configuration above (/3GB)you don't see any change:

- under SQL Server properties memory
- when running sp_configure
- or watching memory on NT level in Task Manager

Is their any simple way to confirm that SQL Server use 3 GB RAM instead of 2 GB.

OK, the fact that you do not see 3 GB available in the SQL properties from EM indicates that either the /3GB switch is not properly set, or you are running a flavor of win2k that does not support user mode addressable spaces > 2GB.

Are you running win2k server standard or advanced? The /3GB switch is only supported on advanced or datacenter server.

see KB article 274750 (http://support.microsoft.com/default.aspx?scid=kb;en-us;274750) for information.

This note http://support.microsoft.com/default.aspx?scid=kb;en-us;291988&Product=win2000 explains that the /3GB switch can be used on win2k pro and standard. When used it will move the kernel into the upper memory space the same way advanced and datacenter server do. However, the user addressable space is still limited to 2 GB in these OSes.

-ec

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2003-09-25 : 01:16:32

quote:
Originally posted by tduggan

Eyechart, Peter mentioned that he already made the change in the boot.ini but SQL Server is still not using over 2GB of memory. I commented that maybe SQL Server doesn't need additional memory yet. Just because it can use it, doesn't mean that it has to or will.

SQL Server properties in EM should show allow min and max memory settings for the buffer pool of up to 3GB of RAM if the /3GB switch is properly set on a supported OS.

-ec

franco
Constraint Violating Yak Guru

255 Posts

Posted - 2003-09-25 : 10:32:24

quote:

Is their any simple way to confirm that SQL Server use 3 GB RAM instead of 2 GB.

Peter, Have you looked at SQL Server error log when you boot your computer?
It has to be one entry that say how many processsors found and how much memory detected.

Franco

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources