Monday, March 10, 2014

Simple way to calculate the Max memory Settings for SQL Server

--Run the below script from management Studio and run the output from SP_CONFIGURE statement.
 
DECLARE
@memInMachine DECIMAL(9,2)
@memOsBase DECIMAL(9,2)
@memOs4_16GB DECIMAL(9,2)
@memOsOver_16GB DECIMAL(9,2)
@memOsTot DECIMAL(9,2)
@memForSql DECIMAL(9,2)
SET
@memInMachine = (SELECT physical_memory_in_bytes/(1024*1024*1024) FROM sys.dm_os_sys_info)

SET @memOsBase = 1
-- When memory <=16G
SET @memOs4_16GB =
CASE
WHEN @memInMachine <= 4 THEN 0
WHEN @memInMachine > 4 AND @memInMachine <= 16 THEN (@memInMachine - 4) / 4
WHEN @memInMachine >= 16 THEN 3
END
-- When memory >16G
SET @memOsOver_16GB =
CASE WHEN @memInMachine <= 16 THEN 0
ELSE (@memInMachine - 16) / 8 END
SET
@memOsTot = @memOsBase + @memOs4_16GB + @memOsOver_16GB
SET  @memForSql = @memInMachine - @memOsTot
PRINT 'Memory in machine ' + CAST(@memInMachine AS VARCHAR(10)) + ' GB.'
PRINT 'Memory for OS ' + CAST(@memOsTot AS VARCHAR(10)) + ' GB.'
PRINT 'Memory for SQL Server ' + CAST(@memForSql AS VARCHAR(10)) + ' GB. Note that this assumes one SQL Server instance!'
PRINT ' '
PRINT 'You can copy below to a query window and execute. Make sure the value is reasonable. This assumes that the OS is dedicated to one SQL Server instance!'
PRINT 'EXEC sp_configure ''max server memory'', ' + CAST(CAST(@memForSql * 1024 AS INT) AS VARCHAR(10)) + ' RECONFIGURE'
 

No comments:

Post a Comment