December 24, 2009 at 11:59 am
I have a server that has:
Windows 2003 Server Enterprise Edition 64 bit
2008 SQL Server 64 bit
8 GB of Physical RAM
Running two Instances of SQL Server
Primary databases are apprx. 300 GB
Second has apprx: 60 GB
SQL is using all the RAM and at times will not allow me to log on to the server without rebooting it.
My questionis is?
Should I set the Max Memory on each of the instances to 3.5 GB and if so, is this the number that I would set it to for each?
3500
Thanks for your help in advance
December 24, 2009 at 3:09 pm
No, I'd leave slightly more than 1GB for the OS. I might leave it 1.5 or 2GB, then set the rest for SQL Server. The value, I believe, is in MB, so you'd pick 3000 for 3GB
http://msdn.microsoft.com/en-us/library/ms178067%28SQL.90%29.aspx
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 24, 2009 at 3:28 pm
Thanks for the reply it's greatly appreciated.
December 25, 2009 at 2:41 pm
Indeed with 64 bit you should always set the max memory configuration or it may actually eat up all your physical ram.
IF you want to get control of processor % utilization, you may even install the free downloadable WSRM (windows system resource monitor) if you want all your instance to be able to use all your cores.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 11, 2010 at 6:34 am
to be honest 8GB of ram is insufficient to run one instance let alone two x64 sql server. If you check all the x64 stuff you'll find that a reasonable server requires best part of 10GB to just run the o/s let alone sql server. I actually start by allocating 12gb of ran to the o/s on x64.
It does to a certain degree depend on how many users and such your sql has - but when you set max/min memory you're basically allocating only the buffer cache so setting max memory to say 4GB does not limit sql server to using 4GB of ram - it will use more, too complex to cover in a post. With such a small setup I'd set max memory to 1GB on each instance - AND - as mentioned above it is absolutely critical that on any x64 setup you ALWAYS set max memory.
Personally I think it's in some ways tricky if you've not come through the sql 2000/2005 route to 2008 as almost all of this was covered in detail for sql 2005 but this was 5 odd years ago and much of what would be just as relevant for sql2008 is now lost in time < grin >
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 11, 2010 at 8:15 am
I'm not sure I agree with that, Colin. Depending on what the server is doing, 4GB could run it. It's a load issue.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 12, 2010 at 12:03 am
IMO for most of us, getting their first x64 is the upmost occasion to "finally" get a bigger server.
In many cases having 2 or 4 poc ( nowadays 4 to 6 cores ) .
Many start with 8GB ram because that already sounds big, but fairly soon learn to add ram up to the boxes capacity for a reasonalble price. :w00t:
Especially in the current economical situation, companies actually want to consolidate sql instances and managers really think this configuration is big enough to do the trick.
DBAs will not complain, because they know it may be used as a lever to fund decent equipement.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 25, 2010 at 9:28 am
Colin,
Could you please explain why you would recommend 12gb of RAM just for the O/S?
Thanks.
January 25, 2010 at 10:25 am
Everywhere I read, they all recommend atleast 10% of your physical RAM for the OS. If you have 128 GIG of RAM, then I would say you can afford to leave 12 GIG of RAM for the OS.
That is our set up. 128 GIG of RAM with 12 GIG for OS. But this is a SQL 2008 with Windows 2008 Datacenter set up.
-Roy
January 25, 2010 at 10:36 am
On our SQL 2005 Enterprise 64bit 96gb clusters we usually leave about 8gb so sounds about right.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply