Get OS Counter Memory Available Mbytes Using TSQL

I have been working recently in making sure that max memory is configured for the hundred plus database servers I support.  In reading numerous books, blog post and attending various talks on memory management people reference the OS memory counter “Available Mbytes” a lot. Various references state that this value should be between 150 and 300.  This would ensure there is always available memory for the OS and applications to grab if needed.

By setting max memory in SQL Server that allows enough memory for the OS you can help make sure you don’t choke out the OS and cause a scenario where SQL and the OS are having to compete for memory.  If you are also running SSAS, SSRS, SSIS, or an application on the SQL Server then you would want to reserve even more memory for the OS.

After following some guidance on what should be the starting point of how much to allocate to SQL and how much to the OS, the next thing I needed to do was monitor ‘Available Mbytes’ OS memory counter to make sure I wasn’t still starving the OS. What I found in most scenarios is that I left plenty of memory on the table.  In this case I can increase the max memory size in SQL Server.

When I was sharing this with a couple of friends the debate broke out on how much did I leave.  I am a more reserved kind of person so I left around 1 GB in most cases.  Memory is cheap these days so I like to be a little extra careful, others would debate to trim it down further. I will continue to monitor the counter and will continue to tweak, but I honestly would be more concerned that I am starving SQL or have a badly tuned box if I had to trim the memory down so low for the OS to keep giving SQL more.

In doing all this, I wanted an easy way within SQL Server to get the memory counter and there didn’t appear to easy of a way to do so. What I found was I could easily grab this with Power Shell so I cheated and within SSMS I call Power Shell to return the value. I had to get a little creative with the output that I pull back in order to clean it up. It isn’t pretty but the following script works well.

I have validated this with 2005/2008/2008R2/2012

CREATE TABLE #output
(
AvailMbytes VARCHAR(255)
)
DECLARE @sql NVARCHAR(2000)
SET @sql = ‘powershell.exe Get-Counter ”\Memory\Available MBytes”’
INSERT  #output
EXEC xp_cmdshell @sql

UPDATE  #output

SET     AvailMbytes = REPLACE(RTRIM(LTRIM(AvailMbytes)), ‘ ‘, ”)
DELETE  #output
WHERE   AvailMbytes IS NULL
OR AvailMbytes = ”
OR AvailMbytes LIKE ‘—-%’
OR AvailMbytes LIKE ‘timestamp%’
OR AvailMbytes LIKE ‘%\\%’

SELECT  AvailMbytes
FROM    #output

DROP TABLE #output