Using large amounts of memory can result in an inefficient plan in SQL Server

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.

One of the factors that impacts the execution plan generated for a query is the amount of memory that is available for SQL Server. In most cases SQL Server generates the most optimal plan based on this value, but occasionally it may generate an inefficient plan for a specific query when you configure a large value for max server memory, thus resulting in a slow-running query.

You can workaround the problem by using trace flag T2335 as a startup parameter.This will cause SQL Server to generate a plan that is more conservative in memory consumption when executing the query. It does not limit how much memory SQL Server can use. The memory configured for SQL Server will still be used by data cache, query execution and other consumers. Please ensure that you thoroughly test this option.