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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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)
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?
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.
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,
              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,
                     'bigint') AS AvailableVirtualAddressSpace,
                     'bigint') AS AvailableExtendedVirtualAddressSpace,
    FROM    RingBuffer AS rb
            CROSS JOIN sys.dm_os_sys_info AS dosi
            CROSS APPLY rb.xRecord.nodes('Record') record (xr)
        DATEADD (ms, -1 * (rb2.ms_ticks - rb2.RecordTime), GETDATE()) AS NotificationTime,
    FROM RingBuffer2 rb2
        --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]
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.