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 :
I would suggest you to leave MIN Server memory to DEFAULT.
Min server memory controls the minimum amount of Physical memory that sql server will try to keep committed. When the SQL Server service starts, it does not acquire all the memory configured in Min Server Memory but instead starts with only the minimum required, growing as necessary. Once memory
usage has increased beyond the Min Server Memory setting, SQL Server won’t release any
memory below that amount.
Bob Dorr explains this settings as :
Min Server Memory
Use the min server memory setting with care. This is a floor to SQL Server. Once committed memory to reach the min server memory setting SQL Server won't release memory below the mark. If you set max server memory to 59GB and min server memory to 56GB, but the server needs to back SQL Server down to 53GB SQL Server won't drop below 56GB. When you combine this setting with locked pages in memory the memory can't be paged. This can lead to unwanted performance behaviors and allocation failures.
Searching the web -- internet has plenty of information for max server memory
This is because, this settings is least tuned (people just set it to default), instead max memory is what is generally tuned as it is the "ceiling" for the buffer pool. A good value for max memory will ensure that windows and other processes runing on the server will have enough physical memory to perform their work without forcing sql server to trim.
Best Answer
No this is not correct, min server memory has nothing to do with how fast SQL Server will consume it if set. Min server memory signifies the minimum amount of memory, if consumed, after which SQL Server will not release memory below this value under normal conditions if asked to do so. Once min server value is reached SQL Server will not trim its caches and release memory below this value.
Yes correct, just to add SQl Server in certain conditions can also consume memory greater than the value specified in max server memory. You should read Memory Management Guide
There is other way which forces SQL Server to reserve more memory during startup and this is called "large pages". This is enterprise edition features and allows SQL Server to quickly reserve some free memory during startup, the SQL Server service account should also have locked pages in memory privilege because allocation of large pages is done by VirtualAlloc() function of windows OS and OS should have more than 8 GB RAM, if these conditions are met automatically Large page allocation is enabled. Once enabled it will appear as below in errorlog
The normal page size of memory is 4 KB on X64 system but when large page is enabled the size will be 2 MB. Also note that LargePageSupport is enabled and used by the engine even if you don’t enable trace flag 834. But not much memory is used for this and buffer pool memory is not used unless trace flag 834 is enabled. See the blog shared.