What is happening on Memory ?

  • 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

  • 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.

    Alex S
  • Check activity log

  • 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)

    ---------------------------------------------------------------------

  • 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.

  • what does the perfmon counter show? I would trust that value

    ---------------------------------------------------------------------

  • 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.

  • 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

  • then SQL is using about 6GB of memory (for buffer cache), the value in task manager is incorrect (due to AWE being used)

    ---------------------------------------------------------------------

  • 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.

  • 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

  • use DBCC MEMORYSTATUS

    Tim White

  • 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