Is it possible for increasing the SQL Server max memory limit to degrade performance?

Question

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).

Answer 1

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.

Answer 2

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.