November 6, 2009 at 1:40 am
Hi,
Needed some help with an observation in our QA environment. When SQL service is started, the mem used by it is 800 MB. This sometimes goes up to 3.3GB, which is pretty much what is available to it (box has total of 4MB) since these are QA servers and DBs.
However when the usage drops, it never comes back to 800MB or even 1.5 GB. It stays around 3 GB itself.
Is there some concept of memory leak out here that is resulting in this behaviour.
AWE is disabled and min mem is set to 0 and max to value higher than what the QA server has.
Any advice would be much appreciated.
Regards,
Ritesh
November 6, 2009 at 1:46 am
Hi
SQL Server takes the memory it needs up to the configured max value. You should restrict the max value to about 3GB to keep some memory for OS.
SQL Server does not free any allocated memory because re-allocation is to expensive.
Greets
Flo
November 6, 2009 at 2:25 am
Florian is correct. SQL Server will release memory to the OS if there is pressure from other applications, but it will not release it on it's own. If you want to limit the memory used by SQL Server, set a maximum.
November 6, 2009 at 2:44 am
Hi,
Thanks for the replies
We take perfmon readings to check server health. Even in idle state, the available memory does not increase, should it not do so if sql service is consuming higher memory than what it needs.
If perfmon is the wrong tool, is there some other way to ascertain how much of the 3 GB is actually being used.
The fact that it is using 3GB is not an issue for us. We are just trying to ascertain what comprises of 3 GB and if some unecessary memory is being hogged by objects/temp tables.
We do not see this behaviour on another platform. There the sql server peaks at 8 GB but during idle time it comes down to 3 GB.
In both cases there is not a lot on server besides SQL so there should not be anything else that needs memory besides OS
Regards,
Ritesh
November 6, 2009 at 2:58 am
Ritesh Narain-345663 (11/6/2009)
We do not see this behaviour on another platform. There the sql server peaks at 8 GB but during idle time it comes down to 3 GB.
Never heard/saw this behavior. All SQL Server instances I've seen didn't free memory. Probably the databases on those servers are configured to auto close (I have no experiences with this feature).
November 6, 2009 at 11:15 am
On this box that peaks at 8GB - do you have AWE enabled for SQL Server? I am assuming this is a 32bit machine. If you do not have AWE enabled - then the maximum memory SQL Server can access is 2GB (unless you have /3GB enabled - which would then allow up to 3GB, but with an 8GB machine I would not have this enabled).
If this is the case, the the peak usage is caused by some other process that is outside of the SQL Server database engine. This could be attributed to SSIS or SSRS using the additional memory to process. Once those processes have completed, the memory utilized by them would be released back to the OS.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 6, 2009 at 12:47 pm
It is a 64-bit machine.
We monitor the memory used by sql server process and not just the whole machine. We also get an alert based on preconfigured thresholds and hence know for sure that it peaks.
Is there any type of memory that sql server may use that does get released? Even in our QA environment, I can see it releasing about 500 MB (going down from 3.2 - 2.7) if it stays idle for a few hours. Maybe it is because of OS requesting additional memory but do not know for sure.
Are there any other tools available to see what amount of memory is being used by sql server, how much it has not released even if it does not need etc?
Cheers,
Ritesh
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply