locked
SQL Server resource pool MAX_MEMORY_PERCENT RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    • MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT

      These settings are the minimum and maximum amount of memory reserved for the resource pool that can not be shared with other resource pools. The memory referenced here is query execution grant memory, not buffer pool memory (for example, data and index pages). Setting a minimum memory value for a pool means that you are ensuring that the percentage of memory specified will be available for any requests that might run in this resource pool. This is an important differentiator compared to MIN_CPU_PERCENT, because in this case memory may remain in the given resource pool even when the pool does not have any requests in the workload groups belonging to this pool. Therefore it is crucial that you be very careful when using this setting, because this memory will be unavailable for use by any other pool, even when there are no active requests. Setting a maximum memory value for a pool means that when requests are running in this pool, they will never get more than this percentage of overall memory.

    the above is the definition from the web 

    https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor-resource-pool?view=sql-server-ver15

    but it didn't tell the MAX_MEMORY_PERCENT control what kind of memory ? buffer pool ? memory grant to query execution ?

    Wednesday, August 12, 2020 9:14 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    In the text you included you find below sentence. That answers your question. BP is *not* part of what Resource Governor governs.

    "The memory referenced here is query execution grant memory, not buffer pool memory (for example, data and index pages). "

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, August 12, 2020 9:32 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    In the text you included you find below sentence. That answers your question. BP is *not* part of what Resource Governor governs.

    "The memory referenced here is query execution grant memory, not buffer pool memory (for example, data and index pages). "

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, August 12, 2020 9:32 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi sakurai_db,

    Any update? If the reply could help you, please kindly mark the useful reply as answer. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

    Best regards,
    Cathy

    ""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 8:46 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    if I set MAX_MEMORY_PERCENT to 30 , so what's the calculation of the max memory?

    30% of what ? SQL server max memory setting ?

    Tuesday, August 25, 2020 9:46 AM