Calculating Max and Min Memory Settings

Here is the T-SQL we use to calculate the Max and Min memory settings for our SQL Server instances

DECLARE @curMem BIGINT,
 @maxMem BIGINT,
 @optMem BIGINT,
 @minMem fLOAT;
IF OBJECT_ID('tempdb..#msver') IS NOT NULL
DROP TABLE #msver
CREATE TABLE #msver
(
[index] int,
[name] varchar(200),
[intval] bigint,
[charval] varchar(2000)
)

IF OBJECT_ID('tempdb..#report') IS NOT NULL
DROP TABLE #report
CREATE TABLE #report
(
[name] varchar(200),
[value] bigint,
[description] varchar(2000)
)

INSERT INTO [#msver] ([Index], [Name], [IntVal], [CharVal]) EXEC [master].dbo.xp_msver;

INSERT INTO [#report] ([Name], [Value], [Description])
SELECT 'total memory', [IntVal], 'Total amount of physical memory in server'
FROM [#msver]
WHERE [Name] = 'PhysicalMemory';

INSERT INTO [#report] ([Name], [Value], [Description])
SELECT [Name], CONVERT(BIGINT,[value_in_use]), [description]
FROM [master].sys.[configurations]
WHERE [name] IN ('max server memory (MB)', 'min server memory (MB)');

INSERT INTO [#report] ([Name], [Value], [Description])
SELECT [Name], CONVERT(BIGINT,[value_in_use]), [description]
FROM [master].sys.[configurations]
WHERE [name] = 'optimize for ad hoc workloads';

SELECT @curMem = CONVERT(BIGINT, [Value]) FROM [#report] WHERE [Name] = 'max server memory (MB)';

SELECT @maxMem = CONVERT(BIGINT, [Value]) FROM [#report] WHERE [Name] = 'total memory';
PRINT 'Current Max Memory Setting: ' + CAST(@curMem AS VARCHAR(20))
PRINT 'MAX Server Memory: ' + CAST(@maxMem AS VARCHAR(20))
SET @optMem = @maxMem - (CASE WHEN @maxMem <= 16384 THEN (@maxMem / 4) ELSE 4096 + (@maxMem - 16384)/8 END);
PRINT 'Suggested Max Memory: ' + CAST(@optMem AS VARCHAR(20))
SET @minMem = @optMem * .60
PRINT 'Suggested Min Memory: ' + CAST(@minMem AS VARCHAR(20))

The Script will output the suggested values for Max and Min memory settings based on the current memory of the server.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s