What is the proper Maximum and Minimum Server Memory?

We just bumped RAM from 16 GB to 48 GB on our physical SQL 2008 server. Do I need to change any Server memory options as the result? What is the proper number?
Currently we have:
Minimum server memory = 2048 mb
Maximum server memory = 12288 mb
Index creation memory = 0
Minimum memory per query = 1024 kb

Please help.
CastlewoodAsked:
Who is Participating?
 
Michael FowlerSolutions ConsultantCommented:
The default setting for min server memory is 0, and the default setting for max server memory is 2147483647 MB. By default, SQL Server can change its memory requirements dynamically based on available system resources.

Allowing SQL Server to use memory dynamically is recommended; however, you can set the memory options manually and restrict the amount of memory that SQL Server can access.

https://msdn.microsoft.com/en-us/library/ms178067.aspx
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
It's a64bit architecture?
It's a dedicated server for SQL Server? When I mean dedicated, I mean only the engine is installed. No SSAS, SSIS or SSRS.

If you answered yes to both questions, then leave at least 4GB for the OS. I would also increase the MIN memory since 2GB is very low for today standards:
Minimum server memory = 8192 mb
Maximum server memory = 45056mb (you can reduce the MAX value if you see that OS is requiring more memory)
0
 
CastlewoodAuthor Commented:
it is a x64, pretty much dedicated.
"if you see that OS is requiring more memory " How/where to tell the number of memory used by OS?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
How/where to tell the number of memory used by OS?
OS will use the available memory. You can check for non-SQL Server processes and see how much memory they're using.
You can also see if SQL Server reaches the 44GB configured for maximum memory. If stays far from that value you can reduce it to 40GB and let 8GB to OS.
0
 
Scott PletcherSenior DBACommented:
First, check to see if there is any memory pressure on the instance now.  I'd say 99% there is, but it can't hurt to check.  Then bump up the RAM to, say, 16GB.  Wait a while, and check again.  If there is still pressure, but it to 20GB, wait a while, and so on.

You don't want to just willy nilly allocate all available RAM to SQL Server.  In fact, I'd stop at 32GB unless memory pressure is still noticeably present, since you'll want a little memory in reserve.

Assuming you're at least at SQL 2008, you can use this code to check for memory pressure:


/*

IndicatorsProcess and IndicatorsSystem::
Value Meaning
1     High Physical Memory
2     Low Physical Memory
4     Low Virtual Memory

*/

WITH    RingBuffer
          AS (SELECT    CAST(dorb.record AS XML) AS xRecord,
                        dorb.TIMESTAMP
              FROM      sys.dm_os_ring_buffers AS dorb
              WHERE     dorb.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
             ),
        RingBuffer2 AS (
    SELECT  xr.value('(ResourceMonitor/Notification)[1]', 'varchar(75)') AS RmNotification,
            xr.value('(ResourceMonitor/IndicatorsProcess)[1]', 'tinyint') AS IndicatorsProcess,
            xr.value('(ResourceMonitor/IndicatorsSystem)[1]', 'tinyint') AS IndicatorsSystem,
            xr.value('(//Record/ResourceMonitor/Indicators)[1]', 'bigint') AS [Indicators_SQL2005],
            xr.value('(//Record/@time)[1]', 'bigint') AS [RecordTime],
            xr.value('(MemoryNode/TargetMemory)[1]', 'bigint') AS TargetMemory,
            xr.value('(MemoryNode/ReserveMemory)[1]', 'bigint') AS ReserveMemory,
            xr.value('(MemoryNode/CommittedMemory)[1]', 'bigint') AS CommitedMemory,
            xr.value('(MemoryNode/SharedMemory)[1]', 'bigint') AS SharedMemory,
            xr.value('(MemoryNode/PagesMemory)[1]', 'bigint') AS PagesMemory,
            xr.value('(MemoryRecord/MemoryUtilization)[1]', 'bigint') AS MemoryUtilization,
            xr.value('(MemoryRecord/TotalPhysicalMemory)[1]', 'bigint') AS TotalPhysicalMemory,
            xr.value('(MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS AvailablePhysicalMemory,
            xr.value('(MemoryRecord/TotalPageFile)[1]', 'bigint') AS TotalPageFile,
            xr.value('(MemoryRecord/AvailablePageFile)[1]', 'bigint') AS AvailablePageFile,
            xr.value('(MemoryRecord/TotalVirtualAddressSpace)[1]', 'bigint') AS TotalVirtualAddressSpace,
            xr.value('(MemoryRecord/AvailableVirtualAddressSpace)[1]',
                     'bigint') AS AvailableVirtualAddressSpace,
            xr.value('(MemoryRecord/AvailableExtendedVirtualAddressSpace)[1]',
                     'bigint') AS AvailableExtendedVirtualAddressSpace,
            dosi.ms_ticks
    FROM    RingBuffer AS rb
            CROSS JOIN sys.dm_os_sys_info AS dosi
            CROSS APPLY rb.xRecord.nodes('Record') record (xr)
        )
    SELECT
        DATEADD (ms, -1 * (rb2.ms_ticks - rb2.RecordTime), GETDATE()) AS NotificationTime,
        rb2.*
    FROM RingBuffer2 rb2
    --WHERE
        --RmNotification LIKE '%MEM%_LOW%' AND (IndicatorsProcess > 0 OR IndicatorsSystem > 0)
    --WHERE --Indicator Values: 1=high(ok) phys mem; 2=low(potential issue of not enough ram) phys mem;4=low virt mem.
        --( IndicatorsSystem >= 2 OR Indicators_SQL2005 >=2 )
    --ORDER BY [NotificationTime]
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.