Laerte Junior (@LaerteSQLDBA on Twitter) has a great post on the RedGate Hub.
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...
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.
Excellent. This ought to do nicely.