Sql-server – Memory continually being eaten up since I had SQL Server 2014 SSMS installed

Looking at your RAM availablity for this particular server and you are running several databases ranging from 30GB to 5 GB, you definitely need more RAM on this server.

You have not mentioned that this is a stand alone instance or this server is having more than one instance of sql server running.

Your MAX Memory settings seems OK for a server having 8GB RAM. See these suggested best practice settings from Glenn Berry.

I would highly recommend you to do a baseline of your environment using below PERFMON counters to get a good value of your memory configuration :

• SQL Server:Buffer Manager\Page Life Expectancy
• SQL Server:Buffer Manager\Page reads/sec
• Physical Disk\Disk Reads/sec
• Memory\Available Mbytes
• SQL Server: Memory Manager - Total Server Memory
• SQL Server: Memory Manager - Target Server Memory

Total Server Memory: Amount of memory currently allocated to Buffer Pool and not the total amount of memory to SQL Server

Target Server memory: Ideal Size of the buffer pool corresponding to max memory for the instance.

Note: If Total Server Memory > Target Server memory, then it suggests memory pressure.

Below script will help you find LOW or HIGH Memory notifications from sys.dm_os_ring_buffers - system health session :

SELECT CONVERT (varchar(30), GETDATE(), 121) as [RunTime],
dateadd (ms, (rbf.[timestamp] - tme.ms_ticks), GETDATE()) as [Notification_Time],
cast(record as xml).value('(//Record/ResourceMonitor/Notification)[1]', 'varchar(30)') AS [Notification_type],
cast(record as xml).value('(//Record/MemoryRecord/MemoryUtilization)[1]', 'bigint') AS [MemoryUtilization %],
cast(record as xml).value('(//Record/MemoryNode/@id)[1]', 'bigint') AS [Node Id],
cast(record as xml).value('(//Record/ResourceMonitor/IndicatorsProcess)[1]', 'int') AS [Process_Indicator],
cast(record as xml).value('(//Record/ResourceMonitor/IndicatorsSystem)[1]', 'int') AS [System_Indicator],
cast(record as xml).value('(//Record/MemoryNode/ReservedMemory)[1]', 'bigint') AS [SQL_ReservedMemory_KB],
cast(record as xml).value('(//Record/MemoryNode/CommittedMemory)[1]', 'bigint') AS [SQL_CommittedMemory_KB],
cast(record as xml).value('(//Record/MemoryNode/AWEMemory)[1]', 'bigint') AS [SQL_AWEMemory],
cast(record as xml).value('(//Record/MemoryNode/SinglePagesMemory)[1]', 'bigint') AS [SinglePagesMemory],
cast(record as xml).value('(//Record/MemoryNode/MultiplePagesMemory)[1]', 'bigint') AS [MultiplePagesMemory],
cast(record as xml).value('(//Record/MemoryRecord/TotalPhysicalMemory)[1]', 'bigint') AS [TotalPhysicalMemory_KB],
cast(record as xml).value('(//Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS [AvailablePhysicalMemory_KB],
cast(record as xml).value('(//Record/MemoryRecord/TotalPageFile)[1]', 'bigint') AS [TotalPageFile_KB],
cast(record as xml).value('(//Record/MemoryRecord/AvailablePageFile)[1]', 'bigint') AS [AvailablePageFile_KB],
cast(record as xml).value('(//Record/MemoryRecord/TotalVirtualAddressSpace)[1]', 'bigint') AS [TotalVirtualAddressSpace_KB],
cast(record as xml).value('(//Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') AS [AvailableVirtualAddressSpace_KB],
cast(record as xml).value('(//Record/@id)[1]', 'bigint') AS [Record Id],
cast(record as xml).value('(//Record/@type)[1]', 'varchar(30)') AS [Type],
cast(record as xml).value('(//Record/@time)[1]', 'bigint') AS [Record Time],
tme.ms_ticks as [Current Time]
FROM sys.dm_os_ring_buffers rbf
cross join sys.dm_os_sys_info tme
where rbf.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR' 
--and cast(record as xml).value('(//Record/ResourceMonitor/Notification)[1]', 'varchar(30)') = 'RESOURCE_MEMPHYSICAL_LOW'
ORDER BY rbf.timestamp ASC

Some good references :

• How much memory does my SQL Server actually need? - by Jonathan Kehayias
• Performance Tuning using the Waits and Queue
• Great SQL Server Debates: Lock Pages in Memory

• In-Memory is for making your heavily accessed table as memory optimized.
• For a memory optimized table, all data is stored in memory and hence unlike disk based tables, pages don't need to be brought into the buffer pool or cache.

• In order to create a memory optimized table, you need to create a database with a filestream filegroup (along with the use of CONTAINS MEMORY_OPTIMIZED_DATA)

  -- enable for in-memory OLTP - change file path as needed 
  ALTER DATABASE imoltp ADD FILEGROUP imoltp_mod CONTAINS MEMORY_OPTIMIZED_DATA 
  ALTER DATABASE imoltp ADD FILE (name='imoltp_mod1', filename='c:\data\imoltp_mod1') TO FILEGROUP imoltp_mod 
  

  GO

Now for creating memory optimized tables you have to create table with WITH (MEMORY_OPTIMIZED=ON)

Getting Started with SQL Server 2014 In-Memory OLTP will give you all the detailed steps to get you started.

For more details, check this BOL article : In-Memory OLTP (In-Memory Optimization)

Update:

@cat you have to follow 3 step process

1. Determining if a Table or Stored Procedure Should Be Ported to In-Memory OLTP ,
2. Determining if a disk based Table or Stored Procedure Should Be Ported to In-Memory OLTP -- informs you about which tables in your database will benefit if ported to use In-Memory OLTP and finally
3. After you identify a table that you would like to port to use In-Memory OLTP, you can use the memory optimization advisor to help you migrate the disk-based database table to In-Memory OLTP.