May 13, 2008 at 8:10 am
Please let me understend or I'll go nuts!!! The Default Memory for the server is set to 2147483647 (MB) right ??
Yes, that is the default that it installs with.
Does it mean over 2 Billion MB ??
2147483647 = 2048 TB
And if yes why so a high value ??
Good question - I hope someone else can answer this since it doesn't make any sense to me either.
So you say that I should have to write 2048 instead of 2147483647?
If you are looking to have SQL Server use 2GB of memory, yes you need to enter 2048. Since you only have 2GB of memory in the server, that will cripple the server. You need to add more memory to take the total to 4GB at a bare minimum.
...and if it is so what about the minimum value , should I have to leave it at 0???
Minimum memory can be left at 0. If I recall correctly, that would be used only if SQL Server was sharing resources on this server with other applications.
May 13, 2008 at 8:19 am
Ok.. Tks I've got it !!..
What remains is the abnormal high value of the default setting ..it dos not make any sense!!! :crazy:
Since I've planned to add 2 more GB RAM ( for a total of 4GB) I'm gonna set the default max value to 2048 and the min to "0".
Hope I'going to do the right thing!!! 😎
Tks
May 13, 2008 at 8:24 am
What remains is the abnormal high value of the default setting ..it dos not make any sense!!! :crazy:
No, it doesn't make any sense. I'm hoping someone else watching this conversation will chime in with why MS decided on that value. I can only assume MS is making the assumption that when you install SQL Server, you want it to use ALL memory in the machine (which is a bad idea since they do need to save some for the OS). Personally, I think that should be changed to say something like "Use all memory except for the following value in MB" since that is generally how this value is determined anyhow.
It looks like your final decision to set the value at 2048 and install an additional 2GB of RAM is the correct choice.
May 13, 2008 at 8:29 am
At this point a "rule of thumb" could be: set the maximum memory for the server to half the total RAM you have onboard... Could it be a goode guide ??
May 13, 2008 at 8:35 am
Not necessarily. There are good resources on MS's website that will help you determine the max memory that SQL Server should be set to. Generally, a system up to 8GB, you could allocate 2GB less than total memory. Up to 32GB, allocate 3GB less than total memory. Up to 64GB, 4GB less than total.
These values are very generalized and have worked for me. However, there are other things to consider such as other services running on the server. SSIS, SSRS, SSAS, just to name a few, do require memory of their own so you would need to set the max memory appropriately to allow those services what they need.
I would strongly suggest searching for articles related to memory usage and SQL Server. There is a wealth of information that will help you make these decisions as you scale up.
May 13, 2008 at 8:38 am
OK I'm gonna look for that..But in the meantime : what would you suggest for a server with 4GB RAM onboard?????
May 13, 2008 at 8:42 am
I mean , what memory would you allocate for my server with 4GB RAM onboard ?? (my databases size is approximately 2 GB)
TIA
May 13, 2008 at 8:42 am
Generally, 2048 should be fine.
Do you have anything else running on the server? How about any of the additional SQL Server services (SSIS, SSRS, SSAS)?
May 13, 2008 at 8:48 am
coastliner (5/13/2008)
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:
2147483647 is a meaningless number, sort of a ceiling value that will never be attained.
As someone mentioned earlier, add 2 GB RAM to your server, so it gets up to 4 GB total, and allocate 2 GB for the SQL. If yours is a 32-bit box, the highest you can get in terms of RAM is 3 GB (if you enable something called the 3GB switch), unless you enable AWE (in that case, you can in principle take up any amount of the memory available on your machine, but for the data cache only; other caches do not take advantage of AWE).
So to sum up:
Add 2 GB RAM to your box
Leave the setting to the default, ridiculously high value, if yours is a 32-bit box. (It makes no difference as SQL will use only up to 2 GB).
Leave min value to 0.
Let me know if you need more details.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 13, 2008 at 8:55 am
I have the following running services
"SQL server",
"SQL server Agent",
"SQL Server Analisys Service",
"SQL Server Full text Search",
"SQL Server Integration Services",
"SQL Server Reporting Services",
"SQL Server VSS Writer"
Plus an old versin of SQLBase ( for previous old software)
May 13, 2008 at 8:59 am
If you are using all of those services, you need to take your total installed RAM to 8GB. 4GB is not enough to run all of those services. 8GB may be tight as well. I assume you'll be growing the database and adding more functionality?
As the previous poster mentioned, if you are running 32-bit, you'll need to enable AWE to address the extra memory. If you are running 64-bit, the memory will be automatically used without having to do anything.
May 13, 2008 at 9:03 am
Coastliner, are you actually using all the services? or have they been installed by someone who clicked every available option on the install and then just left them running? If thats the case then you should disable the services that are not needed.
May 13, 2008 at 9:06 am
Animal Magic (5/13/2008)
Coastliner, are you actually using all the services? or have they been installed by someone who clicked every available option on the install and then just left them running? If thats the case then you should disable the services that are not needed.
Very good point! It does look as if the person installing the SQL did not bother to select what to install, but installed everything.
By all means, if some of the services are not being used, disable or even uninstall them. They take up valuable resources that you cannot afford to give up.
I suspect Full-Text Search is one... 🙂
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 13, 2008 at 9:17 am
I monitored with sysinternal Process Explorer and discovered that , apart the Main SQL Service, the other does not take as much memory, and I think I could disable some of them without problem.
For the moment I Want to try with 4 GB RAM.
BTW 4GB is not the maximum adressable memory space for a 32bit system ???????
May 13, 2008 at 9:20 am
coastliner (5/13/2008)
I monitored with sysinternal Process Explorer and discovered that , apart the Main SQL Service, the other does not take as much memory, and I think I could disable some of them without problem.For the moment I Want to try with 4 GB RAM.
BTW 4GB is not the maximum adressable memory space for a 32bit system ???????
The maximum adressable memory space for a 32bit system (without enabling the 3-GB switch and without AWE) is 2 GB.
If you enable the 3-GB switch, then your max memory used by SQL will go up to 3 GB.
You can do this if there are no other apps - apart from SQL Server - running on your machine.
Leave at least 1 GB for OS use.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 15 posts - 16 through 30 (of 47 total)
You must be logged in to reply to this topic. Login to reply