memory consumption on sql server 2008 64 bit server.
-
Hi All,
We have a production sql server 2008 sp2 enterprise edition (64 bit) running on window 2008 64 bit platform.
The total RAM for this server is 4GB.
I noticed that memory consumption of SQLSERVR is more than 2GB and the free memory is only about 200MB.
I checked some memory counters and they seem to be ok. I kept these counter for a few hours and it doesn't seem to change very much.
I understand that SQL server is designed to use as much as memory possible to keep it's own performance and it can release the memory if the other application needs it.
Since we also have some other applications running on this Windows platform, I am not sure if I need to enlarge the memory on this Windows platform (this is a VM machine, so it should not be very hard to do it) or set the 'max server memory' on this SQL server or just leave it alone.
There are no user complaining about any slow performance so far.
Could any one help?
Thanks very much
-
At a minimum, when SQL Server is the only application running on the server, I will at least leave 2 GB for the OS and any odd processes, etc. With that being said, if you are not seeing memory pressure on SQL Server then you might be ok with 2 GB allocated for SQL.
Check out this article and check out the section called Memory Bottlenecks. Also check out this thread to see if that helps a bit. Read the post from Jonathan Kehayias.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
Thanks for the reply David!
I had a look at these two links but still don't know what I should do.
I performance of SQL itself seems ok. On 64 bit version, it seems that there is no need to use AWE, which is not enabled in our system.
Just wonder how can the system works ok with just about 200mb free memory.
-
Out of curiosity, why do you think it would not work OK? I too usually leave 2GB (more on larger servers) to the OS/AV/Backup. However, in your case, SQL Server appears happy, as does the OS, right? If the OS needed memory to keep the box up, it would either take it from SQL Server (even if locked pages is enabled) or you would be seeing paging.
I'm not so sure you have a problem......
-
You're right, AWE is not required on a 64 Bit system running 64 Bit version of SQL, it could just be that what you're using that box for has not required that much memory at the moment...
-
very simple....
watch your page life expectancy if this drops below 500 then you should start adding memory.....there are other numbers to look out for as well....but look at the average....at the end of the day the higher the better...
and you are correct on the memory if the os needs more memory it will give sql a shout and sql will give some up...set you min and max memory to avoid this.....my guess is the database that is on the vm is small....just looking at the numbers....
at this point I would just set the max memory lower... and you should be ok
-
456789psw (3/18/2011)
very simple....watch your page life expectancy if this drops below 500 then you should start adding memory.....there are other numbers to look out for as well....but look at the average....at the end of the day the higher the better...
and you are correct on the memory if the os needs more memory it will give sql a shout and sql will give some up...set you min and max memory to avoid this.....my guess is the database that is on the vm is small....just looking at the numbers....
at this point I would just set the max memory lower... and you should be ok
Hi,
You are right that the database is not very big, it is about 4GB. Only about 30 people use it at the same time.
We haven't set the max memory, do you think we should?
Cheers,
Yang
-
mnDBA (3/15/2011)
Out of curiosity, why do you think it would not work OK? I too usually leave 2GB (more on larger servers) to the OS/AV/Backup. However, in your case, SQL Server appears happy, as does the OS, right? If the OS needed memory to keep the box up, it would either take it from SQL Server (even if locked pages is enabled) or you would be seeing paging.I'm not so sure you have a problem......
Hi,
Thanks for the reply. Probably I don't have a problem. I am not sure.
What I am worrying about is that there are about 200M free memory left on the server which is not what I normally see on the other servers.
The 'Max Memory' is not set on this SQL server. Just wonder if I need to set it or not.
That's all, probably I don't need to worry about it since there is no user complained.
-
kchant (3/17/2011)
You're right, AWE is not required on a 64 Bit system running 64 Bit version of SQL, it could just be that what you're using that box for has not required that much memory at the moment...Thanks for that. Probably you are right.
-
I would suggest that you set a value for the max-mem setting value to say 2 or 3 GB.
Keep in mind that max-memory refers to the buffer-pool area of memory; this is the memory where data and cached plans are stored. There is another area of memory, known as memtoleave, used for things like extended stored procedures and the SQL CLR. Leave extra space for such objects if you are making heavy use of them.
In 64-bit environments it is always advisable to set a proper limit for the max memory.
The good news is that this limit can be adjusted on the fly without having to restart your server instance.
__________________________________________________________________________________
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] -
Marios Philippopoulos (3/18/2011)
I would suggest that you set a value for the max-mem setting value to say 2 or 3 GB.Keep in mind that max-memory refers to the buffer-pool area of memory; this is the memory where data and cached plans are stored. There is another area of memory, known as memtoleave, used for things like extended stored procedures and the SQL CLR. Leave extra space for such objects if you are making heavy use of them.
In 64-bit environments it is always advisable to set a proper limit for the max memory.
The good news is that this limit can be adjusted on the fly without having to restart your server instance.
Got it. Thanks.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply