During the research I've faced two problems:
1. I had to wait several seconds to get the full result set.
2. I had to scroll down and shuffle 115 different data sets to find the counter I want.
To eliminate both these problem all these different counters have to be in the same table/data set.
That will make research easier and data can be stored or compared with the base line.
So in order to achieve that we have to parse results of "DBCC MEMORYSTATUS".
I've did a quick search over the internet and found several parsing solutions, but all of them are based on PowerShell.
There is no problem with PowerShell for me, but the whole Idea seems wrong.
It is like if I'm asking for driving direction I can get the answer only in Chinese.
Being in frustration, I've decided to make it available in native SQL language.
So, here is the script:
1. Script uses "xp_cmdshell" command, which can be disabled on your server. In case you've got to the point when you are using "DBCC MEMORYSTATUS" command you have to have enough privileges to turn it on/off. Also, there are plenty of resources in the Internet on "how to enable xp_cmdshell".
2. Within "xp_cmdshell" command I use trusted connection "-E" and default local server: "-S localhost". These parameters can be changed adjusting for your instance/environment.
3. I've tested it on SQL Server 2014, but I'm pretty sure the script is supposed to work from 2008 to 2016. For SQL Server 2005 it would require some adjustments.
4. For the Value column I've used "MONEY" data type, which is limiting amount of "Available Virtual Memory" by 838 TB. There is possibility to use BIGINT, but "Memory Broker Clerk (Buffer Pool)" returns decimal values and that might be the problem. If you'd like you can use value data type as "DECIMAL(38,6)", but that I think would be the extreme case, which you might like to catch.