max server memory

Last Post 11 Feb 2011 05:17 AM by rm. 6 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
ctseah
New Member
New Member

--
08 Feb 2011 07:19 PM
Hi,

The server has a physical memory of 2GB.
Currently the output of sp_configure is showing this values for min server memory and max server memory :

                                             min          max    configure_value    run_value
min server memory(MB)        0             2GB             0                         0
max server memory(MB)      16MB       2GB             2GB                     2GB

Please advise whether I should do some configuration by using sp_configure so sql server process will not use up all the memory available. Also advise how to go about setting it, what commands to run.

thanks in advance.

regards
rm
New Member
New Member

--
09 Feb 2011 05:33 AM
I'll set max memory to 1.5gb if it's dedicated sql server.
ctseah
New Member
New Member

--
09 Feb 2011 06:53 PM
Hi,

Thanks,
May I ask why when I use the window task manager to check, the sql server is using about 1.3GB only ?
This sql databases are heavily used by the application, just wonder is there is a way to check exactly how much memory the sql server is using by running commands ?
Or could it be there it actually using more than 1.3GB that the window task manager is showing, perhaps the swapspace used from the harddisk was not included in the 1.3GB ?

Another question is whether I can use the GUI to set the max server memory, from the studio management, under the sql server instance property, there is a tab place for me to change the max server memory ? Do I need to restart the sql service to effect this setting ?

thanks in advance
gunneyk
New Member
New Member

--
10 Feb 2011 06:32 AM
You can look at the perfmon counters for sql server target and total memory but that is for the buffer pool and there could be a few hundred MB's used on top of that as well. So the Max Memory is only for the buffer pool. 2GB is not a lot of memory for SQL Server these days but it might be fine if you only use a small portion of data. Tassk manager is not a good tool to use for looking at memory usage in SQL Server. But if SQL Server really is only using 1.3GB it simply may not have read more data than that. But my guess is that there are other apps using memory on the box and SQL has given some back to the OS for the other apps.
rm
New Member
New Member

--
10 Feb 2011 07:42 AM
By the way, max memory just tells sql will use upto that amount of memory but not necessary guarantee to get them.
ctseah
New Member
New Member

--
10 Feb 2011 07:25 PM
hi,

BTW does changing of the max server memory takes effective immediately or I need to restart the sql service for the new value to take effect ?

regards
rm
New Member
New Member

--
11 Feb 2011 05:17 AM
Don't need restart sql service, but may take a while to release memory if sql uses more than new setting.


Acceptable Use Policy
---