Max memory for sql server - 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

General SQL Server Forums

New to SQL Server Administration

Max memory for sql server

Author

Topic

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2012-10-06 : 15:40:56

Dear Experts,
Need you advise on,
what should be the maximum memory to be set for sql server running data warehouse.
my server configuration is:
32 GB RAM.
with 7 databases on it.
each database currently is around 100 GB.
It also has SSRS,SSIS,SSAS configured on it.

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-10-06 : 15:46:36

If it were dedicated to just the databases, I'd start at 28GB. I'm not sure of the memory requirements of the other products, but maybe start with 20GB and see how everything does.

Are you using 64-bit?

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

Subscribe to my blog

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2012-10-06 : 16:04:16

quote:
Originally posted by tkizer

If it were dedicated to just the databases, I'd start at 28GB. I'm not sure of the memory requirements of the other products, but maybe start with 20GB and see how everything does.

Are you using 64-bit?

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

Subscribe to my blog

Hi Tara,
Thanks for the info.
Yes it is 64 bit.
So,as you mentioned does sql server really utilize 20 or 28 GB of RAM if given.

Thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-06 : 18:58:21

SQL OS will grab as much memory as it is allowed to and does its own memory management. It may not do so initially when you start up the server, but when you run queries it will gradually take up more and more memory up to the limit it is allowed to. What it does is hold on to the memory it grabbed (which allows it to keep data, query plans etc. in memory, so if the same data is needed again, it can provide a faster turn-around).

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2012-10-07 : 03:24:07

quote:
Originally posted by sunitabeck

SQL OS will grab as much memory as it is allowed to and does its own memory management. It may not do so initially when you start up the server, but when you run queries it will gradually take up more and more memory up to the limit it is allowed to. What it does is hold on to the memory it grabbed (which allows it to keep data, query plans etc. in memory, so if the same data is needed again, it can provide a faster turn-around).

Sunita,
Thanks for the detail.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-10-07 : 13:34:46

Some othe rconsiderations may include :
a) Lock Pages in Memory. Yes or No ? This will depend on different scenarios. The main benefit of turning it on is that the buffer pool buffer doesn't get paged out.

b) Consider how much memory the other apps may take - such as AntiVirus

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2012-10-08 : 06:32:40

quote:
Originally posted by jackv

Some othe rconsiderations may include :
a) Lock Pages in Memory. Yes or No ? This will depend on different scenarios. The main benefit of turning it on is that the buffer pool buffer doesn't get paged out.

b) Consider how much memory the other apps may take - such as AntiVirus

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Thanks Jack,
Will look into them.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-10-08 : 10:44:54

let us know how you get on

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources