1

I have a general question regrading setting up the MAX SQL memory settings-

For our current CPU with below specs, server team bumped up OS memory from 400GB to 1TB:

  • Intel® Xeon® Processor E5-4640 v2 - It's a 4 socket 10 core per processors with HT enabled so total of 80 logical processors 4 NUMA nodes

Looking at the specs on intel site here max memory supported seems to be 768.

So what does that exactly mean in terms of SQL space? even OS has 1 TB should one be setting MAX memory settings based on 1 TB or 768 GB? We can see issues from SQL when trying to bump up the MAX memory settings lets say over 850 and when we lower it back to 400 goes back to normal.

Issues when bump up SQL MAX memory - App time outs for queries running over 3 secs based on time out settings.

Tested this couple of times and looks to be same. Need to understand why

Appreciate your inputs on this.

3
  • The spec for the chip is the per-socket (NUMA node) memory. So follow normal guidance. Leave the default, or set Max Server Memory to reserve memory for OS and other processes. learn.microsoft.com/en-us/sql/database-engine/configure-windows/… Apr 17, 2020 at 18:01
  • what other process aside from the Operating System itself are running on the SQL Server? For instance, is there SQL Server Analysis Services, or SQL Server Reporting Services running? Or is there some other service that requires a lot of memory such as an Anti-Virus scanner, or some third-party service? Is this SQL Server running in a VM or on physical hardware? Also, you should really re-think having your app timeouts set to 3 seconds, that is a really short period of time that could easily be running out because of locking.
    – Hannah Vernon
    Apr 21, 2020 at 12:22
  • You should also determine what the system is waiting on by looking at wait stats over the time where you see those timeouts happening.
    – Hannah Vernon
    Apr 21, 2020 at 12:23

1 Answer 1

2

The max memory setting is how much RAM SQL will use. It will usually not use more then the max memory set. (Following Shanky's comment, see below for the exception to that rule): Quoting from Memory Management Architecture Guide

enter image description here

  • Memory allocation requests made directly to Windows: These include Windows heap usage and direct virtual allocations made by modules that are loaded into the SQL Server process. Examples of such memory allocation requests include allocations from extended stored procedure DLLs, objects that are created by using Automation procedures (sp_OA calls), and allocations from linked server providers.

Now if your hardware has 1 TB but only 768Gb are recognize in the OS, setting up the max memory to higher then 768Gb will allow SQL to use all available RAM (all the RAM detected by the OS) and this could cause potential issue with the OS as it will have to do memory pressure on SQL to claim it back.

Set the SQL MaxMemory setting depending on how much RAM is seen by the OS and it should be all good. (Leaving at least 4 Gb for the OS)

P.s. Do not set min memory setting the the same value as the max memory. Set it lower as SQL will not release RAM under the min value when there is memory pressure and this could lead to an OS crash.

0

Your Answer

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

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