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 :
The memory counters in Task Manager or Resource Monitor are not a good way to determine SQL Server's memory usage. If SQL Server is using locked pages, these do not show in the working set or private bytes.
There are many ways to determine if SQL Server is using locked pages. You can look for a message like the following in the error log:
Using locked pages in the memory manager
Or, check the sys.dm_os_process_memory DMV locked_page_allocations_kb column. For example, on a local test instance using locked pages I see this:
Whereas Resource Monitor shows:
Note this is different from the sys.dm_os_sys_memory DMV you mention in the question comments.
There is all sorts of other useful memory usage information in the DMVs, including high-or-low virtual or physical memory indications. These are all described in Books Online.
It seems probable that someone has granted the SeLockMemoryPrivilege to the SQL Server start-up account recently, or the account has been changed to LocalSystem, which has this privilege by default.
Generally speaking, locking pages in memory is a good thing.
Best Answer
SQL Server is not guaranteed to allocate the min server memory. If the load on the server never needs the minimum SQL Server will work with less - BOL http://msdn.microsoft.com/en-us/library/ms178067.aspx
As long as your configurations allow for the use of all memory you have allocated (they do), SQL Server will use what it needs, and generally no more. Since you are running a VM I would make sure you allocate 10% for the OS, remove the minimum as it can cause performance problems and let it run. If your CPU is pegged at 100%, queries are slow, and SQL Server is not allocating all of its available memory then it would be time to further troubleshoot.