locked
SQL Server uses much more RAM then in max server memory configured RRS feed

  • Question

  • Hello @ all

    I've got a problem with one of my database servers. Its an SQL Server 2008 R2 STandard Edition with one database running on the instance in comp. level 80 (SQL 2000).

    The Max Server memory is configured to 4GB, but after 1 - 2 Weeks running the Memory Consumption in TaskManger is over 12 GB.

    I know that the max server memory is only for the buffer pool. but when i run the following query:

    select type, single_pages_kb,multi_pages_kb,virtual_memory_reserved_kb,virtual_memory_committed_kb,awe_allocated_kb from sys.dm_os_memory_clerks
    	where virtual_memory_reserved_kb > 0
    	order by virtual_memory_reserved_kb desc

    It sais that the virtual_memory_commited_KB for the MEMORYCLERK_SQLBUFFERPOOL is over 12GB... why the hell ?

    Is ther max server memory value not working for Databases in compatiliby mode 2000 ?

    The Database size is only 4GB... so the whole database could be 3 times in the RAM by now :) any idea whats going on on my server ?

    The problem is, there's the application part running on this server too... (usually DB and application is on different servers, but not for this instance) and soon we will run again in troubles, that the application won't have enough memory and we start facing issues... any idea what i can do ?

    Thanks a lot (sorry for my bad english, not my native language) :D

    Regards, Dominic

    Monday, August 31, 2015 8:35 AM

Answers

  • The Max Server memory is configured to 4GB, but after 1 - 2 Weeks running the Memory Consumption in TaskManger is over 12 GB.

    Dominic,

    And why are you looking at task manager. Please dont refer to it you can use DMV to see SQl Server memory utilization

    select * from sys.dm_os_process_memory

    go

    select @@Version

    go

    exec sp_configure 'max server memory'

    Please post output of above queries. Please post it neatly and with proper formatting. 

    Is there only a single instance or multiple instance of SQl server ?

    Is ther max server memory value not working for Databases in compatiliby mode 2000 ?

    The Database size is only 4GB... so the whole database could be 3 times in the RAM by now :) any idea whats going on on my server ?

    The problem is, there's the application part running on this server too

    Max server memory has nothing to do with database or its compatibility level. Are you really facing issue at this time.

    max server memory only limits BPOOL as you already said but still there are direct memory allocations for COM objects, linked server, SQLCLR, MAil, backup, third party dll which occur outside BPOOL memory and they can increase SQl Server memory consumption.

    PS: Please reply to all my questions/queries

    PPS: its totally normal for SQl Server to consume as much memory as it can this is by design. It would release memory when OS flags Low memory notification. This is called dynamic memory management


    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 Article

    MVP


    Monday, August 31, 2015 8:56 AM
  • Ok I can see SQl Server using 12.5G at this time. But as you said you have kept it to 4 G so I asked to ran sp_configure query which is showing 1G. You should have changed it after posting the result

    >I changed the max memory value now to 1GB to free some memory.

    If you want to save yourself from future problems NEVER EVER do this on production server. I dont know from where you got this idea.

    Ok, change back max server memory to value it was before and then run above queries again and share the output. Please also add output from below query this will tell us what all clerks are using memory. The output of memory clerk you posted is after changing value to 1 G so it is useless.

    select type,SUM(single_pages_kb)/1024 as memory_allocated_by_SP ,
    SUM(multi_pages_kb)/1024 as memory_allocated_by_MP,
    SUM(awe_allocated_kb)/1024 as Memory_allocated_by_AWE 
    from sys.dm_os_memory_clerks
    group by type 
    order by memory_allocated_by_SP desc
    Take your time post this result tomorrow.


    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 Article

    MVP

    Monday, August 31, 2015 12:50 PM
  • These are the results of your queries:

    physical_memory_in_use_kb    large_page_allocations_kb    locked_page_allocations_kb    total_virtual_address_space_kb    virtual_address_space_reserved_kb    virtual_address_space_committed_kb    virtual_address_space_available_kb    page_fault_count    memory_utilization_percentage    available_commit_limit_kb    process_physical_memory_low    process_virtual_memory_low
    12913324            0                0                8589934464            30842748                13034488                8559091716                8329044            100                21764676            0                0

    (No column name)
    Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)   Jun 28 2012 08:36:30   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

        
    name                              minimum    maximum        config_value    run_value
    max server memory (MB)    16            2147483647    1024              1024

    I changed the max memory value now to 1GB to free some memory.

    What i can't understand is, why is SQLBUFFERPool using 12 times the memory that i set in options with Max Memory:

    type    single_pages_kb    multi_pages_kb    virtual_memory_reserved_kb    virtual_memory_committed_kb    awe_allocated_kb
    MEMORYCLERK_SQLBUFFERPOOL    0    408    18923520    1232640    0
    OBJECTSTORE_LOCK_MANAGER    38896    0    16384    16384    0
    MEMORYCLERK_SQLSTORENG    2520    33328    8832    8832    0
    MEMORYCLERK_XE_BUFFER    0    0    4224    4224    0
    MEMORYCLERK_SQLUTILITIES    80    0    120    120    0

    David is absolutely correct. 1232640 = 1232.64 MB

    There are two things to consider as well:

    1. The MAX Memory configuration settings do NOT govern all memory that SQL uses. Keep in mind that some processes like CLR (and others) are not controlled by this setting.
    2. Virtual Memory is NOT physical memory. An application could theoretically use 8 TB of Virtual memory even if there was only 4 GB of physical RAM. I would recommend reading the Windows Internals books by Mark Russinovich

    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

    Monday, August 31, 2015 4:13 PM

All replies

  • Hi Hubi,

    Check the below link

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/46a87c55-1fdc-4d7b-9022-c025d2b8146d/how-much-memory-will-be-used-by-sql-server-?forum=sqlgetstarted


    Please Dont forget to mark as answer and Helpful Post. It helps others to find relevant posts to the same question. Milan Das

    Monday, August 31, 2015 8:42 AM
  • They are talking about a 32bit system... my server is running on a 64bit system

    Update: on a 32bit system i wouldn't have this problem :)
    • Edited by Hubi8302 Monday, August 31, 2015 8:51 AM
    Monday, August 31, 2015 8:50 AM
  • The Max Server memory is configured to 4GB, but after 1 - 2 Weeks running the Memory Consumption in TaskManger is over 12 GB.

    Dominic,

    And why are you looking at task manager. Please dont refer to it you can use DMV to see SQl Server memory utilization

    select * from sys.dm_os_process_memory

    go

    select @@Version

    go

    exec sp_configure 'max server memory'

    Please post output of above queries. Please post it neatly and with proper formatting. 

    Is there only a single instance or multiple instance of SQl server ?

    Is ther max server memory value not working for Databases in compatiliby mode 2000 ?

    The Database size is only 4GB... so the whole database could be 3 times in the RAM by now :) any idea whats going on on my server ?

    The problem is, there's the application part running on this server too

    Max server memory has nothing to do with database or its compatibility level. Are you really facing issue at this time.

    max server memory only limits BPOOL as you already said but still there are direct memory allocations for COM objects, linked server, SQLCLR, MAil, backup, third party dll which occur outside BPOOL memory and they can increase SQl Server memory consumption.

    PS: Please reply to all my questions/queries

    PPS: its totally normal for SQl Server to consume as much memory as it can this is by design. It would release memory when OS flags Low memory notification. This is called dynamic memory management


    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 Article

    MVP


    Monday, August 31, 2015 8:56 AM
  • These are the results of your queries:

    physical_memory_in_use_kb    large_page_allocations_kb    locked_page_allocations_kb    total_virtual_address_space_kb    virtual_address_space_reserved_kb    virtual_address_space_committed_kb    virtual_address_space_available_kb    page_fault_count    memory_utilization_percentage    available_commit_limit_kb    process_physical_memory_low    process_virtual_memory_low
    12913324            0                0                8589934464            30842748                13034488                8559091716                8329044            100                21764676            0                0

    (No column name)
    Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)   Jun 28 2012 08:36:30   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

        
    name                              minimum    maximum        config_value    run_value
    max server memory (MB)    16            2147483647    1024              1024

    I changed the max memory value now to 1GB to free some memory.

    What i can't understand is, why is SQLBUFFERPool using 12 times the memory that i set in options with Max Memory:

    type    single_pages_kb    multi_pages_kb    virtual_memory_reserved_kb    virtual_memory_committed_kb    awe_allocated_kb
    MEMORYCLERK_SQLBUFFERPOOL    0    408    18923520    1232640    0
    OBJECTSTORE_LOCK_MANAGER    38896    0    16384    16384    0
    MEMORYCLERK_SQLSTORENG    2520    33328    8832    8832    0
    MEMORYCLERK_XE_BUFFER    0    0    4224    4224    0
    MEMORYCLERK_SQLUTILITIES    80    0    120    120    0

    Monday, August 31, 2015 12:19 PM
  • Ok I can see SQl Server using 12.5G at this time. But as you said you have kept it to 4 G so I asked to ran sp_configure query which is showing 1G. You should have changed it after posting the result

    >I changed the max memory value now to 1GB to free some memory.

    If you want to save yourself from future problems NEVER EVER do this on production server. I dont know from where you got this idea.

    Ok, change back max server memory to value it was before and then run above queries again and share the output. Please also add output from below query this will tell us what all clerks are using memory. The output of memory clerk you posted is after changing value to 1 G so it is useless.

    select type,SUM(single_pages_kb)/1024 as memory_allocated_by_SP ,
    SUM(multi_pages_kb)/1024 as memory_allocated_by_MP,
    SUM(awe_allocated_kb)/1024 as Memory_allocated_by_AWE 
    from sys.dm_os_memory_clerks
    group by type 
    order by memory_allocated_by_SP desc
    Take your time post this result tomorrow.


    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 Article

    MVP

    Monday, August 31, 2015 12:50 PM
  • >MEMORYCLERK_SQLBUFFERPOOL    0    408    18923520    1,232,640    0

    =1,203 MB

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, August 31, 2015 3:09 PM
  • >MEMORYCLERK_SQLBUFFERPOOL    0    408    18923520    1,232,640    0

    =1,203 MB

    David


    David http://blogs.msdn.com/b/dbrowne/


    Actually David this is value he obtained after changing max server memory to 1 G so of course memory allocation as tracked by clerks would have changed that is why we have 1 G approx.

    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 Article

    MVP

    Monday, August 31, 2015 4:09 PM
  • These are the results of your queries:

    physical_memory_in_use_kb    large_page_allocations_kb    locked_page_allocations_kb    total_virtual_address_space_kb    virtual_address_space_reserved_kb    virtual_address_space_committed_kb    virtual_address_space_available_kb    page_fault_count    memory_utilization_percentage    available_commit_limit_kb    process_physical_memory_low    process_virtual_memory_low
    12913324            0                0                8589934464            30842748                13034488                8559091716                8329044            100                21764676            0                0

    (No column name)
    Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)   Jun 28 2012 08:36:30   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

        
    name                              minimum    maximum        config_value    run_value
    max server memory (MB)    16            2147483647    1024              1024

    I changed the max memory value now to 1GB to free some memory.

    What i can't understand is, why is SQLBUFFERPool using 12 times the memory that i set in options with Max Memory:

    type    single_pages_kb    multi_pages_kb    virtual_memory_reserved_kb    virtual_memory_committed_kb    awe_allocated_kb
    MEMORYCLERK_SQLBUFFERPOOL    0    408    18923520    1232640    0
    OBJECTSTORE_LOCK_MANAGER    38896    0    16384    16384    0
    MEMORYCLERK_SQLSTORENG    2520    33328    8832    8832    0
    MEMORYCLERK_XE_BUFFER    0    0    4224    4224    0
    MEMORYCLERK_SQLUTILITIES    80    0    120    120    0

    David is absolutely correct. 1232640 = 1232.64 MB

    There are two things to consider as well:

    1. The MAX Memory configuration settings do NOT govern all memory that SQL uses. Keep in mind that some processes like CLR (and others) are not controlled by this setting.
    2. Virtual Memory is NOT physical memory. An application could theoretically use 8 TB of Virtual memory even if there was only 4 GB of physical RAM. I would recommend reading the Windows Internals books by Mark Russinovich

    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

    Monday, August 31, 2015 4:13 PM
  • >MEMORYCLERK_SQLBUFFERPOOL    0    408    18923520    1,232,640    0

    =1,203 MB

    David


    David http://blogs.msdn.com/b/dbrowne/


    Actually David this is value he obtained after changing max server memory to 1 G so of course memory allocation as tracked by clerks would have changed that is why we have 1 G approx.

    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 Article

    MVP


    I don't see where he posted any other values. I agree though that this is after the change. He had mentioned that it is still using 12x the amount and it doesn't look that way from the numbers he posted.

    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

    Monday, August 31, 2015 4:16 PM
  • Daniel,

    Please read complete thread he said he changed max server memory to 1 G. Considering this I have asked him to post result again


    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 Article

    MVP

    Monday, August 31, 2015 5:21 PM