Tag: database

-- MinMaxMemory.sql


-- 1 get the physical memory on the server

 DECLARE @PhyMem INT =
 (
   SELECT total_physical_memory_kb / 1024 
   FROM sys.dm_os_sys_memory
 );


-- 2 get the current min/max-memory settings

 DECLARE @MinMem INT =
 (
   SELECT CONVERT(INT, c.value)
   FROM sys.configurations c
   WHERE c.[name] = 'min server memory (MB)'
 );

 DECLARE @MaxMem INT =
 (
   SELECT CONVERT(INT, c.value)
   FROM sys.configurations c
   WHERE c.[name] = 'max server memory (MB)'
 );


-- 3 calculate the new settings (min = 20%, max = 90% 
--   rounded down to nearest 1024)

 DECLARE @NewMinMem INT = @PhyMem / 100 * 20
 / 1024 * 1024;

 DECLARE @NewMaxMem INT = @PhyMem / 100 * 90 
 / 1024 * 1024;

 SELECT @PhyMem PhysicalMemory,
        @MinMem MinMemory,
        @NewMinMem NewMinMemory,
        @MaxMem MaxMemory,
        @NewMaxMem NewMaxMemory; -- save 4 rollback


-- 4 change min/max-memory settings

 EXEC sys.sp_configure N'show advanced options',N'1';
 RECONFIGURE WITH OVERRIDE;

   EXEC sys.sp_configure N'min server memory (MB)', 
   @NewMinMem;
   RECONFIGURE WITH OVERRIDE;

   EXEC sys.sp_configure N'max server memory (MB)', 
   @NewMaxMem;
   RECONFIGURE WITH OVERRIDE;

 EXEC sys.sp_configure N'show advanced options',N'0';
 RECONFIGURE WITH OVERRIDE;
 GO