Query To Find Out Allocated SQL Server Memory & Total OS Memory Installed On Machine

In this short blog post, you will find a simple Query which will give you the Total OS memory of the box as well as the allocated SQL server memory. Although you can see the same with GUI option, by just right click on the instance and then properties. But, someday you will definitely need this query to get the result. As GUI is best in the case of one or two servers, but what if you want to see the result for more than 2500 servers. You can simply run the below query on your repository server to get the result.

SELECT [total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB],

(SELECT value_in_use FROM sys.configurations WHERE name like '%max server memory (MB)%') as [SQL Allocated Memory_In_MB]

FROM [master].[sys].[dm_os_sys_memory]

Thanks!!