July 28, 2010 at 9:58 am
Hi
In the Mgmt.Studio Server configuration, i am seeing no upper limit is set on the memory configuration
and hence SS is using 7+GB of the physical 8GB memory.
The performance is slow; Perfmon shows available memory for the OS is around 120MB. I am planning
to set the upper limit for the SQL Server to 4GB so that the OS will have 50% of the physical memory for its processes. I am trying to locate some msdn doc which will shed some light on what shall be the default settings for the SQL Server memory. Common sense puts it at 50% (This is a single instance box).
1. Has anyone come across this scenario?
2. How do i see the swapping of memory which the OS is doing, where in it grabs from SQL Server and subsequenly SQL Server grabs from the OS? I will be taking a look at the %os% dynamic views but i would like to see what is happening at the OS level.
TIA
July 28, 2010 at 10:27 am
Unless something other than SQL is being used by the server, 50% to OS is too much; you're wasting memory that SQL could use. 120MB is too small though, indeed. I would personally ensure in your scenario that 1-2GB is available to the OS and cap the SQL memory appropriately. You can tweak this online as needed so it isn't a hard setting to experiment with.
As for watching the pagefile swapping, that is pretty much exclusively an OS thing so you will want to put counters into PerfMon. Look for things like page faults, pages/sec, etc.
July 28, 2010 at 11:39 am
Memory (Figures converted from bytes to MB)
Avbl.bytes 130MB
Cache Bytes 60MB
System Cache Resident bytes : 50MB
SQLServer:Buffer Manager
Buffer cache hit ratio : 99.86
Checkpoint pages/sec : 0.000
Page Reads/sec : mostly 0.000
Page writes/sec : varies from 0.000 to 2000+
Target Pages : 930686
Total Pages : 930686
Stolen Pages : stays steady at around 25000
Reserved Pages : Varies between 0 and 400 occasionally spikes to 5000,25000
I do not see any Lock Timeouts/sec , Lock Waits/sec
As per http://technet.microsoft.com/en-us/library/ms178067.aspx the max.server.memory setting to be (Stolen+Reserved)/100 which comes to ( (Stolen + Reserved) * 8k ) / 100 comes to around 4000MB.
July 28, 2010 at 11:50 am
The article says to use the values only; the extra 8k multiplication is adding too much to your calculation. I see no call for expanding the pages out in bytes. I read it strictly as (Stolen+Reserved)/100 and therefore you want instead about 500 MB which though low seems reasonable to me in theory. Then make it 1-2 GB to be safe. There is no way that the OS needs 50% of memory to run properly on an 8 GB machine unless you are doing other things besides SQL Server.
July 29, 2010 at 3:47 pm
Jeff
After looking at the Task Manager (all processes) for multiple servers, i agree with you. There are not too many OS processes running in the servers and 2 GB out of 8GB should be sufficient for the OS processes.
Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply