Compare SQL Server 'Max Server Memory' with the Physical Server Memory of the Windows Server

Ideally, the SQL Server's max server memory settings should be configured at 80% of the total phyical memory of the Windows Server. That will ensure the OS has enough memory to run all the other processes besides the SQL. If the SQL Server's max server memory is assigned to a value higher than 80% of the total server memory, SQL captures all the memory within its limit when needed and the OS starts feeling the pressuere. If you have any monitoring tools to page you when the available server memory falls below 5%, we will get these annoying alerts.

All that is needed in this case is properly configure the SQL's max server memory to not more than 80% of the server memory. What if you needed to run this check across all the Instances in your company? The below query will give you a quick snapshot of those details in terms of percentage. This query captures the SQL settings from the system table called 'sys.configurations' and physical server memory details from the dmv called 'sys.odm_os_sys_info'. Run this query in Central Management Server and it will give you some valuable information.

Use the below query for SQL 2005, 2008, 2008 R2:

declare @mem1 int

declare @mem2 int

select @mem1 = (select (physical_memory_in_bytes/1048576) as server_memory_in_MB from sys.dm_os_sys_info)

select @mem2 =(select convert(decimal,value) from sys.configurations where name='max server memory (MB)')

select @mem2 as SQL_Max_Memory, @mem1 as Physical_Server_Memory,

cast((cast(@mem2 as float)/ cast(@mem1 as float)) as decimal(4,2))*100 as SQL_Physical_memory_ratio

For SQL 2012 and above use the below query:

declare @mem1 int
declare @mem2 int
select @mem1 = (select (physical_memory_kb/1024) as server_memory_in_MB from sys.dm_os_sys_info)
select @mem2 =(select convert(decimal,value) from sys.configurations where name='max server memory (MB)')
select @mem2 as SQL_Max_Memory, @mem1 as Physical_Server_Memory,
cast((cast(@mem2 as float)/ cast(@mem1 as float)) as decimal(4,2))*100 as SQL_Physical_memory_ratio