locked
DBCC memory status Current commited RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    May i know if the current committed will also include stack memory allocation ? (which is not control by max server memory ) ?

    Tuesday, August 11, 2020 9:13 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    May I double confirm "Target Committed" = MAX server memory setting ?

    No they are not

    Target: Anything which SQL Server "thinks" it may need in future due to current workload

    Current: The current committed is one which is backed by physical memory and is being used.

    Current committed can be larger than target committed because target is calculated based on Max server memory set while current committed can increase if their is load on system and SQL Server starts aggressively using 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

    • Marked as answer by sakurai_db Tuesday, August 25, 2020 9:56 AM
    Thursday, August 13, 2020 7:35 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    This is a bit late, but perhaps it will help someone else who reads this. From seeing Available Virtual Memory of 8 TB, I can tell this is a 64 bit system - along with the absence of any references to AWE allocation.

    As Lette points out, the OS itself only has 256 MB of Available Physical Memory - but that's just what's remaining, not the total amount installed. SQL will try to use as much physical memory that's installed as possible for performance; accessing memory is by far faster than moving a disk head.

    Going by VM Committed, SQL is using 14.1 GB of physical memory going by VM Committed - I'll guess that 16 GB total of physical memory is present, accounting for OS needs, available physical memory, and 16 being a good round number.

    Memory pressure is coming from two primary areas: SQL buffer pool, and SQL Plan Cache.

    SQL Buffer Pool

    About 13.5 GB of memory is benig used for the buffer pool. Not atypical for SQL; it will try to use as much memory as it can.

    SQL Plan Cache:

    Aaccording to 11,382 ad-hoc queries query plans are cached. However, only 28 plans are in use - less than 1%. If we map this back to CACHESTORE_SQLCP, we see an interesting story - no memory is currently being used for these plans at this time, but I think at one point it had consuming 3.24 GB of memory. I must admit that I'm less sure of this, and would certainly appreciate a 2nd opinion on seeing 0 for VM Commmitted but values present for the allocators.
    Tuesday, August 11, 2020 9:19 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    May i know if the current committed will also include stack memory allocation ? (which is not control by max server memory ) ?
    Tuesday, August 11, 2020 10:16 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    May i know if the current committed will also include stack memory allocation ? (which is not control by max server memory ) ?
    Yes it will. Any memory which is committed, which means its is backed by physical memory will be shown in Committed 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


    Tuesday, August 11, 2020 10:36 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Max-44 what are you talking about and from where you got the details about CACHESTORE_SQLCP, 14 GB of physical memory 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

    Tuesday, August 11, 2020 10:38 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi sakurai_db,

    Yes.

    Because each thread has two stacks, one for the kernel mode (Kernel Mode) and the other for the user mode (User Mode). Each stack is a memory space that stores the calling address of the process or function that the thread runs, and the values of all parameters.

    You can also reference the diagram and the description from the reply for this case: sql-server-processes-sqlservexe-memory

    BR,

    Mia

    If the response helped, do "Accept Answer" and upvote it.--Mia

    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Wednesday, August 12, 2020 9:01 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi sakurai_db,

    Is the reply helpful?

    BR,

    Mia

    If the reply helped, please "Mark Answer" and upvote it.--Mia

    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Thursday, August 13, 2020 1:16 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    so which means from the DBCC memorystatus, 

    the current commited memory could be larger than target commited memory ?

    Suppose Target committed memory is control by : MAX server memory (sql server setting) 

    Thursday, August 13, 2020 2:09 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi sakurai_db,

    >the current commited memory could be larger than target commited memory ?

    Yes. It could.

    Quote from this case：what-does-it-mean-when-the-committed-and-target-values-are-the-same-when-being-displayed-using-dbcc

    The Committed being larger than the target is a sign of memory problems because SQL Server is going to trim its working set to get back down to the Target, shrinking its memory usage.

    This could be helpful: target-total-memory-is-higher-than-max-server-memory

    BR,

    Mia

    If the reply helped, please “Mark Answer” and upvote it.--Mia

    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Thursday, August 13, 2020 2:46 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    May I double confirm "Target Committed" = MAX server memory setting ?

    Thursday, August 13, 2020 3:13 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi sakurai_db,

    >May I double confirm "Target Committed" = MAX server memory setting ?

    No. They are not equal. 

    The replies from this case maybe helpful: target-server-memory-less-than-maximum-server-memory

    More information: server-memory-server-configuration-options

    BR,

    Mia

    If the reply helped, please "Mark Answer" and upvote it.--Mia

    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Thursday, August 13, 2020 6:49 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    May I double confirm "Target Committed" = MAX server memory setting ?

    No they are not

    Target: Anything which SQL Server "thinks" it may need in future due to current workload

    Current: The current committed is one which is backed by physical memory and is being used.

    Current committed can be larger than target committed because target is calculated based on Max server memory set while current committed can increase if their is load on system and SQL Server starts aggressively using 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

    • Marked as answer by sakurai_db Tuesday, August 25, 2020 9:56 AM
    Thursday, August 13, 2020 7:35 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi sakurai_db,

    Is the reply helpful?

    BR,

    Mia

    If the reply helped. please "Mark Answer" and upvote it.--Mia

    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Friday, August 14, 2020 1:04 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi sakurai_db,

    Is the reply helpful?

    BR,

    Mia

    If the reply helped. please "Mark Answer" and upvote it.--Mia

    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Monday, August 17, 2020 1:10 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    May I double confirm "Target Committed" = MAX server memory setting ?

    No they are not

    Target: Anything which SQL Server "thinks" it may need in future due to current workload

    Current: The current committed is one which is backed by physical memory and is being used.

    Current committed can be larger than target committed because target is calculated based on Max server memory set while current committed can increase if their is load on system and SQL Server starts aggressively using 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

    so worker thread memory will also be included in "Current committed"?
    Tuesday, August 25, 2020 9:58 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    anyone has the idea? 

    Target Committed should be control by the MAX Server memory setting .~I have tested. 

    So current commited : will also include worker thread memory ?

    Friday, August 28, 2020 8:38 AM