Link to home
Start Free TrialLog in
Avatar of Castlewood
Castlewood

asked on

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.
ASKER CERTIFIED SOLUTION
Avatar of Michael Fowler
Michael Fowler
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Vitor Montalvão
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)
Avatar of Castlewood
Castlewood

ASKER

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?
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.
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]