SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 SQL How to allocate memory
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Robert London
Starting Member

26 Posts

Posted - 08/01/2010 :  04:41:53  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 08/01/2010 :  06:53:45  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page

Robert London
Starting Member

26 Posts

Posted - 08/01/2010 :  07:53:24  Show Profile  Reply with Quote
Thanks Gail.

Current settings are

Min = 0
Max = 2147483647

Your thoughts appreciated?
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 08/01/2010 :  11:11:40  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
My thoughts as I indicated in previous posting.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 08/01/2010 :  20:32:42  Show Profile  Visit russell's Homepage  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37143 Posts

Posted - 08/01/2010 :  23:31:11  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

Robert London
Starting Member

26 Posts

Posted - 08/02/2010 :  07:41:18  Show Profile  Reply with Quote
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?
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 08/02/2010 :  09:44:24  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page

Robert London
Starting Member

26 Posts

Posted - 08/02/2010 :  10:00:51  Show Profile  Reply with Quote
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,
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 08/02/2010 :  11:43:27  Show Profile  Visit russell's Homepage  Reply with Quote
1,024 KB = 1MB
1,024 MB = 1GB

(10829480 / 1024) / 1024 = 10.32779693603515625
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000