none
SQL Server 2016 consumes much more memory than Maximum Server Memory setting

    Question


  • Environment: SQL Server 2016 Std, using Locked Page in Memory. No SSIS/SSAS/SSRS/CLR/Linked server.

    Total server memory: 50GB

    SQL Server Maximum Server Memory set: 35GB

    sys.dm_os_process_memory - physical_memory_in_used_kb: 45GB

    Performance Monitor - Total Server Memory (KB): 35GB             

    It is strange that SQL Server is consuming 45GB, which is much higher than Max Memory i set (35GB). My questions are:

    1) Why physical_memory_in_used_kb is much higher than max memory setting, and how to know what other SQL Server processes are consuming these extra memory. I have searched and run a lot of queries but still not able to find the answer.     

    2) Why physical_memory_in_used_kb is much higher than Performance Monitor - Total Server Memory?   

    Urgently need advice from expert, thank you.                                                                        

      

    Sunday, May 19, 2019 5:47 AM

All replies

  • 1. This can happen I would quote 

    Max server memory controls SQL Server memory allocation, including the buffer pool, compile memory, all caches, qe memory grants, lock manager memory, and CLR memory (basically any “clerk” as found in dm_os_memory_clerks). Memory for thread stacks, heaps, linked server providers other than SQL Server, or any memory allocated by a “non SQL Server” DLL is not controlled by max server memory.

    So you can see SQL Server buffer pool controls most of thing but still some allocations can be outside max server memory

    2. Simply because code behind them are different. While DMV is specifically for SQl Server perfmon counter "total server memory" does not tracks "all" memory allocation so I would relay on DMV sys.dm_os_process_memory 


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Sunday, May 19, 2019 6:33 AM
    Moderator
  • Are you using OLE Automation procs (sp_OA*) or extended stored procedures? Does the instance have the -g startup option specified?

    Try the query below to see if DLLs might be the reason for the higher than expected usage:

    SELECT olm.[name], olm.[file_version], olm.[product_version], olm.[description], SUM(ova.[region_size_in_bytes])/1024 [Module Size in KB], olm.[base_address]
    FROM sys.dm_os_virtual_address_dump ova 
    INNER JOIN sys.dm_os_loaded_modules olm ON olm.base_address = ova.region_allocation_base_address
    GROUP BY olm.[name],olm.[file_version], olm.[product_version], olm.[description],olm.[base_address]
    ORDER BY [Module Size in KB] DESC;


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Sunday, May 19, 2019 12:18 PM
    Moderator
  • Memory for thread stacks, heaps, linked server providers other than SQL Server, or any memory allocated by a “non SQL Server” DLL is not controlled by max server memory.

    Heaps, really!?!

    Never noted that before.  Curious and maybe a little scary.  I've sometimes used naked heaps for ETL and such, which could just make a mess of memory if they got large, if this is true.

    Josh

    Sunday, May 19, 2019 5:49 PM
  • Memory for thread stacks, heaps, linked server providers other than SQL Server, or any memory allocated by a “non SQL Server” DLL is not controlled by max server memory.

    Heaps, really!?!

    Never noted that before.  Curious and maybe a little scary.  I've sometimes used naked heaps for ETL and such, which could just make a mess of memory if they got large, if this is true.

    Josh

    That definition was provided to me by Bob ward when I had similar confusion :)

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, May 20, 2019 6:13 AM
    Moderator
  • I wish to know is it possible to trace what are the sql queries that are consuming Memory for thread stacks, heaps, memory allocated by a “non SQL Server” DLL?
    Monday, May 20, 2019 8:13 AM
  • I wish to know is it possible to trace what are the sql queries that are consuming Memory for thread stacks, heaps, memory allocated by a “non SQL Server” DLL?
    Mostly a query running on linked server which is pointing to RDBMS which is not SQL Server, like oracle Postgresql, mysql etc. 

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, May 20, 2019 8:22 AM
    Moderator
  • That is normal and expected behavior. 

    The "max server memory" setting does not control all memory allocations in SQL Server.  

    Please see:

    https://docs.microsoft.com/en-us/sql/relational-databases/memory-management-architecture-guide?view=sql-server-2017#windows-virtual-memory-manager

    Monday, May 20, 2019 11:09 AM
    Moderator
  • My guess is that "heaps" in this context is not what we SQL Server persons thing of when we hear the word "heap". Escpecially since it sits between the word "stack". I.e., the SQL Server code uses something like https://www.geeksforgeeks.org/heap-data-structure/. I know for sure that cached pages for heap tables are in BP, i.e., "managed" memory ("managed" is in this context my own term).

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, May 22, 2019 10:48 AM
    Moderator