May 9, 2008 at 2:48 am
I have a HP DL360 proliant server with 2 GB RAM onboard. SQL occupies a lot , leaving as much as 256 MB memory. I do not have a huge database and the connection are at most 5 to 10. My question, shoul I leave as it is , shoul I increase the RAM or shoul I tune the memory usage of SQL 2005 (BTW in studio management it states SQL can occupy as mauch as 2147483647 MB (!!!!!!!!) :what means 2 TB ??????).
Please gimme a hint on how I can set my server at best
TIA
May 9, 2008 at 4:22 am
Hmmm .. my desktop has more RAM than that!! I'd suggest you might go to 4GB ram on your server, out of the box that'd give sql server 2gb to use.
Actually having 250mb of free ram isn't too bad, for this box, I always ask the same question why do you need to leave lots of free ram , what do you think this "free" ram actually does? OK so it's not as simple a question as it may seem and the free ram may not be free at all - but that's another whole subject area. For a server as yours I'd leave sql to manage itself unless you actually have problems - I guess for this situation I'd define that as excessive paging by the sqlserver.exe.
I would suggest going to 4gb of ram as a first step - you need to monitor to see if you have problems first though. How big are your databases?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 9, 2008 at 4:48 am
SQL Server does dynamic memory allocation and deallocation. It will allocate free memory from OS and release when other apps need memory. Are you facing any bottleneck on the application/server, or is it the free memory level alarming you?
May 9, 2008 at 7:54 am
SQL Server does dynamic memory allocation and deallocation. It will allocate free memory from OS and release when other apps need memory.
SQL Server WILL only use what it needs but you still must tell it where the limit is. By default, it will use everything in the box, even suffocating the OS, if it thinks it needs it. It doesn't care about anything else running on the box.
My general rule of thumb is to have ONLY SQL Server running the box to begin with. Then, based on a number of articles I have read, if I have 8GB of memory, I allow SQL Server to have 6GB. If I have 16GB of memory, I allow SQL Server to have 12GB. Anything over 16GB, I allow SQL Server to have 6GB less than the total amount of memory in the machine.
If you have other SQL Services running on the server such as SSIS, SSAS, SSRS, etc., you will need to tell SQL Server to use even less so these other services don't get suffocated.
May 12, 2008 at 1:21 am
my databases size is approximately 1.5 GB. Should I go for 4 GB RAM; and if I expand the RAM memory to 4 GB , shall I adjust the max memory usage ( now it is 2147483647 ) : I mean which value Shall I choose ??
May 12, 2008 at 9:00 am
As Colin suggested above, I would definitely move to 4GB. I personally won't run on less than 8GB but that is personal preference. If you use 4GB, set the max memory in SQL to 2048. If you go higher, just make sure you set the max to at least 2GB less than the total.
May 13, 2008 at 1:23 am
You mean that the default value are OK (max 2147483647 is 2048MB???)
(I just can't figure out how the memory value is represented in SQL 2005 when you try to adjust the memory usage!!!)
TIA
May 13, 2008 at 6:20 am
2048MB is just over 2GB
SQL Server likes memory. And if a database is worth anything, it will grow.
Just make sure you leave the OS enough free memory. If you have a large (or runaway) query, this limit will help keep the server alive.
The recommendations I'm seeing follow what I've seen as our server has grown.
Greg E
May 13, 2008 at 6:48 am
ok i'll try to set th max memory usage at 1073741824 and see wha happen...
tks
May 13, 2008 at 6:51 am
ok i'll try to set th max memory usage at 1073741824 and see wha happen...
Your max memory needs to be set at 2048. That value is represented in MB so 1073741824 will still allow SQL Server to consume all memory. Just make sure that you leave at least 2GB (2048MB) for the OS or your server will come to a crawl.
May 13, 2008 at 6:53 am
I mean a max memory usage set in SQL server 2005 of 1073741824 is approximately 1024 MB ???????????????????????????????????
TIA
May 13, 2008 at 6:58 am
coastliner (5/13/2008)
I mean a max memory usage set in SQL server 2005 of 1073741824 is approximately 1024 MB ???????????????????????????????????TIA
1073741824 is 1024 TB. If you want to set the maximum server memory in SQL Server to consume only 1024 MB, enter 1024 in that field. That field is asking for the total memory to consume in MB.
May 13, 2008 at 7:52 am
if you use BOL (a MARVELOUS resource!), it will explain the max memory setting.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 13, 2008 at 7:58 am
Please let me understend or I'll go nuts!!! The Default Memory for the server is set to 2147483647 (MB) right ??.
Does it mean over 2 Billion MB ????
And if yes why so a high value ??
In SQL sever 7 it was easier!!!
I must admit that I am a bit confused.....
So you say that I should have to write 2048 instead of 2147483647 ?
...and if it is so what about the minimum value , should I have to leave it at 0 ???
Please explain me :crying:
May 13, 2008 at 8:10 am
The screen indicates the setting is in MB. So 2048 would be the number after you've got 4GB installed.
If you want to go nutz, run SSAS on the same box. It's done as a percentages. ;>)
Greg E
Viewing 15 posts - 1 through 15 (of 47 total)
You must be logged in to reply to this topic. Login to reply