Microsoft SQL Server forum - Server not using all available memory.

Author

Server not using all available memory.

Ray

2005-03-30, 7:04 pm

I have a SQL Server 2000 (EE) machine dedicated to running only SQL
Server that is not using all of the memoery on the machine. I have read
and tried setting all of the memory setting available in the BOL with
no luck. The database is about 10 Gb in Size. I have four 2.7 Xeon MP's
16Gb of ram. The SQL server is only grabbing about 2 Gb of Memory.
Since I am running Enterprise Edition I am not sure why It will not
load more of the db into memory. I have run all of the profiles and the
results would indicate that it wants more memory but the box should
have plenty. Any suggestions would be greatly appreciated.

Thanks in Advance,

rhorner

Simon Hayes

2005-03-31, 7:01 am

http://support.microsoft.com/defaul...kb;en-us;274750

Simon

Ray

2005-03-31, 8:03 pm

Simon Hayes wrote:
> http://support.microsoft.com/defaul...kb;en-us;274750
>
> Simon

Simon,

Thanks for the link. I have the awe switch set in SQL server. In my
boot.ini if I use the /PAE switch alone SQL Server only uses about 2Gb
of memory but when I add the /3Gb switch it will use almost 3Gb of
memory. Shouldn't it be able to consume more. I have 16Gb of memory in
the server and I have the correct memory values set using the
sp_configure option. I am at a loss. If anyone has any personal
experiences with memory above 4Gb that would be really helpfull.

Thanks,

rhorner

Igor Raytsin

2005-03-31, 8:03 pm

Ray wrote:
> I have a SQL Server 2000 (EE) machine dedicated to running only SQL
> Server that is not using all of the memoery on the machine. I have read
> and tried setting all of the memory setting available in the BOL with
> no luck. The database is about 10 Gb in Size. I have four 2.7 Xeon MP's
> 16Gb of ram. The SQL server is only grabbing about 2 Gb of Memory.
> Since I am running Enterprise Edition I am not sure why It will not
> load more of the db into memory. I have run all of the profiles and the
> results would indicate that it wants more memory but the box should
> have plenty. Any suggestions would be greatly appreciated.
>
> Thanks in Advance,
>
> rhorner
>

Try this:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'min server memory', 14336
RECONFIGURE
GO

sp_configure 'max server memory', 14336
RECONFIGURE
GO

And restart your server (box).

HTH,
Igor

Erland Sommarskog

2005-03-31, 8:03 pm

Ray (rhorner@nuair.com) writes:
> Thanks for the link. I have the awe switch set in SQL server. In my
> boot.ini if I use the /PAE switch alone SQL Server only uses about 2Gb
> of memory but when I add the /3Gb switch it will use almost 3Gb of
> memory. Shouldn't it be able to consume more. I have 16Gb of memory in
> the server and I have the correct memory values set using the
> sp_configure option. I am at a loss. If anyone has any personal
> experiences with memory above 4Gb that would be really helpfull.

A tip is to inquire further in microsoft.public.sqlserver.server. Some
of my MVP colleagues that have a lot more experience of multi-GB servers
than I have hang around there.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Mike Epprecht \(SQL MVP\)

2005-03-31, 8:03 pm

Hi

What are your sp_configure settings.?
OS and SP version?
SQL and SP levels?

How are you determining how much RAM SQL Server is using?

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns962B5E3B98EY
azorman@127.0.0.1...
> Ray (rhorner@nuair.com) writes:
>
> A tip is to inquire further in microsoft.public.sqlserver.server. Some
> of my MVP colleagues that have a lot more experience of multi-GB servers
> than I have hang around there.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Sponsored Links