Saturday, May 12, 2012

SQL Server Memory Settings


It's hard to get a fix on what is the recommended memory settings for SQL Server. It seems to be one of those questions that if you ten DBAs what settings they should use you will get ten different answers. My take on things is pretty much in line with the suggestions from Brent Ozar and Glenn Berry - I've been setting up SQL Servers for years using similar settings to their suggestions without any real memory type issues. The only caveat I have is that as good as their suggestions are, each case is unique and the memory settings need to be monitored and adjusted accordingly. If you've got no idea what the memory settings should be then this is a good place to start followed up by some highly recommended reading at the links below.

Before going any further it's good to keep in mind what the SQL Server memory settings actually do. They control the target memory usage of the SQL Server Memory Manager. That's important because these aren't hard and fast values - SQL Server will try and grab the minimum memory level if it needs it, if it doesn't it may never use the minimum amount of ram allocated to it. Same goes for the maximum level - in fact if you have memory pressure from the operating system SQL Server may not be able to grab all of the memory you specify. Remember - these are target values, not hard and fast memory allocations.

The second point to remember is that this memory is for the SQL Server Memory Manager. The SQL Server Memory Manager consists of pools of memory allocated to different things like caching data or caching query plans. You can't control what happens internally, only the total target memory of this pool. Other processes may run outside of this memory pool such as SQL CLR. In short it's best to know what's happening on your system, what type of workloads are running on the server and keeping any eye on the system resources with any monitoring tools you may have. For more detail on the SQL Server Memory Manager check out this link from Microsoft: http://msdn.microsoft.com/en-us/library/ms190924.aspx.

The Minimum Server Memory Setting
As the name implies the minimum server memory is used to control the minimum amount of memory SQL Server will try and reserve for it's data cache. SQL Server isn't guaranteed to use all of this, it only uses this amount of memory if it has cause to. In practical terms it means that once you start your SQL Server don't expect this memory to be immediately grabbed by SQL Server, it will fill this memory with data cache as it accesses pages in the databases.

The default setting for this is zero and that's a bad number. If the server comes under memory pressure SQL Server will try and release memory back to the operating system. If the minimum memory setting is zero then SQL Server is free to release all of it's data cache back to the operating system. This means that SQL Server has just dumped all of it's data cache and at this point the SQL Server will respond very slowly. It will need to fetch all data for incoming queries from the disk and read it back into memory -  reading from disks is thousands of times slower than memory so this will slow your server down massively.

The golden rule here is to pick a number that is the minimum memory you're comfortable with SQL Server running with. One of the rules of thumb from Brent Ozar's site (www.brentozar.com) is to set the minimum memory to half of the physical memory installed on the server. It's a reasonable rule to follow however I'd strongly suggest that on any critical system that you test this out in a non production environment to test how your workload reacts.


The Maximum Server Memory Setting
The value of the maximum server memory setting is one of those annoying 'it depends' answers. The basic principle is to give SQL Server as much memory as you can whilst still leaving enough memory for the operating system to do what it has to do. The operating system will need memory for it's basic operating system functionality, to cache files that are being copied, for other applications running on the server (eg. antivirus), and for SQL CLR code. That's why this setting is a 'it depends' answer, the more you run outside of SQL Server, even large file copies the more memory  the operating system will require to do what it has to do. 

Glenn Berry has an excellent article at the link below which gives some guidelines on suggested maximum memory settings. 


In any case whatever setting you start with you should carefully monitor the free memory on your system. It's a balancing act but you'll want to leave some memory free. If you find that file copies or the operating system runs slow, you may want to look at decreasing the maximum server memory on SQL Server.


Lock Pages In Memory
Locking pages in memory is a setting which affects the behaviour of the server when it comes under memory pressure. When the setting is enabled and the server comes under memory pressure SQL Server will not release the memory it is using for caching back to the operating system.

This may sound like a good idea however there's mixed advice out there on whether to use the Lock Pages in Memory setting or not. The arguments against using this setting centre around the basic idea that Windows knows best how to manage the memory. That by locking the pages in memory the operating system cannot respond to memory pressure which in turn causes other problems on the server. The counter argument revolves around locking the memory assigned to SQL Server for SQL Server and configuring the min and max memory settings in such a way that there's enough memory left for the operating system to do what it has to do.

Personally I've been locking the pages in memory and it's worked well for me. I've started doing this for a long time before the anti lock pages in memory crowd gathered momentum. In fact I remember doing it because one of the first SQL 2005 boxes I deployed were dumping the buffer cache when the server came under pressure. Luckily it was a development box but still it caused enough problems that the developers were practically unable to work. Due to the changing nature of infrastructure and the wide use of virtual machines I'll be reviewing this setting on my servers in the near future.

MSDN Memory Settings Reference


No comments:

Post a Comment