SQL How to allocate memory - SQL Server Forums

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 2008 Forums

SQL Server Administration (2008)

SQL How to allocate memory

Author

Topic

Robert London
Starting Member

26 Posts

Posted - 2010-08-01 : 04:41:53

Hi,

We are running following configuration:
•Window Server 2003 Enterprise Edition SP2 x64 with 12GB Ram
•SQL Server 2008 SP2

I would like to assign 1.5GB to OS and 10.5GB of ram to SQL.

Could you please advise how to do this? And how I can check once done?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-08-01 : 06:53:45

sp_configure 'max server memory' or from management studio right click the server, properties and go to memory.

I would recommend, with 12 GB memory, allocate no more than 10GB to SQL. The more memory a server has, the more memory it needs to manage the memory.

--
Gail Shaw
SQL Server MVP

Robert London
Starting Member

26 Posts

Posted - 2010-08-01 : 07:53:24

Thanks Gail.

Current settings are

Min = 0
Max = 2147483647

Your thoughts appreciated?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-08-01 : 11:11:40

My thoughts as I indicated in previous posting.

--
Gail Shaw
SQL Server MVP

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-01 : 20:32:42

the # you see is posted in MB. if u need help with the math, Gail is saying set the max memory to 10 GB.

1 GB = 1024 MB

10 GB = 10240 MB

set max memory to 10240

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-01 : 23:31:11

A Microsoft engineer that I work with pretty frequently recommends reserving 2GB of memory to the OS per CPU socket. We've got 16 CPUs (4 quad-core sockets) on most of our production systems. They've got 48GB of memory. We've configured the instances to use 40GB.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Robert London
Starting Member

26 Posts

Posted - 2010-08-02 : 07:41:18

Great, thats all done. I have assigned 10gb to SQL and left the OS with 2gb.

Ok, next question is:

How do I check that SQL is using the 10gb assigned to it?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-08-02 : 09:44:24

By setting the max to 10GB, you're not telling SQL it must use 10GB. You're telling it that 10GB is the max it can use. It'll use what it needs up to that figure.

Try perfmon, one of the SQL counters objects has target server memory and total server memory. Target is what it wants, total is what it has. Don't use task manager to check SQL memory, it's often wrong.

--
Gail Shaw
SQL Server MVP

Robert London
Starting Member

26 Posts

Posted - 2010-08-02 : 10:00:51

Perfmon shows the following:

Target Server Memory(KB) 10829480
Total Server Memory (KB)512256

So I assume this means that it can use up to 10.5gb but is currently using .48gb. Please confirm my understanding is correct.

Thanks,

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-02 : 11:43:27

1,024 KB = 1MB
1,024 MB = 1GB

(10829480 / 1024) / 1024 = 10.32779693603515625

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources