SQL 2008 memory configuration

Question

SQL 2008 memory configuration

James Lavery

SSC Eights!

Points: 808
More actions
June 3, 2011 at 3:21 am

#241632

Hi all,
We've got an instance running on Windows Server 2008 R2, which has 64GB of memory. The box is dedicated to SQL Server - no other applications run on it.
SQL 2008 is configured with no memory limit (well, it's 2,147,483,647 MB!).
The server monitoring system is flagging that physical memory in use is over its limit of 90%.
Looking at the server, SQL Server is indeed taking 91% of physical memory.
The question is - should I configure SQL Server to limit the amount of memory it uses?
Books Online indicates that it monitors available memory and dynamically adjusts itself - however, this documentation does not explicitly refer to behaviour under Server 2008.
Advice/pointers would be appreciated.
Thanks,
James

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Mr. Holio Hall of Fame Points: 3314 More actions · Answer 1

It is advised. This may help you.

http://www.sqlservercentral.com/blogs/glennberry/archive/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008.aspx

__________________________
Allzu viel ist ungesund...

James Lavery SSC Eights! Points: 808 More actions · Answer 2

Perfect - just what I needed to know, thanks.

James

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 3

sorry I disagree, on a x64 system with 64GB of ram allocating 60GB to sql server is too much. The allocated memory is only for data cache it does not include memory for other memory pools. I'd advise you should set max memory at 56GB, this is how I run my prod systems with that config.

There are plenty of blog posts about configuring x64 memory, some from microsoft many years ago now, most were in response to people allocating too much memory and running into problems.

check out this excellent blog http://blogs.msdn.com/b/slavao/

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Mr. Holio Hall of Fame Points: 3314 More actions · Answer 4

colin.Leversuch-Roberts (6/3/2011)
sorry I disagree, on a x64 system with 64GB of ram allocating 60GB to sql server is too much. The allocated memory is only for data cache it does not include memory for other memory pools. I'd advise you should set max memory at 56GB, this is how I run my prod systems with that config.
There are plenty of blog posts about configuring x64 memory, some from microsoft many years ago now, most were in response to people allocating too much memory and running into problems.
check out this excellent blog http://blogs.msdn.com/b/slavao/

Good point! Thanks for sharing.

__________________________
Allzu viel ist ungesund...

James Lavery SSC Eights! Points: 808 More actions · Answer 5

James Lavery

SSC Eights!

Points: 808

June 3, 2011 at 6:27 am

#1333618

Thanks for the info - I'll take on board!

James

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 6

select * from sys.dm_os_performance_counters

where counter_name like 'page life%'

First check the requirement of RAM with the help of this query,this will tell you about RAM required or not

If this value less than 3000 then you have required More RAM on Server otherwise not

My Blog
www.aureus-salah.com

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 7

Syed Jahanzaib Bin hassan (6/4/2011)
select * from sys.dm_os_performance_counters
where counter_name like 'page life%'
First check the requirement of RAM with the help of this query,this will tell you about RAM required or not
If this value less than 3000 then you have required More RAM on Server otherwise not

@Syed

Its 300 not 3000.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 8

yes 300 not 3000 seconds or 5 minutes is an avg page life

My Blog
www.aureus-salah.com