How to Parse DBCC MEMORYSTATUS via Powershell
SQL Server has many ways to dig deep into diagnosing memory related problems. Today it is common to use Dynamic Management Objects (views/functions) to expose a large portion of this information. However, some useful data isn't consumable by DMVs, so we must use DBCC MEMORYSTATUS to get what we need. The roadblock output from DBCC MEMORYSTATUS isn't easily consumable and query-able. So, my goal of this blog post is to create a Powershell script to load the output from the DBCC command into a local database table so that you can query/report off of it.
Note: While you CAN dump the contents from DBCC MEMORYSTATUS into a temp table on the server using INSERT...EXEC, an important aspect of the data isn't inserted into the temp table.
The script accepts 3 different parameters:
$FileLocation - if you have the DBCC MEMORYSTATUS output in a file, specify the location here. Otherwise the script will grab the output from DBCC MEMORYSTATUS from the instance you connect to.
$SQLServer - this is the SQL instance to connect and load the output from DBCC MEMORYSTATUS.
$DBName - this is the name of the database you want the DBCC MEMORYSTATUS output stored.
I've attached the file instead of pasting the output here as I couldn't get the formatting how I wanted it to be. If I get it corrected I'll make the change a later.
Once you've executed the script, you'll have output very similar to the following, which will allow you to run any reports as needed.
I hope you enjoy, and I'd love to hear comments as to how to improve the script!
Thanks,
Tim