November 5, 2010 at 10:05 am
Greetings,
I just inherited a SQL Server 2005 server and I would like determine if the memory is configured correctly. This is a dedicated box with no other applications.
OS-W2K3 R2 32 bit
Memory available to windows-3,931,592
SQL Server 2005 Standard SP2
SQL Server min-0
SQL Server max-2147483647
Is there anything else I should check or be concerned with?
November 5, 2010 at 10:29 am
Since this is a dedicated SQL Server, the memory allocation is fine being set as default. Other things to look at are backups of the databases especially the transaction log files. Check to see if the indexes are getting rebuilt and/or reorganized. Take a look at your Job Activity Monitor to see what is happening with all of the scheduled jobs (is there room for tweaking for effeciency). And so much more.
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
November 5, 2010 at 10:45 am
There is a maintenance plan so backups look good. Indexes are being rebuilt as well.
>>Since this is a dedicated SQL Server, the memory allocation is fine being set as default.
So will SQL Server negotiate with the OS to see how much memory to use? How can I tell how much memory SQL Server is actually using?
November 5, 2010 at 11:44 am
Your memory is being allocated dynamically, on a server with only 4GB memory this is what you want.
Use task manager to see amount of memory it is using (sqlservr.exe), or use system monitor and check out sql server:memory counters total server memory and target server memory.
If memory is about 1.6 - 1.8GB (which I would expect) consider adding the /3GB switch to your boot.ini file and restarting server. Monitor the server afterwards though, in most cases the extra memory SQL will be allowed to use is a good thing, but its not guaranteed.
---------------------------------------------------------------------
November 5, 2010 at 12:30 pm
sqlserver.exe is using 2.8 gb and /3gb is in the boot file. It would seem this is properly configured then.
>>Monitor the server afterwards though, in most cases the extra memory SQL will be allowed to use is a good thing, but its not guaranteed.
Interesting. In what types of situations would this be a bad thing?
November 5, 2010 at 1:44 pm
well firstly I never advise on major configuration changes without caveats, after all I won't be the one to suffer any consequences.
The /3GB switch reduces memory available to the OS, so increased potential of starving OS.
If you have other components of SQL such as SSRS and SSAS they would have less memory.
If you had some software with a memory leak (AV comes to mind here) you would be more likely to be affected by this.
the /3Gb switch only allows SQL to increase the size of the buffer pool, it does not allow more memory for such things as locks or stored proc cache, so if your memory squeeze is there, it won't help.
In almost all cases it is good for performance though and if you have no problems with your existing configuration, you have the best set up you are going to get with the memory you have available to you.
---------------------------------------------------------------------
November 5, 2010 at 3:56 pm
Thanks you for the clarification. over and out...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply