Today I needed to quickly check 68 SQL Server Instances Min and Max memory settings. I didn’t have time to go through each one and I know I will need to do this again in the future. Thank goodness I have my Central Management Server configured with all those servers. I was able to connect to my main CMS server, and run this simple query that will tell me all my servers min and max memory setting:
SELECT ServerName, [Max], [Min] FROM (SELECT @@ServerName AS ServerName, LEFT(name,3) AS Memory, value FROM sys.configurations WHERE name like '%server memory%') AS SourceTable PIVOT ( MAX(Value) FOR Memory IN ([Max], [Min]) ) AS PivotTable;
Then, I also used this code from my last post but added a server name to it so I could see what memory was available on each server.
SELECT @@Servername,physical_memory_kb * 9.5367431640625E-7 FROM sys.dm_os_sys_info