T-SQL: View and Set SQL Server Maximum Memory Usage

Here’s how you can use T-SQL to set the maximum memory to use. In this example, if the server has more than 4GB of RAM, it reduces SQL Server’s usage by 1GB; if it has less than 4GB, it reduces by 512MB.

DECLARE @ServerProperties TABLE (ID INT, Name sysname, Internal_Value INT, VALUE nvarchar(512))
 
DECLARE @PhsycialServerMemory INT
DECLARE @MaximumMemoryForSql INT
 
INSERT @ServerProperties EXEC xp_msver
 
SELECT @PhsycialServerMemory = Internal_Value FROM @ServerProperties WHERE Name = 'PhysicalMemory'
 
SELECT 'Current Memory Settings'
EXEC sp_configure 'max server memory'
 
IF @PhsycialServerMemory > 4096
  SET @MaximumMemoryForSql = @PhsycialServerMemory - 1024
ELSE 
  SET @MaximumMemoryForSql = @PhsycialServerMemory - 512 
 
EXEC sp_configure 'Show Advanced Options', 1
Reconfigure
EXEC sp_configure 'max server memory', @MaximumMemoryForSql
Reconfigure
 
SELECT 'New Memory Settings'
EXEC sp_configure 'max server memory'

Hope this helps!

This entry was posted in T-SQL. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *