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.

*****

#sqlhelp Anyone have DEFINITIVE documentation on what the MemoryManager.Maximum Workspace Memory counter indicates (grant-able mem right?)

— Michael K. Campbell (@AngryPets) October 17, 2017

*****

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...

*****

#sqlhelp i'm never sure what definitive documentation is :-)
https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-memory-manager-object
http://sqlmag.com/sql-server/minding-memory
1/

— L_ N___ (@sqL_handLe) October 18, 2017

*****

And thusly in Part II...

*****

2/2 to tie out measures keep in mind:
-internal resource pool can't be restricted so its max is true max
-2 semaphores in each pool
#sqlhelp pic.twitter.com/ASY9XmzgUo

— L_ N___ (@sqL_handLe) October 18, 2017

*****

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!