June 17, 2009 at 2:36 pm
we have a dedicated sql box which has 16GB RAM, /PAE and AWE setup and min/max memory is set to default (ie allow sql to use as much as it can) but when i run perfmon etc it show that only 13.2GB is actually being used. If i check task manager it says there is just over 1.5GB available.
can anyone tell me why this is? I understand that the OS and AWE will need a certain amount in order to run but why is there 1.5GB not being used?
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
June 19, 2009 at 6:19 am
anyone??
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
June 19, 2009 at 6:27 am
You need to check your Server properties and see if SQL has priority for memory or not. Also, remember that just because the memory is available, if SQL doesn't need it, it won't be using it.
So the question is, are you having performance problems related to this? Or are you just looking at the tools, expecting all of the memory to be constantly used?
June 19, 2009 at 6:33 am
sql server does not have priority:
from http://support.microsoft.com/kb/319942:
IMPORTANT Do not use priority boost for clustered servers that are running SQL Server 7.0, SQL Server 2000, or SQL Server 2005.
Buffer cache hit rate is in the high 90's and proc cache hit rate is hovering around 90%. There is the occaisonal paging going on but nothing extreme but i believe that this extra 1.5GB will be used if it were available.
It just seems a random value - 13.2GB.....
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
June 19, 2009 at 6:43 am
Check your paging file values. It could be tied up with that.
June 19, 2009 at 6:43 am
Operating system? Patch level?
June 19, 2009 at 7:08 am
How do you mean paging file values? Page file is currently set to 20GB (a bit overkill but wasn't setup by myself) and the max ever used is about 3gb.
The server is clustered (active/passive) on Windows Server EE 2003 SP2, SQL Server 2005 EE 9.0.4207 (SP3 CU1)
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
July 17, 2009 at 6:27 am
:w00t:bump:w00t:
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
July 17, 2009 at 8:35 am
You still haven't answered Brandie's question. Are you having performance issues or are you just wondering why the extra 1.5 GB of RAM isn't getting used?
July 17, 2009 at 8:48 am
i'm not having performance issues but i'm interested in knowing why a sql server that is set to consume max amount of memory allowed is only using 13.2GB when there is at least another 1.5GB available.
Page life expectancy is hovering around 800-1000 so there's definately pages being swapped in and out of memory which says to me that it could do with having more RAM available - correct me if i'm wrong.
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
July 17, 2009 at 9:07 am
I have to agree with Brandie, obviously SQL Server doesn't need all of the available memory or it would take it, potentially starving the OS if it needed all of it. With 16 GB of RAM, I'd set the maximum memory for SQL Server between 12 and 14 GB leaving 2 to 4 GB of RAM for the OS.
July 17, 2009 at 9:16 am
but i can't understand why if the page life expectancy is 800, its not trying to starve the server? If it were 8000 then i'd agree, not many pages are being swapped in and out therefore no need to take anymore ram.
I'll set the server over the weekend to take 14GB and see if it actually takes that much over next week.
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
July 17, 2009 at 10:33 am
Just realize that it may not take all 14 GB of RAM. Not saying I wouldn't set the maximum memory available to SQL Server, I would; but the best advice I could give you right now is this: If you aren't having a performance issue, either OS or SQL, don't worry about wether or not SQL Server is using all the available memory allocated to it.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply