8

What are the correct settings for min-memory and max-memory in this use case?

The server has 8GB ram, dual Intel Xeon processors, running Windows Server 2008 R2 / Sql Server 2008 Standard Edition. It's running several databases ranging from 30GB - 5GB in size.

Originally the memory usage was set to the default settings (min=0 max=2,147,483,647). On these settings most of the memory usage was taken up by sqlservr.exe and the server would eventually need to be restarted every day or two. It would run normally at first but within a day start to timeout on simple operations like looking up a record using the primary key.

I have changed min=4,096 and max=6,144. This results in only 1.4GB memory usage. However now all four cpus are running at 50-60% cpu usage constantly. Tasks are taking roughly 1/3rd longer to execute, although the server is much more stable.

5
  • Buy more RAM and leave the max to default. Jul 29, 2013 at 12:24
  • 4
    @RemusRusanu I assume you meant min to default!
    – Kin Shah
    Jul 29, 2013 at 13:03
  • We have very similar use cases and similar problems (OS having no mem due to SQL grabbing it all) when people forget out practices. Our practice is to use 0 as min and half available as max.
    – Paul
    Jul 29, 2013 at 14:09
  • @kin no, I mean max. I should be said leave both min and max at default values, but I never understood why would anyone ever change the min anyway... Jul 29, 2013 at 18:41
  • 1
    @RemusRusanu I assumed that it was a typo, as with the amount of internals knowledge you have + your excellent answers. I would though disagree with leaving Max memory as default.
    – Kin Shah
    Jul 29, 2013 at 18:48

1 Answer 1

10

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 :

3
  • 2
    You have the total and target comparison the wrong way around, but in any case there are better ways to determine if the server is under external memory pressure.
    – Paul White
    Jul 30, 2013 at 1:10
  • @paulwhite can you please clarify a bit more so I can improve my answer ? Thanks for looking into.
    – Kin Shah
    Jul 30, 2013 at 1:33
  • It's a stand-alone instance
    – sa555
    Jul 30, 2013 at 2:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.