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.