June 19, 2009 at 2:21 pm
Hi,
I have a server runnging on Windows Server 2003 32 bit and SQL Server 2005 Enterprise Edition 32 Bit. Physical memory is 8G. SQL server max memory is set up to 6.5G.
It used to work fine. Today the query is very slow against this server. I found sqlservr Mem Usage is around 140M from Processes tab in Task manager.
Why is SQL server only taking 140M Memory?
Thanks
June 19, 2009 at 2:24 pm
it's probably 64 bit cause you cant have 8GB of RAM on a 32 bit system max is 4GB.
Check index fragmentation and rebuild all your indexes.
Check disk fragmentation and defrag your HD.
June 19, 2009 at 4:58 pm
Check activity log
June 19, 2009 at 5:08 pm
If you have SQL memory set to take 6.5GB on a 32bit system I presume you have AWE enabled? In which case task manger does not correctly show memory usage. Use SQL server memory:total server memory in system monitor (perfmon)
---------------------------------------------------------------------
June 22, 2009 at 7:07 am
george sibbald (6/19/2009)
If you have SQL memory set to take 6.5GB on a 32bit system I presume you have AWE enabled? In which case task manger does not correctly show memory usage. Use SQL server memory:total server memory in system monitor (perfmon)
Yes, AWE Enabled. Task manager used to show the right "Mem usage" which is around 6.5G for SQLservr.exe.
June 22, 2009 at 8:41 am
what does the perfmon counter show? I would trust that value
---------------------------------------------------------------------
June 22, 2009 at 9:39 am
george sibbald (6/22/2009)
what does the perfmon counter show? I would trust that value
In one of my server:
Serverserver: Memory Manger Total Server memory (KB): 6287360.
June 22, 2009 at 10:25 am
Hello Judy,
It seems that you have already checked all the possibilities. however, just wanna remind you about PAE as well. Try to change that as well you might see a differene...
However, it is not a permanent fix or would resolve this issue. The permanent fix would be to upgrade your server to 64bit...if cost is not a big hindrance... 🙂
PAE can be changed by going to My computer -> properties->advanced->start and recovery->settings - Edit...
Rgds,
Pankaj
June 22, 2009 at 10:26 am
then SQL is using about 6GB of memory (for buffer cache), the value in task manager is incorrect (due to AWE being used)
---------------------------------------------------------------------
June 22, 2009 at 12:10 pm
Thanks For your reply.
Server has been set up /3GB /PAE. AWE enabled.
One system process takes 25% CPU constantly. When I checked System process details using ProcessExploer, it is called CPQFCAC contriuting to 25%. I guess this might cause the weird Mem usage in Task manager.
Our contract company is working on this CPU thing, once It is soveld, I will check if Task manager shows correct mem usage.
June 22, 2009 at 5:46 pm
Ignore Task Manager
If you have an awe enabled SQL Server instance, do not rely on Task Manager to display memory usage as it does not show the AWE memory a process is using, so the memory usage figure it presents for the SQL Server process (sqlservr.exe) will be incorrect.
http://www.eraofdata.com/blog/tag/awe-enabled/
MJ
June 23, 2009 at 6:56 am
use DBCC MEMORYSTATUS
Tim White
June 25, 2009 at 2:21 am
AlexSQLForums (6/19/2009)
it's probably 64 bit cause you cant have 8GB of RAM on a 32 bit system max is 4GB.
Sure you can. The post says SQL Server 2005 Enterprise Edition, but does not say which edition of Windows Server is being run. 32-bit Windows Server 2003 Standard Edition will let you access only 4 GB RAM; the Enterprise Edition lets you access a load more, which SQL Server 2005 Standard (or Enterprise) will do its best to use it, via AWE.
I wouldn't question that 64-bit should be better, as it can actually address all that RAM properly, whereas 32-bit has to find ways to get at it. But all our experience is with 32-bit (and up to 16 GB RAM), and we probably won't move to 64-bit until we get serious with SQL Server 2008 or its successor.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply