Introduction
Introduction Statistics Contact Development Disclaimer Help
Return Create A Forum - Home
---------------------------------------------------------
ExcelSoft Database Professionals
https://esdbp.createaforum.com
---------------------------------------------------------
*****************************************************
Return to: Scripts
*****************************************************
#Post#: 25--------------------------------------------------
Max Memory Setting Script on SQL Server Database Node
By: srinivasma_exceldbp Date: March 18, 2014, 11:04 pm
---------------------------------------------------------
/*
Script : Max Memory Setting Script on SQL Server Database Node
Purpose:
utilisation on a 64-bit environment
This outputs sp_configure statements to set proper memory on sql
server database system
This helps dba to set memory automatically since all
calculations are carried out in the script
NOTE: This is for single instance on server.
If there are multiple instances are in the database node,
take the value from ''max server memory'','+CONVERT(VARCHAR(6),
@MaxMem)+'; and
allocate to different instances depending on the load on each
instance
Applies to: SQL 2005, SQL 2008/R2, SQL 2012, SQL 2014
*/
--=====================================================
set nocount on;
--=====================================================
declare
--@pg_size INT,
@Instancename
--@RecMem int,
@MaxMem
@MaxRamServer
@sql
@SQLVersion
--=====================================================
select @sqlversion = @@microsoftversion / 0x01000000 -- Get
major version
-- SELECT physical_memory_kb as ServerRAM_KB from
sys.dm_os_sys_info
-- SELECT @pg_size = low from master..spt_values where number =
1 and type = 'E'
-- SELECT @Instancename = LEFT([object_name],
(CHARINDEX(':',[object_name]))) FROM
sys.dm_os_performance_counters WHERE counter_name = 'Buffer
cache hit ratio'
print '-----------------------------------------------'
print 'Optimal MaxServermemory Setting for SQL Server instance '
+ @@SERVERNAME + ' (' + CAST(SERVERPROPERTY('productversion')
AS VARCHAR) + ' - ' + SUBSTRING(@@VERSION,
CHARINDEX('X',@@VERSION),4) + ' - ' +
CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')'
print '-----------------------------------------------'
--=====================================================
if @SQLVersion = 11 --** sql server 2012
begin
print '-------------------------------------------'
print 'Total Memory on the Server (MB)'
exec sp_executesql N'set @_MaxRamServer= (select
physical_memory_kb/1024 from sys.dm_os_sys_info)',
N'@_MaxRamServer int OUTPUT', @_MaxRamServer = @MaxRamServer
OUTPUT
print @MaxRamServer
print '-------------------------------------------'
end
--=====================================================
else
if @SQLVersion in (10,9) --** sql server 2008 R2,2008,2005
begin
print '--------------------------------------------'
print 'Total Memory on the Server (MB)'
exec sp_executesql N'set @_MaxRamServer= (select
physical_memory_in_bytes/1024/1024 from sys.dm_os_sys_info)',
N'@_MaxRamServer int OUTPUT', @_MaxRamServer = @MaxRamServer
OUTPUT
print @MaxRamServer
print '--------------------------------------------'
end
--=====================================================
else
begin
print 'Script only supports SQL Server 2005 or later.'
return
end
--=====================================================
--SELECT @RecMem=physical_memory_kb/1024 from sys.dm_os_sys_info
set @MaxMem =
case
when @MaxRamServer < = 1024*2
/*When the RAM is Less than or equal to 2GB*/
when @MaxRamServer < = 1024*4
/*When the RAM is Less than or equal to 4GB*/
when @MaxRamServer < = 1024*16
Ceiling((@MaxRamServer-4096) / (4.0*1024))*1024 /*When the RAM
is Less than or equal to 16GB*/
-- machines memory calculation
-- RAM= 16GB
-- Case 3 as above:- 16384 RAM-> MaxMem=
16384-1024-[(16384-4096)/4096] *1024
-- MaxMem= 12106
when @MaxRamServer > 1024*16 then @MaxRamServer - 4096 -
Ceiling((@MaxRamServer-1024*16) / (8.0*1024))*1024 /*When the
RAM is Greater than or equal to 16GB*/
end
--=====================================================
set @sql='
exec sp_configure ''Show Advanced Options'',1;
reconfigure with override;
go;
exec sp_configure ''max server memory'','+CONVERT(VARCHAR(6),
@MaxMem)+';
reconfigure with override;
go;
exec sp_configure ''Show Advanced Options'',0;
reconfigure with override;'
--=====================================================
print '---------------------------------------------'
print 'Optimal MaxServerMemory Setting for this instance of SQL'
print (@sql)
/* Do not execute the statement, print it and then execute it
once verified with the second condition as mentioned in the
comments section)*/
--EXEC (@sql);
print '----------------------------------------------'
--=====================================================
#Post#: 151--------------------------------------------------
Re: Max Memory Setting Script on SQL Server Database Node
By: Yasub_exceldbp Date: April 14, 2015, 7:42 am
---------------------------------------------------------
Srini Sir, Thank you for the script.
Can you please share the source?
Also can you please explain the calculations?
-Yasub
*****************************************************
You are viewing proxied material from gopher.createaforum.com. The copyright of proxied material belongs to its original authors. Any comments or complaints in relation to proxied material should be directed to the original authors of the content concerned. Please see the disclaimer for more details.