SQL server only uses 2.7GB on 8GB server

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 server only uses 2.7GB on 8GB server

Author

Topic

j0nx
Starting Member

4 Posts

Posted - 2007-04-07 : 15:07:11

Accidently posted this in the development forum.

Here is the setup.

Hardware:
Dell Quad Xeon 2.7ghz
8 GB ram

Software:
Windows 2003 Server Enterprise
Sql 2000 Enterprise Sp3

Boot.ini Settings:
/PAE /3GB

Sql Settings:
AWE = 1
Max server memory = 7000 (fixed)

Database sizes:
150GB
220GB

With these settings, sqlserver.exe will use only 2.7GB of memory with over 4GB of memory left available to the server (the server properly reports 8GB total). Perfmon "sqlserver:total server memory" confirms this. If I take out the /3GB switch in boot.ini, sqlserver will only use 1.7GB. These are hard limits as SQL never scales past these amounts even under load for several days. What am I missing??

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 2007-04-07 : 15:20:31

Did you restart the server after you changed the max server memory setting?

j0nx
Starting Member

4 Posts

Posted - 2007-04-08 : 11:51:42

quote:
Originally posted by sql_er

Did you restart the server after you changed the max server memory setting?

Yes

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 2007-04-09 : 11:34:52

Hmm ... don't know then.

I followed advices here and as long as server is restarted, it worked:

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

http://www.sql-server-performance.com/awe_memory.asp

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-04-09 : 15:48:15

Does sql service account has following user rights on the server?

Act as part of the operating system
Increase quotas
Log on as a batch job
Log on as a service
Replace a process level token
Bypass traverse checking (that is usually set for Everyone anyway)
Lock pages in memory
access this computer from the network
backup files
restore files
increase scheduling priority
load & unload device drivers

j0nx
Starting Member

4 Posts

Posted - 2007-04-12 : 15:25:23

quote:
Originally posted by rmiao

Does sql service account has following user rights on the server?

Act as part of the operating system
Increase quotas
Log on as a batch job
Log on as a service
Replace a process level token
Bypass traverse checking (that is usually set for Everyone anyway)
Lock pages in memory
access this computer from the network
backup files
restore files
increase scheduling priority
load & unload device drivers

Awesome, that did it! (I'm assuming the lock pages in memory) The task manger is reporting the memory usage incorrectly, but the perfmon object is dead on.

Thanks!

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2007-05-08 : 11:22:28

Where do you go to look how these user rights are set up?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-08 : 15:44:07

Local security policy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources