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!

perfmon "SQLServer:Resource Pool Stats(*)\Query exec memory target (KB)" ties out well with sys.dm_exec_query_resource_semaphores

SELECT opc.[object_name], opc.instance_name, 

       opc.cntr_value AS [Query exec memory target (KB)], 
       SUM(qrs.target_memory_kb) total__target_memory_kb
FROM sys.dm_os_performance_counters opc
JOIN sys.dm_resource_governor_resource_pools rp ON opc.instance_name = rp.[name]
JOIN sys.dm_exec_query_resource_semaphores qrs ON rp.pool_id = qrs.pool_id
WHERE counter_name = 'Query exec memory target (KB)'
GROUP BY rp.pool_id, opc.[object_name], opc.instance_name, opc.cntr_value
ORDER BY rp.pool_id

And the results...

SQL Server 2016 - memory broker clerk DMV ties out well with perfmon

I invest time in trying to fit pieces of memory together, to understand how various types of accounting for database memory work... and work together.

Sometimes its tricky and takes me a while :-)

But today for the first time I tried to tie out sys.dm_os_memory_broker_clerks with equivalent counters from sys.os_performance_counters - the counters we get in perfmon.  I was surprised how easily sys.dm_os_memory_broker_clerks could be reconciled with perfmon.

First, a disclaimer.  The columnstore object pool changes pretty quickly once no queries are running.  And sys.os_performance_counters lags behind sys.dm_os_memory_broker_clerks.  It seems like values are pushed from the source for sys.dm_os_memory_broker_clerks to the source for sys.os_performance_counters on a regular interval - maybe every 2 seconds.  This is most noticeable when the broker clerk for columnstore disappears entirely from sys.dm_os_memory_broker_clerks but is still present in sys.os_performance_counters for a brief time.

OK... here are the queries I used to compare...

;WITH broker_clerks AS
      (SELECT instance_name, counter_name, cntr_value 
       FROM sys.dm_os_performance_counters 
       WHERE object_name = 'SQLServer:Memory Broker Clerks')
SELECT instance_name clerk_name, 8 * [Memory broker clerk size] AS [total_kb], 
8 * [Simulation size] AS [simulated_kb],
8 * [Periodic evictions (pages)] AS [periodic_freed_kb], 
8 * [Pressure evictions (pages/sec)] AS [internal_freed_kb]
FROM broker_clerks
PIVOT (MAX (cntr_value) 
       FOR counter_name IN ([Memory broker clerk size], [Simulation size], [Periodic evictions (pages)], [Pressure evictions (pages/sec)])
       ) broker_clerks_pvt
ORDER BY instance_name;

SELECT   clerk_name, total_kb, simulated_kb, periodic_freed_kb, internal_freed_kb
FROM     sys.dm_os_memory_broker_clerks
ORDER BY clerk_name;

And this is what the results looked like...

SQL Server 2016: RING_BUFFER_RESOURCE_MONITOR memory information

Doing some additional memory work.  This is a great post from Jonathan Kehayias(@SQLPoolBoy on Twitter) on an information source that isn't discussed often.


Identifying External Memory Pressure with dm_os_ring_buffers and RING_BUFFER_RESOURCE_MONITOR
https://www.sqlskills.com/blogs/jonathan/identifying-external-memory-pressure-with-dm_os_ring_buffers-and-ring_buffer_resource_monitor/

I grabbed one of the relevant XML records, to see what other good stuff might be in there.

<Record id="13" type="RING_BUFFER_RESOURCE_MONITOR" time="209467780">

  <ResourceMonitor>

    <Notification>RESOURCE_MEMPHYSICAL_HIGH</Notification>

    <IndicatorsProcess>0</IndicatorsProcess>

    <IndicatorsSystem>1</IndicatorsSystem>

    <IndicatorsPool>0</IndicatorsPool>

    <NodeId>0</NodeId>

    <Effect type="APPLY_LOWPM" state="EFFECT_OFF" reversed="0">0</Effect>

    <Effect type="APPLY_HIGHPM" state="EFFECT_ON" reversed="1">209397985</Effect>

    <Effect type="REVERT_HIGHPM" state="EFFECT_OFF" reversed="0">0</Effect>

  </ResourceMonitor>

  <MemoryNode id="0">

    <TargetMemory>257792432</TargetMemory>

    <ReservedMemory>480359608</ReservedMemory>

    <CommittedMemory>257792432</CommittedMemory>

    <SharedMemory>0</SharedMemory>

    <AWEMemory>0</AWEMemory>

    <PagesMemory>249898840</PagesMemory>

  </MemoryNode>

  <MemoryRecord>

    <MemoryUtilization>100</MemoryUtilization>

    <TotalPhysicalMemory>268434996</TotalPhysicalMemory>

    <AvailablePhysicalMemory>4601664</AvailablePhysicalMemory>

    <TotalPageFile>272039476</TotalPageFile>

    <AvailablePageFile>7497188</AvailablePageFile>

    <TotalVirtualAddressSpace>137438953344</TotalVirtualAddressSpace>

    <AvailableVirtualAddressSpace>136957561248</AvailableVirtualAddressSpace>

    <AvailableExtendedVirtualAddressSpace>0</AvailableExtendedVirtualAddressSpace>

  </MemoryRecord>

</Record>

On my system, I didn't see any values for SharedMemory, AWEMemory, or AvailableExtendedVirtualAddressSpace.  MemoryUtilization and TotalPhysicalMemory seem like items I can forgo for now.
 
I started with Jonathan's query as a base, and after some bumping around, I worked it into this query which gathers the stuff from these XML records other than the 5 items mentioned up there.  Took me a little while to decide how to flatten out the three Effect nodes per record.  And then what really took a while for me to figure out is that each NodeId gets its own ring_buffer_address, so to uniquely identify these XML records it takes Record ID and ring_buffer_address or NodeId.

--based on a Jonathan Kehayias query found at... 
--https://www.sqlskills.com/blogs/jonathan/identifying-external-memory-pressure-with-dm_os_ring_buffers-and-ring_buffer_resource_monitor/ 

;WITH rb_XML AS
(SELECT ring_buffer_address, DATEADD (ss, (-1 * ((cpu_ticks / CONVERT (float, ( cpu_ticks / ms_ticks ))) - [timestamp])/1000), GETDATE()) AS EventTime, 
        CONVERT (xml, record) AS record
 FROM sys.dm_os_ring_buffers
 CROSS JOIN sys.dm_os_sys_info
 WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'),
rb_record AS
(SELECT rb_XML.EventTime,
        record.value('(/Record/@id)[1]', 'int') AS record_id,
        record.value('(/Record/ResourceMonitor/NodeId)[1]', 'int') AS [NodeId],
        record.value('(/Record/ResourceMonitor/Notification)[1]', 'varchar(max)') AS [Type],
        record.value('(/Record/ResourceMonitor/IndicatorsProcess)[1]', 'int') AS [IndicatorsProcess],
        record.value('(/Record/ResourceMonitor/IndicatorsSystem)[1]', 'int') AS [IndicatorsSystem],
        record.value('(/Record/ResourceMonitor/IndicatorsPool)[1]', 'int') AS [IndicatorsPool],
        record.value('(/Record/MemoryNode/TargetMemory)[1]','bigint') AS [TargetMemory_Kb],
        record.value('(/Record/MemoryNode/ReservedMemory)[1]','bigint') AS [ReservedMemory_Kb],
        record.value('(/Record/MemoryNode/CommittedMemory)[1]','bigint') AS [CommittedMemory_Kb],
        record.value('(/Record/MemoryNode/PagesMemory)[1]','bigint') AS [PagesMemory],
        record.value('(/Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS [Avail_Phys_Mem_Kb],
        record.value('(/Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') AS [Avail_VAS_Kb],
        record.value('(/Record/MemoryRecord/TotalPageFile)[1]','bigint') AS [TotalPageFile_Kb],
        record.value('(/Record/MemoryRecord/AvailablePageFile)[1]','bigint') AS [AvailablePageFile_Kb]
 FROM rb_XML),
rb_effect AS
(SELECT record.value('(/Record/@id)[1]', 'varchar(50)') AS record_id,
        rb_XML.record.value('(/Record/ResourceMonitor/NodeId)[1]', 'int') AS [NodeId],
        elem.value('(@type)[1]', 'varchar(50)') AS effect_type,
        elem.value('(@state)[1]', 'varchar(50)') AS effect_state, 
        elem.value('(@reversed)[1]', 'int') AS effect_reversed,
        elem.value('.', 'bigint') AS effect_value
 FROM rb_XML
 CROSS APPLY record.nodes('/Record/ResourceMonitor/Effect') effect(elem)),
rb__APPLY_LOWPM(record_id, NodeId, APPLY_LOWPM__state, APPLY_LOWPM__reversed, APPLY_LOWPM__value) AS
(SELECT rb_effect.record_id, NodeId, effect_state, effect_reversed, effect_value 
 FROM rb_effect
 WHERE effect_type = 'APPLY_LOWPM'),
rb__APPLY_HIGHPM(record_id, NodeId, APPLY_HIGHPM__state, APPLY_HIGHPM__reversed, APPLY_HIGHPM__value) AS
(SELECT rb_effect.record_id, NodeId, effect_state, effect_reversed, effect_value 
 FROM rb_effect
 WHERE effect_type = 'APPLY_HIGHPM'),
rb__REVERT_HIGHPM(record_id, NodeId, REVERT_HIGHPM__state, REVERT_HIGHPM__reversed, REVERT_HIGHPM__value) AS
(SELECT rb_effect.record_id, NodeId, effect_state, effect_reversed, effect_value 
 FROM rb_effect
 WHERE effect_type = 'REVERT_HIGHPM')
SELECT EventTime,
       rb_record.Record_id,
       rb_record.NodeId,
       [Type],
       [IndicatorsProcess],
       [IndicatorsSystem],
       [IndicatorsPool],
       [TargetMemory_Kb],
       [ReservedMemory_Kb],
       [CommittedMemory_Kb],
       [PagesMemory],
       [Avail_Phys_Mem_Kb],
       [Avail_VAS_Kb],
       TotalPageFile_Kb,
       AvailablePageFile_kb,
       APPLY_LOWPM__state,
       APPLY_LOWPM__reversed,
       APPLY_LOWPM__value,
       APPLY_HIGHPM__state,
       APPLY_HIGHPM__reversed,
       APPLY_HIGHPM__value,
       REVERT_HIGHPM__state,
       REVERT_HIGHPM__reversed,
       REVERT_HIGHPM__value
FROM rb_record
JOIN rb__APPLY_LOWPM ON rb_record.record_id = rb__APPLY_LOWPM.record_id AND rb_record.NodeId = rb__APPLY_LOWPM.NodeId
JOIN rb__APPLY_HIGHPM ON rb_record.record_id = rb__APPLY_HIGHPM.record_id AND rb_record.NodeId = rb__APPLY_HIGHPM.NodeId
JOIN rb__REVERT_HIGHPM ON rb_record.record_id = rb__REVERT_HIGHPM.record_id AND rb_record.NodeId = rb__REVERT_HIGHPM.NodeId
ORDER BY rb_record.record_id, rb_record.EventTime 
OPTION (MAXDOP 1);

Powershell: Retrieving external per-process memory info within SQL Server

Laerte Junior (@LaerteSQLDBA on Twitter) has a great post on the RedGate Hub.

The PoSh DBA: Solutions using PowerShell and SQL Server

https://www.red-gate.com/simple-talk/sql/database-administration/the-posh-dba-solutions-using-powershell-and-sql-server/

There's a lot of great stuff available in perfmon, and tons of SQL Server-specific stuff available in the DMVs, DMFs, etc.

But what if you've got SQL Server access without remote connect privileges to the Windows server?  Yeah, that happens sometimes. :-)

That's when executing some PowerShell via xp_cmdshell can become extremely handy.

I started with the code example in the blog post above, and expanded a bit in order to grab all of the process memory information I'm interested in.  This ought to help determine if there's something external putting unexpected memory pressure on SQL Server, or if 'max server memory' is simply set too high.  This process-level information won't answer that question alone - it'll have to be considered together with some information about SQL Server memory gathered from the DMVs and DMFs.  But this is all I have time for today :-)  Note: I'm grabbing the 64-bit measures below.  If you happen to be on 32-bit Windows, you'll want to retrieve the 32-bit equivalents from Powershell (eg WorkingSet rather than WorkingSet64).

I wanted to put this stuff in a table...

CREATE TABLE process_memory
    (capture_tm DATETIME,
     ProcessName VARCHAR(256),
     Id INT,
     PrivateMemorySize64 BIGINT,
     NonpagedSystemMemorySize64 BIGINT,
     PagedSystemMemorySize64 BIGINT,
     PagedMemorySize64 BIGINT,
     PeakPagedMemorySize64 BIGINT,
     VirtualMemorySize64 BIGINT,
     PeakVirtualMemorySize64 BIGINT,
     WorkingSet64 BIGINT,
     PeakWorkingSet64 BIGINT
     constraint pk__process_memory
         primary key clustered (capture_tm, ProcessName, Id))
WITH (data_compression = page);

And here's a stored procedure to grab the info from Powershell, shred the XML into an EAV table, then pivot into the table structure I want.

CREATE OR ALTER PROCEDURE get__process_memory
AS
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE [NAME] = 'process_memory')
BEGIN
    PRINT 'Target table process_memory does not exist, early exit.'
    RETURN
END
IF NOT EXISTS (SELECT 1 FROM sys.configurations WHERE [name] = 'xp_cmdshell' AND value_in_use = 1)
BEGIN
    PRINT 'External operations with xp_cmdshell not enabled, early exit.'
    RETURN
END

SET NOCOUNT ON
DECLARE @powershell_cmd VARCHAR(1000);
DECLARE @XML_as_String VARCHAR(MAX);
DECLARE @XML_again XML;

--create a table variable for the data to go into, preserving the order of insertion
DECLARE @XML TABLE (TheXML VARCHAR(2000), theOrder INT IDENTITY(1,1) PRIMARY KEY);
SET @powershell_cmd = '@PowerShell -noprofile -command "Get-Process | Select-Object ProcessName,Id,PrivateMemorySize64,NonpagedSystemMemorySize64,'
                      + 'PagedSystemMemorySize64,PagedMemorySize64,PeakPagedMemorySize64,VirtualMemorySize64,PeakVirtualMemorySize64,WorkingSet64,'
                      + 'PeakWorkingSet64|ConvertTo-XML -As string"';

--insert the XML into the table, line by line
INSERT INTO @XML(TheXML)
EXECUTE xp_cmdshell @powershell_cmd;

--now assemble the XML as a string in the correct order
SELECT @XML_as_String=COALESCE(@XML_as_String,'') + theXML 
  FROM @XML 
  WHERE theXML IS NOT NULL 
  ORDER BY theOrder; 
 
SELECT  @XML_again = @XML_as_String;
 
;WITH eav_cte AS
  (SELECT --shred the XML into an EAV table along with the number of the object in the collection
     [property].value('(./text())[1]', 'Varchar(32)') AS [Value],
     [property].value('@Name', 'Varchar(32)') AS [Attribute],
   DENSE_RANK() OVER (ORDER BY [object]) AS unique_object
   FROM @XML_again.nodes('Objects/Object') AS b ([object])
   CROSS APPLY b.object.nodes('./Property') AS c (property))
INSERT INTO process_memory
SELECT GETDATE() capture_tm, ProcessName, Id, PrivateMemorySize64, NonpagedSystemMemorySize64, PagedSystemMemorySize64, 
       PagedMemorySize64, PeakPagedMemorySize64, VirtualMemorySize64, PeakVirtualMemorySize64, WorkingSet64, PeakWorkingSet64
FROM eav_cte
PIVOT (MAX([Value]) FOR [Attribute] IN 
       (ProcessName, Id, PrivateMemorySize64, NonpagedSystemMemorySize64, PagedSystemMemorySize64, PagedMemorySize64,
        PeakPagedMemorySize64, VirtualMemorySize64, PeakVirtualMemorySize64, WorkingSet64, PeakWorkingSet64)) AS eav_pivot;

Excellent.  This ought to do nicely.