(DIR) Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
 (HTM) https://esdbp.createaforum.com
       ---------------------------------------------------------
       *****************************************************
 (DIR) 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
       *****************************************************