Post reply

SQL2008R2 memory usage - strange issue

  • May 12, 2011 at 4:45 pm

    #237287

    Hello. I recently started a new job and am just getting acquainted with all the SQL servers that I have inherited. One in particular has me a little confused on memory usage.

    When I run the following query:

    SELECT

    total_physical_memory_kb/1024 AS 'Total Physical Mem (MB)',

    available_physical_memory_kb/1024 AS 'Available Physical Mem (MB)'

    FROM sys.dm_os_sys_memory

    I get the following results:

    Total Physical Mem (MB)Available Physical Mem (MB)

    98288 1243

    That got me thinking - why does this box need 100GB of memory, and why is it almost all used up?

    I looked at Task Manager on the box, and saw the following:

    That confirms that there is about 100GB of physical memory on the box, and almost all is used up. However, inspection of the individual processes showed the following:

    That confused me even more - there is clearly nowhere near 100GB of memory being consumed.

    Then I checked with Process Explorer - at first glance, it showed about 340MB for a SQL working set. However, then I found a "Virtual Memory" section that showed SQL has a "Virtual Size" of 102,XXX,XXX KB:

    Memory configuration in the SQL Server Properties look like they are default:

    So, what am I missing? Why is this instance of SQL consuming almost 100GB of memory but not reporting it in task manager?

  • May 12, 2011 at 4:57 pm

    #1324018

    Because Task manager lies. Never use it to check SQL's memory, use the Total Memory counter (perfmon)

    In short, task manager does not count locked pages, so if you're using AWE or have the locked pages privilege granted, task manager will be completely wrong.

    p.s. Set that max memory. Default settings are dangerous, especially combined with locked pages.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 12, 2011 at 5:07 pm

    #1324021

    Don't rely on Task Manager to check the SQL Server Memory utilization. Follow Gail's advice. She made it very clear.

    Thank You,

    Best Regards,

    SQLBuddy

  • May 13, 2011 at 12:03 pm

    #1324496

    Thanks Gail and SQLBuddy.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply