As far as I can tell, raising the SQL Server max memory limit has no drawbacks, provided that sufficient memory is left for the OS and other applications.

Assuming this is the case, can there be any detrimental effects to raising the max memory limit?

The only thing I have seen is this KB article from Microsoft, which states,

When you configure max server memory sp_configure option in Microsoft SQL Server to a large value, you may notice that a particular query may run slowly. But if you lower the value for this option, the same query may run much faster.

but it only applies to old versions of SQL Server (<=2008).

  • Why are you wondering? Are you thinking you need more memory?
    – Hannah Vernon
    Dec 1, 2014 at 17:24
  • You might be interested in reading up difference in execution plans on UAT and PROD server and @PaulWhite' s excellent explanation.
    – Kin Shah
    Dec 1, 2014 at 17:29
  • This is more a general question than anything particular. If unused memory is available that can be given to SQL, are there any potential downsides?
    – Aaroninus
    Dec 1, 2014 at 17:47
  • First tell me what is version and edition of your SQL Server. memory management code changed form 2012 onwards.
    – Shanky
    Dec 1, 2014 at 18:35
  • I am using SQL Server 2012 and 2014.
    – Aaroninus
    Dec 1, 2014 at 18:54

2 Answers 2


Before I start, I am proponent of FIXING MAX SERVER MEMORY always

1. I will first address SQL Server versions before SQL Server 2012. That means if you are using SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2 (no I will not include SQL Server 2000)

If you are using SQL Server version before 2012 and your operating system is Windows Server 2008 R2 updated with the latest Service Pack and of course the system is a dedicated system for SQL Server database engine, then you might not need to set max server memory, or you can allow the SQL Server buffer pool to grow dynamically. Please see this link for a list of documented working set trimming issues with various Windows Server editions.

If you are using SQL Server 2005 on windows server 2003, which is not patched to the latest Service Pack, you might be a victim of SQL Server trimming by windows. This Link describes the bug.

There are a lot of third party drivers which SQL Server does not support that might be loaded into SQL Server address space. If you are using linked server query, this might cause memory leaks.

Considering all these bugs and fixes, Microsoft recommends to cap the SQL Server (before 2012) buffer pool memory to the optimum value.

A very classic reason why you should not allow SQL Server to grow memory dynamically:

  1. You my have multiple instances. What if some rogue query starts running on one instance and starts consuming too much memory? All other applications/instances would suffer because of this, which I am sure you would not want.

  2. Consider a two node multi-instance cluster which undergoes a failover and now both instances are on the same node and one starts taking more memory than other. The other would have to suffer and the cluster instance would become extremely slow

  3. If you don't fix max server memory you can be a victim of working set trimming and the OS becoming unresponsive.

  4. Consider a scenario where you are running SQL Server Enterprise, with SQL Server running with the Local System account, or with an account which has the Locked Pages in Memory privilege. You are doomed if some heavy query runs on the system when is already under load. It would try to consume all memory, and since due to LPIM memory cannot be paged out, SQL Server would try to trim as SQLOS will ask it to do. But if memory pressure is grave, it might take some time for SQL Server to react, and in that case OS processes might get paged out and eventually crash, leading to an OS reboot.

2. Starting from SQL Server 2012

Memory code has changed so that a single memory manager is used to allocate memory to both processes which require memory pages less than 8 KB and to processes which requires more than 8 KB. Max server memory controls SQL Server memory allocation, including the buffer pool, compile memory, all caches, qe memory grants, lock manager memory, and CLR memory (basically any clerk as found in dm_os_memory_clerks). Memory for thread stacks, heaps, linked server providers other than SQL Server, or any memory allocated by a non-SQL Server DLL is not controlled by max server memory.

So again, as I said all processes loaded in SQL Server address space which are not monitored by memory clerks would still take memory outside max server memory. So again you should as a best practice set the optimum value for max server memory.


Generally speaking, no, there is no detrimental effect from maxing out memory once the OS and other applications have been accounted for. This is exemplified through the default settings of SQL Server:

The default setting for min server memory is 0, and the default setting for max server memory is 2147483647 MB.

SQL Server Memory Configurations (MSDN)

While this setting allows dynamic re-sizing of the memory SQL Server acquires, it also goes to show that there isn't a number that is "too much" (aside from OS/app/version considerations). SQL Server will only use the memory it requires, even though it may reserve more based on the min/max settings. Giving it access to as much as possible simply gives it the room it may need to operate efficiently.

As Shanky mentioned, in 2012 and onwards, SQL also uses this setting for thread stacks, CLR allocations, and more which may have other effects in specific scenarios when it comes to memory optimization.

  • 1
    If other non-SQL Server processes get added to the server, you will want to review your memory settings occasionally.
    – RLF
    Dec 1, 2014 at 18:07
  • I would not directly say NO considering the fact that from 2012 onwards max server memory account for much more things than just buffer pool
    – Shanky
    Dec 1, 2014 at 18:38
  • @Shanky True - but as the asker wanted a "general" answer, I can't see anything that would be negative for general use cases. I've updated my answer to convey this better. Dec 1, 2014 at 18:46
  • IMO you should have asked him about what version of SQL Server you are talking about.Because before 2012 increasing buffer pool indefinitely on system which uses monitoring tools, Loads third party drivers and uses Mcafee is not advisable. Nothing in SQL Server should be increased indefinately
    – Shanky
    Dec 1, 2014 at 18:52

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.