/3GB memory usage

I upgraded the SQL SErver from standard to Enterprise edition recently on a Windows 2000 server. Even though the system has 4GB memory, still only 1.7 GB is used by SQL server. I have set /3GB option and also awe is turned on. why it is not using more memory?
The startup parametrs are set to use memory dynamically upto a max of 2.8 GB. But still no improvement.
Could anyone help me with this?

First of all, you don't need awe enabled or the /PAE switch if you only have 4GB of RAM, so take them off. Second, either set the MIN and MAX RAM to 2.8GB and leave it at that, or expect it not to use the entire 3GB unless it needs it. Look at your total and target SQL Server memory counters. If they are even, SQL Server basically doesn't think it needs more RAM. In addition, look at the buffer cache hit ratio and the cache hit ratio. If they are above 97%, then you aren't going to probably see a huge benefit with more RAM.

How big are the databases on this server?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

/3GB switch is going to make the SQL Server address space to be 3GB instead of 2GB by default. So, if sql server does not have a big load that it will not use the 3Gigs of RAM even if you have more memory.

In the perfmon

Check for Virtual Bytes max for the SQL Server process, that should have touched 3GB some time. That indicates that SQL Server virtual address space has increased to 3GB.

Also if you have only 4Gigs of RAM there is no need to apply /PAE swtich. What are teh values for Target Server memory and Total Server memory.

If Target Server Memory is more that total server memory and if physical server memory is available that the additional space is going to be committed.

I'm running into the same issue as Hari in this original post. I have done my homework with this issue and I just can't make sense as to why the /3GB switch doesn't seem to be working as designed.

Windows 2000 Server, SQL 2000 Developer Edition, 4GB RAM, /3GB switched turned on in the boot.ini. PAE and AWE are disabled.

I ran a load simulator in SQL that creates a temp table and populates the table with 100,000 rows and just loops itself. This used up the RAM fairly quickly but RAM usage stops climbing when I reach about 1.7GB.

I can't seem to get SQL to use more than 1.677 GB RAM. I'm using SQLEM to set the MIN and MAX size for memory to 3GB. Perfmon shows Target Server Memory @ 1.677 GB and Total Server Memory @ 1.677 GB. Process - Working Set never goes above 1.7 GB. Buffer Cache Hit Ratio is at 99% constantly. Page file is around 5% usage

So I can see that if the buffer cache hit ratio is over 90%, it doesn't need more RAM. How can this be when I'm running a stress test on the server and it stops at 1.7GB. RAM should still be climbing until I reach my 3GB limit as mentioned in SQLEM, Server, Properties, Memory tab.

It's almost like the /3GB switch in the boot.ini isn't doing anything.

Performance doesn't seem to be too bad when executing the stress test. The cpu stays around 50%. This box has 4 700mhz procs.

Do you suggest another counter in perfmon to find my problem?

About djmaro76's problem:

Does SQL 2000 Developer Edition support more than 2 GB of RAM? I thought only Enterprise Edition did that.

Yes, Developer Edition supports up to 64GB.
http://www.microsoft.com/sql/evaluation/overview/default.asp

Sayta, per your request.
Memory:
Available Kbytes – 1686388.840
Cache Bytes – 114914918.400
Page Reads/Sec – 0.109
Page Writes/sec – 0.000

PhysicalDisk:
% Disk Read Time – 0.337
% Disk Time – 0.386
% Disk Write Time – 0.050
% Idle Time – 99.646
Avg. Disk Bytes/Read – 10000.340
Avg. Disk Bytes/Transfer – 6309.415
Avg. Disk Bytes/Write – 5137.297
Disk Bytes/Sec – 22328.323
Disk Read Bytes/sec – 8529.921
Disk Reads/Sec – 0.853
Disk Transfer/sec – 3.539
Disk Write Bytes/Sec – 13798.402
Disk Writes/sec – 2.686
Split IO/Sec – 0.109

Process: Total
Page File Bytes – 1930123715
Pool Paged Bytes – 1128578.200
Working Set – 1966914929

Processor: Total
% DPC Time – 0.053
% Interrupt Time – 0.276
% Privileged Time – 1.708
% Processor Time – 6.259
% User Time – 4.548
APC Bypasses/sec – 62.484
DPC Bypasses/sec – 0.000
DPC Rate – 1.160
DPCs Queued/sec – 163.787
Interrupts/sec – 503.230

SQL Server: Memory Manager
Granted Workspace (KB) – 102.480
Target Server Memory (KB) – 1678650.320
Total Server Memory (KB) – 1678650.320

I'm only using this server to test the /3GB switch option. If I can get this to work properly, then my goal is to make recommendations to clients to make changes to the server to take full advantage of the RAM. But for some reason, this server just won't use all the RAM. It doesn't make sense that the server just doesn't need the extra RAM and decides to not use more than 1.7 GB.

We have clients with servers that have 4 GB or more RAM and they are only using SQL 2000 Standard Edition. I'm trying to justify the need to upgrade to SQL 2000 Enterprise Edition. I was able to take another server with 8GB and enable AWE so now SQL uses 6.5GB for itself.

I'm not too worried about performance issues at this point. I just need to get that darn /3GB switch to actually give SQL that extra 1GB of RAM. Everything on this server checks out. My boot.ini has /FASTDETECT /3GB.

I'm a little confused on something. In some of the threads, I see recommendations of enabling PAE and AWE on a server with only 4GB RAM. But in other threads, I see suggestions of turning it off and only using the /3GB switch. If PAE and AWE are only for servers with more than 4GB, why are there recommendations floating around to enable those options?

I found one issue that might be the cause of all my problems but could use some feedback.

Will the /3GB switch work if you have less than 4 GB of RAM in the server?

The server I supplied the PERFMON counters for this morning only has 3,800,866KB of total RAM. I was assuming that 3.8GB was actually 4GB minus some RAM that the OS takes for itself.

Anyone have any ideas on my previous question? I'm thinking that the reason I can't get SQL to use more than 1.7 GB is because Windows only notices 3.8GB of RAM. I just need someone to verify this for me.

The server does have 4GB but it allocates 256mb for the video card.

I have searched the net and can't seem to find a true answer.

I found the problem. It has nothing to do with 3.8 GB as apposed to 4GB. The problem is that I'm running Windows 2000 Server Standard and that version doesn't support the /3GB switch. Only 2000 Advanced Server supports this feature.

http://support.microsoft.com/kb/833722/EN-US/

Live and learn!

I believe also if you have 2003 server that you'll find the OS uses 1GB and applications are automatically allowed 3GB which SQL Server pretty much immediately grabs. Not sure if this is the same for Windows 2000 Server - I suspect probably not and it has a max of 2GB per application.

Small Businesses
www.matiogi.com

Hi all,

After reading lot of articles on this large memory isssues on MSSQL, I made a table summary assumping a server with 8GB of phy memory and following OS and MSSQL installed

OS : Win 2K Adv Server or Win 2k Datacenter or Win 2003 Server or Win 2003 Enterprise or Win 2003 Datacenter
MSSQL : MSSQL 2000 Enterprise or MSSQL 2000 Developer

The table is the memory achievable with the possible memory settings combination in OS and MSSQL:

Settings..............Kernel Mode Memory (OS)............Appl Mode Memory (e.g. MSSQL)..........Extended Mem > 4GB range
Standard........................2GB..........................................2GB.................................................Nil
/PAE..............................2GB..........................................2GB.................................................4GB
/PAE, AWE enabled..........2GB..........................................6GB (2GB + 4GB ext mem)..................4GB
/PAE, /3GB.....................1GB..........................................3GB..................................................4GB
/PAE, /3GB, AWE enabled.1GB...........................................7GB (3GB + 4GB ext mem).................4GB


Can someone care to confirm my understanding of this whole things correct?

P/S: Sorry for the dotted line to align the columns.

Can someone in this forum pls comment/confirm on the my understanding above.
I need senior people to assure my understanding/absorbtion from threads/articles that I have read.

Thank you.

That looks good retro. There really isn't a reason to put Datacenter on the list though. [<img src='/community/emoticons/emotion-1.gif' alt='' />] You have to buy a complete "datacenter solution" from a vendor to even get into that product line. It's extremely expensive. Other than that though, it looks great.<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.

Hi,

I just tested the AWE memory in SQL Server 2000 (SP3a) on Windows Server 2000 Standard Edition with 4GB phy memory.

No /3GB switch and No /PAE switch in boot.ini

1. Grant 'Lock Page in Memory' to Administrator (because MSSQLServer startup account is LocalSystem)

2.
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'min server memory', 3666
RECONFIGURE
GO
sp_configure 'max server memory', 3666
RECONFIGURE
GO

3. Restart MSSQLServer service

Interestingly Total Server Memory & Target Server Memory in Perf Monitor showed same figure around 3GB respectively.

From AWE article on this site http://www.sql-server-performance.com/awe_memory.asp) stated use /3GB switch for server with 4GB and below. But I didn't set that in boot.ini.

My question is does that mean that AWE and /3GB switch on its own has the same effect in a Windows 2000 Standard Edition that will set the user mode memory to 3GB and kernel mode memory to 1GB?