By default, SQL Server’s max memory is 2147483647 – a heck of a lot more than you actually have.  Trivia time – that’s the max number for a signed 32-bit integer.  SQL Server will just keep using more and more memory until there’s none left on the system.

If the operating system has no memory available, it will start using the page file instead of RAM. Using the page file in place of memory will result in poor system performance – operations that should be fast and in memory will read and write to disk constantly.  You can learn more about low memory conditions in this Microsoft blog post.

In our Sysadmin’s Guide to Microsoft SQL Server Memory, we explain why SQL Server really does need as much memory as possible, and what it uses memory for.  However, that doesn’t mean we should leave SQL Server’s max memory set to unlimited.  If we starve the OS for memory, then Windows itself may not get the memory it needs.

We compare the ‘max server memory’ setting in sys.configurations to the amount of memory the server actually has (as seen in sys.dm_os_sys_memory).

We also check sys.dm_os_nodes to see if any NUMA nodes are reporting that memory is dangerously low, and can no longer create threads.

Just Don’t Make Things Worse

A lot of folks are tempted to set Min Memory to the same value as Max Memory to make sure SQL Server always has plenty of memory.

But,  per Microsoft:

If the same value is specified for both min server memory and max server memory, then once the memory allocated to the SQL Server Database Engine reaches that value, the SQL Server Database Engine stops dynamically freeing and acquiring memory for the buffer pool.

This can impact other caches and memory consuming operations.

Before You Fix It – Understand the Unit of Measure

Word to the wise– the max memory setting in SQL Server is set in megabytes, not gigabytes. So if you intend to tell SQL Server to use 20GB as it’s max memory setting, you’d set that to 20480 (the equivalent in megabytes). Misunderstanding this can lead to the SQL Server not allocating enough memory to properly start up.

Good News, Standard Edition May Use More Memory Than You Think

Starting in SQL Server 2012, the 64GB memory limit for Standard Edition applies only to data cache, and other parts of SQL Server can use memory above that. Standard Edition’s data cache limit goes up to 128GB in SQL Server 2014. Read more about it here.

To Fix the Problem

1) Decide what to set your max server memory (MB) to. Our simple “starter” rule of thumb is to leave 4GB or 10% of total memory free, whichever is LARGER on your instance to start with, and adjust this as needed.

2) Execute the change. We’ve got detailed steps on how execute your change below. This does not require restarting your SQL Server instance, but it will cause execution plans to recompile.


How to Set Max Server Memory (MB) in SQL Server

Choose your own adventure: use the GUI or TSQL.

Option 1: Use THE GUI

2015-04-09_12-23-09

Right click!

Once you get to the properties…

2015-04-09_12-26-42

This is 12GB. Only use this number if you are also on a laptop.

Remember that 1GB is 1024MB, so calculate accordingly!

Option 2: Set Max Server Memory with T-SQL

Only run these commands if you’re comfortable with sp_configure and have it set to let you access advanced options.

GBMBRecommended SettingCommand
161638412288EXEC sys.sp_configure ‘max server memory (MB)’, ‘12288’; RECONFIGURE;
323276829491EXEC sys.sp_configure ‘max server memory (MB)’, ‘29491’; RECONFIGURE;
646553658982EXEC sys.sp_configure ‘max server memory (MB)’, ‘58982’; RECONFIGURE;
128131072117964EXEC sys.sp_configure ‘max server memory (MB)’, ‘117964’; RECONFIGURE;
256262144235929EXEC sys.sp_configure ‘max server memory (MB)’, ‘235929’; RECONFIGURE;
512524288471859EXEC sys.sp_configure ‘max server memory (MB)’, ‘471859’; RECONFIGURE;
10241048576943718EXEC sys.sp_configure ‘max server memory (MB)’, ‘943718’; RECONFIGURE;
204820971521887436EXEC sys.sp_configure ‘max server memory (MB)’, ‘1887436’; RECONFIGURE;
409641943043774873EXEC sys.sp_configure’max server memory (MB)’, ‘3774873’; RECONFIGURE;

 

These numbers make a few assumptions:

  • You’re only running one SQL Server
  • You’re not running SSIS, SSRS, SSAS, etc.
  • You’re not using it to run any other applications on the server

We recommend isolating SQL workloads as much as possible. If you have to consider any of the listed items, memory settings become more complicated.

After this change, your server should be less vulnerable to paging to disk.  If the system was already paging, then you should see increased performance.  To double-check if you’re paging to disk, check Task Manager, Performance tab, and look at the Free Memory metric for Windows 2008.  If it’s under 200, you’re in danger of swapping to disk.

4 Comments.

  • I’m still digesting the truckload of great information on your blog, Brent. First sp_Blitz run came up with about 170 rows (yes, I’m a developer). Little nitpick: 2147483647 is the max number for an SIGNED 32-bit integer. Anyway, keep up the great work!

  • Your effort and supports from others on making this tool is really appreciated. Thanks so much and keep up a good work. =)

  • Alberto Castillo
    May 31, 2012 7:32 pm

    Since i began in the DBA World i have seen your posts, webcasts, etc, Thanks for share your knowledge with others. “Thanks God and to you for your blog”

Menu
{"cart_token":"","hash":"","cart_data":""}