none
sql 2008 r2 / memory utilization oddity

    Question

  • I am running Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) on my Win2008 R2 workstation with 12gb of ram.  I have a sql script that continuously copies records from Tables A-D to Tables E-F for some future large full text search testing.   I run it in SSMS currently.   Task Manager physical memory keeps creeping up until 99% of all memory is used.  I then look at all processes in task manager and sort by working set memory.   SSMS32.exe and SQLServr.exe are pretty steady in the memory utilization (working set of 303,000kb for SSMS32.exe and working set of 106,000kb for SQLServr.exe).   The odd thing is the sum total of all processes don't add to the 12gb of memory that task manager is showing as being used.   Something odd is going on.   Any one else run into this?

    Friday, February 24, 2012 3:37 AM

Answers

  • If SQL Server is using Lock Pages in Memory, the buffer pool memory allocations won't show up in Task Manager.  You would have to look at the SQL Server Memory Manager\Total Server Memory counter to see what the total memory used by SQL Server is.  If you don't want SQL to use all the memory on the machine, open the Server Properties in Object Explorer and configure max server memory to lower the size of the buffer pool space for SQL.  The default configuration allows it to use as much memory as is available.

    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    • Marked as answer by scott_m Friday, February 24, 2012 3:49 AM
    Friday, February 24, 2012 3:43 AM
    Moderator

All replies

  • If SQL Server is using Lock Pages in Memory, the buffer pool memory allocations won't show up in Task Manager.  You would have to look at the SQL Server Memory Manager\Total Server Memory counter to see what the total memory used by SQL Server is.  If you don't want SQL to use all the memory on the machine, open the Server Properties in Object Explorer and configure max server memory to lower the size of the buffer pool space for SQL.  The default configuration allows it to use as much memory as is available.

    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    • Marked as answer by scott_m Friday, February 24, 2012 3:49 AM
    Friday, February 24, 2012 3:43 AM
    Moderator
  • Just wanted to add this blurb that I found in Microsoft's Troubleshooting Performance Problems in SQL 2008:

    "Address Windowing Extensions (AWE) is an API that allows a 32-bit application to manipulate physical memory beyond 4 GB memory limit. The AWE mechanism technically is not necessary on 64-bit platform. It is, however, present there. Memory pages that are allocated through the AWE mechanism are referred as locked pages on the 64-bit platform.

    On both 32-bit and 64-bit platforms, memory that is allocated through the AWE mechanism cannot be paged out. This can be beneficial to the application. (This is one of the reasons for using AWE mechanism on 64-bit platform.) This also affects the amount of RAM that is available to the system and to other applications, which might have detrimental effects. For this reason, in order to use AWE, the Lock Pages in Memory privilege must be granted for the account that runs SQL Server.

    From a troubleshooting perspective, an important point is that the SQL Server buffer pool uses AWE allocated memory; however, only database (hashed) pages can mapped or unmapped to take advantage of using additional memory allocated through AWE. Memory allocated through the AWE mechanism is not reported by Task Manager or in the Process: Private Bytes performance counter. You need to use counters that are specific to SQL Server counters or dynamic management views to obtain this information."

    http://msdn.microsoft.com/en-us/library/dd672789(v=sql.100).aspx



    • Edited by scott_m Monday, February 27, 2012 10:58 PM formatting
    Monday, February 27, 2012 10:57 PM