Wednesday, October 18, 2017

Oops. When is a #SQLServer maximum not really *the* maximum? (Workspace Memory)

When is a maximum not really the maximum?
When it's a maximum for an explicitly or implicitly modified default.
Whether "the definitive documentation" says so or not.

Yesterday on Twitter #sqlhelp this question came up.

*****
*****

Aha! I thought to myself.  For this I am purposed! To show how Perfmon and DMV data tie out!

So I responded thusly in part I...

*****
*****

And thusly in Part II...

*****
*****

To wit, I included a clever graphic in the erstwhile conclusion of my rejoinder...


Indeed the internal resource pool defies limitation by the Resource Governor; the sum of resource semaphore target_memory_kb values for that pool is the true maximum workspace memory limit for the SQL Server instance.  But is that number necessarily the same as is reported by "SQLServer:Memory Manager\Maximum Workspace Memory (KB)"?

Based on the two sources I cited, that may look to be the case.  From the Microsoft.com link above for the MemoryManager object:


But there are wrinkles.  As it turns out, the number reported in that perfmon counter is the sum of resource semaphore targets for the default pool.  Two factors can cause the default pool resource semaphores' targets to be lower than those of the internal pool: conditions in the max_memory_percent and min_memory_percent of Resource Governor pools other than the internal pool.

Consider what happens when default pool max_memory_percent is changed from 100 to 50, with min_memory_percent remaining at 0 for all other pools.  The RP_maxmem_100 pool still reports the same sum of target values as the internal pool.  Both of these pools report a total number twice as large as the default pool reports.  And its the default pool's sum of resource semaphore targets that gets reported by "SQLServer:Memory Manager\Maximum Workspace Memory (KB)".


OK, now lets bring default pool max_memory_percent back to 100.  What happens if a pool other than default has a nonzero min_memory_percent defined?  Pool RP_maxmem_100 has min_memory_percent 27 in the example below. Remember, the internal pool is unphased by design.  No other pool has a minimum memory set, so the sum of Pool RP_maxmem_100 resource semaphore targets is the as for the internal pool.  But the default pool now has a semaphore target total and "Query exec memory target" value of 100% - 27% = 73% => 73% * 192890640 = 140861040 (when rounded down to the nearest multiple of 8k).


As Paul Harvey would say:
Now you know... the rest of the story.

Ciao for now!

2 comments:

  1. If query lands thru RP_macimum_100 resource pool, what will be the maximum grant. Please elaborate

    ReplyDelete
  2. The Resource Governor classifier function assigns a workload group to a session. The workload group has an associated maximum query grant percent. The workload group is contained in one an only one resource pool, which has a maximum memory percent.

    The maximum query memory grant is the product of the relevant workload group max grant percent and the relevant resource pool max memory percent.

    The maximum grantable memory for a pool is not directly related to the maximum memory grant for a single query.

    ReplyDelete