(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 *****************************************************