locked
SQL 2012 Express - Memory Management RRS feed

  • Question

  • I have a server running Windows Server 2012 with a default instance of SQL Server 2012 Express running on it.  The server has 16GB of RAM in it and runs a number of other apps.  SQL 2012 Express is supposed to be limited to 1GB of RAM.  I have seen the server go well over 5GB of usage for the sqlserver.exe service.  A couple of days ago, I adjusted the "Maximum server memory (in MB)" to 1024 to force it to 1GB and rebooted the server.

    I just looked at this server and the sqlserver.exe service is using almost 9GB of RAM.

    What can I do to really limit this usage?  It is supposed to be capped by the software, yet it still goes way up.  I have forced a limitation in the software, yet it still goes way up.  This used to work fine in SQL 2000 MSDE.

    Any assistance would be greatly appreciated!


    My.Settings.Signature = Nothing

    Wednesday, November 20, 2013 9:00 PM

Answers

  • Hello,

    By Default, memory is managed dynamically in sql server so its default behaviour is to try and get hold of as much as possible. Max Server memory only limit the size of the SQL Server buffer pool. There are other memory allocations required outside of the buffer pool for the SQLOS and internal operations of SQL Server that allow it to be able to allocate more than max server memory.
    You can refer to the following thread about troubleshooting high memory usage:
    http://social.msdn.microsoft.com/Forums/en-US/9802159d-518c-4e7d-bef9-ae6debdd57f7/high-memory-usage-by-sql-server-and-very-slow

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click here.


    Fanny Liu
    TechNet Community Support

    • Proposed as answer by Shanky_621MVP Thursday, November 21, 2013 11:46 AM
    • Marked as answer by Fanny Liu Monday, December 2, 2013 1:42 AM
    Thursday, November 21, 2013 2:32 AM
  • Hello,

    Agree with Fanny.Even though memory configuration has changed in sql server 2012 still some memory allocations are not managed by buffer pool.Following allocations come outside the buffer pool for SQL 2012 memory( and i guess this is what causing memory beyond 1G)

    1. Memory allocations for thread stacks within SQL Server process

    2. Memory allocation requests made directly to Windows [Ex: Allocations (Heap,Virtualalloc calls ) from 3rd party Dll’s loaded in SQL Server process , objects created using OLE Automation procedures (sp_oa) etc]

     more details in below link

    http://mssqlwiki.com/2012/10/21/sql-server-2012-memory-2/

    I never look at task manager for sql server memory instaed I use below query

    select * from sys.dm_os_process_memory

    http://technet.microsoft.com/en-us/library/bb510747.aspx


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers



    • Edited by Shanky_621MVP Thursday, November 21, 2013 1:26 PM
    • Marked as answer by Fanny Liu Monday, December 2, 2013 1:42 AM
    Thursday, November 21, 2013 11:51 AM

All replies

  • while I do not know whats causing sqlserver.exe to spike to 9GB but I think the appropiate way to check the sql memory usage is using dmv..
    SELECT *
    FROM sys.[dm_os_performance_counters]  
    where counter_name like 'Target Server Memory (KB)%' or
    counter_name like 'Total Server Memory (KB)%'
    ORDER BY[object_name];
    Target Server is how much it can ideally use while Total Server is how much it is using now.  check the output of this..

    Hope it Helps!!

    Wednesday, November 20, 2013 11:11 PM
  • Hello,

    By Default, memory is managed dynamically in sql server so its default behaviour is to try and get hold of as much as possible. Max Server memory only limit the size of the SQL Server buffer pool. There are other memory allocations required outside of the buffer pool for the SQLOS and internal operations of SQL Server that allow it to be able to allocate more than max server memory.
    You can refer to the following thread about troubleshooting high memory usage:
    http://social.msdn.microsoft.com/Forums/en-US/9802159d-518c-4e7d-bef9-ae6debdd57f7/high-memory-usage-by-sql-server-and-very-slow

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click here.


    Fanny Liu
    TechNet Community Support

    • Proposed as answer by Shanky_621MVP Thursday, November 21, 2013 11:46 AM
    • Marked as answer by Fanny Liu Monday, December 2, 2013 1:42 AM
    Thursday, November 21, 2013 2:32 AM
  • Hello,

    Agree with Fanny.Even though memory configuration has changed in sql server 2012 still some memory allocations are not managed by buffer pool.Following allocations come outside the buffer pool for SQL 2012 memory( and i guess this is what causing memory beyond 1G)

    1. Memory allocations for thread stacks within SQL Server process

    2. Memory allocation requests made directly to Windows [Ex: Allocations (Heap,Virtualalloc calls ) from 3rd party Dll’s loaded in SQL Server process , objects created using OLE Automation procedures (sp_oa) etc]

     more details in below link

    http://mssqlwiki.com/2012/10/21/sql-server-2012-memory-2/

    I never look at task manager for sql server memory instaed I use below query

    select * from sys.dm_os_process_memory

    http://technet.microsoft.com/en-us/library/bb510747.aspx


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers



    • Edited by Shanky_621MVP Thursday, November 21, 2013 1:26 PM
    • Marked as answer by Fanny Liu Monday, December 2, 2013 1:42 AM
    Thursday, November 21, 2013 11:51 AM
  • I'd be tempted to have a look at the output from DBCC MEMORYSTATUS.  I would be surprised if the buffer pool (MEMORYCLERK_SQLBUFFERPOOL) was over 1GB for example.
    Thursday, November 21, 2013 2:30 PM