T-SQL query to check SQL Server min server memory(MB) and max server memory(MB)

Updated: Jan 13

As per best practice, you must set SQL Server min and max server memory(MB) as post-installation steps. SQL Server 2019 installation configures the default or recommended values as well.


How do we check the configured values for min server memory(MB) and max server memory(MB) in SQL Server?


Execute the following T-SQL statement to return the required value.


SELECT c.value, c.value_in_use
FROM sys.configurations c WHERE c.[name] = 'max server memory (MB)'
Go
SELECT c.value, c.value_in_use
FROM sys.configurations c WHERE c.[name] = 'min server memory (MB)'

As shown below, it returns the max server memory(MB) value as 2147483647 MB. It is the default value for the new SQL Server installation.


Similarly, the default configuration for min server memory(MB) is 0, i.e., dynamic value.


SQL Server min server memory(MB) and max server memory(MB)

I already set the min and max server memory configuration in my demo environment. Therefore, it returns the following values.

Non-default SQL Server min server memory(MB) and max server memory(MB)

Note: You can refer to the article Min and Max memory configurations in SQL Server Database instances for understanding the concept behind memory configurations.

75 views0 comments